postgresql中的Background Writer

Background Writer

Beginning in PostgreSQL 8.0, there is a separate server process called the background writer , whose sole function is to issue writes of “dirty” shared buffers. The intent is that server processes handling user queries should seldom or never have to wait for a write to occur, because the background writer will do it. This arrangement also reduces the performance penalty associated with checkpoints. The background writer will continuously trickle out dirty pages to disk, so that only a few pages will need to be forced out when checkpoint time arrives, instead of the storm of dirty-buffer writes that formerly occurred at each checkpoint. However there is a net overall increase in I/O load, because where a repeatedly-dirtied page might before have been written only once per checkpoint interval, the background writer might write it several times in the same interval. In most situations a continuous low load is preferable to periodic spikes, but the parameters discussed in this subsection can be used to tune the behavior for local needs.

bgwriter_delay ( integer )

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. 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 may 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_percent ( floating point )

To reduce the probability that server processes will need to issue their own writes, the background writer tries to write buffers that are likely to be recycled soon. In each round, it examines up to bgwriter_lru_percent of the buffers that are nearest to being recycled, and writes any that are dirty. The default value is 1.0 (1% of the total number of shared buffers). This parameter can 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 many buffers will be written as a result of scanning soon-to-be-recycled buffers. The default value is five buffers. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

bgwriter_all_percent ( floating point )

To reduce the amount of work that will be needed at checkpoint time, the background writer also does a circular scan through the entire buffer pool, writing buffers that are found to be dirty. In each round, it examines up to bgwriter_all_percent of the buffers for this purpose. The default value is 0.333 (0.333% of the total number of shared buffers). With the default bgwriter_delay setting, this will allow the entire shared buffer pool to be scanned about once per minute. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

bgwriter_all_maxpages ( integer )

In each round, no more than this many buffers will be written as a result of the scan of the entire buffer pool. (If this limit is reached, the scan stops, and resumes at the next buffer during the next round.) The default value is five buffers. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

Smaller values of bgwriter_all_percent and bgwriter_all_maxpages reduce the extra I/O load caused by the background writer, but leave more work to be done at checkpoint time. To reduce load spikes at checkpoints, increase these two values. Similarly, smaller values of bgwriter_lru_percent and bgwriter_lru_maxpages 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. To disable background writing entirely, set both maxpages values and/or both percent values to zero.