From: Tomáš Vondra <tv@fuzzy.cz>
To: Marcin Stępnicki <mstepnicki@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL Monitoring
Date: Wed, 10 Oct 2007 12:41:49 +0200
Message-ID: <470CAC6D.601@fuzzy.cz> (raw)
In-Reply-To: <179149fe0710090736l1a863d71yb04e2598d0faed4e@mail.gmail.com>
References: <B10E6810AC2A2F4EA7550D072CDE8760197DA7@SAB-FENWICK.sab.uiuc.edu>
<179149fe0710090736l1a863d71yb04e2598d0faed4e@mail.gmail.com>
> On 10/9/07, Campbell, Lance <lance@uiuc.edu> wrote:
>
>> I have noticed a slight spike in the amount of CPU usage in the last few
>> weeks. I am sure it has to do with a change or two that was made to some
>> queries. What is the best way to log the SQL that is being executed? I
>> would prefer to limit the size of the log file to 2 G. Is there a way to do
>> this?
>>
>>
>
> Use http://pgfouine.projects.postgresql.org/.
The best thing you can do is setting the log_min_duration_statement to
some reasonable value (say 200 ms or something like that), and then
repeatedly fix the worst queries (modifying them, adding indexes, ...)
etc. We've adopted this as a common part of weekly development /
production tuning, and the performance of the apps shoot up (response
time of the web application dropped from 2 seconds to less than 0.5 second).
Actually we wrote something similar as pgfounie was not as nice as
today, at that time (2005] - you can find that tool on
http://opensource.pearshealthcyber.cz/. Actually I'm working on a
complete rewrite of that tool into Java (new features, performance etc.)
- it's almost done, the alpha release should be ready in two weeks or
something like that. If you are interested in this, just let me know and
I'll notify you once the first version is available on sf.net.
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, mstepnicki@gmail.com
Subject: Re: SQL Monitoring
In-Reply-To: <470CAC6D.601@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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