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;
扫一扫
883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
