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，实例启动后为只读参数。


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

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

12. static
13. int
14. innodb_buffer_pool_size_validate(
15. {
16. ...
17. //当srv_buf_pool_instances > 1，要求size不小于1GB。
18. if (srv_buf_pool_instances > 1 && intbuf < BUF_POOL_SIZE_THRESHOLD) {
19. buf_pool_mutex_exit_all();

21. push_warning_printf(thd, Sql_condition::SL_WARNING,
22. ER_WRONG_ARGUMENTS,
23. "Cannot update innodb_buffer_pool_size"
24. " to less than 1GB if"
25. " innodb_buffer_pool_instances > 1.");
26. return(1);
27. }
28. ...
29. ulint requested_buf_pool_size
30. = buf_pool_size_align(static_cast<ulint>(intbuf));
31. }

35. /** Calculate aligned buffer pool size based on srv_buf_pool_chunk_unit,
36. if needed.
37. @param[in]  size  size in bytes
38. @return aligned size */
39. UNIV_INLINE
40. ulint
41. buf_pool_size_align(
42. ulint size)
43. {
44. const ulint m = srv_buf_pool_instances * srv_buf_pool_chunk_unit;
45. size = ut_max(size, srv_buf_pool_min_size);

47. if (size % m == 0) {
48. return(size);
49. } else {
50. return((size / m + 1) * m);
51. }
52. }

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。

1. /** Resize the buffer pool based on srv_buf_pool_size from
2. srv_buf_pool_old_size. */
3. void
4. buf_pool_resize()
5. {
6. /* disable AHI if needed */
7. btr_search_disable(true);

9. /* set withdraw target */
10. for (ulint i = 0; i < srv_buf_pool_instances; i++) {
11. if (buf_pool->curr_size < buf_pool->old_size) {
12. ...
13. while (chunk < echunk) {
14. withdraw_target += chunk->size;
15. ++chunk;
16. }
17. ...
18. }
19. }

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

26. should_retry_withdraw |=
27. buf_pool_withdraw_blocks(buf_pool);
28. }
29. }
30. ...
31. if (should_retry_withdraw) {
32. ib::info() << "Will retry to withdraw " << retry_interval
33. << " seconds later.";
34. os_thread_sleep(retry_interval * 1000000);

36. if (retry_interval > 5) {
37. retry_interval = 10;
38. } else {
39. retry_interval *= 2;
40. }

42. goto withdraw_retry;
43. }
44. ...
45. /* add/delete chunks */
46. for (ulint i = 0; i < srv_buf_pool_instances; ++i) {
47. if (buf_pool->n_chunks_new < buf_pool->n_chunks) {
48. /* delete chunks */
49. /* discard withdraw list */
50. }
51. }
52. /* reallocate buf_pool->chunks */
53. if (buf_pool->n_chunks_new > buf_pool->n_chunks) {
54. /* add chunks */
55. }
56. ...
57. const bool  new_size_too_diff
58. = srv_buf_pool_base_size > srv_buf_pool_size * 2
59. || srv_buf_pool_base_size * 2 < srv_buf_pool_size;

61. /* Normalize page_hash and zip_hash,
62. if the new size is too different */
63. }

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。而这期间的写入操作也会被阻塞。


1. 02:54:09.798912Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2. 02:54:09.798935Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 1151680 blocks.
3. 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.)
4. 02:54:57.687189Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (1151680/1151680)
5. 02:54:57.687237Z 0 [Note] InnoDB: buffer pool 0 : withdrew 1151653 blocks from free list. Tried to relocate 27 pages (1151680/1151680).
6. 02:54:57.753014Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 1151680 blocks.

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

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


1. 03:31:57.734231Z 0 [Note] InnoDB: Resizing buffer pool from 1887436800 to 5242880 (unit=1048576).
2. 03:31:58.480061Z 0 [Note] InnoDB: Completed to resize buffer pool from 1887436800 to 5242880.
3. ...
4. 03:31:46.453250Z 10 [Note] InnoDB: Resizing buffer pool from 524288 (new size: 1887436800 bytes)
5. 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的收集。不过这段时间并不会完全阻塞用户的操作。


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

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

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

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

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

17. InnoDB: Resizing buffer pool from 16777216 to 5242880 (unit=1048576).
18. InnoDB: Withdrawing blocks to be shrunken.
19. InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
20. InnoDB: buffer pool 0 : withdrew 239 blocks from free list. Tried to relocate 126 pages (689/704).
21. InnoDB: buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 0 pages (689/704).
22. ...
23. InnoDB: Will retry to withdraw 1 seconds later.
24. InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
25. ...
26. InnoDB: buffer pool 0 : will retry to withdraw later.
27. InnoDB: Will retry to withdraw 2 seconds later.
28. ...
29. InnoDB: buffer pool 0 : will retry to withdraw later.
30. InnoDB: Will retry to withdraw 4 seconds later.
31. InnoDB: buffer pool 0 : start to withdraw the last 704 blocks.
32. ...
33. InnoDB: Will retry to withdraw 8 seconds later.
34. InnoDB: buffer pool 0 : withdrawn target 704 blocks.

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

原文：http://mysql.taobao.org/monthly/2018/03/06/