[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    [ADMIN] Checkpoint_segments optimal value
From:       Prabhjot Sheena <prabhjot.sheena () rivalwatch ! com>
Date:       2014-07-17 17:44:32
Message-ID: CAOf_bQYehfKp7CNRa_pYQ+-SmHyDSYJ8bddbaixXUcWmSjH6wA () mail ! gmail ! com
[Download RAW message or body]

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


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

[Attachment #3 (text/html)]

<div dir="ltr"><div><span style="font-family:verdana,sans-serif">Hello \
All<br></span></div><span style="font-family:verdana,sans-serif">            i had a \
database load issue today and when i was checking logs i saw this message. I m \
running postgresql 8.3<br>

</span><div><div><span style="font-family:courier \
new,monospace"><br>db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoints are occurring \
too frequently (26 seconds apart)<br>db::ts:2014-07-17 09:38:21 PDT::HINT:  Consider \
increasing the configuration parameter &quot;checkpoint_segments&quot;.<br>

db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoint starting: \
xlog<br><br><br></span></div><div><span style="font-family:courier new,monospace">pls \
suggest the optimal checkpoint_segment value i should set<br><br> \
</span></div><div><span style="font-family:courier new,monospace"><b>My current \
values are this</b><br><br>checkpoint_segments = 32                # in logfile \
segments, min 1, 16MB each<br>#checkpoint_timeout = 5min              # range \
30s-1h<br>

#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - \
1.0<br>#checkpoint_warning = 30s               # 0 is \
off<br><br></span></div><div><span style="font-family:courier \
new,monospace"><b>Current Memory values</b><br>

<br># - Memory -<br><br>effective_cache_size = 8GB<br><br>shared_buffers = 1024MB     \
# min 128kB or max_connections*16kB<br>                                        # \
(change requires restart)<br>temp_buffers = 64MB                     # min 800kB<br>

#max_prepared_transactions = 5          # can be 0 or more<br>                        \
# (change requires restart)<br><br>work_mem = 512MB                                # \
min 64kB<br>maintenance_work_mem = 2048MB           # min 1MB     !!! increased to \
1GB 10/1/2010 mrostron (to assist autovacuum workers)<br>

#max_stack_depth = 2MB                  # min 100kB<br><br># - Free Space Map \
-<br><br>max_fsm_pages = 809600                  # min max_fsm_relations*16, 6 bytes \
each<br>                                        # (change requires restart)<br>

synchronous_commit = off<br><br>#full_page_writes = on                  # recover \
from partial page writes<br>#wal_buffers = 64kB                     # min 32kB<br>    \
# (change requires restart)<br>

#wal_writer_delay = 200ms               # 1-10000 milliseconds<br><br>#commit_delay = \
0                       # range 0-100000, in microseconds<br>#commit_siblings = 5     \
# range 1-1000<br><br></span><br> </div>
</div></div>



[prev in list] [next in list] [prev in thread] [next in thread]