MySQL5.6 参数优化笔记

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。

---

数据库分享一： MySQL的Innodb缓存相关优化

你真的了解 MySQL 数据库的运行状况吗？

MySQL调优配置整理

How to set global innodb_buffer_pool_size?

mysql 5.6 参数详解