From: | Jean-David Beyer <jeandavid8(at)verizon(dot)net> |
---|---|
To: | Pgsql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: update query taking too long |
Date: | 2007-06-28 11:44:57 |
Message-ID: | 46839F39.5020402@verizon.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Chris wrote (in part):
> I didn't have logging set up before but it's up and running now and I
> was getting
>
> LOG: checkpoints are occurring too frequently (26 seconds apart)
> HINT: Consider increasing the configuration parameter
> "checkpoint_segments".
>
> So I increased that from 10 to 30 and it finished:
>
> UPDATE 3500101
> Time: 146513.349 ms
>
I have not used postgreSQL since I tried it once in about 1998 (when I found
it unsatisfactory, but much has changed since then), but I am going to try
it again. What would be a good checkpointing interval? I would guess 26
seconds is too often. What considerations go into picking a checkpointing
interval?
I note, from the book "PostgreSQL" second edition by Douglas and Doublas,
the following parameters are available:
WAL_BUFFERS The default is 8.
CHECKPOINT_SEGMENTS The default is 3. This would have been too low for the
O.P. Would it make sense to start with a higher value
or is this a good value and just not appropriate for
the O.P.? Should CHECKPOINT_SEGMENTS be raised until
the checkpointing is about half CHECKPOINT_TIMEOUT,
e.g., 150 seconds while the dbms is running typical
work?
CHECKPOINT_TIMEOUT The default is 300 seconds.
CHECKPOINT_WARNING The default is 30 seconds.
My machine has 8 GBytes RAM and it worked perfectly well (very very little
paging) when it had 4 GBytes RAM. I doubled it because it was cheap at the
time and I was afraid it would become unavailable later. It is usually
between 2/3 and 3/4 used by the cache. When I run IBM DB2 on it, the choke
point is the IO time spent writing the logfiles.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 07:20:01 up 7 days, 14:55, 3 users, load average: 4.26, 4.15, 4.07
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2007-06-28 12:01:03 | Re: update query taking too long |
Previous Message | Richard Huxton | 2007-06-28 09:16:00 | Re: PostgreSQL 8.0 occasionally slow down |