Re: [PERFORM] average query performance measuring
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-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] average query performance measuring
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 rot...@manta.com Manta.comhttp://manta.com/?referid=emailSig Where Small Business Grows(tm)
Re: [PERFORM] average query performance measuring
* Rick Otten (rot...@manta.com) wrote: 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. I'm afraid the best answer to this is, honestly, upgrade to 9.2 once it's out.. http://pgeoghegan.blogspot.com/2012/03/much-improved-statement-statistics.html If what's described there doesn't match what you're looking for, then please let us know what else you'd like, so we can further improve things in that area.. Thanks, Stephen signature.asc Description: Digital signature
Re: [PERFORM] average query performance measuring
On 8/21/2012 1:53 PM, Stephen Frost wrote: * Rick Otten (rot...@manta.com) wrote: 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. I'm afraid the best answer to this is, honestly, upgrade to 9.2 once it's out.. http://pgeoghegan.blogspot.com/2012/03/much-improved-statement-statistics.html If what's described there doesn't match what you're looking for, then please let us know what else you'd like, so we can further improve things in that area.. Thanks, Stephen That looks EXTREMELY useful and I'm looking forward to checking it out in 9.2; I have asked a similar question about profiling actual queries in the past and basically it came down to turn on explain or run a separate explain yourself since the app knows what's similar and what's not, which of course has hideous performance implications (as the query basically executes twice.) -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC signature.asc Description: OpenPGP digital signature
Re: [PERFORM] average query performance measuring
Karl, * Karl Denninger (k...@denninger.net) wrote: That looks EXTREMELY useful and I'm looking forward to checking it out in 9.2; I have asked a similar question about profiling actual queries in the past and basically it came down to turn on explain or run a separate explain yourself since the app knows what's similar and what's not, which of course has hideous performance implications (as the query basically executes twice.) Just to clarify one thing- if your application is currently using prepared queries for everything, you can probably use the existing contrib module. The difference is that, with 9.2, it'll actually do normalization of non-PREPARED queries and will include some additional statistics and information. Thanks, Stephen signature.asc Description: Digital signature
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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] average query performance measuring
On 21 August 2012 22:08, Tomas Vondra t...@fuzzy.cz wrote: 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 people would like to know about a better way to monitor query execution costs on earlier versions, I think that I'll probably have new information about that for my talk at Postgres Open. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
