From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: average query performance measuring |
Date: | 2012-08-21 21:08:09 |
Message-ID: | 5033F8B9.4080806@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2012-08-21 21:51:12 | Re: average query performance measuring |
Previous Message | Scott Marlowe | 2012-08-21 21:06:56 | Re: Does setval(nextval()+N) generate unique blocks of IDs? |