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
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