PGstatspack - Getting at Postgres performance data.

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

$ rpt.sh 1 2 

DATABASE THROUGHPUT 
==============================================================
 database  |  tps   | hitrate | lio_ps  |  rd_ps  | rows_ps  | ins_ps | upd_ps | del_ps 
-----------+--------+---------+---------+---------+----------+--------+--------+--------
 igen      | 169.55 |   94.00 | 3909.70 |  211.15 | 23543.05 |  50.87 |  46.74 |   0.00 
 tpce      |   0.04 |    0.00 | 2310.97 | 2307.90 |     0.65 |   0.01 |   0.00 |   0.00 
 postgres  |   0.03 |   99.00 |    1.86 |    0.00 |     0.44 |   0.00 |   0.00 |   0.00 
 template1 |   0.00 |    0.00 |    0.00 |    0.00 |     0.00 |   0.00 |   0.00 |   0.00 
 template0 |   0.00 |    0.00 |    0.00 |    0.00 |     0.00 |   0.00 |   0.00 |   0.00 
(5 rows)

MOST ACCESSED TABLES by pct of tuples: igen database
==============================================================
    table     | tuples_pct | tab_hitpct | idx_hitpct | tab_read | tab_hit | idx_read | idx_hit 
--------------+------------+------------+------------+----------+---------+----------+---------
 order_125    |         45 |         91 |         77 |    67566 |  698578 |    58050 |  202950
 product_125  |         42 |         99 |         99 |       82 |  120060 |       30 |  127345
 industry_125 |         10 |         99 |          0 |        1 |   22409 |        0 |       0
 customer_125 |          1 |         94 |         99 |    34978 |  657096 |     6858 | 1032477

Note: This prototype is built on top of the 8.3 version of Postgres. Some modification would be required to use it on other versions of Postgres.