Details about the checkpoint concept in PostgreSQL

Checkpoint, also known as a checkpoint, means that all the dirty data is written back to the disk in Oracle. The database achieves consistency and data integrity. Oracle performs transaction rollback with the latest checkpoint as the reference point during media recovery. In PostgreSQL, checkpoint plays the same role: Writing dirty data and completing database integrity check.

Checkpoints parameters:

Checkpoint_segments:

The maximum number of WAL logs. The default value is 3. The larger the value, the larger the data volume processed during media recovery, and the longer the time.

Checkpoint_timeout:

The maximum interval between checkpoints automatically executed by the system. The larger the interval, the longer the media recovery time. The default value is 5 minutes.

Checkpoint_completion_target:

This parameter indicates the completion target of the checkpoint. The default value is 0.5. That is to say, each checkpoint must be completed within 50% of the checkpoints interval.

Checkpoint_warning:

The default value is 30 seconds. If the actual interval between checkpoints and checkpoints is smaller than this parameter, a related message is written into the server log. You can disable information writing by setting it to 0.

Checkpoint execution control:

1. When the data volume reaches checkpoint_segments * 16 m, the system will automatically trigger it;

2. When the time interval reaches the checkpoint_timeout parameter value;

3. When the user issues the checkpoint command.

Checkpoints parameter adjustment:

Correct and appropriate parameter values can always bring benefits to the system. The reasonable configuration of the checkpoints parameter can not only reduce the system IO write blocking, but also reduce the pressure on the system during peak hours. First, you can observe the logs written by the checkpoint_warning parameter to estimate the data volume written by the system: in general, the checkpoint_warning parameter value is smaller than checkpoint_timeout;

Estimation Formula: checkpoint_segments * 16 M * (60 s/m)/checkpoint_warning = roughly the data volume written per minute to get the data volume written per minute (all here are estimates, based on the reasonable setting of the warning parameter ).

Reasonable configuration: checkpoint_segments * 16 M * checkpoint_timeout (m) is slightly greater than the preceding value.

Based on the above formula, configure checkpoint_segments and checkpoint_timeout. The two parameters should be balanced to a large value and a small value. When the data volume is abnormal, consider the relationship between the disk bandwidth and the data volume at checkpoint.

Personal Opinion:

If the checkpoint_segments parameter is used as the threshold value, you can calculate the peak bandwidth (in seconds): checkpoint_segments * 16 M/(checkpoint_warning * checkpoint_completion_target ).

If the checkpoint_timeout parameter is used as the threshold value, it is recommended that the checkpoint_warning value be greater than or equal to checkpoint_timeout, and the Total data volume written is collected through the Monitoring System data dictionary.
Total/(checkpoint_timeout * checkpoint_completion_target) to obtain the IO bandwidth requirement.

Original article title: postgresql checkpoints

Link: http://www.cnblogs.com/daduxiong/archive/2010/09/28/1837682.html