[MySQL] Buffer Pool Adaptive Flush, adaptiveflush

[MySQL] Buffer Pool Adaptive Flush, adaptiveflush
Buffer Pool Adaptive Flush

In the MySQL help document, Tuning InnoDB Buffer Pool Flushing mentions that,innodb_adaptive_flushing_lwm,innodb_max_dirty_pages_pct_lwm,innodb_io_capacity_max, Andinnodb_flushing_avg_loopsThe formula determines the number of pages flushed by Adaptive Flush.

"For systems with constant heavyWorkloads, Or workloads that fluctuate widely, several configuration options let you fine-tuneFlushing BehaviorInnoDB Tables:innodb_adaptive_flushing_lwm,innodb_max_dirty_pages_pct_lwm,innodb_io_capacity_max, Andinnodb_flushing_avg_loops. These options feed into the formula used byinnodb_adaptive_flushing Option."

Innodb_adaptive_flush_lwm: low water level of the adaptive flush mechanism.
Innodb_max_dirty_page_pct_lwm: low level of dirty pages, used to control the dirty page ratio of buffer pool.
Innodb_io_capacity_max: Maximum redo log capacity. If this parameter is not set, it is twice that of innodb_io_capacity.
Innodb_flushing_avg_loops: After n cycles, recalculate the average refreshed dirtypage And LSN.

The main code of page And lsn to be refreshed is in af_get_pct_for_dirty and af_get_pct_for_lsn.

The adaptive flush code is located in the af_get_pct_for_lsn of buf0flu. cc:

1. first determine whether the redo log capacity has arrivedinnodb_adaptive_flushing_lwmLow water level threshold value.
2. Whether adaptive flush or age is configured to exceed the threshold value for asynchronous refresh.
3. Percentage of lsn_age_factor = age in the asynchronous refresh threshold.
4. ratio to be refreshed = innodb_io_capacity_max/innodb_io_capacity * lsn_age_factor * sqrt (innodb_io_capacity)/7.5

staticulintaf_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));}

 

The function related to the number of dirty pages to be refreshed is af_get_pct_for_dirty:
1. If there are dirty pages and innodb_max_dirty_pages_pct = 0, refresh immediately.
2. If innodb_max_dirty_pages_pct_lwm is not set and the dirty page ratio exceeds innodb_max_dirty_pages_pct, refresh immediately.
3. innodb_max_dirty_pages_pct_lwm is set, and the dirty page ratio is greater than that of the low level. af_get_pct_for_dirty = dirty page ratio/(innodb_max_dirty_pages_pct + 1)

staticulintaf_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 to be refreshed (max (af_get_pct_for_dirty, af_get_pct_for_lsn) + avg_page (calculated when innodb_flushing_avg_loops expires ).

LSN = oldest_lsn + (page to be refreshed this time/page already refreshed last time) + 1) * avg_lsn (calculated by innodb_flushing_avg_loops expiration)

The help document also mentions that the greater innodb_flushing_avg_loops, the slower the adaptive flush. However, from the code perspective, innodb_flushing_avg_loops will affect all flush operations, not just adaptive. When the write size increases, innodb_flushing_avg_loops remains unchanged or becomes larger, which may cause flush to fail.

staticulintpage_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);}

 

Reference:

[1] http://dev.mysql.com/doc/refman/5.6/en/innodb-lru-background-flushing.html14.12.1.6 Tuning InnoDB Buffer Pool Flushing
[2] http://hedengcheng.com /? P = 88 InnoDB native Checkpoint policy and version Optimization
[3] http://mysqllover.com /? P = 620 Innodb: how to calculate the asynchronous/Synchronous dirty brush and checkpoint critical range