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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
