Buffer Pool Adaptive Flush
在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的临界范围
本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/p/4450840.html,如需转载请自行联系原作者