Re: Checkpoint_segments optimal value - Mailing list pgsql-general

On Thu, 17 Jul 2014 10:44:32 -0700
Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote:

> Hello All
>             i had a database load issue today and when i was checking logs
> i saw this message. I m running postgresql 8.3
>
> db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoints are occurring too
> frequently (26 seconds apart)
> db::ts:2014-07-17 09:38:21 PDT::HINT:  Consider increasing the
> configuration parameter "checkpoint_segments".
> db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoint starting: xlog

Is this the first time you've had the load problem?  How often does it
happen?  How often is that message in the logs?

The downside (as described in the docs) is that increasing the value will
cause PG to take longer to recover in the event of a crash.  That and that
you need enough disk space to hold the extra segments.

If the warning isn't happening too often, I would try increasing it only a
little and see if it helps.  If it's not enough you can then increase it some
more.  Various sources around the Internet suggest that you don't want to go
much larger than 256 for this (if only because it's uncommon to do so and is
probably indicative of other tuning that you need to do).  Unfortunatley, you
need to restart PG for the change to take effect, so you have to balance
experimenting with your tuning against how often you can get away with a server
restart.

If it's just that one time that you got that message, then you might want to
double checkpoint_segments to 64.  Anything more than that seems unjustified,
unless you're seeing the problem a lot more often than your email suggests.

If the problem is happening frequently, you're probably way better off
organizing an upgrade to PG 9.3 than fidgeting with a lot of tuning.  The
number of performance improvements from 8.3 to 9.3 is quite significant.

> pls suggest the optimal checkpoint_segment value i should set
>
> *My current values are this*
>
> checkpoint_segments = 32                # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min              # range 30s-1h
> #checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 -
> 1.0
> #checkpoint_warning = 30s               # 0 is off
>
> *Current Memory values*
>
> # - Memory -
>
> effective_cache_size = 8GB
>
> shared_buffers = 1024MB                 # min 128kB or max_connections*16kB
>                                         # (change requires restart)
> temp_buffers = 64MB                     # min 800kB
> #max_prepared_transactions = 5          # can be 0 or more
>                                         # (change requires restart)
>
> work_mem = 512MB                                # min 64kB
> maintenance_work_mem = 2048MB           # min 1MB     !!! increased to 1GB
> 10/1/2010 mrostron (to assist autovacuum workers)
> #max_stack_depth = 2MB                  # min 100kB
>
> # - Free Space Map -
>
> max_fsm_pages = 809600                  # min max_fsm_relations*16, 6 bytes
> each
>                                         # (change requires restart)
> synchronous_commit = off
>
> #full_page_writes = on                  # recover from partial page writes
> #wal_buffers = 64kB                     # min 32kB
>                                         # (change requires restart)
> #wal_writer_delay = 200ms               # 1-10000 milliseconds
>
> #commit_delay = 0                       # range 0-100000, in microseconds
> #commit_siblings = 5                    # range 1-1000


--
Potentialtech <wmoran@potentialtech.com>