Thread: SQL Monitoring

SQL Monitoring

From
"Campbell, Lance"
Date:

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

 

Re: SQL Monitoring

From
"Marcin Stępnicki"
Date:
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/.

Re: SQL Monitoring

From
"Heikki Linnakangas"
Date:
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

Re: SQL Monitoring

From
Gregory Stark
Date:
"Heikki Linnakangas" <heikki@enterprisedb.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

Re: SQL Monitoring

From
Tomáš Vondra
Date:
> 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

Re: SQL Monitoring

From
"Rodrigo De León"
Date:
On 10/10/07, Tomáš Vondra <tv@fuzzy.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

Re: SQL Monitoring

From
Josh Trutwin
Date:
On Wed, 10 Oct 2007 12:41:49 +0200
Tomáš Vondra <tv@fuzzy.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