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

This article mainly explains "Innodb page clean thread analysis in MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "Innodb page clean thread analysis in MySQL".

1. Data structure and entry function 1. Data structure

Page_cleaner_t: there is only one entire Innodb, which contains information about the entire page clean thread. It contains a pointer to page_cleaner_slot_t.

The variable name implies that mutex is used to protect the entire page_cleaner_t structure and page_cleaner_slot_t structure. When you need to modify the structure information, you need to obtain this mutex, such as is_requested a condition variable in the pc_request function to wake up the worker thread blocked above this condition, is_finished a condition variable. This value is used to notify the coordinating thread that the refresh work has been completed. The total number of worker threads currently existing in n_workers, whether dirty data refresh is required, needs to be refreshed to the location of lsn. When synchronous refresh is needed, this value will be given to ensure that logs smaller than this lsn have completed the number of n_slots slots of disk refreshing work. The number of slots is the same as the number of buffer instance slots n_slots_requested is currently in the state of refresh (PAGE_CLEANER_STATE_REQUESTED) the number of slots n_slots_flushing is currently in the state of refresh (PAGE_CLEANER_STATE_FLUSHING) the number of slots currently in the state of refresh completed (PAGE_CLEANER_STATE_FINISHED) flush_time the whole (in innodb buffer) refresh consumed Time (cumulative page_cleaner- > flush_time + = ut_time_ms ()-tm ) flush_pass the total number of refreshes (in innodb buffer) (cumulative page_cleaner- > flush_pass++;) the slots pointer points to the actual slot is_running Boolean value. If innodb is turned off, it will be set to false to refresh dirty data.

Page_cleaner_slot_t: each buffer instance contains a structure that, when the page clean worker thread refreshes, each thread polls to detect each slot until it finds a slot that has not been refreshed by other page clean threads until each slot (buffer instance) is refreshed. Refer to the pc_flush_slot function.

The variable name means one of the state states PAGE_CLEANER_STATE_REQUESTED, PAGE_CLEANER_STATE_FLUSHING, and PAGE_CLEANER_STATE_FINISHED n_pages_requested total number of blocks that need to be refreshed in this slot n_flushed_list number of blocks that have been refreshed succeeded_list Boolean value Whether the refresh of the flush_list_time slot is completed (cumulative reference pc_flush_slot function) the number of refresh operations performed by the flush_list_pass slot (cumulative reference pc_flush_slot function) 2. Entry function

Coordinate worker thread entry: buf_flush_page_cleaner_coordinator

Worker thread entry: buf_flush_page_cleaner_worker

II. Analysis of the main cycle

It is implemented by the function buf_flush_page_cleaner_coordinator. The actual normal operation of the work is included in the while (srv_shutdown_state = = SRV_SHUTDOWN_NONE) this big cycle.

1. Judge whether you need to sleep for 1 second.

First of all, if there is no active change buffer and no physical blocks of pending, and the number of blocks refreshed last time is 0

You don't need to sleep for 1 second:

If (srv_check_activity (last_activity) | | buf_get_n_pending_read_ios () | | n_flushed = = 0) {ret_sleep = pc_sleep_if_needed (next_loop_time, sig_count); / / one second sleep if (srv_shutdown_state! = SRV_SHUTDOWN_NONE) {break }} else if (ut_time_ms () > next_loop_time) {/ / if the current time is greater than the last refresh time + 1 second, then set to OS_SYNC_TIME_EXCEEDED ret_sleep = OS_SYNC_TIME_EXCEEDED;} else {ret_sleep = 0;}

But this sleep can be awakened, for example, a synchronous refresh should wake it up (buf_flush_request_force function). Reference function os_event::wait_time_low

2. Warning of insufficient IO capabilities

As described earlier, the following warning is generated here:

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

Source code snippet:

If (curr_time > next_loop_time + 3000) {/ / if the refresh time is greater than the last time + 1 second + 3 seconds, report info if (warn_count = = 0) {ib::info () mutex; ulint flush_tm = page_cleaner- > flush_time; ulint flush_pass = page_cleaner- > flush_pass; page_cleaner- > flush_time = 0 Page_cleaner- > flush_pass = 0; ulint list_tm = 0; ulint list_pass = 0; for (ulint I = 0; I

< page_cleaner->

) {/ / scan all slots page_cleaner_slot_t* slot; slot = & page_cleaner- > slots [I]; list_tm + = slot- > flush_list_time; list_pass + = slot- > flush_list_pass; slot- > flush_list_time = 0; slot- > flush_list_pass = 0 } mutex_exit (& page_cleaner- > mutex); oldest_lsn = buf_pool_get_oldest_modification (); / / get the oldest ls ut_ad in flush list (oldest_lsn oldest_lsn? Cur_lsn-oldest_lsn: 0; / get the difference between the current LSN and the oldest LSN pct_for_dirty = af_get_pct_for_dirty (); / / calculate a refresh percentage (for example, 100)!! Focus on pct_for_lsn = af_get_pct_for_lsn (age); / / calculate the percentage of lsn (l listed as 4.5) pct_total = ut_max (pct_for_dirty, pct_for_lsn); / / take their large values / * Estimate pages to be flushed for the lsn progress * / / calculate target_lsn ulint sum_pages_for_lsn = 0 Lsn_t target_lsn = oldest_lsn + lsn_avg_rate * buf_flush_lsn_scan_factor; / / calculate that the target lsn and target_lsnbuf_flush_lsn_scan_factor for the next refresh is a fixed value of 3 for (ulint I = 0; I

< srv_buf_pool_instances; i++) {//循环整个buffer instance找到小于target_lsn的脏块 buf_pool_t* buf_pool = buf_pool_from_array(i); ulint pages_for_lsn = 0; buf_flush_list_mutex_enter(buf_pool); for (buf_page_t* b = UT_LIST_GET_LAST(buf_pool->

Flush_list) / / the flush list at the end of each innodb buffer is scanned and inserted into the head? B! = NULL; b = UT_LIST_GET_PREV (list, b)) {if (b-> oldest_modification > target_lsn) {break;} + + pages_for_lsn; / / the page count of flush list less than this target lsn in an innodb buffer instance} buf_flush_list_mutex_exit (buf_pool) Sum_pages_for_lsn + = pages_for_lsn; / / here summarizes the total number of page with flush list less than this target lsn in all innodb buffer instances mutex_enter (& page_cleaner- > mutex); ut_ad (page_cleaner- > slotts [I] .state = = PAGE_CLEANER_STATE_NONE) / / assert that all slots are not refreshed page_cleaner- > slots [I] .n _ pages_requested = pages_for_lsn / buf_flush_lsn_scan_factor + 1; / / confirm the npagesrequestd value mutex_exit (& page_cleaner- > mutex) of the slot;} sum_pages_for_lsn / = buf_flush_lsn_scan_factor / / buf_flush_lsn_scan_factor is the fixed value 3 / * Cap the maximum IO capacity that we are going to use by max_io_capacity. Limit the value to avoid too quick increase * / n_pages = PCT_IO (pct_total); / / the number of chunks to be refreshed based on the pct_total and srv_io_capacity parameters obtained earlier! The second calculation parameter is generated. If (age

< log_get_max_modified_age_async()) { //如果日质量小于 异步刷新的范畴 ulint pages_for_lsn = std::min(sum_pages_for_lsn, srv_max_io_capacity * 2); //即便是需要刷新的块数很多，最多只能刷max_io_capacity*2的数量!!!第三个计算参数生成 n_pages = (n_pages + avg_page_rate + pages_for_lsn) / 3; // 3部分组成 1、根据参数计算出来的IO能力 2、以往每秒刷新页的数量 3、根据target lsn 计算出来的一个需要刷新的块数 } if (n_pages >

Srv_max_io_capacity) {n_pages = srv_max_io_capacity;} return (n_pages);}

This function finally calculates the blocks that need to be refreshed, in which the key functions for calculating the refresh ratio are af_get_pct_for_dirty and af_get_pct_for_lsn. The code comments are given below. In fact, the algorithm in the previous article comes from af_get_pct_for_dirty.

4. Af_get_pct_for_dirty and af_get_pct_for_lsn functions

Af_get_pct_for_dirty function

Double dirty_pct = buf_get_modified_ratio_pct (); / / percentage of modified blocks / total blocks remember dirty data ratio if (dirty_pct = = 0.0) {/ * No pages modified * / return (0) } ut_a (srv_max_dirty_pages_pct_lwm = srv_max_buf_pool_modified_pct) {/ / if the dirty data ratio is greater than innodb_max_dirty_pages_pct, the return ratio is 100% / * We have crossed the high water mark of dirty pages In this case we start flushing at 100% of innodb_io_capacity. * / return;}} else if (dirty_pct > = srv_max_dirty_pages_pct_lwm) {/ / if innodb_max_dirty_pages_pct_lwm is set and the dirty data ratio is greater than / * We should start flushing pages gradually. * / / set the innodb_max_dirty_pages_pct_lwm parameter return (static_cast ((dirty_pct * 100) / (srv_max_buf_pool_modified_pct + 1)); / / return (dirty data ratio / (innodb_max_dirty_pages_pct+1)) * 100 is also a ratio such as (45Universe 76) * 100} return (0); / / otherwise return 0

Af_get_pct_for_lsn function:

Notice that the innodb_cleaner_lsn_age_factor parameter is set to high_checkpoint by default, and you can see that the algorithm is finally divided by 700.5, so the ratio calculated by this function is generally small.

Lsn_t af_lwm = (srv_adaptive_flushing_lwm * log_get_capacity ()) / 100X hand / srv_adaptive_flushing_lwm=10 then it is about logtotalsize* (9 + 10) * (1 + + 10) 943349 to calculate a low water mark if (age).

< af_lwm) { //如果当前生成的redo 小于了 low water master 则返回0 也就是说 redo日志量生成量不高则不需要权衡 /* No adaptive flushing. */ //可以看出这里和redo设置的大小有关，如果redo文件设置越大则af_lwm越大，触发权衡的机率越小 return(0); } max_async_age = log_get_max_modified_age_async(); //获取需要异步刷新的的位置 大约为logtotalsize*(9/10)*(7/8) if (age < max_async_age && !srv_adaptive_flushing) { //如果小于异步刷新 且 自适应flush 没有开启 /* We have still not reached the max_async point and the user has disabled adaptive flushing. */ return(0); } /* If we are here then we know that either: 1) User has enabled adaptive flushing 2) User may have disabled adaptive flushing but we have reached max_async_age. */ lsn_age_factor = (age * 100) / max_async_age; //比率lsn_age_factor = (本次刷新的日志量/(logtotalsize*(9/10)*(7/8))) ut_ad(srv_max_io_capacity >

= srv_io_capacity) Switch ((srv_cleaner_lsn_age_factor_t) srv_cleaner_lsn_age_factor) {case SRV_CLEANER_LSN_AGE_FACTOR_LEGACY: return (static_cast ((srv_max_io_capacity / srv_io_capacity) * (lsn_age_factor * sqrt ((double) lsn_age_factor) / 7.5)) / / 430 case SRV_CLEANER_LSN_AGE_FACTOR_HIGH_CHECKPOINT: / / innodb_cleaner_lsn_age_factor parameter defaults to high_checkpoint return (static_cast ((srv_max_io_capacity / srv_io_capacity) / / (max) _ io_cap / io_cap) * (sqrt (lsn_age_factor) * lsn_age_factor*lsn_age_factor) / 700.5 * (lsn_age_factor*lsn_age_factor / / (10 * (3.3 / 10 / 10)) / 700.5) / / Thank you for reading, the above is the content of "Innodb page clean thread analysis in MySQL". After the study of this article, I believe you have a deeper understanding of the problem of Innodb page clean thread analysis in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.