pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Monitoring
7+ messages / 7 participants
[nested] [flat]
* SQL Monitoring
@ 2007-10-09 14:11 Campbell, Lance <lance@uiuc.edu>
2007-10-09 14:36 ` Re: SQL Monitoring Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-09 15:07 ` Re: SQL Monitoring Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 2 replies; 7+ messages in thread
From: Campbell, Lance @ 2007-10-09 14:11 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
@ 2007-10-09 14:36 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-10 10:41 ` Re: SQL Monitoring Tomáš Vondra <tv@fuzzy.cz>
1 sibling, 1 reply; 7+ messages in thread
From: Marcin Stępnicki @ 2007-10-09 14:36 UTC (permalink / raw)
To: pgsql-performance
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/.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
2007-10-09 14:36 ` Re: SQL Monitoring Marcin Stępnicki <mstepnicki@gmail.com>
@ 2007-10-10 10:41 ` Tomáš Vondra <tv@fuzzy.cz>
2007-10-10 14:58 ` Re: SQL Monitoring Rodrigo De León <rdeleonp@gmail.com>
2007-10-10 17:25 ` Re: SQL Monitoring Josh Trutwin <josh@trutwins.homeip.net>
0 siblings, 2 replies; 7+ messages in thread
From: Tomáš Vondra @ 2007-10-10 10:41 UTC (permalink / raw)
To: Marcin Stępnicki <mstepnicki@gmail.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
2007-10-09 14:36 ` Re: SQL Monitoring Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-10 10:41 ` Re: SQL Monitoring Tomáš Vondra <tv@fuzzy.cz>
@ 2007-10-10 14:58 ` Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rodrigo De León @ 2007-10-10 14:58 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
2007-10-09 14:36 ` Re: SQL Monitoring Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-10 10:41 ` Re: SQL Monitoring Tomáš Vondra <tv@fuzzy.cz>
@ 2007-10-10 17:25 ` Josh Trutwin <josh@trutwins.homeip.net>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Trutwin @ 2007-10-10 17:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
@ 2007-10-09 15:07 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-09 19:00 ` Re: SQL Monitoring Gregory Stark <stark@enterprisedb.com>
1 sibling, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2007-10-09 15:07 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL Monitoring
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
2007-10-09 15:07 ` Re: SQL Monitoring Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-10-09 19:00 ` Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Gregory Stark @ 2007-10-09 19:00 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Campbell\, Lance <lance@uiuc.edu>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-10-10 17:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-09 14:11 SQL Monitoring Campbell, Lance <lance@uiuc.edu>
2007-10-09 14:36 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-10 10:41 ` Tomáš Vondra <tv@fuzzy.cz>
2007-10-10 14:58 ` Rodrigo De León <rdeleonp@gmail.com>
2007-10-10 17:25 ` Josh Trutwin <josh@trutwins.homeip.net>
2007-10-09 15:07 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-09 19:00 ` Gregory Stark <stark@enterprisedb.com>
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