本文主要是介绍MySQL 中的状态变量，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
文章目录
- 前言
- 1. 连接相关
- 1.1 连接线程
- 1.2 连接异常
- 1.3 最大连接数
- 2. Com 相关
- 3. 临时表相关
- 4. Table Cache 相关
- 5. 缓冲池相关
- 6. Redo log 相关
- 7. 行锁相关
- 8. 排序相关
- 9. 查询相关
- 10. 流量相关
前言
本篇文章介绍一些 MySQL 中常用的监控指标，常见的监控工具都是采集 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%。
show status where Variable_name in ('Threads_connected', 'Threads_running', 'Threads_cached', 'Threads_created');
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 ('Aborted_clients', 'Aborted_connects');
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 的作用就是减少重复数据页加载频率。
推荐阅读：快速掌握 Innodb
以下是缓冲池中数据页面的相关变量：
| 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 恢复到崩溃前的状态。
推荐阅读：8.0 新特性 - innodb_redo_log_capacity
以下是 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
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 可以反映当前数据库行锁的情况，可根据该状态变量配置行锁告警。
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：数据库的流量吞吐，可以帮助我们了解数据库的负载状况和并发处理能力。建议为其每秒增量配置监控。
这篇关于MySQL 中的状态变量的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
