From: | Scott Mead <scottm(at)openscg(dot)com> |
---|---|
To: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
Cc: | depesz(at)depesz(dot)com, "J(dot)V(dot)" <jvsrvcs(at)gmail(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: monitoring sql queries |
Date: | 2011-11-17 17:27:01 |
Message-ID: | CAKq0gvL_5WPkLsxsbTbacmdJH_0q44nbczyEY8wsXatZUc8c5w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Siva Palanisamy | 2011-11-17 18:02:02 | Please recommend me the best bulk-delete option |
Previous Message | Ivan Voras | 2011-11-17 16:58:30 | Session variables and C functions |