天涯头条

数据库性能大揭秘：玩转MySQL监控指标状态变量

2024-08-16 11:00 由 袋鼠云数栈 发表于 #数据库

前言

在本文中，我们将深入探讨MySQL数据库的性能监控世界。通过了解并应用一系列常用的监控指标，我们能够更精准地把握数据库的运行状况。这些指标，通常以状态变量（status variables）的形式呈现，是洞察数据库性能的关键。

接下来，我们将逐一解析与连接相关的监控变量，包括当前连接数、活跃连接数、缓存线程数以及已创建的线程数。这些指标不仅帮助我们评估连接使用效率，还能预警潜在的性能问题。

此外，文章还将涵盖查询效率、临时表使用、表缓存效率、缓冲池状态、日志文件操作、行锁争用、排序操作、查询模式以及流量吞吐等多个方面的监控指标。通过对这些指标的深入分析，我们能够构建一个全面而有效的数据库性能监控体系。

通过本文，希望读者能够游刃有余地配置和优化MySQL监控方案，从而在数据库性能问题出现时，能够迅速定位并解决。

1. 连接相关

MySQL 默认的调度方式是每一个连接一个线程，既 one-thread-per-connection 所以本节涉及到的变量，基本可以视为连接。
Tips：one-thread-per-connection 适合于低并发长连接的环境，而在高并发或大量短连接环境下，大量创建和销毁线程，以及线程上下文切换，会严重影响性能，如果遇到此类瓶颈，可以使用线程池（pool-of-threads）来优化。

1.1 连接线程

以下是连接线程涉及到的变量：

Variable Name Variable Scope Variable Meaning
Threads_connected Global 当前连接（线程）数，该值等于 SHOW PROCESSLIST 的总数。
Threads_running Global 当前处于活跃状态的连接（线程）数，如果该值过大，会导致系统频繁地切换上下文，CPU 使用率也会比较高。
Threads_cached Global Threads cache 缓存的线程数。在创建新的连接时，会首先检查 Threads cache 中是否有缓存的线程。如果有则复用，如果没有则创建新的线程。在线程池的场景中，会禁用 Threads cache 此时该值为 0。
Threads_created Global 已创建的线程数。反应的是累加值，如果该值过大，说明 Threads cache 过小，可考虑适当增大 thread_cache_size 的值。

Tips：建议配置连接数使用率和活跃连接数使用率告警，连接数被占满会导致业务报错，Threads_connected / max_connections 推荐阈值 85% 活跃连接数使用率过高，通常 CPU 使用率也会高，意味着系统很繁忙 Threads_running / max_connections 推荐阈值 50%。

1.2 连接异常

以下是连接异常相关的状态变量：

Aborted_clients：客户端已成功建立，但中途异常断开连接的次数。常见原因有以下几种。

  • 客户端程序断开连接前，没有调用 mysql_close() 方法。
  • 客户端连接的休眠时间超过 wait_timeout 的会话值，被服务器主动断开。
  • 客户端程序在数据传输时突然断开。
  • 数据包的大小超过 max_allowed_packet 的限制。

对于中途断开的连接，错误日志（log_error_verbosity = 3）中通常会有如下信息：
[Note] Aborted connection 184618 to db: 'xxx' user: 'xxx' host: 'xxxx' (Got an error reading communication packets)

Aborted_connects：连接 MySQL 服务端失败的次数。常见的原因有以下几种。

  • 客户端账号密码不准确。
  • 没有指定库的访问权限。
  • 连接包中没有包含正确的信息。
  • 超过 connect_timeout 服务端没有收到客户端的连接包。
show status where Variable_name in ('Threads_connected', 'Threads_running', 'Threads_cached', 'Threads_created');

1.3 最大连接数

以下是连接数相关的状态变量：

Variable Name Variable Scope Variable Meaning
Max_used_connections Global 数据库历史最大的连接数。
Max_used_connections_time Global 连接数达到历史最大的时间。
Connection_errors_max_connections Global 连接数占满后，应用有新的连接后返回 Too many connections 错误，该值也会随之增大。

Tips：MySQL 中的最大连接数由参数 max_connections 控制，默认是 151。当连接数达到 max_connections 的限制，业务会返回报错 Too many connections 状态变量 Connection_errors_max_connections 也会随之增大。建议基于 Threads_connected / max_connections 做好连接数使用率监控，如果大于 85% 则触发告警。

show status where Variable_name in ('Max_used_connections', 'Max_used_connections_time', 'Connection_errors_max_connections');

2、Com 相关

统计操作执行的次数。以下状态变量在监控中使用较多，可以反应数据库的繁忙程度。

Variable Name Variable Scope Variable Meaning
Com_insert Both insert 语句执行次数。
Com_select Both select 语句执行次数。
Com_update Both update 语句执行次数。
Com_delete Both delete 语句执行次数。
Com_commit Both commit 语句执行次数。
Com_rollback Both rollback 语句执行次数。
Com_replace Both replace 语句执行次数。

Tips：此类变量可以使用 flush status 命令归零，重新累加统计。每秒执行事务的次数 TPS 可通过 Com_commit + Com_rollback 每秒增量来计算。

这里只列出了部分常见操作，完整的可以使用下方 SQL 查看。

show status like 'Com%';

3、临时表相关

MySQL 在执行 order by、group by 查询时，通常会建立一个或两个临时表，当临时表较小时，可以放到内存中，较大时则会存在于磁盘上。可以通过以下 3 个变量监控临时文件使用情况。

Variable Name Variable Scope Variable Meaning
Created_tmp_disk_tables Both MySQL 内部临时表转化为磁盘表的数量。
Created_tmp_files Global MySQL 创建临时文件的数量。
Created_tmp_tables Both MySQL 创建在内存临时表的数量。

Tips：理论上来讲使用临时表无法避免，但是肯定是越少越好，并且磁盘临时表需要保持在一个很小的值，经验值 Created_tmp_disk_tables / Created_tmp_tables 小于 20%。

show status like 'Created%';

4、Table Cache 相关

为了提升表的访问效率，表在使用完毕后，不会立即关闭，而是会缓存在 Table Cache 中，可通过以下 6 个变量监控 Table Cache 使用情况。

Variable Name Variable Scope Variable Meaning
Open_tables Both 当前打开表的数量。
Open_table_definitions Global 当前缓存的 frm 文件的数量。
Opened_tables Both 打开过的表的数量。
Open_table_definitions Global 缓存过的 frm 文件的数量。
Table_open_cache_hits Both Table Cache 的命中次数。
Table_open_cache_misses Both Table Cache 没有命中的次数。
Table_open_cache_overflows Both 表缓存被删除的次数。

当 MySQL 要访问一张表的时候，首先会检查该表的文件描述符是否在 Table Cache 中，如果存在则直接使用，并增大 Table_open_cache_hits 的值，如果不存在，则打开表，并增大 Opened_tables 和 Table_open_cache_misses 的值。然后将表缓存在 Table Cache 中。

当 Table Cache 达到了 table_open_cache 的限制，此时分两种场景：

  1. 缓存中存在未使用的表： 会使用 LRU 算法淘汰掉未使用的表，并在 Table Cache 中删除，同时会增大 Table_open_cache_overflows 的值。

  2. 缓存中的表都在使用： 会临时扩容 Table Cache， 一旦检测出未使用的表，则触发清理，从而保持在 table_open_cache 之下。

Tips：如果观测 Opened_tables 大于 table_open_cache 且在持续增大，意味着 table_open_cache 相对较小，此时可适当调大参数。

show status where Variable_name in ('Open_tables', 'Open_table_definitions', 'Opened_tables', 'Open_table_definitions', 'Table_open_cache_hits', 'Table_open_cache_misses', 'Table_open_cache_overflows');

5. 缓冲池相关

对于 innodb 表引擎来说，用户数据和索引及系统元数据，都是以页的形式存储在表空间中，表空间是 innodb 对文件系统上一个或多个物理文件的抽象，也就是说数据到底还是存储在磁盘中的。但是磁盘的速度要比内存慢太多，速度跟不上 CPU 的计算速度，所以 innodb 引擎需要访问某个页的数据时，就会把完整的页全部加载的内存中（页大小默认 16 k）即使访问一个页的一行数据，也需要先把完整的页加载的内存中，Innodb 所有读写操作都是在内存中完成的，完成读写后 innodb 并不会立刻释放掉，而是先缓存起来，后面如果有请求需要用到这张页的话，就可以直接从内存读取，可以省去磁盘 IO 的开销。

MySQL 缓冲池也使用 LRU 算法进行调度，本质是让热数据页在缓存中长时间保留，提高查询访问效率，但是缓存是有限的，LRU 的作用就是减少重复数据页加载频率。

以下是缓冲池中数据页面的相关变量：

Variable Name Variable Scope Variable Meaning
innodb_buffer_pool_pages_data Global 缓冲池中数据页的数量，包括干净页和脏页。
innodb_buffer_pool_bytes_data Global 数据页的大小，单位是字节。
innodb_buffer_pool_pages_dirty Global 脏页的数量。
innodb_buffer_pool_bytes_dirty Global 脏页的大小，单位是字节。
innodb_buffer_pool_pages_free Global 空闲页的数量。
innodb_buffer_pool_pages_misc Global 用于管理开销而分配的页的数量，比如行锁、自适应哈希索引等。
innodb_buffer_pool_pages_total Global 页的总数量。
innodb_buffer_pool_pages_flushed Global 脏页被刷盘的次数。
innodb_buffer_pool_wait_free Global 等待空闲页的次数。 
show status 
where 
  Variable_name in (
    'innodb_buffer_pool_pages_data', 
    'innodb_buffer_pool_bytes_data', 
    'innodb_buffer_pool_pages_dirty', 
    'innodb_buffer_pool_bytes_dirty', 
    'innodb_buffer_pool_pages_free', 
    'innodb_buffer_pool_pages_misc', 
    'innodb_buffer_pool_pages_total', 
    'innodb_buffer_pool_pages_flushed', 
    'innodb_buffer_pool_wait_free'
  );
	```
	
	如果有大表全表扫描的 SQL 执行的时候需要将整张表都加载到 buffer pool 中，导致 buffer pool 中的热点数据被置换出去，这种情况叫做缓存污染，可以通过缓存命中率来监控此类情况。
● Innodb_buffer_pool_read_requests：逻辑读的数量，既缓存读。
● Innodb_buffer_pool_reads：物理读的数量，既磁盘读。

Innodb 缓存命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests
OLTP 型业务，缓存命中率应大于 95%，如果命中率低，则需要调大 innodb_buffer_pool_size 及排查是否有全表扫描 SQL。
另外，通过下方 SQL 可以观测 Innodb 删除、插入、读取、更改的行数。
show status like 'innodb_rows%';

# 6. Redo log 相关
为了取得更好的读写性能，InnoDB 会将数据缓存在内存中（InnoDB Buffer Pool）对磁盘数据的修改也会落后于内存，这时如果进程或机器崩溃，会导致内存数据丢失，为了保证数据库本身的一致性和持久性，InnoDB 维护了 REDO LOG。

修改 Page 之前需要先将修改的内容记录到 REDO 中，并保证 REDO LOG 早于对应的 Page 落盘，也就是常说的 WAL（Write Ahead Log）日志优先写，Redo Log 的写入是顺序 IO，可以获得更高的 IOPS 从而提升数据库的写入性能。

当故障发生导致内存数据丢失后，InnoDB 会在重启时，通过重放 REDO，将 Page 恢复到崩溃前的状态。

以下是 Redo log 相关的状态变量：

| Variable Name | Variable Scope | Variable Meaning  |
|  -  |  -  |  -  |
|Innodb_log_waits|Global|因 redo buffer 过小，导致 redo log buffer 刷盘的次数。|
|Innodb_log_write_requests| Global  |写 redo log buffer 的次数。|
|Innodb_log_writes|  Global  |写 redo log 次数。|
|Innodb_os_log_fsyncs|  Global  |对 redo log 调用 fsync 操作的次数。|
|Innodb_os_log_pending_fsyncs| Global  |fsync 操作等待的次数。|
|Innodb_os_log_pending_writes|  Global |写 redo log 等待次数。|
|Innodb_os_log_written|   Global  |redo log 的写入量，单位是字节。|

通过以上状态变量可以看出数据库的写入情况，如果 Innodb_log_waits 持续增大，需要确认 redo log 文件和 buffer 相关配置是否合适。另外不能通过 Innodb_os_log_written 来反映 redo 的写入量，因为 redo log 基本存储单位是 block 512 bytes 小于基本存储单位的写入也会以基本单位来计算。
要评估 Redo log 写入量可参考下方文档。

推荐阅读：[How to calculate a good InnoDB log file size](https://www.percona.com/blog/how-to-calculate-a-good-innodb-log-file-size/)

# 7. 行锁相关
数据库的核心方向就是高并发，整体业务场景大多是 读-读、读-写、写-写，三类并发场景，看似容易融合到业务场景后也比较复杂。通过锁机制主要可以帮助我们解决 写-写 和 读-读 场景下的并发安全问题，所以锁争用和锁等待也是经常遇到的情况，
可通过下方状态变量了解数据库中的行锁信息：

● Innodb_row_lock_current_waits：当前正在等待行锁的操作数。
● Innodb_row_lock_time：获取行锁花费的总时间，单位毫秒。
● Innodb_row_lock_time_avg：获取行锁花费的平均时间，单位毫秒。
● Innodb_row_lock_time_max：获取行锁花费的最大时间，单位毫秒。

下面我们来做一个实验：

root@mysql 14:38: [(none)]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 16582 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 2 |
+-------------------------------+-------+


| Session 1 | Session 2 | 
|  -  |  -  |  
|Begin;|  |
|delete from score where id = 5;|  |
|  |update score set number = 66 where id = 5; -- 等待行锁|

root@mysql 14:41: [test]>show status like '%Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 11055 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+-------+


此时可以发现 Innodb_row_lock_waits 和 Innodb_row_lock_current_waits 都增长了，time 相关的变量需要等事务结束后才会进行计算。
Tips：Innodb_row_lock_current_waits 可以反映当前数据库行锁的情况，不过不是很准确，有 bug：https://bugs.mysql.com/bug.php?id=71520

# 8. 排序相关
MySQL 中如果有涉及到排序的操作（ORDER BY、GROUP BY、DISTINCT）操作时，如果无法使用索引，则会使用文件排序。执行计划中的 Extra 列会显示 Using filesort。

MySQL 会为需要 filesort 的会话分配单独排序的缓存区（sort buffer）排序缓存区是需要时才分配，且按需分配，最大限制由 sort_buffer_size 控制，默认是 256KB。如果需要排序的记录较少，既 sort buffer 够用，那么在内存中排序也是非常快的。如果需要排序的记录非常多，MySQL 会分批处理，每一批首先会在排序缓存区中排序，排序后的结果会存储在临时文件中。每个排序缓存区对应一个临时文件中的一个 block。处理完毕后，最后再对临时文件中的 block 进行归并排序，相比直接在内存中排序需要消耗额外的 IO 和 CPU 计算资源。

以下是排序相关的状态变量：
● Sort_merge_passes：反映的是 sort buffer 不够用，使用临时文件归并排序的次数。
● Sort_range：对索引范围扫描的结果进行排序的次数。
● Sort_rows：排序的记录数。
● Sort_scan：对全表扫描的结果进行排序的次数。

show status like '%Sort%';


Tips：需要关注 Sort_merge_passes 的值，如果持续增大，说明有行数较大的排序操作，需要定位 SQL 判断是否调大 sort buffer。

# 9. 查询相关
以下是查询相关的状态变量：

| Variable Name | Variable Scope | Variable Meaning  |
|  -  |  -  |  -  |
|Select_scan|Both|全表扫描的次数，如果是关联查询，指的是驱动表执行了全表扫描。|
|Select_full_join| Both |同样是全表扫描，不过只包含关联场景，驱动表全表扫描的次数。|
|Select_range|  Both |范围查询次数。如果是关联查询，指的是驱动表执行了范围查询。|
|Select_full_range_join|  Both  |同样是范围查询，不过只包含关联场景，驱动表全表扫描的次数。|
|Select_range_check| Both |常用于非等值的关联查询中。|
|Slow_queries|  Both |慢查询的数量，无论是否开启了慢查询，只要 SQL 执行耗时大于 long_query_time 该值就会增加。|

show status
where
Variable_name in (
'Select_scan',
'Select_full_join',
'Select_range',
'Select_full_range_join',
'Select_range_check',
'Select_range_check',
'Slow_queries'
);
```

Tips：Select_scan 可以反映数据库是否存在全表扫描的 SQL，从 Slow_queries 可以看出存储中慢 SQL 的数量，建议为这两个状态变量配置监控。

10. 流量相关

以下是流量吞吐相关的状态变量：
● bytes_received：从客户端接收的流量大小，单位是字节。
● bytes_sent：发送给客户端端流量大小，单位是字节。

show status 
where 
  Variable_name in (
    'bytes_received', 
    'bytes_sent'
  );
	```
	
	Tips：数据库的流量吞吐，可以帮助我们了解数据库的负载状况和并发处理能力。建议为其每秒增量配置监控。
《行业指标体系白皮书》下载地址：https://www.dtstack.com/resources/1057?src=szsm

《数栈产品白皮书》下载地址：https://www.dtstack.com/resources/1004?src=szsm

《数据治理行业实践白皮书》下载地址：https://www.dtstack.com/resources/1001?src=szsm

想了解或咨询更多有关大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=szbky

热门相关：驭房我不止有问心术   剑斗九天   闪婚总裁很惧内   国民女神：重生王牌千金   隐婚99天：首长，请矜持