Re: SQL Monitoring

PGSQL 8.2.4

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?

Thanks for any help,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

On 10/9/07, Campbell, Lance <lance(at)uiuc(dot)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/.

Campbell, Lance 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?

Take a look at statement_timeout and log_statement configuration variables.

> I would prefer to limit the size of the log file to 2 G. Is
> there a way to do this?

log_rotation_size, together with an external tool to delete old log
files. Or use log_truncate_on_rotation and log_rotation_age instead of
log_rotation_size.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:

> Campbell, Lance 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?
>
> Take a look at statement_timeout and log_statement configuration variables.

I suspect he meant log_min_duration_statement which lets you log only queries
which take too long and not statement_timeout which would actually kill your
query if it took too long.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

> On 10/9/07, Campbell, Lance <lance(at)uiuc(dot)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

On 10/10/07, Tomáš Vondra <tv(at)fuzzy(dot)cz> wrote:
> 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.

+1

On Wed, 10 Oct 2007 12:41:49 +0200
Tomáš Vondra <tv(at)fuzzy(dot)cz> wrote:

<snip>

> 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.

Can you post an announcement here?

Josh