MySQL 5.7中如何动态修改innodb_buffer_pool大小

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

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，此时开启

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