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.
Thanks!
--
Rick Otten
Data-Systems Engineer
rotten@manta.com
Manta.com Where Small Business Grows™