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@gregsmith.com http://www.gregsmith.com Baltimore, MD
