On Wed, 16 Apr 2008 06:07:04 +0200, Thomas Spreng <[EMAIL PROTECTED]> wrote:
On 16.04.2008, at 01:24, PFC wrote:
The queries in question (select's) occasionally take up to 5 mins even
if they take ~2-3 sec under "normal" conditions, there are no
sequencial scans done in those queries. There are not many users
connected (around 3, maybe) to this database usually since it's still
in a testing phase. I tried to hunt down the problem by playing around
with resource usage cfg options but it didn't really made a difference.
Could that be caused by a CHECKPOINT ?
actually there are a few log (around 12 per day) entries concerning
checkpoints:
LOG: checkpoints are occurring too frequently (10 seconds apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".
But wouldn't that only affect write performance? The main problems I'm
concerned about affect SELECT queries.
OK, so if you get 12 of those per day, this means your checkpoint
interval isn't set to 10 seconds... I hope...
Those probably correspond to some large update or insert query that comes
from a cron or archive job ?... or a developer doing tests or filling a
table...
So, if it is checkpointing every 10 seconds it means you have a pretty
high write load at that time ; and having to checkpoint and flush the
dirty pages makes it worse, so it is possible that your disk(s) choke on
writes, also killing the selects in the process.
-> Set your checkpoint log segments to a much higher value
-> Set your checkpoint timeout to a higher value (5 minutes or
something), to be tuned afterwards
-> Tune bgwriter settings to taste (this means you need a realistic load,
not a test load)
-> Use separate disk(s) for the xlog
-> For the love of God, don't keep the RAID5 for production !
(RAID5 + 1 small write = N reads + N writes, N=3 in your case)
Since this is a test server I would suggest RAID1 for the OS and database
files and the third disk for the xlog, if it dies you just recreate the
DB...
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
