Skip site navigation (1) Skip section navigation (2)

Re: Performance 8.4.0

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Chris Dunn <chris(dot)dunn(at)bigredsky(dot)com>
Cc: "'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance 8.4.0
Date: 2009-07-31 04:53:11
Message-ID: alpine.GSO.2.01.0907310042230.14911@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Your settings look reasonable, I'd bump up checkpoint_segments to at least 
double where you've got it set at now to lower general overhead a bit.  I 
doubt that will help you much though.

If you're at 100% CPU with no I/O wait, typically that means you have some 
heavy queries running that are gobbling up your CPU time.  Finding and 
improving those will probably buy you more than adjusting server 
parameters given that you already have everything in the right general 
ballpark.

Suggestions:

-Set log_min_duration_statement and analyze the results.  See 
http://wiki.postgresql.org/wiki/Logging_Difficult_Queries for more 
information about tools that might help.

-Capture snapshots of what the system is doing when it gets bogged down. 
I like to run the following periodically:

top -c -b -n 1
psql -c "select * from pg_stat_activity"

"top -c" will show you which processes are gobbling CPU time, and then you 
can see more detail about what those processes are doing by matching them 
up with the corresponding lines in pg_stat_activity.

If you want more performance out of the hardware you've already, finding 
the worst queries and seeing if you can speed them up would be where I'd 
start in your case.  It only takes one badly written one to drag the whole 
system to crawl if a couple of clients get caught up executing it at the 
same time.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-07-31 05:11:55
Subject: Re: PostgreSQL 8.4 performance tuning questions
Previous:From: Chris DunnDate: 2009-07-31 04:22:45
Subject: Performance 8.4.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group