Friday Sep 28, 2007

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.

Friday Jan 27, 2006

HOTSOS PD101 Course - more than just technical

A colleague and myself just returned from the "Diagnosing Oracle Performance Problems" course with HOTSOS given by Cary Millsap. This course was definitely top-notch with not only the technical aspect but the "people" aspect of solving performance problems. In my own experience I too have found that by partnering across the organization, you are better able to solve performance problems. Often times when a consultant is brought into an organization they can serve as a focal point. A good consultant can help provide a bridge between the user, developer, DBAs, and sysadmins. This course puts all of these aspects into perspective along with method-R.

I was also excited to be able to use the new version of HOTSOS. The new version is far superior to the share-ware versions on the web. I particularly like the ability to show skew in the various events... very useful for determining root cause.

Finally, I was happy to learn that HOTSOS is creating a new products which will use trace application data to help model and size systems for growth. This is a huge development. Other tools which try to model based on system resources completely miss the fact that all aspects of the application do not grow at the same rate. Using this new methodology, predicting grow should be much easier. Hats off once again to HOTSOS.
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