PostgreSQL - checkpoints are occurring too frequently

https://access.redhat.com/solutions/2610161

 SOLUTION 已验证 - 已更新 2017年一月23日12:45 - 

English 

环境

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:

Raw

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:

Raw

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:

Raw

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:

Raw

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.