pg_statsinfo -- monitor statistics and activities of PostgreSQL server
pg_statsinfo monitors an instance of PostgreSQL server and gather the statistics and activities of the server as snapshots. The snapshots will be stored in the same or another repository database. It also parses PostgreSQL's CSV server log filesm and extracts performance logs from them. Error messages are routed into textlog and syslog according to the message levels.
pg_statsinfo can be easily installed and maintained because it starts or stops automatically when PostgreSQL server starts or stops. Also etting parameters are integrated with setting files of the server. Statistics counters from pg_statsinfo can be displayed with pg_reporter as graphical reports.
pg_statsinfo works as the following image. It monitors one PostgreSQL instance (server).
pg_statsinfo gathers statistics periodically and stores them as snapshots into a repository database. The repository can be in the same database with the monitored instance or in another instance. Also, one repository can store snapshots from multiple monitored instances.
Snapshot holds the following statistics information:
Size of a snapshot depends on the numbers of objects in DB. There are about 600 - 800kB per snapshot. In case of pg_statsinfo default settings, snapshots for each monitored DB requires 180 - 230MB per day.
Note that pg_statsinfo doesn't delete old snapshots. Please delete them manually.
Note that pg_statsinfo doesn't delete old server log files. Please delete them manually.
A stardard usage of pg_statsinfo is explained in the section.
You can use pgxs to bulld the module from the source codes. You don't have to run sql files manually because pg_statsinfo installs them automatically for both monitored instances and repositories.
$ cd pg_statsinfo $ make USE_PGXS=1 $ su $ make USE_PGXS=1 install
Note: RPMs of PostgreSQL9.0 change the install directory path. Therefore, the RPM of pg_statsinfo also changes install paths. For example, executable files are installed to "/usr/bin" previously. But from PostgreSQL9.0, these files are installed to "/usr/pgsql-9.0/bin".
Second, allow the user who run the PostgreSQL server to login the server without any passwords from localhost. The recommended method is "ident" authentication here. Add the following line to pg_hba.conf when you use "OS-user = DB-superuser = postgres", that is the most common case. Note that you will need to add it in pg_hba.conf in front of other methods. The ident authentication method with TYPE=local will be convenient on UNIX.
# TYPE DATABASE USER CIDR-ADDRESS METHOD [for UNIX] local all postgres ident
On the other hand, the method is not available on Windows. So use the trust authentication instead. If you mind the trust authentication for security reasons, please use other authentication methods and .pgpass file to connect all databases without password prompt.
# TYPE DATABASE USER CIDR-ADDRESS METHOD [for Windows] host all postgres 127.0.0.1/32 trust
Finally, setup postgresql.conf with the following setting when the monitored PostgreSQL instance stops. In this settings, the repository will be created in 'postgres' database on the same server with monitored database. See also "Configuration File" for other setting parameters.
log_checkpoints = on # record checkpoint activities log_autovacuum_min_duration = 0 # record autovacuum activities shared_preload_libraries = 'pg_statsinfo' # preload the library
In addition, contrib/pg_statsinfo.sql and contrib/pg_statsrepo.sql are installed automactically. So, you don't need to install them manually.
You only have to start PostgreSQL server when you start pg_statsinfo. pg_statsinfo will be kicked to start by the server; It cannot run in standalone.
$ pg_ctl start [OPTIONS]
Also pg_statsinfo automatically stops when PostgreSQL server shutdowns. Non-smart shutdown with 'pg_ctl stop -m fast|immediate' might cause some error messages, but you can ignore those errors.
$ pg_ctl stop -m smart [OPTIONS]
When you uninstall pg_statsinfo, you have to restart PostgreSQL server after removing 'pg_statsinfo' from
shared_preload_libraries and all of
pg_statsinfo.* parameters in postgresql.conf.
After that, drop all objects used by pg_statsinfo installed in the monitored instance. Run $PGSHARE/contrib/uninstall_pg_statsinfo.sql for 'postgres' database in the monitored instance.
$ psql -d postgres -f $PGSHARE/contrib/uninstall_pg_statsinfo.sql
If you also want to delete snapshots, run $PGSHARE/contrib/uninstall_pg_statsrepo.sql to the repository. Be careful to run the script file because it will delete all of snapshots including other server's ones when the repository was shared by multiple monitored instances.
$ psql -d <repository> -f $PGSHARE/contrib/uninstall_pg_statsrepo.sql
Configuration parameters and usages are explained in the section.
Also, if you use PostgreSQL 8.4 or later, pg_stat_statements is installed in 'postgres' database in the monitored instance, pg_statsinfo gathers the query statistics and add them into snapshot. If you will use it, run the following command only once at the fist server start.
$ psql -d postgres -f $PGSHARE/contrib/pg_stat_statements.sql
pg_statsinfo uses postgresql.conf in the monitored instance for a configuration file. Settings in the configuration file are read at the instance's startup and reloads with 'pg_ctl reload'.
You must setup the following parameters before using pg_statsinfo. Some of the paramters cannot be changed online and require server's restart.
|shared_preload_libraries||'pg_statsinfo'||Preloaded libraries. If you also use pg_stat_statements, append the module name with a comma like 'pg_statsinfo, pg_stat_statements'.|
|track_counts||on||Enables collection of statistics on database activity.|
|track_activities||on||Enables the collection of information on the currently executing command of each session.|
|log_min_messages||debug5 ∼ log||A message level threshoulds for server logs. The value must be more verbose than 'log', pg_statsinfo.syslog_min_messages, and pg_statsinfo.textlog_min_messages.|
|log_timezone||unknown, gmt, utc||Only those values are supported.|
|log_destination||must have 'csvlog', and can have 'syslog' or 'eventlog' optionally||pg_statsinfo always adds 'csvlog' and removes 'stderr' during startup.|
|logging_collector||on||pg_statsinfo always set it to this value during startup.|
There some optional parameters that affect pg_statsinfo's activities. They can be changed online when reloaded with pg_ctl reload.
If you use PostgreSQL 8.3, read 'pg_statsinfo.*' as 'statsinfo.*'.
|track_functions||none||Enables tracking of function call counts and time used. Set to 'pl' or 'all' if you want pg_statsinfo to gather the statistics.|
|log_checkpoints||off||Causes checkpoints to be logged in the server log. 'on' is recommended.|
|log_autovacuum_min_duration||-1||Causes autovacuums to be logged in the server log. 0 ∼ 1min are recommended.|
|log_directory||'pg_log'||A directory for csvlog and textlog files.|
|log_filename||'postgresql-%Y-%m-%d_%H%M%S.log'||A file format for csvlog and textlog files. The value should be contain %Y, %m, %d, %H, %M, and %S in this order.|
|log_rotation_age||1d||Rotates logs in the specified duration.|
|log_rotation_size||10MB||Rotates logs in the specified file size. The size will be compared with csvlogs.|
|custom_variable_classes||'pg_statsinfo'||Required when you set parameters that starts with "pg_statsinfo.".|
|pg_statsinfo.textlog_min_messages||warning||Minimum message levels for textlog (*1).|
|pg_statsinfo.syslog_min_messages||disable||Minimum message levels for syslog (*1). Event log is used on Windows instead of syslog.|
|pg_statsinfo.textlog_filename||'postgresql.log'||The latest textlog filename. Should not be empty.|
|pg_statsinfo.textlog_line_prefix||'%t %p '||A printf-style string that is output at the beginning of each textlog line. (*2)|
|pg_statsinfo.syslog_line_prefix||'%t %p '||A printf-style string that is output at the beginning of each syslog line. (*2) Note that timestamp and process IDs appended by syslog is replaced to values of pg_statsinfo daemon. So, you need to add %t and %p to record the original values.|
|pg_statsinfo.textlog_permission||0600||Permission setting for textlog files.|
|pg_statsinfo.sampling_interval||5s||sampling interval (*3)|
|pg_statsinfo.snapshot_interval||5min||snapshot interval (*3)|
|pg_statsinfo.excluded_dbnames||'template0, template1'||Unmonitored database names.|
|pg_statsinfo.repository_server||'dbname=postgres'||Connection string to connect the repository (*4). You need to avoid password input.|
Operations manually executed by user is explained in the section.
All of the server log files inclduing csvlog and textlog are not deleted automatically. You need to delete old log files periodically.
All of the snapshots are not deleted automatically. You need to delete old snapshots rows periodically.
You can use
statsrepo.del_snapshot(timestamptz) function to delete snapshots in the repository.
Snapshots older than the specified timestamp will be deleted.
Example: delete snapshots older than 2010-02-01 07:00:00.
$ psql -d <repository> -c "SELECT statsrepo.del_snapshot('2010-02-01 07:00:00'::timestamptz);"
If you wanto take a snapshot in arbitrary time, you can execute
statsinfo.snapshot(text DEFAULT NULL) function on 'postgres' database in the monitored instance.
The function takes a comment for the snapshot as an argument.
Example: Take a snapshot in manual with a comment 'comment'.
$ psql -d postgres -c "SELECT statsinfo.snapshot('comment')"
If you want to reset configuration parameters online, just modify postgresql.conf and send a reload signal with pg_ctl.
$ pg_ctl reload
if you want to rotate logs in arbitrary time, execute the following function on the monitored instance.
$ psql -d postgres -c "SELECT pg_rotate_logfile()"
PostgreSQL instance will still run even if pg_statsinfo daemon crashes. To restart pg_statsinfo daemon, you need to restart PostgreSQL instance or execute the following command on the monitored instance.
$ psql -d postgres -c "SELECT statsinfo.restart()"
Note that rotated logs during pg_statsinfo stops are not parsed even after daemon's restart. Please read csvlog files directly for messages during no daemons.
There are still some restrictions and limitations in pg_statsinfo.
More advanced usages and internal structures are explained in the section.
"statsrepo.alert()", an alert function, will be called in the repository just after a new snapshot is stored.
The prototype of the function is
CREATE FUNCTION statsrepo.alert(snap_id bigint) RETURNS SETOF text.
Alert functions are not installed in default. More practice sample function ($PGSHARE/contrib/pg_statsrepo_alert.sql) is isnatalled.
The alert function have to be register on repository DB. An alert function will calculate an delta of snapshots, and raise alearts as a set of text when abnormal activities are found. Those messages are recorded in server logs with ALERT message levels. The alert function is useful when you want to know abnormal activities as soon as possible. You can view the historical activities as graphical reports with pg_reporter, but it is not suit for realtime monitoring.
statsrepo.alert (snap_id bigint) : setof text
Here is a basic sample alert function: alert-sample.sql. The pg_statsrepo_alert.sql has more advanced check features. You can configure following alert-threashold to describe postgresql.conf on repository instance.
|variables||default||criterion of alert|
|statsrepo.alert_garbage_size||20GB||Garbage size in whole instance (byte)|
|statsrepo.alert_garbage_percent||30||Garbage ratio in whole instance (%)|
|statsrepo.alert_garbage_percent_table||30||Garbage ratio in each tables (%)|
|statsrepo.alert_response_average||10s||Query average response (sec)|
|statsrepo.alert_response_worst||60s||Query worst response (sec)|
If you configure above parameters, you also have to add 'statsrepo' to custom_variable_classes.
custom_variable_classes = 'statsrepo' statsrepo.alert_transactions_per_second = 5000
pg_statsinfo consists of a library and a daemon. The library is loaded by PostgreSQL server, and the daemon is run as a database client. Since the daemon is executed from a hook function in the library, users don't have to execute the daemon explicitly. See also "pg_statsinfo: internal" for details.