MySQL · 特性分析 · innodb_buffer_pool_size在线修改

最新推荐文章于 2025-02-28 15:21:04 发布

weixin_34200628

最新推荐文章于 2025-02-28 15:21:04 发布

阅读量855

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/560511

介绍了MySQL InnoDB缓存池大小在线调整的原理及流程，包括如何避免长时间阻塞和资源不足的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

InnoDB Buffer Pool缓存了表数据和二级索引在内存中，提高数据库效率，因此设置innodb_buffer_pool_size到合理数值对实例性能影响很大。当size设置偏小，会导致数据库大量直接磁盘的访问，而设置过大会导致实例占用内存太多，容易发生OOM。在MySQL 5.7之前innodb_buffer_pool_size的修改需要重启实例，在5.7后支持了动态修改innodb_buffer_pool_size。本文会根据源码介绍该特性。

innodb_buffer_pool_size 设置范围

innodb_buffer_pool_size默认值是128M，最小5M(当小于该值时会设置成5M)，最大为LLONG_MAX。当innodb_buffer_pool_instances设置大于1的时候，buffer pool size最小为1GB。同时buffer pool size需要是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。innodb_buffer_pool_chunk_size默认为128M，最小为1M，实例启动后为只读参数。

static MYSQL_SYSVAR_LONGLONG(buffer_pool_size, innobase_buffer_pool_size,
  PLUGIN_VAR_RQCMDARG,
  "The size of the memory buffer InnoDB uses to cache data and indexes of its tables.",
  innodb_buffer_pool_size_validate,
  innodb_buffer_pool_size_update,
  static_cast<longlong>(srv_buf_pool_def_size),//128M
  static_cast<longlong>(srv_buf_pool_min_size),//5M
  LLONG_MAX, 1024*1024L);

#define BUF_POOL_SIZE_THRESHOLD   (1024 * 1024 * 1024) //1GB

static
int
innodb_buffer_pool_size_validate(
{
  ...
  //当srv_buf_pool_instances > 1，要求size不小于1GB。
  if (srv_buf_pool_instances > 1 && intbuf < BUF_POOL_SIZE_THRESHOLD) {
    buf_pool_mutex_exit_all();

    push_warning_printf(thd, Sql_condition::SL_WARNING,
          ER_WRONG_ARGUMENTS,
          "Cannot update innodb_buffer_pool_size"
          " to less than 1GB if"
          " innodb_buffer_pool_instances > 1.");
    return(1);
  }
  ...
  ulint requested_buf_pool_size
  = buf_pool_size_align(static_cast<ulint>(intbuf));
}



/** Calculate aligned buffer pool size based on srv_buf_pool_chunk_unit,
if needed.
@param[in]  size  size in bytes
@return aligned size */
UNIV_INLINE
ulint
buf_pool_size_align(
  ulint size)
{
  const ulint m = srv_buf_pool_instances * srv_buf_pool_chunk_unit;
  size = ut_max(size, srv_buf_pool_min_size);

  if (size % m == 0) {
    return(size);
  } else {
    return((size / m + 1) * m);
  }
}

buffer pool resize流程

如果开启了AHI（adaptive hash index，自适应哈希索引）就关闭AHI，这里因为AHI是通过buffer pool中的B+树页构造而来。
如果新设定的buffer pool size小于原来的size，就需要计算需要删除的chunk数目withdraw_target。
遍历buffer pool instances，锁住buffer pool，收集free list中的chunk page到withdraw，直到withdraw_target或者遍历完，然后释放buffer pool锁。
停止加载buffer pool。
如果free list中没有收集到足够的chunk，则重复遍历收集，每次重复间隔时间会指数增加1s、2s、4s、8s…，以等待事务释放资源。
锁住buffer pool，开始增减chunk。
如果改变比较大，超过2倍，会重置page hash，改变桶大小。
释放buffer_pool,page_hash锁。
改变比较大时候，重新设置buffer pool大小相关的内存结构。
开启AHI。

/** Resize the buffer pool based on srv_buf_pool_size from
srv_buf_pool_old_size. */
void
buf_pool_resize()
{
  /* disable AHI if needed */
  btr_search_disable(true);

  /* set withdraw target */
  for (ulint i = 0; i < srv_buf_pool_instances; i++) {
    if (buf_pool->curr_size < buf_pool->old_size) {
      ...
      while (chunk < echunk) {
        withdraw_target += chunk->size;
        ++chunk;
      }
      ...
    }
  }

  /* wait for the number of blocks fit to the new size (if needed)*/
  for (ulint i = 0; i < srv_buf_pool_instances; i++) {
    buf_pool = buf_pool_from_array(i);
      if (buf_pool->curr_size < buf_pool->old_size) {

      should_retry_withdraw |=
       buf_pool_withdraw_blocks(buf_pool);
    }
  }
  ...
  if (should_retry_withdraw) {
  ib::info() << "Will retry to withdraw " << retry_interval
    << " seconds later.";
  os_thread_sleep(retry_interval * 1000000);

  if (retry_interval > 5) {
    retry_interval = 10;
  } else {
    retry_interval *= 2;
    }

    goto withdraw_retry;
  }
  ...
  /* add/delete chunks */
  for (ulint i = 0; i < srv_buf_pool_instances; ++i) {
    if (buf_pool->n_chunks_new < buf_pool->n_chunks) {
      /* delete chunks */
      /* discard withdraw list */
    }
  }
  /* reallocate buf_pool->chunks */
  if (buf_pool->n_chunks_new > buf_pool->n_chunks) {
    /* add chunks */
  }
  ...
  const bool  new_size_too_diff
  = srv_buf_pool_base_size > srv_buf_pool_size * 2
    || srv_buf_pool_base_size * 2 < srv_buf_pool_size;

  /* Normalize page_hash and zip_hash,
  if the new size is too different */
}

resize过程中的等待和阻塞

在支持动态修改innodb_buffer_pool_size之前，该值的修改需要修改配置项然后重启实例生效。而重启实例会导致用户连接强制断开，导致一段时间的实例不可用，如果有大事务在回滚就需要等待很长时间。

动态修改innodb_buffer_pool_size只有在收集回收块；查找持有block阻止buffer pool收集回收chunk的事务；resizing buffer pool操作时会阻塞用户写入。而这几部分操作都是内存操作，会较快完成。

如果对innodb_buffer_pool_size修改量很大，同时遇到page cleaner工作时间久，就可能导致一段时间的阻塞。例如下面一个较为极端的例子，innodb_buffer_pool_instances为1，innodb_buffer_pool_size由18GB改为5M，innodb_buffer_pool_chunk_size为1M，page cleaner loop花费近48s，导致收集回收块会花费很长时间，可以看到在测试机器上用时近48s。而这期间的写入操作也会被阻塞。

02:54:09.798912Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
02:54:09.798935Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 1151680 blocks.
02:54:57.660725Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 47685ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
02:54:57.687189Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (1151680/1151680)
02:54:57.687237Z 0 [Note] InnoDB: buffer pool 0 : withdrew 1151653 blocks from free list. Tried to relocate 27 pages (1151680/1151680).
02:54:57.753014Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 1151680 blocks.


> insert into t values(10000001, 2);
Query OK, 1 row affected (9.03 sec)

正常不需要等待时的内存操作会很快。

03:31:57.734231Z 0 [Note] InnoDB: Resizing buffer pool from 1887436800 to 5242880 (unit=1048576).
03:31:58.480061Z 0 [Note] InnoDB: Completed to resize buffer pool from 1887436800 to 5242880.
...
03:31:46.453250Z 10 [Note] InnoDB: Resizing buffer pool from 524288 (new size: 1887436800 bytes)
03:31:57.734231Z 0 [Note] InnoDB: Resizing buffer pool from 1887436800 to 5242880 (unit=1048576).

另一个方面，如果当前有事务占用大量buffer pool数据导致无法收集到足够的chunk，resize过程也会变久。下面极端测试中当执行xa rollback回滚大事务的时候，innodb_buffer_pool_chunk_size由16M改为5M，即等待了较久时间才完成回收chunk的收集。不过这段时间并不会完全阻塞用户的操作。

> xa begin 'y';
Query OK, 0 rows affected (0.00 sec)

> update t set c2 = 2 where c2 =1;
Query OK, 999996 rows affected (8.32 sec)
Rows matched: 999996  Changed: 999996  Warnings: 0

> xa end 'y';
Query OK, 0 rows affected (0.00 sec)

> xa prepare 'y';
Query OK, 0 rows affected (0.11 sec)

> xa rollback 'y';
Query OK, 0 rows affected (10.10 sec)

InnoDB: Resizing buffer pool from 16777216 to 5242880 (unit=1048576).
InnoDB: Withdrawing blocks to be shrunken.
InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
InnoDB: buffer pool 0 : withdrew 239 blocks from free list. Tried to relocate 126 pages (689/704).
InnoDB: buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 0 pages (689/704).
...
InnoDB: Will retry to withdraw 1 seconds later.
InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
...
InnoDB: buffer pool 0 : will retry to withdraw later.
InnoDB: Will retry to withdraw 2 seconds later.
...
InnoDB: buffer pool 0 : will retry to withdraw later.
InnoDB: Will retry to withdraw 4 seconds later.
InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
...
InnoDB: Will retry to withdraw 8 seconds later.
InnoDB: buffer pool 0 : withdrawn target 704 blocks.

从上面可以看到innodb_buffer_pool_size的online修改相比重启对用户实例的影响降低了很多，但也最好选择业务低峰期和没有大事务操作时候进行，同时要修改MySQL配置文件，防止重启后恢复到原来的值。