Postgres Pro
Downloads
Home   >   mailing lists

Thread: checkpoints are occurring too frequently

checkpoints are occurring too frequently

From
"Anibal David Acosta"
Date:

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?

 

 

Re: checkpoints are occurring too frequently

From
Gregg Jaskiewicz
Date:
increase your checkpoint segments

--
GJ

Re: checkpoints are occurring too frequently

From
Gabriele Bartolini
Date:
 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

Re: checkpoints are occurring too frequently

From
"Anibal David Acosta"
Date:
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
notbe 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
youcan describe us  better your workload, if it is subject to usage spikes or regularly  distributed throughout the day
mainlyin 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