On Thu, 17 Jul 2014 10:44:32 -0700 Prabhjot Sheena <prabhjot.sheena@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>
