Re: monitoring sql queries

On Thu, Nov 17, 2011 at 11:46 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:

> On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
> > On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
> >> I am in need of a tool or method to see each/every SQL query that
> >> hits the PostgreSQL database. By query I mean the query in SQL
> >> syntax with all the parameters passed.
> >>
> >> What I want to do is:
> >> 1) see the query
> >> 2) Determine how long the query takes to execute
> >> 3) Possibly log both of the above to a log file
> >>
> >> Is there any tool internal to PostgreSQL that would allow me to do this?
> >>
> >> I cannot monitor it from the code that is actually connecting &
> >> sending the query.
> >>
> >> any help or pointers would be greatly appreciated.
> >
> > just enable logging of queries.
>
> As depesz mentioned, there's a log_min_duration GUC, that allows you to
> log queries that exceed some time interval. If you want to log all
> queries, you may set this to 0 but it may easily fill your log with
> garbage.
>

Just as a warning, on heavily-loaded systems, this logging can have a
significant impact to your performance. Not so much because it's logging,
but due to the fact that your log-files may start requiring more disk I/O
than the actual database. If you are going to do this under any serious
load, I would recommend separating 'pg_log' on to a separate [set of]
physical disk[s].

--Scott

>
> There are two contrib modules that might help you - pg_stat_statements and
> auto_explain. The former one is probably more interesting in this case.
>
> Tomas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>