PostgreSQL Log Analysis using Ruby’s PQA

Lastweek I had to look at a slow postgreSQL server to figure out what queries taking long time and identify bottlenecks. So I thought of trying out Ruby’s Practical Query  Analysis (PQA). So here is the way to install and use it on a RedHat Linux box already running PostgreSQL 7/8.

Step1 : Install Ruby

Download Source ruby-1.8.5.tar.gz

$ tar -xvfz ruby-1.8.5.tar.gz

$ cd ruby-1.8.5

$ configure

$ make

$ make install

Step 2: Install Ruby gems

Download rubygems-0.9.4.tgz

$ tar -xvfz rubygems-0.9.4.tgz

$ cd rubygems-0.9.4

$ ruby setup.rb

Step 3: Install Practical Query Analysis

Download pga gem

$ gem install pqa-1.6.gem

Step 4: Configure PostgreSQL 

In postgresql.conf file, makesure these settings are on

log_statement  true

log_duration   true

log_pid        true

Restart PostgreSQL to make these changes in effect.In my case postgresql is setup as a service to start at boot. so restarting is easy.

service postgresql restart

Step 5:   Use PQA to anlayze log

pqa -file /path/to/your/logfile -normalize -top 5

To generate a HTML Report

pqa -file /path/to/your/logfile -normalize -top 5 -format html > /tmp/myReport.html

Thats it!. You could find slowest queires, most run queries and other cool stuff from this great tool.  you could out details and detailed documentation at http://pgfoundry.org/projects/pqa/