Monitoring Postgres Performance

Issue:

Logging long-running queries means having to tediously parse the logs and make sense of what's there, see http://community.jaspersoft.com/wiki/logging-long-running-queries-postgres-and-mysql-databases. Beginning with PostgreSQL version 8.4, pg_stat_statements was added to track metrics for queries such as the number of times a query was called, the total number of rows retrieved by a query, the total time spent in a statement and more. So now, everything is in the database!

Resolution:

Enabling pg_stat_statements

1. Su to user postgres

2. Navigate to the postgres data directory (see the long-running query article above to find the location for various systems)

3. Stop the database server

   pg_ctl stop -D /var/lib/pgsql/data -m fast
   

4. Open postgresql.conf and search for "shared_preload_libraries." Uncomment the line and add 'pg_stat_statements' inside the single quotation marks. The line will look like:

   shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
   

5. Save the file and start the database

   pg_ctl start -D /var/lib/pgsql/data
   

6. Connect to the repository database using a SQL client and run

   CREATE extension pg_stat_statements;
   

This will ceate the pg_stat_statements view and you can run a variety of queries on it, such as

SELECT (total_time / 60) as total_minutes,
       total_time,  
       (total_time/calls) as average_time,  
       calls,   
       query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

SELECT count(*), query 
FROM  pg_stat_statements 
GROUP BY 2 
ORDER BY 1 DESC 
LIMIT 10;

Note that prior to 9.1 total_time was measured in seconds, from 9.2 on it is measured in milliseconds.