MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3759字,纯文字阅读大概需要6分钟。
内容图文
在之前的版本,调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效,如今在MySQL5.7里,可以直接动态设置,方便了很多。这个功能应用的场景:一、机器
在之前的版本,调整Innodb_Buffer_Pool_size大小必须重启mysql进程才可以生效,如今在MySQL5.7里,可以直接动态设置,方便了很多。
这个功能应用的场景:
一、机器增加内存,DBA粗心大意忘记调大Innodb_Buffer_Pool_size了
二、工作交接,新来的DBA发现前任DBA设置的Innodb_Buffer_Pool_size不合理
需要注意的地方,在调整Buffer_Pool期间,用户的请求将会阻塞,直到调整完毕,所以请勿在白天调整,在凌晨3-4点低峰期调整。
调整时,内部把数据页移动到一个新的位置,单位是块。如果想增加移动的速度,需要调整innodb_buffer_pool_chunk_size参数的大小,默认是128M。
例(把BP 128M增大为384M):
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 134217728 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 402653184 |
+---------------------------+
1 row in set (0.00 sec)
innodb_buffer_pool_chunk_size的大小,,计算公式是innodb_buffer_pool_size / innodb_buffer_pool_instances
比如现在初始化innodb_buffer_pool_size为2G,innodb_buffer_pool_instances实例为4,innodb_buffer_pool_chunk_size设置为1G,那么会自动把innodb_buffer_pool_chunk_size 1G调整为512M,例:
./mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4
--innodb_buffer_pool_chunk_size=1073741824;
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 2147483648 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 4 |
+--------------------------------+
1 row in set (0.00 sec)
# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 536870912 |
+---------------------------------+
1 row in set (0.00 sec)
监控Buffer Pool调整进程
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)
查看错误日志:
(增大)
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
内容总结
以上是互联网集市为您收集整理的MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程全部内容,希望文章能够帮你解决MySQL5.7在线调整Innodb_Buffer_Pool_size不用重启mysql进程所遇到的程序开发问题。
如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
来源:【匿名】