MySQL & #183; Performance Optimization & #183; 5.7.6 InnoDB page flush Optimization

MySQL · performance optimization · 5.7.6 InnoDB page flush Optimization

In this article, we have introduced in detail the latest Optimization of InnoDB page flush by Oracle MySQL and Community branches. Further improvements have been made in the latest release 5.7.6. It mainly includes the following changes:

Modify 1. More precise loop Time

After srv_flushing_avg_loops is flush, Page cleaner calculates the growth rate of dirty brush and Redo LSN. Since the workload of each Page cleaner is adaptive, a flush operation may take more than 1 second.

In the new version, the time difference between the current time and the last update rate is used to determine whether to recalculate the rate. Therefore, the innodb_flushing_avg_loops parameter is equivalent to the re-computing rate every so many seconds.

Modify 2. Dirty flushing is determined based on the dirty page distribution of the buffer pool instance.

From version 5.7 onwards, multiple page cleaner threads can be configured to achieve parallel dirty flushing. In versions earlier than 5.7.6, the Page cleaner coordination thread calculates the total number of pages to be flushed and the target LSN based on the current load, and performs an equal share among multiple bp instances.

However, consider one scenario: if the load between bp instances is unbalanced, a certain instance has many dirty pages before the target LSN, but some instances have very few, therefore, back-propagation of dirty brush operations may generate accumulation. We have had a similar discussion in the webscalesql google public discussion group before. If you are interested, please take a look.

Back to the topic, in version 5.7.6, the method for calculating the number of target pages is roughly as follows:

• Calculate the percentage of I/O Capacity (pct_total) based on the current dirty page percentage and Redo LSN growth)

• Calculation target LSN:

 target_lsn = oldest_lsn + lsn_avg_rate * buf_flush_lsn_scan_factor

Oldest_lsn indicates the LSN of the oldest page in the current buffer pool, and lsn_avg_rate indicates the average rate of LSN promotion per second. buf_flush_lsn_scan_factor is currently in hardcode and the value is 3.

• Count the number of pages where each buffer pool is smaller than target_lsn. pages_for_lsn

It is preliminarily estimated that n_pages_requested = pages_for_lsn/buf_flush_lsn_scan_factor of each bp instance. The pages_for_lsn of each bp is accumulated to the sum_pages_for_lsn.

• Estimate the total number of pages to be flushed Based on io capacity:

sum_pages_for_lsn /= buf_flush_lsn_scan_factor;n_pages = (PCT_IO(pct_total) + avg_page_rate + sum_pages_for_lsn) / 3;

N_pages: If innodb_io_capacity_max is exceeded, set it to innodb_io_capacity_max.

• Round-Robin of each Buffer pool instance:

If you have enough Redo space: n_pages_requested = n_pages/srv_buf_pool_instances; otherwise: n_pages_requested = n_pages_requested * n_pages/sum_pages_for_lsn

That is to say, when the Redo space is sufficient, the dirty logic is still balanced.

In earlier versions, the dirty progress of each bp is determined based on two conditions: the target LSN and the number of pages. In version 5.7.6, in most cases, it is determined only based on the more accurate number of page refreshes requested (except for page flush of 100% io capactiy when the system is idle, crash recovery, and dirty flushing during instance shutdown)

Although the calculation formula is clear, some factor values are still confusing. It may be an ideal configuration for official tests. However, it is best to set it to configurable, which can be customized by experienced users based on their specific load conditions.

 

3. the user thread does not participate in dirty refreshing when checking the Redo Space

In earlier versions, when too many checkpoint logs are not performed, the user thread will perform the batch flush operation to push the LSN of each buffer pool instance to a specified value. If another thread in a bp instance is flush, the next instance is skipped and the flush operation fails. A Retry is returned.

When the user thread participates in the dirty brush, it is usually considered as a performance inflection point, TPS will drop sharply, and a large number of threads will fall into condtion wait and concurrent flush. Therefore, in 5.7.6, when the user thread needs to push the LSN, it no longer actively initiates dirty brush. This work will be left to the page cleaner thread. The user thread only polls each bp instance until the LSN of all bp instances exceeds the target LSN. The default sleep retry time for each round is 10000.

In fact, Percona Server uses the same policy as early as 5.6.

 

Modify 4. Set a higher priority for the page cleaner thread

In Linux, the CPU priority of the coordinator and worker threads of page cleaner is set to-20, that is, the highest priority, which is set through the set_priority function. Currently, parameter configuration is not supported.

 

Modify 5. prevent overwrite checkpoint LSN

In earlier versions, although each time a Redo log is written, it checks whether the log file contains sufficient percentage of available space, but the length of the Redo log to be written is not considered. If we operate a large number of records and generate a long Redo log record, this may cause the checkpoint LSN to be overwritten. If the crash fails to perform crash recovery safely at this time.

In the new logic, when detecting that the currently written Redo may overwrite the last checkpoint, it will enter sleep, wait for the page cleaner thread to dirty, and then perform a Redo log checkpoint again. This cycle continues until the LSN of the checkpoint is pushed to the secure position.

Reference: worklog: wl #7868, and patch

This article permanently updates the link address: