Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0E7C19FA5D9 for ; Thu, 11 Oct 2007 00:14:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 22121-03 for ; Thu, 11 Oct 2007 00:14:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id F3EB49FA5D6 for ; Thu, 11 Oct 2007 00:14:02 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id l9B3E38Y026709; Wed, 10 Oct 2007 23:14:03 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id l9B3E2vw026705; Wed, 10 Oct 2007 23:14:02 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Wed, 10 Oct 2007 23:14:02 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Kevin Kempter cc: pgsql-performance@postgresql.org Subject: Re: building a performance test suite In-Reply-To: <200710101621.41749.kevin@kevinkempterllc.com> Message-ID: References: <200710101621.41749.kevin@kevinkempterllc.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/121 X-Sequence-Number: 27143 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@gregsmith.com http://www.gregsmith.com Baltimore, MD