Shulou(Shulou.com)06/01 Report--

Translated from 5.7 official documents

InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer pool.

Innodb performs some tasks in the background, including brushing dirty pages from the buffer pool (these pages have been changed but have not yet been written to the database file)

InnoDB starts flushing buffer pool pages when the percentage of dirty pages in the buffer pool reaches the low water mark setting defined byinnodb_max_dirty_pages_pct_lwm. This option is intended to control the ratio of dirty pages in the buffer pool and ideally prevent the percentage of dirty pages from reaching innodb_max_dirty_pages_pct. If the percentage of dirty pages in the buffer pool exceedsinnodb_max_dirty_pages_pct, InnoDB begins to aggressively flush buffer pool pages.

Start brushing dirty pages when the percentage of dirty pages in the buffer pool reaches the low water mark set by innodb_max_dirty_pages_pct_lwm. This option is designed to control the ratio of dirty pages in the buffer pool and ideally organize the percentage of dirty pages to reach the value set by innodb_max_dirty_pages_pct. If the ratio of dirty pages in the buffer pool exceeds that of innodb--max_dirty_pages_pct, innodb begins to actively brush the buffer pages.

InnoDB uses an algorithm to estimate the required rate of flushing, based on the speed of redo log generation and the current rate of flushing. The intent is to smooth overall performance by ensuring that buffer flush activity keeps up with the need to keep the buffer pool "clean". Automatically adjusting the rate of flushing can help to avoid sudden dips in throughput, when excessive buffer pool flushing limits the I/O capacity available for ordinary read and write activity.

Innodb uses an algorithm to estimate the required refresh rate, based on the transaction log generation rate and the current refresh rate. The goal is to smooth the overall performance as much as possible while ensuring that the buffer pool is' clean'. Automatically adjusting the flushing rate helps prevent excessive buffer pool flushing from limiting the I / O capacity that can be used for normal read and write activities, thereby avoiding a sudden drop in throughput.

InnoDB uses its log files in a circular fashion. Before reusing a portion of a log file, InnoDB flushes to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file, a process known as a sharp checkpoint. If a workload is write-intensive, it generates a lot of redo information, all written to the log file. If all available space in the log files is used up, a sharp checkpoint occurs, causing a temporary reduction in throughput. This situation can happen even if innodb_max_dirty_pages_pct is not reached.

Innodb uses log files in a circular manner. Before reusing this part of the log file, the dirty buffer page containing that part of the redo entry is brushed to disk using the sharp checkpoint thread. If the program is write-intensive, it will generate a lot of redo information. If the free space of the log file is used up, a sharp checkpoint occurs, resulting in a temporary reduction in throughput. This can happen even if the innodb_max_dirty_pages_pct has not been reached.

InnoDB uses a heuristic-based algorithm to avoid such a scenario, by measuring the number of dirty pages in the buffer pool and the rate at which redo is being generated. Based on these numbers, InnoDB decides how many dirty pages to flush from the buffer pool each second. This self-adapting algorithm is able to deal with sudden changes in workload.

InnoDB uses heuristic-based algorithms to avoid this by measuring the number of dirty pages in the buffer pool and the rate at which redo is generated. Based on this data, InnoDB decides how many dirty pages are refreshed from the buffer pool per second. This adaptive algorithm can deal with sudden changes in workload.

Internal benchmarking has shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.

Internal benchmark tests show that the algorithm can not only maintain throughput over time, but also significantly improve the total throughput.

Because adaptive flushing can significantly affect the I/O pattern of a workload, the innodb_adaptive_flushing configuration parameter lets you turn off this feature. The default value for innodb_adaptive_flushing is ON, enabling the adaptive flushing algorithm. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPERprivilege.

Because adaptive refresh can significantly affect the I / O mode of the workload, the innodb_adaptive_flushing configuration parameter allows you to turn this feature off. The default value is on. You can set this parameter in the configuration file or dynamically using the set global command. Super permission is required.

For information about fine-tuning InnoDB buffer pool flushing behavior, see Section 14.6.3.7, "Fine-tuning InnoDB Buffer Pool Flushing".

For more information about InnoDB I take O performance, see Section 8.5.8, "Optimizing InnoDB Disk I take O".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.