pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to figure out how long a query takes in the pg log file...
2+ messages / 2 participants
[nested] [flat]
* how to figure out how long a query takes in the pg log file...
@ 2009-03-10 23:24 Jessica Richard <rjessil@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jessica Richard @ 2009-03-10 23:24 UTC (permalink / raw)
To: pgsql-admin
I need to identify the slowest queries by analyzing the postgre log file.
By the following log_line_prefix set up, I can see all the query contents and I can see the duration times:
log_line_prefix = '%u %d %h %m %c %x '
..
..
postgres testdb [local] 2009-03-10 14:36:35.110 EDT 49b6b31a.8ad 30898958 DETAIL: ! system usage stats:
postgres testdb [local] 2009-03-10 14:36:35.110 EDT 49b6b31a.8ad 30898958 LOG: statement: select count(*) from testtable;
postgres testdb [local] 2009-03-10 14:36:35.111 EDT 49b6b31a.8ad 30898958 LOG: PARSE ANALYSIS STATISTICS
postgres testdb [local] 2009-03-10 14:36:35.111 EDT 49b6b31a.8ad 30898958 DETAIL: ! system usage stats:
..
postgres testdb [local] 2009-03-10 14:36:47.935 EDT 49b6b31a.8ad 0 LOG: duration: 12825.670 ms
But I am unable to link he duration time taken by the original query. Even though I used the session id %c and the transaction id %x, when it comes to the duration line, the transaction id is always "0" -- I can't match it to the query line that has a non-zero transaction id.
I ideally I expect to see something in common between the duration line and the query line so that I can group them together --- then I will be able to see the slowest queries and look for the tuning options from there. Since all the queries take different amounts of time to finish, the duration time showed up few lines after the query line does not mean it was the time that query took. The same session id can have many different transaction ids.
Could some one out there help me on this?
Thanks a lot,
Jessica
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: how to figure out how long a query takes in the pg log file...
@ 2009-03-10 23:30 Shoaib Mir <shoaibmir@gmail.com>
parent: Jessica Richard <rjessil@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Shoaib Mir @ 2009-03-10 23:30 UTC (permalink / raw)
To: pgsql-admin; +Cc: Jessica Richard <rjessil@yahoo.com>
On Wed, Mar 11, 2009 at 10:24 AM, Jessica Richard <rjessil@yahoo.com> wrote:
> I need to identify the slowest queries by analyzing the postgre log file.
>
>
I will recommend using log_min_duration for that purpose from
postgresql.conf file. Another good project I have used for such purpose in
the past is pgFouine --> http://pgfouine.projects.postgresql.org
--
Shoaib Mir
http://shoaibmir.wordpress.com/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-03-10 23:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-10 23:24 how to figure out how long a query takes in the pg log file... Jessica Richard <rjessil@yahoo.com>
2009-03-10 23:30 ` Shoaib Mir <shoaibmir@gmail.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