Write by lyc at 2019-11-20
参考博文：mysql参数优化(解决大并发，高IO)
1.查看innodb_buffer_pool 缓冲区状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| 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 | 245119 | # 分配出去，正在被使用的buffer_pool缓冲区 页数
| Innodb_buffer_pool_bytes_data | 4016029696 |
| Innodb_buffer_pool_pages_dirty | 22 | # 脏页数，没有被flush到磁盘的页数
| Innodb_buffer_pool_bytes_dirty | 360448 |
| Innodb_buffer_pool_pages_flushed | 491254595 |
| Innodb_buffer_pool_pages_free | 8192 | # buffer_pool缓冲区 剩余（空闲）页数
| Innodb_buffer_pool_pages_misc | 8825 |
| Innodb_buffer_pool_pages_total | 262136 | # buffer_pool缓冲区 总页数
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 21962518 |
| Innodb_buffer_pool_read_ahead_evicted | 500 |
| Innodb_buffer_pool_read_requests | 40979165180407 | # 总共从buffer_pool缓冲区读取的页数
| Innodb_buffer_pool_reads | 3106120 | # 从磁盘上读取的页数，先读buffer_pool缓冲区没有，就会从磁盘读取
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 2165075095 |
+---------------------------------------+----------------+
17 rows in set (0.00 sec)
|
2.buffer_pool缓冲区 使用率
- 如果
Innodb_buffer_pool_pages_free 偏大的话，说明有很多缓存页没有被利用到，考虑减少innodb_buffer_pool_size
1
2
3
4
| (Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total * 100%
(262136-8192)/262136 * 100% = 96.9%
# buffer_pool缓冲区 接近用完，考虑增加buffer_pool
|
3.buffer_pool缓冲区 Read命中率
一般来讲这个命中率不会低于99%，如果低于这个值的话就要考虑加大innodb_buffer_pool_size
1
2
3
| (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100%
(40979165180407-3106120)/40979165180407 * 100% = 99.9%
|
