Infrastructure at your Service

Daniel Westermann

Monitoring tools for PostgreSQL – pgcluu

The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself and other useful stuff.

All you need to run pgcluu is a modern perl distribution but this should be available if you are on a recent operating system. If you not only want to have statistics about a PostgreSQL instance but also want to have OS statistics you’ll need the sysstat package in addition (this should be available for your distribution). You can install pgcluu on the server where the PostgreSQL instance you want to monitor runs (as I will do for this post) or on a remote host. Installation is quite easy;

postgres@oel7:/var/tmp/ [dummy] tar -axf pgcluu-2.4.tar.gz
postgres@oel7:/var/tmp/ [dummy] cd pgcluu-2.4
postgres@oel7:/var/tmp/ [dummy] perl Makefile.PL
postgres@oel7:/var/tmp/ [dummy] make && sudo make install

pgcluu is divided into two parts:

  • The collector which is responsible for collecting the statistics: pgcluu_collectd
  • The report generator which generates the reports out of the files the collector generated: pgcluu

To collect statistics start the pgcluu_collectd script as deamon:

postgres@oel7:/home/postgres/ [PG2] mkdir /var/tmp/test_stats
postgres@oel7:/home/postgres/ [PG2] pgcluu_collectd -D -i 60 /var/tmp/test_stats/
postgres@oel7:/home/postgres/ [PG2] LOG: Detach from terminal with pid: 10423

This will collect statistics for the PostgreSQL instance you have the environment set for every 60 seconds and stores the results in the /var/tmp/test_stats/ directory:

postgres@oel7:/var/tmp/pgcluu-2.4/ [postgres] ls -la /var/tmp/test_stats/
total 196
drwxrwxr-x. 2 postgres postgres  4096 Dec  7 16:16 .
drwxrwxrwt. 4 root     root        64 Dec  7 16:05 ..
-rw-rw-r--. 1 postgres postgres  8280 Dec  7 16:16 pg_class_size.csv
-rw-rw-r--. 1 postgres postgres   274 Dec  7 16:16 pg_database_size.csv
-rw-rw-r--. 1 postgres postgres  4214 Dec  7 16:15 pg_hba.conf
-rw-rw-r--. 1 postgres postgres  1636 Dec  7 16:15 pg_ident.conf
-rw-rw-r--. 1 postgres postgres 30694 Dec  7 16:16 pg_settings.csv
-rw-rw-r--. 1 postgres postgres     0 Dec  7 16:15 pg_stat_connections.csv
-rw-rw-r--. 1 postgres postgres   333 Dec  7 16:16 pg_stat_database.csv
-rw-rw-r--. 1 postgres postgres  2682 Dec  7 16:16 pg_statio_user_indexes.csv
-rw-rw-r--. 1 postgres postgres  1040 Dec  7 16:16 pg_statio_user_sequences.csv
-rw-rw-r--. 1 postgres postgres  1582 Dec  7 16:16 pg_statio_user_tables.csv
-rw-rw-r--. 1 postgres postgres  1004 Dec  7 16:16 pg_stat_locks.csv
-rw-rw-r--. 1 postgres postgres   764 Dec  7 16:16 pg_stat_unused_indexes.csv
-rw-rw-r--. 1 postgres postgres  2682 Dec  7 16:16 pg_stat_user_indexes.csv
-rw-rw-r--. 1 postgres postgres  1430 Dec  7 16:16 pg_stat_user_tables.csv
-rw-rw-r--. 1 postgres postgres     0 Dec  7 16:15 pg_tablespace_size.csv
-rw-rw-r--. 1 postgres postgres   343 Dec  7 16:15 postgresql.auto.conf
-rw-rw-r--. 1 postgres postgres 24821 Dec  7 16:15 postgresql.conf
-rw-rw-r--. 1 postgres postgres 56896 Dec  7 16:16 sar_stats.dat
-rw-rw-r--. 1 postgres postgres  3111 Dec  7 16:15 sysinfo.txt

After some time, when hopefully there was some activity in the PostgreSQL instance, stop the deamon:

postgres@oel7:/home/postgres/ [PG2] pgcluu_collectd -k
OK: pgcluu_collectd exited with value 0
postgres@oel7:/home/postgres/ [PG2] 

Once we have some statistics collected we can generate a report:

postgres@oel7:/home/postgres/ [PG2] mkdir /var/tmp/test_report/
postgres@oel7:/home/postgres/ [PG2] pgcluu -o /var/tmp/test_report/ /var/tmp/test_stats/

The report can be viewed in any modern browser that supports javascript and css. Once you open the index.html you are presented with an overview of the system:

pgcluu1

On the top there is a menu which allows you to navigate to various reports of your operating system and the PostgreSQL instance, e.g. the cluster:

pgcluu2

pgcluu3

Reports for various OS statistics are available through the system menu:

pgcluu4
pgcluu5

To get the best of out this you probably should let the collector running all the time and use the build in rotation functionality:

postgres@oel7:/home/postgres/ [PG2] pgcluu_collectd -D -i 60 --rotate-daily /var/tmp/test_stats/  

Having statistics available for each day of the week helps a lot in troubleshooting. Reports can than be scheduled automatically by cron or any other scheduler.

Conclusion: pgcluu is an easy to setup and easy to use monitoring solution for PostgreSQL instances. Spend some time in thinking about how to collect, how to report and how to archive the reports and you’ll have plenty of information to troubleshoot and to plan capacity.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure