Database Reference
In-Depth Information
See also
There is more information on tools for query analysis at the end of the Chapter,
Monitoring
and
Diagnosis
Collecting regular statistics from pg_stat*
views
This recipe describes how to collect the statistics needed to understand what is going on in
the database system on a regular basis, so that they can be used for further optimizing the
queries which are slow or which are becoming slow as the database changes.
The code to do is in file
pg_statlogs.tar.gz
Getting ready
Find the package
pg_statlogs.tar.gz
.
Set up a directory as shown next for running the scripts:
mkdir /opt/pg_statlogs
cd /opt/pg_statlogs
tar xzvf pg_statlogs.tgz
Set up a schema for collecting the snapshots of statistics data and generating deltas from
it as follows:
psql mydatabase <./pg_statlogs_prepare.sql
How to do it...
Run the following from command line:
psql mydatabase <./pg_statlogs_collect.sql
to collect the changes in
pg_stat*
tables since the last invocation.
You should probably set up a cron job to run on a regular basis, so that you have good
coverage of what happens at what time of day and week. Running it at interval of 5 to 15
minutes should usually give you enough temporal resolution to understand what is going on
with your tables.
