后台写入进程（Background Writer）

功能：将共享内存中的内容基于算法周期性的写入磁盘。当启动子进程完成或者归档恢复启动的时候，bgwriter由postmaster主进程启动，之后一直运行，直到postmaster命令它终止。
如果太快：一个数据块可能会被修改很多次，太快的情况下，每修改一次都要写入磁盘中，是一个相当浪费资源和性能的过程。
如果太慢：如果有新的查询或者更新等操作需要用内存保存从磁盘中读取数据，而此时内存却没有足够的空间，就需要把“脏数据”刷入磁盘来释放一部分内存。那么此时的查询或者更新操作就会发生“等待”，性能体验降低。
下面介绍三个常用的控制参数
bgwriter_delay (integer)
后台写入器周期性执行“脏数据”写入时候的延迟值。默认为200ms。即为此次写入操作执行完后等待200ms继续下次脏数据写入。在一些操作系统上该值只能设置为10的倍数，若设置为213，则自动默认为220.
bgwriter_lru_maxpages (integer)
每次循环写入磁盘的最大缓冲区数量，设置为0的话，后台写入进程将被禁用，注意的是，该值不会对检查点进程产生影响。
bgwriter_lru_multiplier (floating point)
该值可用来控制下轮需要的缓冲区数量，在每轮中写入的脏缓冲区的数量是基于最近几轮服务器进程所需的新缓冲区的数量，如果最近写入的脏缓冲区的数量的平均值为10的话，那么下轮需要的缓冲区数量即为2*10。当然，每次循环的值不会超出bgwriter_lru_maxpages，我建议将此值设置为总是大约1。

bgwriter_flush_after (integer)此处不多做介绍，后期有机会单独详解，感兴趣的读者可以参考官方文档理解。

 附官方文档：https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html

Background Writer

There is a separate server process calledthe background writer, whose function is to issue writes of "dirty"(new or modified) shared buffers. It writes shared buffers so server processeshandling 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 onceper checkpoint interval, the background writer might write it several times asit is dirtied in the same interval. The parameters discussed in this subsectioncan be used to tune the behavior for local needs.

 

bgwriter_delay (integer)

Specifies the delay between activity roundsfor the background writer. In each round the writer issues writes for somenumber of dirty buffers (controllable by the following parameters). It thensleeps for bgwriter_delay milliseconds, and repeats. When there are no dirtybuffers in the buffer pool, though, it goes into a longer sleep regardless ofbgwriter_delay. The default value is 200 milliseconds (200ms). Note that onmany 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 thesame results as setting it to the next higher multiple of 10. This parametercan only be set in the postgresql.conf file or on the server command line.

 

bgwriter_lru_maxpages (integer)

In each round, no more than this manybuffers will be written by the background writer. Setting this to zero disablesbackground 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 servercommand line.

 

bgwriter_lru_multiplier (floating point)

The number of dirty buffers written in eachround is based on the number of new buffers that have been needed by serverprocesses during recent rounds. The average recent need is multiplied bybgwriter_lru_multiplier to arrive at an estimate of the number of buffers thatwill be needed during the next round. Dirty buffers are written until there arethat many clean, reusable buffers available. (However, no more thanbgwriter_lru_maxpages buffers will be written per round.) Thus, a setting of1.0 represents a "just in time" policy of writing exactly the numberof buffers predicted to be needed. Larger values provide some cushion againstspikes in demand, while smaller values intentionally leave writes to be done byserver processes. The default is 2.0. This parameter can only be set in thepostgresql.conf file or on the server command line.

 

bgwriter_flush_after (integer)

Whenever more than bgwriter_flush_afterbytes have been written by the bgwriter, attempt to force the OS to issue thesewrites to the underlying storage. Doing so will limit the amount of dirty datain the kernel's page cache, reducing the likelihood of stalls when an fsync isissued at the end of a checkpoint, or when the OS writes data back in largerbatches in the background. Often that will result in greatly reducedtransaction latency, but there also are some cases, especially with workloadsthat are bigger than shared_buffers, but smaller than the OS's page cache,where performance might degrade. This setting may have no effect on someplatforms. The valid range is between 0, which disables forced writeback, and2MB. The default is 512kB on Linux, 0 elsewhere. (If BLCKSZ is not 8kB, thedefault and maximum values scale proportionally to it.) This parameter can onlybe set in the postgresql.conf file or on the server command line.

 

Smaller values of bgwriter_lru_maxpages andbgwriter_lru_multiplier reduce the extra I/O load caused by the backgroundwriter, but make it more likely that server processes will have to issue writesfor themselves, delaying interactive queries.

BY  海无涯