https://access.redhat.com/solutions/2610161
SOLUTION 已验证 - 已更新 2017年一月23日12:45 -
环境
Red Hat Enterprise Linux 5, 6, 7
PostgreSQL 8, 9
问题
The following messages occur regularly in our /var/lib/pgsql/data/pg_log/postgresql*-log
files:
LOG: checkpoints are occurring too frequently (4 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
决议
Increase the checkpoint_segments
parameter in the /var/lib/pgsql/data/postgresql.conf
file.
PostgreSQL's standard setting for the minimum time between checkpoints is 30 seconds. Therefore, divide thirty by the current period in seconds per checkpoint, and then multiply the current checkpoint_segments
parameter by this. For example, if checkpoint_segments
was set to 8, and the period is 4 seconds between checkpoints, then the new setting should be 8 * (30 / 4)
or 60. This defines a reasonable minimum for the checkpoint_segments
value. The default value of checkpoint_segments
is 3 segments.
PostgreSQL normally defaults to a time between checkpoints of five minutes, which is ten times the minimum time above. Therefore, for a more realistic checkpoint_segments
value to have checkpoints occurring every five minutes, multiply the figure from the above calculation by ten.
Likewise, if you have set your checkpoint_timeout
value higher than this, use this value in seconds instead of the '30' in the above calculation.
Once the PostgreSQL configuration has been changed, you will need to get the PostgreSQL daemon to reload its configuration using the command:
pg_ctl reload
根源
PostgreSQL uses a write-ahead log (WAL) to store changes to the database before it actually updates the table files. In the event of a crash, these logs are replayed to ensure data consistency. The process of writing the logs back to the table files is done in a checkpoint, which temporarily halts transaction processing. Therefore, if checkpoints are happening too frequently this slows access to the database.
For more information, read:
https://www.postgresql.org/docs/9.2/static/wal-configuration.html
诊断步骤
Check the '/var/lib/pgsql/data/pg_log/postgresql*-log' files for warnings about frequent checkpoints:
grep 'checkpoints' /var/lib/pgsql/data/pg_log/postgresql*.log
To determine a better value for checkpoint_segments
, find the current values of the checkpoint_segments
and checkpoint_timeout
configuration settings:
grep '^checkpoint' /var/lib/pgsql/data/postgresql.conf
This looks for all non-commented values. If the checkpoint_timeout
setting is not listed, this defaults to five minutes.