Skip site navigation (1) Skip section navigation (2)

Re: building a performance test suite

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Kevin Kempter <kevin(at)kevinkempterllc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: building a performance test suite
Date: 2007-10-11 03:14:02
Message-ID: Pine.GSO.4.64.0710102252570.23235@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 10 Oct 2007, Kevin Kempter wrote:

> should I be looking at others like pg_statio or pg_stat_all_tables ? If 
> so, what should I look for in these (or other) tables?

There are a good set of monitoring scripts for performance-oriented things 
included with the dbt2 benchmarking package, 
http://sourceforge.net/projects/osdldbt

You can just use the SVN browse to take a look at the data collected by 
that; see /trunk/dbt2/bin/pgsql/dbt2-pgsql-db-stat.in for some good things 
to get started with.  For example, here's the pg_statio info they save:

SELECT relid, relname, heap_blks_read, heap_blks_hit, idx_blks_read, 
idx_blks_hit FROM pg_statio_user_tables ORDER BY relname;

SELECT relid, indexrelid, relname, indexrelname, idx_blks_read, 
idx_blks_hit FROM pg_statio_user_indexes ORDER BY indexrelname;

Pretty much everything in pg_stat_user_tables is worth collecting.  And 
you probably want to use the user oriented views rather than the all ones 
(pg_stat_user_tables instead of pg_stat_all_tables) so you don't clutter 
your results with what's going on in the system tables--unless your test 
incudes lots of table modifications that is.  Look at both of them and 
you'll see what I mean.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-performance by date

Next:From: Ow Mun HengDate: 2007-10-11 03:16:23
Subject: Re: Query taking too long. Problem reading explainoutput.
Previous:From: Scott MarloweDate: 2007-10-11 00:49:23
Subject: Re: Shared Buffer setting in postgresql.conf

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group