Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the basic knowledge of Innodb page clean threads in MySQL". In daily operation, I believe that many people have doubts about the basic knowledge of Innodb page clean threads in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is the basic knowledge of Innodb page clean threads in MySQL?" Next, please follow the editor to study!

I. the concept of page clean thread

The page clean thread in Innodb writes dirty data to disk, and after the dirty data is written to disk, the corresponding redo can be overwritten, and then achieve the purpose of redo recycling. Parameter 5.7 enables multiple page clean threads to serve multiple innodb buffer instances as follows:

The innodb_page_cleaners default value waschanged from 1 to 4 in MySQL 5.7. If the numberof page cleaner threads exceeds the numberof buffer pool instances, innodb_page_cleaners is automatically set to the same value asinnodb_buffer_pool_instances.

In fact, in the internal implementation, if there are four page clean threads, then there is a coordinated worker thread and three worker threads, which also do part of the work. In MySQL, we can look up these worker threads through statements:

| | 17 | 57982 | innodb/page_cleaner_thread | NULL | BACKGROUND | NULL | NULL | | 18 | 57983 | innodb/page_cleaner_thread | NULL | BACKGROUND | NULL | | 19 | 57984 | innodb/page_cleaner_thread | NULL | BACKGROUND | NULL | NULL | 20 | 57985 | innodb/page_cleaner_thread | NULL | BACKGROUND | NULL | NULL |

In fact, in my analysis, I found that all worker threads constantly rotate each slot corresponding to buffer instance until all buffer instance have been scrubbed, and that worker thread is not fixed to serve that buffer instance instance.

Second, refresh mode

Generally speaking, there are three ways for page clean threads to refresh:

1. Active refresh

Generally speaking, our online database is generally active, as long as there are statements such as DML/DDL will be active, but SELECT is not included in the active state. Refresh in this state will enable a coordinated worker thread and multiple worker threads to work at the same time. In this state, the number of blocks to be refreshed is (page_cleaner_flush_pages_recommendation function):

(number of pages calculated based on parameters + number of pages previously refreshed per second + number of blocks that need to be refreshed based on target lsn) / 3

In fact, what you need to pay attention to here is (the number of pages calculated based on the parameters). The algorithm is roughly as follows (af_get_pct_for_dirty function):

If innodb_max_dirty_pages_pct_lwm is not on: if dirty data ratio is greater than or equal to innodb_max_dirty_pages_pct setting: return 100% if innodb_max_dirty_pages_pct_lwm is on: if dirty data ratio is greater than or equal to innodb_max_dirty_pages_pct_lwm: return (dirty data ratio * 100) / (innodb_max_dirty_pages_pct+1) such a percentage

We calculate the above percentage as A, which is also related to the percentage calculated by innodb_adaptive_flushing and innodb_adaptive_flushing_lwm. We write it down as B (calculated by af_get_pct_for_lsn function), but because the parameter innodb_cleaner_lsn_age_factor is set to high_checkpoint by default, this percentage is relatively small. For the specific algorithm, see later, and its final value.

The number of pages calculated based on the parameter = MAX (A _ Magi B) * innodb_io_capacity

2. Idle refresh

In general, in addition to active refresh is idle refresh, idle case because the server IO should be relatively idle, so Innodb uses the coordination worker thread itself to refresh, refresh the number of blocks is relatively simple to calculate the value set by innodb_io_capacity.

3. Synchronous refresh

Synchronous refresh is blocking refresh, all user threads that need to write dirty database will be blocked, which is a very serious situation. At checkpoint,

The redo is checked or whether the redo is in a safe position during the execution of the DML statement. This is done by calling the log_free_check function. If you think that there are too many dirty blocks and the redo is already in an unsafe position (log_checkpoint_margin), then the synchronous refresh will be awakened.

This section will also be mentioned in the source section.

Third, about a warning

The warning is as follows:

Page_cleaner: 1000ms intended loop took * * ms. The settings might not be optimal. (flushed= "*", during the time.)

In fact, this warning comes from two refresh time tests:

This refresh time-the last refresh time > 1 second (sleep time) + 3 seconds will report a warning.

This warning is generally the result of insufficient IO capabilities or insufficient parameters optimization. With the above basis, we know that the following actions should be done here:

Innodb_io_capacity should be reduced.

Innodb_max_dirty_pages_pct should be reduced.

If innodb_max_dirty_pages_pct_lwm is set, you should consider reducing it.

Innodb_io_capacity_max considers reducing the calculation related to the percentage B mentioned above (af_get_pct_for_lsn function)

The purpose of the reduction is to reduce the amount of each refresh and make the number of blocks per refresh more average. This prevents the page clean thread from explosively refreshing the dirty database and blocking the IO channel. If it doesn't work after a slow adjustment, then IO really can't handle it.

At this point, the study on "what is the basic knowledge of Innodb page clean threads in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.