MySQL5.6 参数优化笔记
Innodb_buffer_pool_size
调整 Innodb_buffer_pool_size
对 MySQL Innodb 的性能有较大的反馈。MySQL5.6 不支持动态更新此变量,MySQL5.7 支持动态变更。
本例子的服务器规格是 4CPU8G, 200G SSD, 使用 docker-ce 18.09.7
运行 MySQL5.6, 启动的 docker-compose.yaml
文件如下:
version: '2.2'
services:
mysql:
image: mysql:5.6
command: --innodb-buffer-pool-size=2147483648
environment:
- MYSQL_ROOT_PASSWORD=root
- MYSQL_ROOT_HOST=%
ports:
- "3306:3306"
登陆mysql后,先查看 innodb_buffer_pool_size
的具体数值: show variables like 'innodb_buffer_pool_size';
,应该与 docker-compose.yaml
设置的 innodb-buffer-pool-size
的参数一致。
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+-----------+
1 row in set (0.00 sec)
innodb_buffer_pool_size
的单位是字节,具体是:2147483648/1024/1024 = 2048 MiB = 2G。
再执行命令:show status like 'Innodb_buffer_pool_%';
查看先行的 Innodb_buffer_pool_size
的运行情况。
mysql> show status like 'Innodb_buffer_pool_%';
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 122758 |
| Innodb_buffer_pool_bytes_data | 2013134848 |
| Innodb_buffer_pool_pages_dirty | 513 |
| Innodb_buffer_pool_bytes_dirty | 8036352 |
| Innodb_buffer_pool_pages_flushed | 548515 |
| Innodb_buffer_pool_pages_free | 8174 |
| Innodb_buffer_pool_pages_misc | 132 |
| Innodb_buffer_pool_pages_total | 131064 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 466023 |
| Innodb_buffer_pool_read_ahead_evicted | 256 |
| Innodb_buffer_pool_read_requests | 249584247 |
| Innodb_buffer_pool_reads | 4113 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 64415528 |
+---------------------------------------+-------------+
17 rows in set (0.00 sec)
其中缓存命中率的公式是:
(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100%
。
本文的例子中的缓存命中率是 (249584247-4113)/249584247 * 100%
= 99.998352%,是一个十分不错的命中率。
然后看看缓存空闲率,公式是:Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total * 100%
。
本文的例子中的缓存空闲率是 8174/131064 * 100%
=6.236647%,空闲率比较低。
针对缓存命中率和缓存空闲率进行定期采样,如果缓存命中率一直比较高,而缓存空闲率一直比较高,那么可以适当减少 innodb_buffer_pool_size
。如果是缓存空闲率一直下降,然后缓存命中率也出现下降时,需要继续增加 innodb_buffer_pool_size
。