Greg -
Thanks for the heads up. The box in question is a dual cpu (xeon dual cores) with 8 gig & a pair of 10k 146gb raid 1
arrays. I have the pg_xlog dir on one array (along with the OS) & the rest of the data on the other array by itself.
Given that this is a production system I'm going to tone things down a bit as you suggested prior to the open today.
WhileI don't like the 10-20 second pauses every 5 minutes it's a system I need to have running and I'd rather not take
thechance of bringing the system to its knees.
A couple of quick questions. On the fly I can change these params and use 'pg_ctl reload" to put them in effect,
correct? That way I can play a little today and see what the effects are. Also, I have my checkpoint_segments set to
10,if I were to lower this (say 5) would this possible have the effect of checkpointing a little more often with less
data? (right now I hit the checkpoint_timeout).
Thanks again,
Marc
----- Original Message ----
From: Greg Smith <gsmith@gregsmith.com>
To: Marc Rossi <marc_rossi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Friday, August 10, 2007 2:36:28 AM
Subject: Re: [GENERAL] UPDATES hang every 5 minutes
On Thu, 9 Aug 2007, Marc Rossi wrote:
> as well as made changes to the bgwriter settings as shown below (taken
> from a post in the pgsql-performance list)
>bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round
>bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round
>bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round
>bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round
Be warned that these are settings from a much more powerful server than it
sounds like you have, and I wouldn't be surprised to find your average
performance tanks as a result. Making the background writer this
aggressive will waste a lot of I/O, and unless you've got a lot of spare
I/O to waste (which was the case on the source of this tuning) it can make
your problem worse. I'd hesitate to recommend setting either percentage
over 5% or either maxpages>100 as a first step on a production system.
You may be in for a bad day tomorrow.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
