MySQL5.7中如何动态修改innodb_buffer_pool大小

MySQL 5.7中如何动态修改innodb_buffer_pool大小，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

成都创新互联公司-专业网站定制、快速模板网站建设、高性价比东兴网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式东兴网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖东兴地区。费用合理售后完善，10多年实体公司更值得信赖。

MySQL5.7版本开始支持buffer pool动态调整大小，每个buffer_pool_instance都由同样个数的chunk组成(chunks数组), 每个chunk内存大小为innodb_buffer_pool_chunk_size(实际会偏大5%，用于存放chuck中的block信息)。
buffer pool以innodb_buffer_pool_chunk_size为单位进行动态增大和缩小。调整前后innodb_buffer_pool_size应一直保持是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

实验如下:

C:\Users\duansf>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set, 1 warning (0.61 sec)


mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)



 将innodb_buffer_pool_size从 134217728 扩大到 268435456


mysql> SET GLOBAL innodb_buffer_pool_size=268435456;
Query OK, 0 rows affected (0.02 sec)


mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 268435456 |
+---------------------------+
1 row in set (0.00 sec)



-- 查看日志记录(.err结尾的文件)
2017-03-09T05:41:50.036769Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 268435456 bytes)
2017-03-09T05:41:50.067742Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).
2017-03-09T05:41:50.068754Z 0 [Note] InnoDB: Disabling adaptive hash index.
2017-03-09T05:41:50.229853Z 0 [Note] InnoDB: disabled adaptive hash index.
2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-03-09T05:41:50.230853Z 0 [Note] InnoDB: Latching whole of buffer pool.
2017-03-09T05:41:50.231853Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 2.
2017-03-09T05:41:50.257873Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were added.
2017-03-09T05:41:50.274899Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 268435456.
2017-03-09T05:41:50.275895Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-03-09T05:41:50.276895Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:41:50.


加大buffer pool的过程大致如下：
1、以innodb_buffer_pool_chunk_size为单位，分配新的内存pages；
2、扩展buffer pool的AHI(adaptive hash index)链表，将新分配的pages包含进来；
3、将新分配的pages添加到free list中；


将innodb_buffer_pool_size从268435456缩减回134217728

mysql> SET GLOBAL innodb_buffer_pool_size=134217728;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+
1 row in set (0.00 sec)


-- 查看日志记录(.err结尾的文件)
2017-03-09T05:57:42.759623Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 134217728 bytes)
2017-03-09T05:57:42.759623Z 0 [Note] InnoDB: Resizing buffer pool from 268435456 to 134217728 (unit=134217728).
2017-03-09T05:57:42.761625Z 0 [Note] InnoDB: Disabling adaptive hash index.
2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: disabled adaptive hash index.
2017-03-09T05:57:42.762626Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2017-03-09T05:57:42.763627Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 8192 blocks.
2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0 : withdrawing blocks. (8192/8192)
2017-03-09T05:57:42.765642Z 0 [Note] InnoDB: buffer pool 0 : withdrew 8192 blocks from free list. Tried to relocate 0 pages (8192/8192).
2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 8192 blocks.
2017-03-09T05:57:42.767636Z 0 [Note] InnoDB: Latching whole of buffer pool.
2017-03-09T05:57:42.768631Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 2 to 1.
2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were freed.
2017-03-09T05:57:42.772633Z 0 [Note] InnoDB: Completed to resize buffer pool from 268435456 to 134217728.
2017-03-09T05:57:42.773633Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2017-03-09T05:57:42.774648Z 0 [Note] InnoDB: Completed resizing buffer pool at 170309 13:57:42.


缩减buffer pool的过程大致如下：
1、重整buffer pool，准备回收pages；
2、以innodb_buffer_pool_chunk_size为单位，释放删除这些pages（这个过程会有一点点耗时）；
3、调整AHI链表，使用新的内存地址。


附:resize的详细流程如下:
如果开启了AHI，需禁用AHI
如果是收缩内存
计算需收缩的chunk数， 从chunks开始尾部删除指定个数的chunk.
锁buf_pool
从free_list中摘除待删chunk的page放入待删链表buf_pool->withdraw
如果待删chunk的page为脏页，则刷脏
重新加载LRU中要删除的页，从LRU中摘除，重新从free列表获取page老的page放入待删链表buf_pool->withdraw
释放buffer pool锁
如果需收缩的chunk pages没有收集全，重复2-6
开始resize
锁住所有instance的buffer_pool，page_hash
收缩pool：以chunk为单位释放要收缩的内存
清空withdraw列表buf_pool->withdraw
增大pool:分配新的chunk
重新分配buf_pool->chunks
如果改变/缩小超过2倍，会重置page hash，改变桶大小
释放buffer_pool,page_hash锁
如果改变/缩小超过2倍,会重启和buffer pool大小相关的内存结构，如锁系统(lock_sys_resize)，AHI(btr_search_sys_resize), 数据字段(dict_resize)等
如果禁用了AHI，此时开启

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。