理解postgresql.conf中的check_completion_target参数

理解postgresql.conf中的check_completion_target参数：

翻译自：https://www.depesz.com/2010/11/03/checkpoint_completion_target

开始新一系列的博客–解释各种配置参数。当然了 我不会遵循任何时间表或命令，除非它是我的工作，我认为我这样的方式很有趣。

第一篇参数配置是关于checkpoint_completion_target.

首先，让我们先想想什么是checkpoint?

你可能知道PostgreSQL把缓存页(默认是8kB的磁盘块)存在RAM中。为了使它对所有的后台进程可用，它存储在 shared_buffers中，而且通常会占据大部分的shared_buffers。

当Pg需要对给出的页面做任何事情时，检查点会检查该数据页是否在共享内存中，如果不在就加载它（通过添加新页面来执行一些逻辑以确保它不会超出共享内存的大小）
这种情况也会发生在写入时。

当你写入任何数据时（仔细思考一下：插入，更新，删除）这些更改不是直接写入到表中，而是写入到内存中的页面中去。

现在有一个问题—当服务器崩溃时会发生什么？数据在从RAM中加载的过程中我是否会丢失它？当然不会。因为数据还会被写入“WAL”。WAL是pg_xlog目录下的那些文件。它们实际上从不读取，只包含对页面的更改列表。

因此，流程如下，你发出insert命令，PostgreSQL加载页面到内存中（如果页面在加载之前不存在）对其进行修改，把关于修改操作的信息写入到WAL中。这些更改不会应用到真正的表文件中。

现在，过了一会，内存中有了很多被修改过的数据页，并没有被应用到表中。写入表中会在检查点发生的时候。当检查点命令被触发时 会有些逻辑上的变化（稍后我会写一篇关于这些变化的博客），但是现在，重要的部分是：当检查点发生的时候所有被修改过的数据页（也叫脏页）会被写进真实的表文件和索引文件中。

到目前一切都好吗？希望如此吧。

现在，检查点的大小当然是可变的了，但是依赖于共享内存的大小，检查点发生的频率，还有你的应用怎样进行完整的写。

让我们假设，你的服务器有100GB的共享内存（这是很多，但并非闻所未闻）。而且是写密集型。因此，当检查点发生时，它可能需要留出100GB磁盘空间将数据转储到磁盘。或多或少会进行一次。这的确是个问题。因为这是巨大的I/O峰值，而且肯定会严重影响并发的查询性能。

这就是为什么要设置checkpoint_completion_target。

通常来说这个参数使PostgreSQL尽可能慢地去写数据-能在checkpoint_completion_target*checkpoint_timeout的时间内就行。通常默认情况下checkpoint_timeout设置为5分钟（除非你修改了它），默认checkpoint_completion_target是0.5.这意味着PostgreSQL会尝试在2.5分钟进行一次检查点操作，以便I/O加载地更慢点。

让我们回到我们的例子中来。我们有100GB数据要写入数据文件中去。我的磁盘可以支持每秒1GB的写入量（这是一个具有巨大I/O的大服务器）。当正常进行检查点操作时（8.3之前的方式），写入数据会导致一百秒100%的宽带利用率。

但是 在checkpoint_completion_target设置为0.5,PostgreSQL会尽量在2.5分钟内写入数据，因此只有效的使用了700MB/s的宽带，给其他任务留下了30%I/O容量。这样有着巨大的好处。

这种方法的问题相对简单—因为检查点花费更长的时间写入，旧的。过时的的WAL文件段在pg_xlog目录下存留的时间更长了，而pg_xlog目录会变得更大。

在8.2版本中，pg_xlog目录下的文件数量通常为2*checkpoint_segments+1。

现在，它的计算公式为（2+checkpoint_comletion_target）*checkpoint_segments+1.

如果你仔细阅读了以上段落有些细节你可能注意到了就是我用了一个词“try”。原因很简单—有效的宽带I/O是可变的，因此没有保证，所以，让我们来看看它是怎样工作的。

测试一下，我用我的测试机，设置数据目录，xlogs和查询日志目录分别在不同的区下（对测试锁和查询变慢很有用）。

把这些目录设置在同一块磁盘上的不同区上不会提高性能，会让我清楚地知道每个分区在测试时发生了什么。

我是这样来设置这三个区的：

• sda1—交换区，不应该被使用，所以它不重要

• sda2—根目录，是查询和io状态日志存储的地方

• sda3 – $PGDATA

• sda4 - pg_xlog-wal分段区

为了测试Pg,我使用标准的pgbench程序，比例为35（-s 35,意味着产生的数据库，没有任何测试，大约为550MB）。

我本可以使用更大的数据库，但是因为pgbench在整个数据库都写入，我想确保写表只发生在检查点发生时，而不是因为必须释放一些空间来为另一个让位。

我的硬盘支持50MB/s的写入，我能假设出在checkpoint_completion_target=0的情况下检查点大约花费11秒。

使用的配置：

• shared_buffers=1024MB–我需要大的共享内存才能监测到检查点时间

• log_checkpoints = on —我需要知道检查点什么时候发生

• checkpoint_segments=30—仅仅是在检查点发生之前WAL分段区能被写多少个

• checkpoint_timeput = 5min --检查点发生的频率

• checkpoint_warning = 5min–如果检查点发生的次数超过此次数（与超时相同）- 在日志中会发出警告。这是为了查看checkpoint_segments太少的情况。

• log_line_prefix = ‘%m %r %u %d %p’–让pg查询日志很好地添加重要信息前缀

• log_min_duration_statement =5 – 我不想记录所有查询，因为实在太多了。我只想把查询时间超过5毫秒的查询存到日志中–因此我会看到在检查点进行时是否会有此类查询数量的峰值

我做了5个测试，这5个测试中checkpointg_completion_target分别为：0，0.3，0.6，0.9和1.

这是checkpoint_completion_target = 0 时的PGDATA 分区的IO利用率：

什么是%util?来自man iostat：

向设备发出IO请求的CPU时间百分比（设备的宽带利用率）. 当此值接近100%时发生器件饱和。

并且，根据上面的图表，我能立即看到我的测试数据集太大了，因为checkpoint 占用了绝大部分时间。

所以，我重做了一次测试，用更小的数据集 - 比例为15，数据库的大小大约为300MB。

结果

checkpoint_completion_target = 0

Pgbench 在14:57:34 和15:09:34之间的对数据库施加负载，在此之前的5分钟内，服务器没有做任何事情。

这是PGDATA分区的 IO利用率：

什么是%util?来自man iostat：

向设备发出IO请求的CPU时间百分比（设备宽带利用率），当此值接近于100%时器件趋于饱和。

写入吞吐量：

最后，非常重要的价值 - 等待:

The average time (in milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.

​ 向要服务的设备发出IO请求的平均时间（以毫秒未单位）已经被提供出来了。这包括队列中的请求所花费的时间以及它们提供服务所花费的时间。

checkpoint_completion_target = 0.3

Pgbench在 15:29:44 和15:41:44之间对数据库施加负载，在此之前有5分钟，服务器没做任何事情

****

checkpoint_completion_target = 0.6

Pgbench在16:01:55 和 16:13:55之间对数据库施加负载，在此之前有5分钟，服务器没有做任何事情。

checkpoint_completion_target = 0.9

Pgbench在16:34:08 和 16:46:08之间对数据库施加负载，在此之前有5分钟，服务器没有做任何事情。

checkpoint_completion_target = 1

Pgbench在17:06:21 和 17:18:22之间对数据库施加负载，在此之前有5分钟，服务器没有做任何事情

---

这些是非常参差不齐的图表，但我希望你能看到，虽然checkpoint_complete_target在一个使用40MB写流量的大峰值中写入，但所有其他的checkpoint_Complete_target写峰值要低得多。

这些慢查询的数字和它有什么关联呢？你可能记得 - 我把每个超过5ms的查询都记在日志中了。以这些查询日志为基础，我能看到慢查询的频率。

cct	number of queries running more than
100ms	200ms	300ms	400ms	500ms
0	122	52	51	44	37
0.3	92	41	37	36	31
0.6	122	34	29	28	23
0.9	164	30	18	17	16
1	173	23	13	13	12

如您所见，增加checkpoint_completion_target可以明显减少超过200ms的查询数量。范围(100，200)ms是不同的，但我们可以看到，更长的、更具破坏性的查询在较大的CHECKPOINT_COMPLETION_TARGET中发生的可能性要小得多。

那么，增加它有什么缺点吗？

好吧，对于初学者，它会使pg_xlog目录膨胀，根据我在文章开头提到的公式。

其次 - 它不总是起作用，如果你的shared_buffers太大而且写入频繁 – pg几乎所有时间都必须设置检查点 – 就像我展示的第一个图中发生的那样，因此任何‘减速’都不会起作用。

最后，请记住，从上一个检查点开始的时间越长，在强制关机的情况下（想想：断电的情况）-- 恢复所需的时间就越长。由于较大的checkpoint_completion_target意味着越慢的checkpoints – 它还会使您（平均而言）离最后一个检查点更远–从而使最终的恢复时间更长。

事实上另一方面 – 我也真的想不出任何理由不让它等于0.9（不会让它等于1.0，是为了避免下一次检查点被推迟的情况，因为上一次检查点操作还没完成）作为调优PostgreSQL的基本改变.