在MySQL的幫助文檔中Tuning InnoDB Buffer Pool Flushing提到, innodb_adaptive_flushing_lwm
,innodb_max_dirty_pages_pct_lwm
, innodb_io_capacity_max
, 和innodb_flushing_avg_loops
. 公式決定了Adaptive Flush刷入的page數。
“For systems with constant heavy workloads, or workloads that fluctuate widely, several configuration options let you fine-tune the flushing behavior for InnoDB
tables: innodb_adaptive_flushing_lwm
,innodb_max_dirty_pages_pct_lwm
, innodb_io_capacity_max
, and innodb_flushing_avg_loops
. These options feed into the formula used by the innodb_adaptive_flushing
option.”
Innodb_adaptive_flush_lwm:自適應flush機制的低水位。
innodb_max_dirty_page_pct_lwm:髒頁的低水位,用來控制buffer pool髒頁比率。
innodb_io_capacity_max:redo log最大容量,如果不設置是innodb_io_capacity的兩倍。
innodb_flushing_avg_loops:n次循環後,重新計算平均刷新的dirty
page和LSN。
要刷新多少page和lsn主要代碼在af_get_pct_for_dirty,af_get_pct_for_lsn中
主要控制adaptive flush的代碼位於buf0flu.cc的af_get_pct_for_lsn中:
1.先判斷redo log的容量是否到了innodb_adaptive_flushing_lwm
低水位閥值。
2.是否配置了adaptive flush或者age超過了異步刷新的閥值。
3.lsn_age_factor=age占異步刷新閥值的比例。
4.要被刷新的比率=innodb_io_capacity_max/innodb_io_capacity*lsn_age_factor*
sqrt(innodb_io_capacity)/7.5
static ulint af_get_pct_for_lsn( /*===============*/ lsn_t age) /*!< in: current age of LSN. */ { lsn_t max_async_age; lsn_t lsn_age_factor; lsn_t af_lwm=(srv_adaptive_flushing_lwm *log_get_capacity())/100; if(age<af_lwm){ /* No adaptive flushing. */ return(0); } max_async_age=log_get_max_modified_age_async(); if(age<max_async_age&&!srv_adaptive_flushing){ /* 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; ut_ad(srv_max_io_capacity>=srv_io_capacity); return(static_cast<ulint>( ((srv_max_io_capacity/srv_io_capacity) *(lsn_age_factor*sqrt((double)lsn_age_factor))) /7.5)); }
和要刷新多少髒頁有關的函數是af_get_pct_for_dirty:
1.如果有髒頁,並且innodb_max_dirty_pages_pct=0立馬刷新。
2.如果沒有設置innodb_max_dirty_pages_pct_lwm,並且髒頁比率超過innodb_max_dirty_pages_pct,立馬刷新。
3.設置了innodb_max_dirty_pages_pct_lwm並且髒頁比率比低水位大,af_get_pct_for_dirty
=髒頁比率/(innodb_max_dirty_pages_pct+1)
static ulint af_get_pct_for_dirty() /*==================*/ { ulintdirty_pct=buf_get_modified_ratio_pct(); if(dirty_pct>0&&srv_max_buf_pool_modified_pct==0){ return(100); } ut_a(srv_max_dirty_pages_pct_lwm <=srv_max_buf_pool_modified_pct); if(srv_max_dirty_pages_pct_lwm==0){ /* The user has not set the option to preflush dirty pages as we approach the high water mark. */ if(dirty_pct>srv_max_buf_pool_modified_pct){ /* We have crossed the high water mark of dirty pages In this case we start flushing at 100% of innodb_io_capacity. */ return(100); } }elseif(dirty_pct>srv_max_dirty_pages_pct_lwm){ /* We should start flushing pages gradually. */ return((dirty_pct*100) /(srv_max_buf_pool_modified_pct+1)); } return(0); }
要刷新的page=PCT_IO(max(af_get_pct_for_dirty,af_get_pct_for_lsn))+avg_page(由innodb_flushing_avg_loops到期計算得到)。
要刷新到的LSN= oldest_lsn+((這次要刷新的page/上次已經刷新的page)+1)*avg_lsn(由innodb_flushing_avg_loops到期計算得到)
幫助文檔還提到innodb_flushing_avg_loops越大,也意味著adaptive flush越慢,但是從代碼看,innodb_flushing_avg_loops會影響所有的flush不單單是adaptive。當寫入變大,innodb_flushing_avg_loops不變或者變大,是有可能導致flush跟不上。
static ulint page_cleaner_flush_pages_if_needed(void) /*====================================*/ { staticlsn_t lsn_avg_rate=0; staticlsn_t prev_lsn=0; staticlsn_t last_lsn=0; staticulint sum_pages=0; staticulint last_pages=0; staticulint prev_pages=0; staticulint avg_page_rate=0; staticulint n_iterations=0; …… cur_lsn=log_get_lsn(); if(prev_lsn==0){ /* First time around. */ prev_lsn=cur_lsn; return(0); } if(prev_lsn==cur_lsn){ return(0); } /* We update our variables every srv_flushing_avg_loops iterations to smooth out transition in workload. */ if(++n_iterations>=srv_flushing_avg_loops){ avg_page_rate=((sum_pages/srv_flushing_avg_loops) +avg_page_rate)/2; /* How much LSN we have generated since last call. */ lsn_rate=(cur_lsn-prev_lsn)/srv_flushing_avg_loops; lsn_avg_rate=(lsn_avg_rate+lsn_rate)/2; prev_lsn=cur_lsn; n_iterations=0; sum_pages=0; } oldest_lsn=buf_pool_get_oldest_modification(); ut_ad(oldest_lsn<=log_get_lsn()); age=cur_lsn>oldest_lsn?cur_lsn-oldest_lsn:0; pct_for_dirty=af_get_pct_for_dirty(); pct_for_lsn=af_get_pct_for_lsn(age); pct_total=ut_max(pct_for_dirty,pct_for_lsn); /* Cap the maximum IO capacity that we are going to use by max_io_capacity. */ n_pages=(PCT_IO(pct_total)+avg_page_rate)/2; if(n_pages>srv_max_io_capacity){ n_pages=srv_max_io_capacity; } if(last_pages&&cur_lsn-last_lsn>lsn_avg_rate/2){ age_factor=static_cast<int>(prev_pages/last_pages); } …… prev_pages=n_pages; n_pages=page_cleaner_do_flush_batch( n_pages,oldest_lsn+lsn_avg_rate*(age_factor+1)); last_lsn=cur_lsn; last_pages=n_pages+1; …… if(n_pages){ …… sum_pages+=n_pages; } return(n_pages); }
[1] http://dev.mysql.com/doc/refman/5.6/en/innodb-lru-background-flushing.html
14.12.1.6 Tuning InnoDB Buffer Pool Flushing
[2] http://hedengcheng.com/?p=88 InnoDB原生Checkpoint策略及各版本優化詳解
[3] http://mysqllover.com/?p=620 Innodb:如何計算異步/同步刷髒及checkpoint的臨界范圍