MySQL5.7.5以前,调整innodb_buffer_pool_size需要重启mysql进程才可以生效,建议业务低峰时间执行,在MySQL 5.7.5版本后,innodb_buffer_pool_size参数的值可以动态的设置,可以通过innodb_buffer_pool_chunk_size参数配置块的大小,Innodb_buffer_pool_resize_status状态变量记录了从调整操作的状态。

innodb_buffer_pool_size参数表示缓冲池字节大小,InnoDB缓存表和索引数据的内存区域。mysql默认的值是128M。最大值与你的CPU体系结构有关,在32位操作系统,最大值是 4294967295 (2^32-1) ,在64 位操作系统,最大值为18446744073709551615 (2^64-1)。在32位操作系统中,CPU和操作系统实用的最大大小低于设置的最大值。

innodb_log_file_size表示在一个日志组每个日志文件的字节大小。日志文件的总大小(innodb_log_file_size* innodb_log_files_in_group)不能超过最高值512GB。例如一对255 GB的日志文件,已经接近了极限,不能超过它。默认值是48M。比较合适的值的范围是从1MB到1 / N个的缓冲池大小,其中N是该组中的日志文件的数量。该值越大,缓冲池中必要的检查点刷新活动就会越少,节省磁盘I/ O。但是越大的日志文件,mysql的崩溃恢复就越慢,尽管在mysql5.5之后改进了恢复性能和日志文件恢复的代价,一般2G左右就可以了;

在线调整innodb_buffer_pool_size方法如下:
当前大小为128M

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
row in set (0.03 sec)

select 134217728/1024/1024;  
+---------------------+
| 134217728/1024/1024 |
+---------------------+
|        128.00000000 |
+---------------------+
row in set (0.00 sec)

动态调整为512M

set global innodb_buffer_pool_size = 512*1024*1024;
Query OK, 0 rows affected (0.18 sec)

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
row in set (0.02 sec)

select 536870912/1024/1024;
+---------------------+
| 536870912/1024/1024 |
+---------------------+
|        512.00000000 |
+---------------------+
1 row in set (0.00 sec)

调整时,内部会把数据页移动到一个新的位置,单位是块。如果想提升移动速度,则需要调整
innodb_buffer_pool_chunk_size的参数大小,默认是128M
innodb_buffer_pool_size/innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size的参数大小,默认是128M
调整完以后在my.cnf的mysqld部分增加

innodb_buffer_pool_size = 536870912

以防止重启后仍然生效。

扫描下面二维码,给我点动力吧~

微信

微信

支付宝

支付宝

带符号 * 的表示必填项