MySQL管理之道-筆記-MySQL5.7-在線調(diào)整innodb_buffer_pool_size

在線調(diào)整innodb_buffer_pool_size不用重啟mysql進(jìn)程
MySQL5.7以前，調(diào)整innodb_buffer_pool_size需要重啟mysql進(jìn)程才可以生效。
建議業(yè)務(wù)低峰時(shí)間執(zhí)行

1 當(dāng)前大小128M

root@localhost:mysql3306.sock [(none)]>show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
row in set (0.03 sec)

root@localhost:mysql3306.sock [(none)]>select 134217728/1024/1024;  
+---------------------+
| 134217728/1024/1024 |
+---------------------+
|        128.00000000 |
+---------------------+
row in set (0.00 sec)

2 動(dòng)態(tài)調(diào)整為256M

root@localhost:mysql3306.sock [(none)]>set global innodb_buffer_pool_size = 256*1024*1024;
Query OK, 0 rows affected (0.18 sec)

root@localhost:mysql3306.sock [(none)]>show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
row in set (0.02 sec)

root@localhost:mysql3306.sock [(none)]>select 268435456/1024/1024;
+---------------------+
| 268435456/1024/1024 |
+---------------------+
|        256.00000000 |
+---------------------+
row in set (0.00 sec)

root@localhost:mysql3306.sock [(none)]>select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
row in set (0.03 sec)

調(diào)整時(shí)，內(nèi)部會(huì)把數(shù)據(jù)頁移動(dòng)到一個(gè)新的位置，單位是塊。如果想提升移動(dòng)速度，則需要調(diào)整
innodb_buffer_pool_chunk_size的參數(shù)大小，默認(rèn)是128M

innodb_buffer_pool_size/innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size的參數(shù)大小，默認(rèn)是128M