pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedaverage query performance measuring
7+ messages / 5 participants
[nested] [flat]
* average query performance measuring
@ 2012-08-21 18:35 Rick Otten <rotten@manta.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rick Otten @ 2012-08-21 18:35 UTC (permalink / raw)
To: pgsql-performance
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<http://manta.com/?referid=emailSig; Where Small Business Grows(tm)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-21 18:53 Stephen Frost <sfrost@snowman.net>
parent: Rick Otten <rotten@manta.com>
1 sibling, 1 reply; 7+ messages in thread
From: Stephen Frost @ 2012-08-21 18:53 UTC (permalink / raw)
To: Rick Otten <rotten@manta.com>; +Cc: pgsql-performance
* Rick Otten (rotten@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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-21 19:48 Karl Denninger <karl@denninger.net>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 7+ messages in thread
From: Karl Denninger @ 2012-08-21 19:48 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Rick Otten <rotten@manta.com>; pgsql-performance
On 8/21/2012 1:53 PM, Stephen Frost wrote:
> * Rick Otten (rotten@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
Attachments:
signature.asc (application/pgp-signature, 550B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-21 20:27 Stephen Frost <sfrost@snowman.net>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 7+ messages in thread
From: Stephen Frost @ 2012-08-21 20:27 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Rick Otten <rotten@manta.com>; pgsql-performance
Karl,
* Karl Denninger (karl@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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-21 21:08 Tomas Vondra <tv@fuzzy.cz>
parent: Rick Otten <rotten@manta.com>
1 sibling, 2 replies; 7+ messages in thread
From: Tomas Vondra @ 2012-08-21 21:08 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-21 21:51 Peter Geoghegan <peter@2ndquadrant.com>
parent: Tomas Vondra <tv@fuzzy.cz>
1 sibling, 0 replies; 7+ messages in thread
From: Peter Geoghegan @ 2012-08-21 21:51 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
On 21 August 2012 22:08, Tomas Vondra <tv@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: average query performance measuring
@ 2012-08-22 14:04 Rick Otten <rotten@manta.com>
parent: Tomas Vondra <tv@fuzzy.cz>
1 sibling, 0 replies; 7+ messages in thread
From: Rick Otten @ 2012-08-22 14:04 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-08-22 14:04 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-21 18:35 average query performance measuring Rick Otten <rotten@manta.com>
2012-08-21 18:53 ` Stephen Frost <sfrost@snowman.net>
2012-08-21 19:48 ` Karl Denninger <karl@denninger.net>
2012-08-21 20:27 ` Stephen Frost <sfrost@snowman.net>
2012-08-21 21:08 ` Tomas Vondra <tv@fuzzy.cz>
2012-08-21 21:51 ` Peter Geoghegan <peter@2ndquadrant.com>
2012-08-22 14:04 ` Rick Otten <rotten@manta.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox