Oracle Blog

Sun Database Performance

Friday Sep 28, 2007

I thought I posted this a while ago... Maybe a blog bug?
=====
I have been working with Oracle for the past 18 years, mostly in the performance arena. Last year, I began working with Postgres as well. Being a performance guy, I naturally was looking at how to get at the performance data necessary to tune the database for maximum performance. To my surprise, little existed in the way of performance tools for Postgres. I was looking for the "Statpack" or "AWR" report for Postgres. I found several on-off tools but nothing that provided a "Load Profile" like Statspack.

PG_STAT\* tables... V$ tables in disguise

Postgres has a series of tables that are essentially counters like the V$ tables. They record the counts of things like:

commited transactions

rolled back transactions

tuples accessed

tuples inserted

block read

block hits

tuples accessed by table and index

physical reads by table and index

Creating a prototype

I fashioned the prototype after Oracle's Statspack. I created a simple schema where I essentially duplicated the PG_STAT\* tables and added a key for the snapshot. There is also a management table "pgstatspack_snap" which stores the snapid, timestamp, and a short description.
To keep with the statspack like theme, a simple PLPGSQL procedure was created to take snapshots:

SELECT pgstatspack_snap('My test run');

Creating pgstatspack reports

Now \*all\* you have to do is create the reports. I have created a simple report that gets at the heart of what is encapsulated in the "Load Profile" section of the Statspack. Additionally, I have profiled some of the table objects in terms of access, IO, etc. The report essentially does a diff of the counters between the two snap intervals. Time data is applied to calculate the per-second rates.
This is meant to be a launch pad for experimentation. Hopefully, you will find it interesting. The prototype package and report can be downloaded here: pgstatspack.tar.gz