What is pg_statsinfo ?
pg_statsinfo -- monitor statistics and activities of PostgreSQL server. It is easy to use pg_statsinfo that pg_statsinfo is working with PostgreSQL start or stop.
Description
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).
Statistics Snapshot
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.
- Snapshots are gathered periodically (default: every 5 minites) or by manual.
- An event handler function is called after every snapshots taken. The function can be defined by users. A new snapshot can be compared with previous snapshots and raise alert messages in the function.
Snapshot holds the following statistics information:
- All of the information collected by the statistics collector. For example, numbers of INSERT/UPDATE/DELETE and buffer access counters.
- Disk usages of each tablespace, pg_xlog, and archive log directory.
- Long transactions and their query strings.
- Process status in running, waiting for locks, idle in transaction, and idle.
- Duration and buffer accesses in checkpoints and autovacuums.
- SQLs and functions that take long time.
- PostgreSQL configuration parameters.
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.
Server Log Filter
- Split server log lines according to the message levels. You can set different message threshold for csvlog, textlog, and syslog.
- Fix filename of the latest textlog. The default is $PGDATA/pg_log/postgresql.log. You can always read the latestlog in the same filename and setup of log monitoring tools would be easier.
- Set arbitrary access permission for each textlog files. You can control the default permission not only 600.
Note that pg_statsinfo doesn't delete old server log files. Please delete them manually.
Other Features
- Start or stop with PostgreSQL. pg_statsinfo daemon can automatically start or stop with PostgreSQL instance. No additional operations are required.
- Reload parameters online. Configuration parameters like snapshot interval can be modified with "pg_ctl reload".
A stardard usage of pg_statsinfo is explained in the section.
Install
Requirement
- PostgreSQL versions
- PostgreSQL 8.3, 8.4, 9.0
- OS
- RHEL 5.3, CentOS 5.3, Windows XP
- Required connections
- 1 connection for the monitored instance / 1 connections for repository.
2 connections required if monitored instance and repository are in the same server.
Linux
RPM
This example is How to install PostgreSQL8.4 x86_64 rpm.$ su # rpm -ivh pg_statsinfo-2.0.1-1/pg84.rhel5.x86_64.rpm
source
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 $ tar xzvf pg_statsinfo.tar.gz $ 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".
Configuration (Linux and Windows)
This section is configuration of pg_statsinfo.configuration of postgresql.conf
First, you check the observation postgresql is stoped. This configuration method is that snapshot repository is same instance in a PostgreSQL database. If you want to config other setting, you read this section.#minimam configuration shared_preload_libraries = 'pg_statsinfo' # preload pg_statsinfo libraries log_destination = 'csvlog' # pg_statsinfo needs CSV log setting logging_collector = 'on' # stderr is logged by logging_collector log_directory = 'pg_log' # set the log directory log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # pg_statsinfo need this log_filename setting track_counts = 'on' # stats activity of database track_activities = 'on' # infomation of executed command in session
#recommended configuration
custom_variable_classes = 'pg_statsinfo'
pg_statsinfo.snapshot_interval = 30min # set snapshot interval
pg_statsinfo.enable_maintenance = 'on' # enable maintenance mode('on' or 'off')
pg_statsinfo.maintenance_time = '00:00:00' # Delete old snapshots every day in this time.
pg_statsinfo.repository_keepday = 7 # keep old snapshots in this period in maintenance.
log_min_messages = 'log'
pg_statsinfo.syslog_min_messages = 'error'
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' # This format is same as syslog's format.
pg_statsinfo.syslog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' # This format is same as syslog's format.
track_functions = 'all'
log_checkpoints = on
log_autovacuum_min_duration = 0
configration of pg_hba.conf
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.
configuration of input query statistic
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
That's all. Install is finished.
usage
This section is explained maintenance and configuration in pg_statsinfo.
Start or Stop
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]
get or maintenance snapshot
automatic snapshot
Pg_statsinfo gets snapshot every interval time. Interval time configuration is in the following.
example: snapshot interval time is 30min
pg_statsinfo.snapshot_interval = 30min
manual snapshot
If you want to get snapshot manualy, you use SQL statsinfo.snapshot function(text DEFAULT NULL).
$ psql -d postgres -c "SELECT statsinfo.snapshot('comment')"
automatic maintenance
If you want to maintain snapshot repository, you configure automatic maintenance mode in postgresql.conf. Auto maintenance is executed every day. Enable auto maintenance mode is in the following.
pg_statsinfo.enable_maintenance = 'on' pg_statsinfo.maintenance_time = '00:00:00' pg_statsinfo.repository_keepday = 7
Caution! : If you don't use automatic maintenance mode, repository size will be very large. Plese maintenance repository manualy in every term.
manual maintenance
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);"
Output of Logfile
pg_statsinfo has log filtering function. Following section is explain about output of logfile in pg_statsinfo.
postgresql.log
This log is latest log output with filltered by pg_statsinfo in PostgreSQL. If you don't set filltering level (pg_statsinfo.textlog_min_messages), logfile output is more than warning level log. And, if you set pg_statsinfo.textlog_filename in postgresql.conf, logfile name is this parameter.
postgresql-2011-12-01_000000.log
This log is lotated old 'postgresql.log' which is over following. When you start PostgreSQL, you can see this log with filesize 0. This is normal and not problem.
postgresql-2011-12-01_000000.csv
This log is PostgreSQL output original csv log. Pg_statsinfo is filltered the original csv log and output the filltering log. But this log is not filltered at all!
Configuration File
Configuration parameters and usages are explained in the section.
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.
| Name | Setting | Description |
|---|---|---|
| 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.*'.
| Name | Default Setting | Description |
|---|---|---|
| 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. |
| syslog_facility | 'LOCAL0' | syslog facility |
| syslog_ident | 'postgres' | syslog indent |
| 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. |
- *1 : Message Levels
- The following values are available for message levels. The specified level and more verbose messages are recorded in the logs. "disable" will record no logs. The order fo levels are same as log_min_messages except additional "disable" and "alert" levels and "debug".
- disable > alert > panic > fatal > log > error > warning > notice > info > debug
- *2 : Prefix Format
- Spefied with the same format with configuration parameter log_line_prefix. Note that log_line_prefix itself is ignored when pg_statsinfo is enabled.
- *3 : Time Format
- Available units are d (day), h (hour), min (minite), and s (second). If no unit, the value is interpreted as seconds.
- Using pg_statsinfo in Postgresql 8.3, please set second unit(not nesessary 's').(ex. 3min = 180)
- *4 : Connection String
- It is a libpq-style connection string. For example, 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres'. See also PQconnectdb in "Database Connection Control Functions" for details. In addition, environment variables used by libpq will be used.
- You need to avoid password input to connect the repository. If password is required, create .pgpass for the OS user who execute the local postgres server to connect the repository to automate password input. And, If you use .pgpass file, please set host name in connection string to repository in "host=xxxx".
Operations
Operations manually executed by user is explained in the section.
Delete Server Log Files
All of the server log files inclduing csvlog and textlog are not deleted automatically. You need to delete old log files periodically.
Uninstall
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
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);"
Taking a snapshot in manual
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')"
Reload Configuration Files
If you want to reset configuration parameters online, just modify postgresql.conf and send a reload signal with pg_ctl.
$ pg_ctl reload
Rotate Logs
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()"
Restart after Crash
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.
Restrictions
There are still some restrictions and limitations in pg_statsinfo.
- Requires the same encoding and lc_messages setting on all databases in the monitored instance
- pg_statsinfo supports encodings and message locales in PostgreSQL, but all of database in an instance must use the same encoding and locale because mixed-encoded server logs cannot be parsed correctly.
- Restrictions for log_filename
- pg_statsinfo expects log_filename behaves as the following:
- Every restarts of the server always create new log files. (The logs must have 'seconds' unit in their filename.)
- Server log are named ascending order.
- Restrictions for pg_statsinfo.textlog_filename
- A fixed named textlog is always required.
- log_timezone should be unknown, gmt, or utc
- Only those values are supported. PostgreSQL uses own timezone implementation, but we cannot use it from an external program.
- Error messages in fast or immediate shutdown
- Those error messages are not avoidable on PostgreSQL shutdown unless it is a smart shutdown.
- Cannot collect shutdown checkpoint logs
- That't because the repository might be unavailable if it is in the same server with the monitored one.
Q&A
Q1. How confirm the method if pg_statsinfo works normal?
Pg_statsinfo gets snapshot and stores it in statsrepo schema. If you want to confirm if pg_statsinfo waorks normal, you can use under following SQL.
$psql -d postgres -c "SELECT statsinfo.snapshot('test')"
$psql -d postgres -c "SELECT * FROM statsrepo.snapshot WHERE COMMENT = 'test'"
If you execute 2nd SQL and get result, pg_statsinfo works normal.
Q2. How to use snapshots?
Pg_statsinfo's snapshot function has only getting PostgreSQL's statistics every uniform regularly. If you want to see useful and visual report from snapshot, you can use pg_reporter. Example of pg_repoter's output report is here.
Q3. Auto maintenance mode does not work well.
First, please confirm your configuration file(postgresql.conf) that is right settings. The other things, when you installed from source file and mistaked to install pg_statsrepo.sql, or when you did not use "make USE_PGXS=1" compile option and installed, this situation does not work well. In this situation, you drop the statsrepo schema and restart your PostgreSQL. pg_statsrepo.sql is schema for only PostgreSQL8.3. If you use PostgreSQL8.4 or later, you can not execute this SQL file.
Q4. When I use log_statement=all configuration in my PostgreSQL, a lot of pg_statsinfo's SQL executed log is output. How method does not output these log?
please use method under following.
1. Create role for pg_statsinfo in motitored PostgreSQL instance. # CREATE ROLE statsinfo WITH LOGIN SUPERUSER; 2. Set log_statement = 'none' in pg_statsinfo's role. # ALTER ROLE statsinfo SET log_statement = 'none' 3. Set PGUSER=statsinfo in enviroment variable in OS for pgstatsinfo's role. $ export PGUSER=statsinfo 4. Restart your PostgreSQL. $ pg_ctl restart
Details
More advanced usages and internal structures are explained in the section.
Alert Function
"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
- [argument] snap_id:
- The latest snapshot ID is passed.
- [result] setof text:
- Returns messages shown as ALERT.
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_rollbacks_per_second | 100 | rollbacks/sec |
| statsrepo.alert_transactions_per_second | 1000 | commits/sec |
| 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
Warm Standby
Warm standby can be used together with pg_statsinfo. There are two typical setups for warm standby mode. See also "pg_statsinfo: warm-standby" for details.
- Setup another repository server independent from active and standby servers.
- Place repositories for each active and standby servers.
Internals
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.