Re: average query performance measuring - Mailing list pgsql-performance

From Rick Otten
Subject Re: average query performance measuring
Date
Msg-id 362605510214004CB3313A03D3D697DE55091835@cmhvm005
Whole thread Raw
In response to Re: average query performance measuring  (Tomas Vondra <tv@fuzzy.cz>)
List pgsql-performance
Thanks!  That looks like a handy tool.

I think in this case we'll wait for 9.2.  We are looking forward to it.


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tomas Vondra
Sent: Tuesday, August 21, 2012 5:08 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] average query performance measuring

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 'steady state'.
>
> What I'd like to know is the average query time.  I'd like to see if
> query performance is consistent, or if environmental changes, or code
> releases, are causing it to drift, spike, or change.   I'd also like to
> be able to compare the (real) query performance on the different nodes.
>
> I know I can put some sort of query wrapper at the application layer
> to gather and store timing info.  (I'm 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'd much rather get something directly
> out of each database node if I can.
>
> Turning on statement logging crushes the database performance, so I
> don't want to do that either.  (Not to mention I'd still have to parse
> the logs to get the data.)
>
> 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
shouldlook 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
queryhistogram. 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
(howmuch it varies) etc. 

Tomas


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


pgsql-performance by date:

Previous
From: Matt Daw
Date:
Subject: Re: Performance of Seq Scan from buffer cache
Next
From: Jeff Janes
Date:
Subject: Re: Increasing WAL usage followed by sudden drop