MySQL 中的状态变量
本篇文章介绍一些 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 的作用就是减少重复数据页加载频率。 推荐阅读： 以下是缓冲池中数据页面的相关变量：
| 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 写入量可参考下方文档。 推荐阅读：
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。
