MySQL性能优化之buffer pool配置优化

MySQL性能优化之buffer pool配置优化

如果MySQL同时接收到了多个请求，他自然会用多个线程并发来处理这多个请求，每个线程会负责处理一个请求。
MySQL多个线程并发访问一个buffer pool，必须要先加锁，然后让一个线程先完成一系列操作（例如：从磁盘加载数据页到buffer pool中的缓存页，更新free链表、lru链表、flush链表等）之后再释放锁，接着下一个线程再执行一系列操作。即多线程并发访问一个buffer pool是串行的。

一般大部分情况下，每个线程都是查询或者更新buffer pool中的缓存页里的数据，这个操作是发生在内存里的，都是很快的（微秒级），包括更新free、flush、lru这些链表，因为都是基于链表进行一些指针操作，性能也是极高的。
但是线程拿到锁之后，他可能要从磁盘里读取数据页加载到缓存页里去，这个过程会发生了一次磁盘IO操作，时间开销会多一些。

例如：如果4个线程并发访问同一个buffer pool 和 4个线程并发访问4个buffer pool（每线程访问一个buffer pool），如果发生磁盘IO操作时，4个buffer pool比1个buffer pool更优。

MySQL buffer pool 相关参数配置查看：
show variables like '%buffer_pool%';
show global variables like '%buffer_pool%';

> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_populate         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+

buffer pool 相关配置参数说明：
innodb_buffer_pool_chunk_size: Chunk大小，默认值 134217728（128MB）。一个buffer pool中由多个（最多不超1000）Chunk组成；
innodb_buffer_pool_instances: buffer pool数量，默认值1，支持1~64。设置值大于1时，需要保证：innodb_buffer_pool_size / innodb_buffer_pool_instances > 1GB。
innodb_buffer_pool_size: buffer pool总大小，默认值268435456（256MB）；一般可设置为机器内存的50%~60%，剩下的留给OS和其他人来用。

注意：
1）修改参数后，需要重启数据库生效；
2）如果修改了 innodb_buffer_pool_chunk_size 值将会导致 innodb_buffer_pool_size 的值改变。在修改该参数的时候，需要计算好最后的 innodb_buffer_pool_size 是否符合服务器的硬件配置；

buffer pool 相关参数设置时的规则：
Chunk 大小： buffer pool总大小 = (Chunk大小 * buffer pool数量) 的N倍数（N>=1，为每个buffer pool内的Chunk个数）
即公式为：
innodb_buffer_pool_size = ( innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances ) * N

关于N：N为每个buffer pool内的Chunk个数，MySQL会自动根据三个参数动态计算得到，设置三个参数时需要保证N的范围不超过1000，即：[1,1000)。例如：
机器内存8GB，可以有多种设置：

innodb_buffer_pool_size = 4GB; innodb_buffer_pool_chunk_size = 256MB; innodb_buffer_pool_instances = 4; 即：4GB = (256MB * 4) * 4
innodb_buffer_pool_size = 4GB; innodb_buffer_pool_chunk_size = 512MB; innodb_buffer_pool_instances = 4; 即：4GB = (512MB * 4) * 2
innodb_buffer_pool_size = 4GB; innodb_buffer_pool_chunk_size = 1GB;   innodb_buffer_pool_instances = 4; 即：4GB = (1GB * 4) * 1

机器内存32GB，可以有多种设置：
innodb_buffer_pool_size = 16GB; innodb_buffer_pool_chunk_size = 256MB; innodb_buffer_pool_instances = 4; 即：16GB = (256MB * 4) * 16
innodb_buffer_pool_size = 16GB; innodb_buffer_pool_chunk_size = 512MB; innodb_buffer_pool_instances = 4; 即：16GB = (512MB * 4) * 8
innodb_buffer_pool_size = 16GB; innodb_buffer_pool_chunk_size = 1GB;   innodb_buffer_pool_instances = 4; 即：16GB = (1GB * 4) * 4
innodb_buffer_pool_size = 16GB; innodb_buffer_pool_chunk_size = 2GB;   innodb_buffer_pool_instances = 4; 即：16GB = (2GB * 4) * 2
innodb_buffer_pool_size = 16GB; innodb_buffer_pool_chunk_size = 4GB;   innodb_buffer_pool_instances = 4; 即：16GB = (4GB * 4) * 1

如何判断当前 buffer pool 配置是否满足业务需求、是否需要修改：查看相关的status变量。

innodb_buffer_pool_reads : 缓存池中不能满足的逻辑读的次数，这些读需要从磁盘中直接读取。备注：逻辑读是指从缓冲池中读，物理读是指从磁盘读。
innodb_buffer_pool_read_requests : 从buffer pool中逻辑读请求次数。逻辑读就是从缓存池中读取。

查询语法：SHOW [GLOBAL | SESSION] STATUS like '%innodb_buffer_pool_%';

show global status like '%innodb_buffer_pool_%';
show global status like '%innodb_buffer_pool_read%';
show global status like 'innodb_buffer_pool_reads';
show global status like 'innodb_buffer_pool_read_requests';

也可以执行 select 从 information_schema.global_status 或 information_schema.session_status 中查询：
select variable_name,variable_value from information_schema.global_status where variable_name like '%innodb_buffer_pool_%';
select variable_name,variable_value from information_schema.session_status where variable_name like '%innodb_buffer_pool_%';

> show global status like '%innodb_buffer_pool_%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        |                                                  |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 230728 13:20:40 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 15346                                            |
| Innodb_buffer_pool_bytes_data         | 251428864                                        |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 2355528                                          |
| Innodb_buffer_pool_pages_free         | 1024                                             |
| Innodb_buffer_pool_pages_misc         | 14                                               |
| Innodb_buffer_pool_pages_total        | 16384                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 9636                                             |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 185983361                                        |
| Innodb_buffer_pool_reads              | 5893                                             |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 5751200                                          |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)

> show global status like '%innodb_buffer_pool_read%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0         |
| Innodb_buffer_pool_read_ahead         | 9636      |
| Innodb_buffer_pool_read_ahead_evicted | 0         |
| Innodb_buffer_pool_read_requests      | 185983419 |
| Innodb_buffer_pool_reads              | 5893      |
+---------------------------------------+-----------+
5 rows in set (0.00 sec)

> show global status like 'innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 5893  |
+--------------------------+-------+
1 row in set (0.00 sec)

> show global status like 'innodb_buffer_pool_read_requests';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 185983499 |
+----------------------------------+-----------+
1 row in set (0.00 sec)

计算从缓冲池读取的百分比：
计算公式：
p = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%

select 
(select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests') as hit
,
(select variable_value as b from information_schema.global_status where variable_name = 'innodb_buffer_pool_reads') as unhit
,
(select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')/((select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')+(select variable_value as b from information_schema.global_status where variable_name = 'innodb_buffer_pool_reads')) as p;

select 
(select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests') as hit
,
(select variable_value as b from information_schema.global_status where variable_name = 'innodb_buffer_pool_reads') as unhit
,
(select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')/((select variable_value as a from information_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')+(select variable_value as b from information_schema.global_status where variable_name = 'innodb_buffer_pool_reads')) as p;

+-----------+-------+--------------------+
| hit       | unhit | p                  |
+-----------+-------+--------------------+
| 186105976 | 5893  | 0.9999683362483454 |
+-----------+-------+--------------------+

一般如果 p >= 95% 则表示我们的 innodb_buffer_pool_size的值是可以满足当前的需求的。否则可以考虑增加 innodb_buffer_pool_size 的大小。
 

如果执行报错：
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

原因为：MySQL 5.6 中 information_schema 中存在系统变量和状态变量的表，show variables 和 show status 也是基于此库中的表。但在 MySQL 5.7.6 时被 performance_schema 也存在这四张表，show 语句开始基于 performance_schema 中的表，如果 show_compatibility_56 参数开启，则兼容5.6。
因此，对应的SQL使用如下：

select 
(select variable_value as a from performance_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests') as hit
,
(select variable_value as b from performance_schema.global_status where variable_name = 'innodb_buffer_pool_reads') as unhit
,
(select variable_value as a from performance_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')/((select variable_value as a from performance_schema.global_status as t where variable_name = 'innodb_buffer_pool_read_requests')+(select variable_value as b from performance_schema.global_status where variable_name = 'innodb_buffer_pool_reads')) as p;
 

其他几个相关的状态信息查询：

查询当前激活的连接数：
select * from information_schema.global_status where variable_name = 'Threads_running';

查询当前打开的连接数：
select * from information_schema.global_status where variable_name = 'Threads_connected';

查询MySQL服务响应的最大连接数：
select variable_value as n from information_schema.global_status where variable_name = 'Max_used_connections';

查询连接信息详情（包括用户连接、系统连接）：
select * from information_schema.processlist;

连接数配置信息查询：
select @@max_connections;
show variables like '%max_conn%';
select * from information_schema.global_variables where variable_name = 'max_connections';

检查MySQL最大连接数配置是否合理：
检查服务器响应的最大连接数 / 配置参数设置的最大连接数，如果比例比较高时，需要修改最大连接数配置参数，检查SQL如下：
select 
(select variable_value as max_used from information_schema.global_status where variable_name = 'Max_used_connections') as max_used,
(select variable_value as max_set from information_schema.global_variables where variable_name = 'max_connections') as max_set,
(select variable_value as max_used from information_schema.global_status where variable_name = 'Max_used_connections')/(select variable_value as max_set from information_schema.global_variables where variable_name = 'max_connections') as p;
 

如果执行报错：
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
 

原因为：MySQL 5.6 中 information_schema 中存在系统变量和状态变量的表，show variables 和 show status 也是基于此库中的表。但在 MySQL 5.7.6 时被 performance_schema 也存在这四张表，show 语句开始基于 performance_schema 中的表，如果 show_compatibility_56 参数开启，则兼容5.6。
因此，对应的SQL使用如下：

select 
(select variable_value as max_used from performance_schema.global_status where variable_name = 'Max_used_connections') as max_used,
(select variable_value as max_set from performance_schema.global_variables where variable_name = 'max_connections') as max_set,
(select variable_value as max_used from performance_schema.global_status where variable_name = 'Max_used_connections')/(select variable_value as max_set from performance_schema.global_variables where variable_name = 'max_connections') as p;