Postgresql之background writer processes进程

在postgresql的进程中，有两个进程负责将shared_buffers的脏页刷新到磁盘中，分别是checkpointer processes和background writer processes。两者又有不同分工，checkpointer processes在checkpoint开始时，记录一个checkpoint record 到WAL文件，并开始刷新全部脏页。本文主要讲述background writer processes进程。

background writer processes进程会减少检查点开始时大量脏页刷新对机器IO的影响，background writer processes进程会持续的，一点一点的刷新脏页，对数据库IO性能的影响几乎可以忽略。控制background writer processes进程刷新脏页主要有以下4个参数。

There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. It writes shared buffers so server processes handling user queries seldom or never need to wait for a write to occur. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.

bgwriter_delay (integer)

每隔bgwriter_delay毫秒，background writer进程会启动一次刷新脏页的工作。也就是说完成一次刷新脏页工作之后，background writer进程会休眠bgwriter_delay毫秒。默认值是200毫秒。

Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). It then sleeps for bgwriter_delay milliseconds, and repeats. When there are no dirty buffers in the buffer pool, though, it goes into a longer sleep regardless of bgwriter_delay. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting bgwriter_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

bgwriter_lru_maxpages (integer)

每次启动刷新脏页的工作时，最多刷新的脏页数。设置为0代表关闭background writer进程的工作。默认值是100页。

In each round, no more than this many buffers will be written by the background writer. Setting this to zero disables background writing. (Note that checkpoints, which are managed by a separate, dedicated auxiliary process, are unaffected.) The default value is 100 buffers. This parameter can only be set in the postgresql.conf file or on the server command line.

bgwriter_lru_multiplier (floating point)

此参数是个乘数因子。假设在最近的几次background writer进程每次工作的时间段内，后台进程在每个时间段内需要的buffer page平均值是a。则下次background writer进程启动刷新脏页数为：a * bgwriter_lru_multiplier个，但是刷新页面数不得超过bgwriter_lru_maxpages个。默认值是2。

The number of dirty buffers written in each round is based on the number of new buffers that have been needed by server processes during recent rounds. The average recent need is multiplied by bgwriter_lru_multiplier to arrive at an estimate of the number of buffers that will be needed during the next round. Dirty buffers are written until there are that many clean, reusable buffers available. (However, no more than bgwriter_lru_maxpages buffers will be written per round.) Thus, a setting of 1.0 represents a “just in time” policy of writing exactly the number of buffers predicted to be needed. Larger values provide some cushion against spikes in demand, while smaller values intentionally leave writes to be done by server processes. The default is 2.0. This parameter can only be set in the postgresql.conf file or on the server command line.

bgwriter_flush_after (integer)

background writer进程刷新了多少bytes数据之后，开始调用操作系统的sync操作，需要将数据刷新到硬盘。

Whenever more than bgwriter_flush_after bytes have been written by the background writer, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of stalls when an fsync is issued at the end of a checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger than shared_buffers, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. The valid range is between 0, which disables forced writeback, and 2MB. The default is 512kB on Linux, 0 elsewhere. (If BLCKSZ is not 8kB, the default and maximum values scale proportionally to it.) This parameter can only be set in the postgresql.conf file or on the server command line.

Smaller values of bgwriter_lru_maxpages and bgwriter_lru_multiplier reduce the extra I/O load caused by the background writer, but make it more likely that server processes will have to issue writes for themselves, delaying interactive queries.

设置较小的bgwriter_lru_maxpages and bgwriter_lru_multiplier当然会减小background writer进程对IO的影响，但是没有足够的空闲shared_buffers   会导致后台进程在运行SQL时必须自己刷新shared_buffers的脏页到后台存储中，影响运行效率。

转载于:https://my.oschina.net/207miner/blog/3016242