Re: Queries running the longest?

On 19 August 2010 08:58, Machiel Richards <machielr(at)rdc(dot)co(dot)za> wrote:
> Good day everyone.
>
>
>             Firstly I would like to thank everyone in advance for all the
> help over the last couple of weeks on this mailing list as it has helped me
> out a lot in my efforts to try and understand Postgresql better.
>
>            I am however once again here with some more questions which I
> hope someone can assist me with as I can not seem to find good documentation
> on this as yet...
>
>
>           As part of our daily health checks and efforts to assist our
> client to improve their performance on their postgresql databases, we need
> to look at the queries that takes the longest to run and then use explain or
> other methods to try and improve the queries, add indexes where required,
> etc...
>
>
>            However, I am trying to find out how I will be able to track /
> see these queries in order for me to be able to investigate these.
>
>            the client have a total of 100 databases and to manually monitor
> these will be too time consuming considering that we have many clients to
> look after each day.
>
>
>       Can anyone perhaps point me in the right direction here, even if it is
> in the form of documentation that will assist me in this.
>
>

Hi Machiel,

If you look in postgresql.conf, you'll see an option called
log_min_duration_statement. If you set this to a minimum amount of
time (in milliseconds) that the query must run before it's logged (of
course you need logging enabled first), you can then analyze the log
though something like pgFouine
(http://pgfouine.projects.postgresql.org/) which will be able to rank
the longest-running queries for you. Note that pgFouine requires that
you configure your log format beforehand, so check the pgFouine
documentation for how to do that.

Alternatively, if you really don't wish to use pgFouine, just set your
log_min_duration_statement setting to a value over which queries would
become unacceptably long. Then just manually examine the logs.

Regards
--
Thom Brown
Registered Linux user: #516935