MySQL调优之innodb_buffer_pool_size大小设置

客官°小女子只卖身不卖艺 2022-02-03 10:41 272阅读 0赞

MySQL调优之innodb\_buffer\_pool\_size大小设置  
相关查看命令  
sql> show global variables like 'innodb\_buffer\_pool\_size';  
sql> show global status like 'Innodb\_buffer\_pool\_pages\_data';  
sql> show global status like 'Innodb\_page\_size';  
或  
sql> use mysql;  
sql> select @@innodb\_buffer\_pool\_size;  
....

MariaDB \[(none)\]> show global variables like 'innodb\_buffer\_pool\_size';  
\+-------------------------+-----------+  
| Variable\_name           | Value     |  
\+-------------------------+-----------+  
| innodb\_buffer\_pool\_size | 268435456 |  
\+-------------------------+-----------+  
1 row in set (0.00 sec)  
MariaDB \[(none)\]> show global status like 'Innodb\_buffer\_pool\_pages\_data';  
\+-------------------------------+-------+  
| Variable\_name                 | Value |  
\+-------------------------------+-------+  
| Innodb\_buffer\_pool\_pages\_data | 6082  |  
\+-------------------------------+-------+  
1 row in set (0.00 sec)  
MariaDB \[(none)\]> show global status like 'Innodb\_buffer\_pool\_pages\_total';  
\+--------------------------------+-------+  
| Variable\_name                  | Value |  
\+--------------------------------+-------+  
| Innodb\_buffer\_pool\_pages\_total | 16383 |  
\+--------------------------------+-------+  
1 row in set (0.00 sec)  
MariaDB \[(none)\]> show global status like 'Innodb\_page\_size';  
\+------------------+-------+  
| Variable\_name    | Value |  
\+------------------+-------+  
| Innodb\_page\_size | 16384 |  
\+------------------+-------+  
1 row in set (0.00 sec)  
MariaDB \[(none)\]>  
官方对这个几个参数的解释：  
Innodb\_buffer\_pool\_pages\_data  
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and  
clean pages.  
Innodb\_buffer\_pool\_pages\_total  
The total size of the InnoDB buffer pool, in pages.  
Innodb\_page\_size  
InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be  
easily converted to bytes  
调优参考计算方法：  
val = Innodb\_buffer\_pool\_pages\_data / Innodb\_buffer\_pool\_pages\_total \* 100%  
val > 95% 则考虑增大 innodb\_buffer\_pool\_size， 建议使用物理内存的75%  
val < 95% 则考虑减小 innodb\_buffer\_pool\_size， 建议设置为：Innodb\_buffer\_pool\_pages\_data \* Innodb\_page\_size \* 1.05 / (1024\*1024\*1024)  
设置命令：set global innodb\_buffer\_pool\_size = 2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M  
设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

修改配置文件的调整方法，修改my.cnf配置：  
innodb\_buffer\_pool\_size = 2147483648  \#设置2G  
innodb\_buffer\_pool\_size = 2G  \#设置2G  
innodb\_buffer\_pool\_size = 500M  \#设置500M  
MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL，但是低版本，静态的，修改后，需要重启MySQL。