Re: SQL Monitoring

> 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