Background Writer
This section describes background writer parameters. The background writer thread is used to write dirty data (new or modified data) in shared buffers to disks. This mechanism ensures that database processes seldom or never need to wait for a write action to occur when handling user queries.
It also mitigates performance deterioration caused by checkpoints because the background writer continues to write dirty pages to disks and only several pages need to be written to disks at each checkpoint. This mechanism, however, increases the overall net I/O load because while a repeatedly-dirtied page may otherwise be written only once per checkpoint interval, the background writer may write it several times as it is dirtied in the same interval. In most cases, continuous light loads are preferred, instead of periodic load peaks. The parameters discussed in this section can be set based on actual requirements.
bgwriter_delay
Parameter description: Specifies the interval for periodically scanning dirty data in the buffer block by block. In full checkpoint mode, the bgwriter_lru_maxpages parameter is used to control the amount of data written each time. After the bgwriter_lru_maxpages parameter is hibernated for bgwriter_delay milliseconds, the checkpoint is started again. In incremental checkpoint mode, the number of target idle buffer pages is calculated based on the configured candidate_buf_percent_target. When candidate buffer pages are insufficient, 10 GB buffer pages are scanned every bgwriter_delay ms. When dirty pages are detected, the dirty pages are flushed to disks and then added to the candidate linked list, when a clean page is encountered, it is directly put into the candidate linked list. In many systems, the effective resolution of the sleep time is 10 milliseconds. Therefore, setting this parameter to a value that is not a multiple of 10 has the same effect as setting it to a value multiple of 10.
Parameter type: integer.
Unit: millisecond
Value range: 10 to 10000
Default value: 2000 (2s)
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion:
- Incremental checkpoint mode: If the data volume is multiple times the value of shared_buffers, the value of bgwriter_delay cannot be greater than 2s. If the data volume is smaller than the value of shared_buffers, the value of bgwriter_delay can be increased to save I/O resources.
- Full checkpoint mode: Retain the default value. If the disk capability is poor, increase the value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
candidate_buf_percent_target
Parameter description: Specifies the expected percentage of the candidate linked list of clean pages to the total number of pages in the buffer when incremental checkpointing is enabled. If the proportion of pages in the linked list of clean pages is less than the value of this parameter, the bgwriter thread starts, scans the buffer pages, adds clean pages to the linked list, flushes dirty pages to disks, and then adds dirty pages to the linked list.
Parameter type: floating point.
Unit: none
Value range: 0.1 to 0.85
Default value: 0.3
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: This parameter directly affects the number of pages flushed by the background writer thread. If the data volume is greater than the value of shared_buffers and candidate_buf_percent_target is set to an excessively small value, the performance is affected. If the data volume is twice or more of the value of shared_buffers, the value of this parameter cannot be smaller than the default value. In other scenarios, to reduce I/Os, set this parameter to a smaller value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
bgwriter_lru_maxpages
Parameter description: Specifies the maximum number of dirty pages that can be flushed by the background writer thread each time.
Parameter type: integer.
Unit: none
Value range: 0 to 1000. 0 indicates that the background writer is disabled. This setting does not affect checkpoints.
Default value: 100
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
bgwriter_lru_multiplier
Parameter description: The database kernel sets shared_buffers based on the current buffer size. If the value of shared_buffers is too small or too large when the kernel runs services, you can adjust the value of bgwriter_lru_multiplier to provide the optimal recommended value, the recommended value is the product of the value of this parameter and the value of shared_buffers. After you set this parameter to the recommended value, the setting takes effect when the database is restarted.
Parameter type: floating point.
Unit: none
Value range: 0 to 10
Default value: 2
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion:
- The number of dirty buffers written in each round depends on the number of buffers used by server processes during recent rounds. The estimated number of buffers used in the next round is calculated using the following formula: Average number of recently used buffers x bgwriter_lru_multiplier. The background writer writes dirty buffers until sufficient, clean, and reusable buffers are available. The number of buffers the background writer writes in each round is always equal to or less than the value of bgwriter_lru_maxpages.
- Therefore, the value 1.0 of bgwriter_lru_multiplier indicates a just-in-time policy of writing exactly the number of dirty buffers predicted to be required. Larger values provide some cushion against spikes in demand, whereas smaller values intentionally leave more writes to be done by server processes.
- 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 response to queries.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
pagewriter_thread_num
Parameter description: Specifies the number of threads for backend page flushing after incremental checkpointing is enabled. Data is flushed to disks based on the sequence in which dirty pages are set to dirty, clean buffer pages are generated, and recovery points for checkpointing are pushed.
Parameter type: integer.
Unit: none
Value range: 1 to 16
Default value: 4
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
dirty_page_percent_max
Parameter description: Specifies the maximum expected percentage of dirty pages to shared_buffers after the incremental checkpoint is enabled. When the value of this parameter is reached, the background page flush thread flushes dirty pages based on the value of max_io_capacity.
Parameter type: floating point.
Unit: none
Value range: 0.1 to 1
Default value: 0.9
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
pagewriter_sleep
Parameter description: Specifies the dirty data flush period of the pagewriter page flush thread in incremental checkpoint mode.
Parameter type: integer.
Unit: millisecond
Value range: 0 to 3600000
Default value: 2000 (2s)
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Do not set this parameter to a value greater than 2s. If dirty pages are generated quickly, you are advised to set this parameter to a value ranging from 100 ms to 500 ms. If this parameter is set to a large value, redo points are updated slowly, affecting Xlog recycling.
Risks and impacts of improper settings: If the value is too large, the page flushing speed decreases and redo points are pushed too slowly, affecting Xlog recycling. In addition, the instance needs to wait for the time specified by this parameter when being stopped. As a result, the process exit times out.
max_io_capacity
Parameter description: Specifies the maximum I/Os per second for the background writer to flush dirty pages in batches.
Parameter type: integer.
Unit: kB
Value range: 30720 to 10485760
Default value: 512000 (that is, 500 MB)
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. If the value of the parameter does not contain a unit, the default unit is KB. The unit can also be MB or GB. For example, max_io_capacity=512000, max_io_capacity=512000kB and max_io_capacity=500MB indicate that max_io_capacity is set to 500 MB.
Setting suggestion: Set this parameter based on the service scenario and disk I/O capability of the host. For example, if the disk I/O bandwidth is 500 MB, you are advised to set the bandwidth to a value less than 500 MB. If a shorter RTO is required or a large capacity is required, you can increase the value of max_io_capacity, for example, 2 GB.
Risks and impacts of improper settings: If the value is too small, the dirty page flushing speed is slower than the dirty page generation speed. As a result, the dirty pages in the buffer are full and the performance deteriorates. If the value is too large, write amplification occurs, affecting the service life of the storage device.
enable_consider_usecount
Parameter description: Specifies whether the page eviction algorithm of the buffer considers the page popularity.
Parameter type: Boolean.
Unit: none
Value range:
- on: The page popularity is considered.
- off: The page popularity is not considered.
Default value: off
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Enable this parameter in scenarios where single-page elimination is frequently performed, such as large-capacity scenarios.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
dw_file_num
Parameter description: Specifies the number of batch doublewrite files.
Parameter type: integer.
Unit: none
Value range: 1 to 16
Default value: 1
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: The value of this parameter is related to pagewriter_thread_num and cannot be greater than that of pagewriter_thread_num. If the value of dw_file_num is too large, the value is internally corrected to the value of pagewriter_thread_num.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
dw_file_size
Parameter description: Specifies the size of each doublewrite file.
Parameter type: integer.
Unit: MB
Value range: 32 to 256
Default value: 256
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
postmaster_parallel_init_thread_num
Parameter description: Specifies the number of parallel threads that can be used when the database is started.
Parameter type: integer.
Unit: none
Value range: 1 to 256
Default value: 16
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: This parameter indicates the number of threads for parallel initialization. Therefore, you are advised to set this parameter to a value less than or equal to the number of CPU cores minus 1.
Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
