详解PostgreSQL中的checkpoint概念

richardtc

checkpoint又名检查点，在Oracle中checkpoint的发生意味着之前的脏数据全部写回磁盘，数据库实现了一致性与数据完整性。Oracle在实现介质恢复时将以最近的checkpoint为参照点执行事务前滚。在PostgreSQL中checkpoint起着相同的作用：写脏数据；完成数据库的完整性检查。

checkpoints相关参数：

checkpoint_segments:

WAL log的最大数量，系统默认值是3。该值越大，在执行介质恢复时处理的数据量也越大，时间相对越长。

checkpoint_timeout:

系统自动执行checkpoint之间的最大时间间隔，同样间隔越大介质恢复的时间越长。系统默认值是5分钟。

checkpoint_completion_target:

该参数表示checkpoint的完成目标，系统默认值是0.5,也就是说每个checkpoint需要在checkpoints间隔时间的50%内完成。

checkpoint_warning:

系统默认值是30秒，如果checkpoints的实际发生间隔小于该参数，将会在server log中写入写入一条相关信息。可以通过设置为0禁用信息写入。

checkpoint执行控制:

1,数据量达到checkpoint_segments*16M时，系统自动触发；

2,时间间隔达到checkpoint_timeout参数值时；

3,用户发出checkpoint命令时。

checkpoints参数调整：

正确合适的参数值总能够给系统带来益处，checkpoints参数合理的配置不仅能够减少系统IO写入的阻塞，同时还会减少高峰时IO给系统带来的压力。首先可以通过观察checkpoint_warning参数写入的日志，来估算系统写入的数据量：一般情况下checkpoint_warning参数值小于checkpoint_timeout；

估算公式：checkpoint_segments*16M*(60s/m)/checkpoint_warning=大致每分钟数据量,得到每分钟写入的数据量(这里全部是估算，建立在warning参数的合理设置上)。

合理配置情况：checkpoint_segments*16M*checkpoint_timeout(m)略大于上述值.

以上述公式为依据，配置checkpoint_segments与checkpoint_timeout，两个参数应该尽量平衡为一个足够大和足够小的值。在数据量异常高的情况下应该考虑，磁盘带宽与checkpoint时数据量的关系。

个人观点：

假如以checkpoint_segments参数为阀值时，可以计算高峰时需要的带宽(秒)：checkpoint_segments*16M/(checkpoint_warning*checkpoint_completion_target)。

假如以checkpoint_timeout参数为阀值时，checkpoint_warning值最好大于等于checkpoint_timeout，通过监控系统数据字典统计写入的数据量Total。
Total/(checkpoint_timeout*checkpoint_completion_target)得到IO的带宽要求。

wang1352083

checkpoint_completion_target 这个参数的准确定义是什么呢?

osdba

回复 2# wang1352083
In 8.3, the checkpoint writes can be spread out over a much longer time period. A new parameter called checkpoint_completion_target suggests how far along the system should aim to have finished the current checkpoint relative to when the next one is expected.
If your checkpoints are being driven by the timeout, it's fairly easy to predict how this will work. Let's say you have checkpoint_timeout=300 seconds. With checkpoint_completion_target=0.5 (the default), checkpoints should be finished writing out all their data by 150 seconds after one starts. If you set it to 0.9 instead, the practical upper limit, the checkpoints will aim to be finished 270 seconds later, leaving only 30 seconds for the operating system to finish writing everything out before the next checkpoint should start.

The parameter works similarly if your system tends to hit the segments limit before the timeout. For example, let's say your current system has checkpoint_segments=10. Assume that you have checkpoint_timeout set to a large number such that the checkpoints are typically being driven by the number of segments being filled (so you get a checkpoint every 10 WAL segments, period). If checkpoint_completion_target was set to 0.5, the expectation is that the writes for the currently executing checkpoint would be finished about the time that 0.5*10=5 segments of new WAL data had been written. If you set it to 0.9 instead, you'd expect the checkpoint is finishing just about when the 9th WAL segment is being written out, which again is cutting things a bit tight.