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.