Wednesday, January 23, 2013

PostgreSQL Tool To Analyze DB Performance

I needed to do some fine-grained performance analysis of my PostgreSQL database setup and came across an interesting tool. It's pgBadger and it is used to take a snapshot of your PostgreSQL performance data. It is not a realtime monitoring system - but it is easy to install and run and it outputs a nice HTML report that is a snapshot of your logged performance data.

I didn't have to do anything special to get it built and installed on my Fedora 15 box. I just followed their simple instructions and it built and ran fine.

I did have to configure PostgreSQL to spit out performance data in its logs, but again that was easy to do following their instructions. It just required simple changes to postgresql.conf and a restart of the DB. Once I did that, PostgreSQL started logging performance data in log files located in the PostgreSQL data/pg_log directory.

I then just ran pgbadger, passing in the names of the logs files as command line arguments, and after a few seconds it spit out an HTML report. A typical snapshot report that it generates out can be seen here.