checkpoints are occurring too frequently
I have a lot of entries like this in the log file
2011-11-17 02:02:46 PYST LOG: checkpoints are occurring too frequently (13
seconds apart)
2011-11-17 02:02:46 PYST HINT: Consider increasing the configuration
parameter "checkpoint_segments".
No, checkpoint parameters in postgres.conf are:
checkpoint_segments = 32 # in logfile segments, min
1, 16MB each
checkpoint_timeout = 10min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 -
1.0
What should be a correct value for checkpoint_segments to avoid excessive
checkpoint events?
Hi Anibal,
On Thu, 17 Nov 2011 09:48:10 -0300, "Anibal David Acosta"
<aa@devshock.com> wrote:
What should be a correct value for checkpoint_segments to avoid
excessive checkpoint events?
There is no golden rule or value that fits all scenarios. Usually 32 is
a good value to start with, however it might not be perfectly tailored
for your environment.
To give you an idea, currently you are issuing a checkpoint every
32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us
better your workload, if it is subject to usage spikes or regularly
distributed throughout the day mainly in terms of
inserts/updates/deletes.
I would gradually try and increase checkpoint_segments by 32 and
monitor the effects.
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
Thanks!
-----Mensaje original-----
De: Gabriele Bartolini [mailto:Gabriele.Bartolini@2ndQuadrant.it]
Enviado el: jueves, 17 de noviembre de 2011 10:14 a.m.
Para: Anibal David Acosta
CC: pgsql-general@postgresql.org
Asunto: Re: [GENERAL] checkpoints are occurring too frequently
Hi Anibal,
On Thu, 17 Nov 2011 09:48:10 -0300, "Anibal David Acosta"
<aa@devshock.com> wrote:
What should be a correct value for checkpoint_segments to avoid
excessive checkpoint events?
There is no golden rule or value that fits all scenarios. Usually 32 is a good value to start with, however it might not be perfectly tailored for your environment.
To give you an idea, currently you are issuing a checkpoint every 32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us better your workload, if it is subject to usage spikes or regularly distributed throughout the day mainly in terms of inserts/updates/deletes.
I would gradually try and increase checkpoint_segments by 32 and monitor the effects.
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it