MySQL 5.7中如何动态更改innodb_buffer_pool大小
发布时间：2021-12-18 23:24:01 所属栏目：MySql教程 来源：互联网
导读：MySQL 5.7中如何动态修改innodb_buffer_pool大小，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 MySQL5.7版本开始支持buffer pool动态调整大小，每个buffer_pool_instanc
|
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:Usersduansf>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) 缩减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，此时开启 看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
