MySQL 8.0 新参数 innodb_idle_flush_pct

innodb_idle_flush_pct 参数是 MySQL 8.0.18 版本引入的新参数,用于InnoDB空闲状态下,限制刷脏页的速度。

innodb_idle_flush_pct:

  • 作用范围:Global
  • 动态修改:Yes
  • 默认值:100
  • 最小值:0
  • 最大值:100

innodb_idle_flush_pct 的值是一个百分比,该百分比乘以基数,也就是参数 innodb_io_capacity 值,获得的结果即为InnoDB空闲状态下刷脏页的速度。

举个例子:

设置innodb_io_capacity=1000,设置innodb_idle_flush_pct=50,那么在空闲状态下,InnoDB刷脏页的速度为 500。

innodb_idle_flush_pct 参数源码分析:

源码版本:8.0.20

innodb_idle_flush_pct默认值为100,如下:

// 源码文件 srv0srv.cc

const ulong srv_idle_flush_pct_default = 100;
ulong srv_idle_flush_pct = srv_idle_flush_pct_default;

调用函数buf_flush_lists(),传入srv_idle_flush_pct参数,来控制InnoDB空闲状态下刷脏页的速度,如下:

// 源码文件 buf0flu.cc

else if (ret_sleep == OS_SYNC_TIME_EXCEEDED && srv_idle_flush_pct) {
      /* no activity, slept enough */
      buf_flush_lists(PCT_IO(srv_idle_flush_pct), LSN_MAX, &n_flushed);
      }

PCT_IO 宏定义:

// 源码文件 srv0srv.h

#define PCT_IO(p) ((ulong)(srv_io_capacity * ((double)(p) / 100.0)))

buf_flush_lists()函数,调用buf_flush_do_batch()函数,传入参数min_n,控制刷脏页速度,主要逻辑如下:

// 源码文件 buf0flu.cc

bool buf_flush_lists(ulint min_n, lsn_t lsn_limit, ulint *n_processed) {

if (min_n != ULINT_MAX) {
/* Ensure that flushing is spread evenly amongst the
    buffer pool instances. When min_n is ULINT_MAX
    we need to flush everything up to the <u>lsn</u> limit
    so no limit here. */

    min_n = (min_n + srv_buf_pool_instances - 1) / 
srv_buf_pool_instances;

  }
  
    /* Flush to lsn_limit in all buffer pool instances */
  for (ulint i = 0; i < srv_buf_pool_instances; i++) {
    buf_pool_t *buf_pool;
    ulint page_count = 0;

    buf_pool = buf_pool_from_array(i);

    if (!buf_flush_do_batch(buf_pool, BUF_FLUSH_LIST, min_n, lsn_limit,
                            &page_count)) {
      /* We have two choices here. If lsn_limit was
      specified then skipping an instance of buffer
      pool means we cannot guarantee that all pages
      up to lsn_limit has been flushed. We can
      return right now with failure or we can try
      to flush remaining buffer pools up to the
      lsn_limit. We attempt to flush other buffer
      pools based on the assumption that it will
      help in the retry which will follow the
      failure. */
      success = false;

      continue;
    }

    n_flushed += page_count;
  }

发表评论