Search Postgresql Archives

Re: monitoring sql queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Thu, Nov 17, 2011 at 11:46 AM, Tomas Vondra <tv@xxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux