PGstatspack - Getting at Postgres performance data.

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.
Comments:

pgstatspack :)

... A couple more tools like that ( ADDM, AWR ) and some might actually think that Postgres could be used instead of 3% of Oracle functionality, they exploit.

Anyhow, I like your weblog, so keep on the good work - more often if possible.

Posted by matjazc on October 02, 2007 at 04:51 AM PDT #

Great !

Is it possible to run pgstatspack on pg 8.2.4, which is included in Solaris 10 U4 ?

Posted by Thorleif Wiik on October 08, 2007 at 05:45 AM PDT #

I will put together a 8.2.4 version and post it on my blog.

This was originally created for some benchmark work on Postgres 8.3... but these things tend to take on a life of their own
:)

Posted by Glenn Fawcett on October 11, 2007 at 08:44 AM PDT #

Great!
Please keep this work for pgstatspack!!
I wish this tool would be bundled into the future postgres release.

Posted by Hisayoshi Kato on October 15, 2007 at 09:42 AM PDT #

Glenn,
I did something very similar though I wrote it using pl/pgsql. I am an ex-Oracle DBA so I also was looking for performance insight once I started working for EnterpriseDB.
I captured data from several stats tables plus 3 wait views that we've added to our Postgres Plus Advanced Server product.

Regards,
Peter

Posted by Peter Steinheuser on March 26, 2008 at 04:40 AM PDT #

Glenn,

I've written a pgstatspack package inspired on yours. Josh Berkus suggested to make it a pgfoundry project, which I did (it's pending approval now)

It's written so it could work from postgresql version 8.1 onward.

Posted by Frits Hoogland on May 02, 2008 at 09:25 PM PDT #

Excellent! This is what is good about open source! Thanks for your contributions.

Posted by Glenn Fawcett on May 03, 2008 at 01:20 AM PDT #

It now an official pgfoundry project!

Download the latest version on: http://pgstatspack.projects.postgresql.org/

Posted by Frits Hoogland on May 07, 2008 at 06:06 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder