PostgreSQL Log Analysis using Ruby’s PQA

September 25, 2007

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/

 
Advertisements

5 Responses to “PostgreSQL Log Analysis using Ruby’s PQA”

  1. Mips Says:

    Restart PostgreSQL is not necesary.

    $pg_ctl reload

    is better.


  2. Yes… pg_ctl reload is enough and is a better option when you are working on a production server… reload mode simply sends the postmaster process a SIGHUP signal, causing it to reread its configuration files…. Thanks Mips for pointing that…

  3. Diego ayala Says:

    Buenos dias estoy ejecutando el PQA sobre CentOS 5.3 64 bits, gem -v 1.3.6 y ruby 1.9.2dev (2009-07-18 trunk 24186) [x86_64-linux], pero al ejecutar

    pqa -file /var/lib/pgsql/data/pg_log/postgresql-2010-05-14_000000.log -logtype pglog -normalize -top 5 -format txt>/ruby/prueba3.txt me lanza lo siguiente
    Continuation for no previous query
    Continuation for no previous query y al abrir el archivo
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8
    invalid byte sequence in UTF-8, que estoy haciendo mal, como podria solucionarlo…?? gracias por la ayuda..!!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: