Thursday, September 29, 2011

Postgresql- the pg_stat_activity table | incrementing the number of checkpoint_segments | the pg_log directory | Disabling the use of an index

postgresql- the pg_stat_activity table

  • Today I stumbled upon a table I don't know how could I live without.


http://postgresql.1045698.n5.nabble.com/pg-stat-activity-howto-td2093180.html

This goodie gives you information about who's connected through what ip, which process id,  which query is being executed and whether such query is waiting for a lock to be released.  Extremely important info!

 Incrementing the number of checkpoint_segments for heavy write operations
When the transaction log no longer has available space, the installation of a so-called control checkpoint takes place. This serves as an order for the system to remove all the unprocessed material to a disk, so the log can become available for use once again. Additionally, the control checkpoint may be installed not upon occasion, but after a certain period of time, typically standing at 5 min. When there is massive logging to the database, the transaction log can become overloaded too rapidly. This, in turn, will lead to a substantial deceleration of the aforementioned material removal to a disk.

Establishing the installation frequency of control checkpoints:

The installation of control checkpoints must take place every couple of minutes. If the installation takes place too frequently (for instance, every minute), the productivity of the system would notably decrease. To establish the current frequency, at which the system installs control checkpoints, you can use the log's analysis of timestamps. First, however, make sure that you've allowed access to the log. Check follow options in the file postgresql.conf:

http://morozovsky.blogspot.com/2007/11/postgresql-checkpoints.html

http://www.depesz.com/index.php/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-checkpoint_timeout-checkpoint_warning/

The pg_log directory

Another thing worth mentioning is having a look one in while to the pg_log directory (within the postgresql cluster directory).

http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html

Just for the sake of it I decided to log everything in our production database, but now I thing it's likely to become bloated and cause storage havoc. I need to set that option back to how it was.

Disabling the use of an index

set the idisvalid property to false in the pg_index table. The index will be updated but won't be use for queries. Why would you desire such behavior? To profile query performance maybe... In the case of bulk insertions, it might be better to drop them altogether.

No comments:

Post a Comment