Message-ID: <5033F8B9.4080806@fuzzy.cz>
Date: Tue, 21 Aug 2012 23:08:09 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: average query performance measuring
References: <362605510214004CB3313A03D3D697DE5508FF27@cmhvm005>
In-Reply-To: <362605510214004CB3313A03D3D697DE5508FF27@cmhvm005>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

On 21.8.2012 20:35, Rick Otten wrote:
> I have a PostgreSQL 9.1 cluster.  Each node is serving around 1,000
> queries per second when we are at a =91steady state=92.
>=20
> What I=92d like to know is the average query time.  I=92d like to see i=
f
> query performance is consistent, or if environmental changes, or code
> releases, are causing it to drift, spike, or change.   I=92d also like =
to
> be able to compare the (real) query performance on the different nodes.
>=20
> I know I can put some sort of query wrapper at the application layer to
> gather and store timing info.  (I=92m not sure yet how the application
> would know which node the query just ran on since we are using pgpool
> between the app and the db.)   I=92d much rather get something directly
> out of each database node if I can.
>=20
> Turning on statement logging crushes the database performance, so I
> don=92t want to do that either.  (Not to mention I=92d still have to pa=
rse
> the logs to get the data.)
>=20
> It seems like we almost have everything we need to track this in the
> stats tables, but not quite.  I was hoping the folks on this list would
> have some tips on how to get query performance trends over time out of
> each node in my cluster.

As others already mentioned, the improvements in pg_stat_statements by
Peter Geoghean in 9.2 is the first thing you should look into I guess.
Especially if you're looking for per-query stats.

If you're looking for "global stats," you might be interested in an
extension I wrote a few months ago and collects query histogram. It's
available on pgxn.org: http://pgxn.org/dist/query_histogram/

The question is whether tools like this can give you reliable answers to
your questions - that depends on your workload (how much it varies) etc.

Tomas