cancel
Showing results for 
Search instead for 
Did you mean: 

Log: checkpoints occurring too frequently

When there is a bulk load  on the database server it can reach the threshold set using the max_wal_size (for v9.5 and greater) / checkpoint_segments for v9.4 and lower PostgresDB and if the checkpoints are occurring very frequently the database starts logging the below messages

 

Example logs for PG/EPAS v9.5+
2019-02-01 04:10:11 CET [24870]: [7771-00000] user=,db=,app=,client= LOG:  checkpoints are occurring too frequently (16 seconds apart) 2019-02-01 04:10:11 CET [24870]: [7772-00000] user=,db=,app=,client= HINT:  Consider increasing the configuration parameter "max_wal_size".
For PG/EPAS v9.4 or lower
2019-03-13 23:52:26 IST LOG:  checkpoints are occurring too frequently (24 seconds apart) 2019-03-13 23:52:26 IST HINT:  Consider increasing the configuration parameter "checkpoint_segments".

 The message are at 'LOG' level does and it is NOT error/fatal messages ..does that impact database server? 

 

Yes. Checkpoints are fairly expensive.

 

1. At checkpoint time, all dirty data pages are flushed to disk. So, frequent checkpoint consume OS resources, causing high IO 

2. They result in extra subsequent WAL traffic.

 

By default the full_page_write is set to "on" which is recommended as well to ensure data page consistency, the first modification of a data page after each checkpoint results in logging the entire page content. In that case, a smaller checkpoint interval increases the volume of output to the WAL log.

 

We can check that when frequent checkpoint occurs the amount WALs generated is due to full page write using the pg_waldump stats which 

says in below example FPI (Full page image size): 88%

 

[enterprisedb@localhost bin]$ ./pg_waldump -b -p /opt/edb/as10/data/pg_wal/ -z 0000000100000002000000EB 000000010000000300000011

Screen Shot 2018-06-02 at 7.11.50 PM.png

Note: The FATAL message in the above image is normal and it is because of end of the WAL file.

 

 In order to overcome the frequent checkpoints it is recommended to increase the max_wal_size but it should not be increased to very high

values due to the fact the crash recovery time gets increases.

 

Or if your database has any planned load acitivities at any particular time you can use the high max_wal_size during this period and

then revert the changes once the activity finishes. Changes to this parameter can be applied with "Reload" of database server which

can be changed on fly without impacting the database activities.

 

edb=# show max_wal_size ;
 max_wal_size 
--------------
 1GB
(1 row)

edb=# show data_directory ;
       data_directory       
----------------------------
 /var/lib/edb/as10/cluster1
(1 row)

edb=# \! echo max_wal_size='2GB' >>/var/lib/edb/as10/cluster1/postgresql.conf
edb=# 
edb=# select pg_reload_conf;
 pg_reload_conf 
----------------
 t
(1 row)

edb=# show max_wal_size ;
 max_wal_size 
--------------
 2GB
(1 row)
Version history
Revision #:
1 of 1
Last update:
‎05-10-2019 01:57 PM
Updated by:
 
Contributors