Thread: average query performance measuring

* 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
folkson 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

* 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,

* 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

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

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

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
shouldlook 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
queryhistogram. 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
(howmuch 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