MySQL常用监控指标学习了解

‍MySQL的监控指标还是很多的，可能各使用这都有自己关注的方向，阈值这些都会有所不同，陈老师新书中的这篇文章《一文说透 MySQL 常用监控指标》介绍了监控指标，很全面，学习下。

在使用监控时，很多人都会遇到这样一个问题：监控项明明很多，但当线上实例出现问题时，还是两眼一抹黑，不知道该分析哪个监控项。

之所以会出现这样的问题，究其原因，还是对监控项不熟，不懂得监控项的具体含义。

基于此，本文会系统梳理 MySQL 中那些常用的监控指标，包括指标含义、导致指标发生变化的原因及使用过程中的注意事项。

MySQL 中绝大多数的监控指标都来源于状态变量（Status Variable），所以下面会从状态变量出发，来看看相关监控项的具体含义。

常用的状态变量主要包括以下几大类，

1. 连接相关

2. Com 相关

3. Handler 相关

4. 临时表相关

5. Table Cache 相关

6. 文件相关

7. 主从复制相关

8. Buffer Pool 相关

9. Redo log 相关

10. 锁相关

11. 排序相关

12. 查询相关

13. 其它重要指标

一、连接相关

Aborted_clients

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

• 客户端程序在断开前，没有调用 mysql_close() 方法。

• 客户端连接的空闲时间超过 wait_timeout 的会话值，被服务端主动断开。

• 客户端程序在数据传输过程中突然结束。

• 数据包的大小超过 max_allowed_packet 的限制。

对于中途异常断开的连接，错误日志中通常会输出如下信息：

[Note] Aborted connection 42 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)

错误日志中是否输出此类信息还与 log_error_verbosity 的设置有关，只有为 3 时才输出。该参数是 MySQL 5.7 引入的，默认值在 MySQL 5.7 中是 3，在 MySQL 8.0 中调整为了 2。所以，在 MySQL 5.7 中我们会经常看到此类信息。

Aborted_connects

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

• 客户端的账号密码不准确。

• 没有指定库的访问权限。

• 连接包中没有包含正确信息。

• 超过 connect_timeout（默认 10s ），服务端还没有收到客户端的连接包。

Threads_connected，Threads_running，Threads_cached，Threads_created

MySQL 默认的线程调度方式是每个连接一个线程（one-thread-per-connection）。所以，在理解这四个变量时，基本上可将其等价为连接。各个状态变量的具体含义如下，

• Threads_connected：当前连接（线程）数，这个值等于 SHOW PROCESSLIST 的总条数。

• Threads_running：当前处于活跃状态的线程数，即 SHOW PROCESSLIST 中非 sleep 状态的连接数 。

  如果该值过大，会导致系统频繁的上下文切换，CPU sys 使用率较高。

• Threads_cached：Thread Cache 缓存的线程数。

  在创建新的连接时，会首先检查 Thread Cache 中是否有缓存的线程。如果有，则直接复用，如果没有，则会创建新的线程。

  在线程池场景，会禁用 Thread Cache，此时，Threads_cached 会显示为 0。

• Threads_created：已创建的线程数。反映的是累计值。

  如果该值持续增大，则代表 Thread Cache 过小，此时，可适当增大 thread_cache_size 的值。

Max_used_connections，Max_used_connections_time，Connection_errors_max_connections

MySQL 中的最大连接数由参数 max_connections 控制，默认是 151。

如果当前连接数达到了 max_connections 的限制，则新的连接会创建失败，并提示 Too many connections 错误，Connection_errors_max_connections 也会随之增加。

当我们观察到 Connection_errors_max_connections 大于 0 时，就已经晚了，最好是基于 Threads_connected / max_connections 做一个连接数使用率监控，如果达到 85% 则触发告警。

Max_used_connections 反映的是历史最大连接数。Max_used_connections_time 是连接数达到最大时的时间。

二、Com 相关

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

Com_select，Com_insert，Com_delete，Com_update，Com_commit，Com_rollback

分别用来统计 SELECT，INSERT，DELETE，UPDATE，COMMIT 和 ROLLBACK 操作执行的次数。

以 Com_select 为例，看下面这个示例，

这几个变量中，可用 Com_commit + Com_rollback 来衡量 TPS（ transaction per second，每秒事务数）。一些资料中，我们会看到下面这个计算公式，

TPS = ( Com_commit + Com_rollback ) / Uptime

不过通过这个公式得到的结果是平均值，参考意义不大。建议取 Com_commit 的每秒增量 + Com_rollback 的每秒增量。

三、Handler 相关

Handler 是一个类，按不同的功能模块定义了若干接口。MySQL Server 在做增删改查等操作时，不会直接与存储引擎层交互，而是调用 Handler 对象的相关方法。由此带来的好处是，实现了 Server 层与存储引擎层的解耦，方便了新引擎的引入。

Handler_delete，Handler_update，Handler_write

分别对应 DELETE，UPDATE，INSERT 操作影响的记录数，这点与 Com_xxx 不一样，后者反映的只是操作的次数。看下面这个示例，

Handler_commit，Handler_prepare，Handler_rollback，Handler_savepoint，Handler_savepoint_rollback

分别对应 COMMIT，PREPARE，ROLLBACK，SAVEPOINT， ROLLBACK TO SAVEPOINT 操作的数量。

需要注意的是，对于 InnoDB 存储引擎，在进行 DML 操作时，因为涉及到事务的两阶段提交，对于任何一个操作，都会导致对应的 Handler 加 2（一个对应 Binlog ，一个对应 InnoDB）。

Handler_read_key，Handler_read_first，Handler_read_last，Handler_read_next，Handler_read_prev，Handler_read_rnd，Handler_read_rnd_next

这些变量的具体含义如下，

• Handler_read_key：基于索引来定位记录，该值越大，代表基于索引的查询越多。

• Handler_read_first：读取索引的第一个值，该值越大，代表涉及索引全扫描的查询越多。

• Handler_read_last：和 Handler_read_first 相反，是读取索引的最后一个值。

  若该值增大，基本上可以判定查询中使用了基于索引的 ORDER BY DESC 子句。

• Handler_read_next：根据索引的顺序来读取下一行的值，常用于基于索引的范围扫描和 ORDER BY LIMIT 子句中。

• Handler_read_prev：根据索引的顺序来读取上一行的值。一般用于基于索引的 ORDER BY DESC 子句中。

• Handler_read_rnd：将记录基于某种标准进行排序，然后再根据它们的位置信息来遍历排序后的结果，这往往会导致表的随机读。

• Handler_read_rnd_next：读取下一行记录的次数，常用于全表扫描中。

在使用时需注意以下几点：

• Handler_read_key 的值越大越好，代表基于索引的查询较多。

• Handler_read_first，Handler_read_last，Handler_read_next，Handler_read_prev 都会利用索引。但查询是否高效还需要结合其它 Handler_read 的值来判断。

• Handler_read_rnd 不宜过大。

• Handler_read_rnd_next 不宜过大。过大的话，代表全表扫描过多，要引起足够的警惕。

四、临时表相关

Created_tmp_files，Created_tmp_tables，Created_tmp_disk_table

这些变量的具体含义如下，

• Created_tmp_files：创建的临时文件的数量。

• Created_tmp_tables：创建的临时表的数量。

• Created_tmp_disk_tables：内存临时表转化为磁盘临时表的数量。

内存临时表超过一定的大小会转化为磁盘临时表。这个大小与内存临时表使用的引擎有关。

如果使用的是 MEMORY 引擎，这个大小由以下两个参数中的最小值决定。

• tmp_table_size：内存临时表的最大大小，默认 16M。

• max_heap_table_size：MEMORY 表的最大大小，默认 16M。

如果使用的是 TempTable 引擎，则上述大小由 tmp_table_size 决定。TempTable 引擎是 MySQL 8.0 引入的。

五、Table Cache 相关

为了提升表的访问效率，表使用完毕后，不会立即关闭，而是会缓存在 Table Cache 中。当 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 Cache 的大小降到 table_open_cache 之下。

除此之外，FLUSH TABLES 操作也会删除 Table Cache 中的缓存。

Table Cache 相关的参数有，

• table_open_cache：Table Cache 的大小。

  该参数在 MySQL 5.6，5.7 中，默认为 2000，在 MySQL 8.0 中，默认为 4000。

• table_open_cache_instances：Table Cache 实例的数量。

  设置多个实例，每个实例可缓存的表的数量就等于 table_open_cache / table_open_cache_instances 。

  在表数量较多的场景，能有效降低锁争用。如果主机 CPU 的核数大于或等于16，建议将该参数设置为 8 或 16。

• table_definition_cache：限制可缓存的 frm 文件的数量。

Table Cache 相关的变量有：

• Open_tables：当前打开的表的数量。

• Open_table_definitions：当前缓存的 frm 文件的数量。

• Opened_tables：打开过的表的数量。

• Opened_table_definitions：缓存过的 frm 文件的数量。

• Table_open_cache_hits：Table Cache 命中的次数。

• Table_open_cache_misses：Table Cache 没有命中的次数。

• Table_open_cache_overflows：表缓存被删除的次数。

如果我们观察到 Opened_tables > table_open_cache 且 Opened_tables 持续增加，则意味着 table_open_cache 过小，此时，可适当调大 table_open_cache 的值。

六、文件相关

MySQL 进程可打开的最大文件描述符数由 open_files_limit 决定，当文件描述符不足时，会提示 Too many open files 错误。

需要注意的是，open_files_limit 并不是所设即所得，配置的和实际生效的并不完全一样。它的生成逻辑具体如下：

open_files_limit 首先会取以下三个值中的最大值。

• 10 + max_connections + table_open_cache * 2

• max_connections * 5

• open_files_limit ? open_files_limit : 5000。如果配置文件中指定了 open_files_limit，则取 open_files_limit，否则是 5000。

接着，会比较 open_files_limit 与 MySQL 进程可打开的最大文件描述符数的大小。

1、如果进程的最大文件描述符数超过 open_files_limit，则实际生效的是进程的最大文件描述符数。

2、如果进程的最大文件描述符数小于 open_files_limit，这个时候需区分启动用户。

• 如果启动用户是 root，则实际生效的是 open_files_limit。

• 如果启动用户是普通用户，则需要继续比较 open_files_limit 与硬限制（ulimit -Hn）的大小。

  • 如果 open_files_limit 小于等于硬限制，则实际生效的是 open_files_limit。

  • 如果 open_files_limit 大于硬限制，则实际生效的是进程的最大文件描述符数。

另外，如果进程是通过 mysqld_safe 启动的，mysqld_safe 会基于 open_files_limit 调整进程的最大文件描述符数。

ulimit -n $open_files

文件相关的状态变量有，

• Innodb_num_open_files: InnoDB 当前打开的文件数。

  InnoDB 同时能打开的最大文件数由 innodb_open_files 参数决定，后者默认为 -1，基于 table_open_cache 自动调整。

  innodb_open_files 并不是一个硬限制，如果 InnoDB 打开的文件数达到了 innodb_open_files 的限制，会通过 LRU 算法关闭其它文件。

• Open_files: 当前打开的文件数。

  注意，它不包括存储引擎使用自己内部函数打开的文件数。所以，在计算 MySQL 进程当前打开的文件数时，需加上 Innodb_num_open_files。

• Opened_files: 打开过的文件数。

当 Open_files + Innodb_num_open_files 接近 open_files_limit 时，就需要调整进程的最大文件描述符数了。

常规思路是修改 open_files_limit，但 open_files_limit 是个只读参数，重启实例才能生效。

实际上，不用这么麻烦。从 CentOS 6 开始，可在线调整进程的最大文件描述符数，无需重启进程。具体命令如下，

# CentOS 6
echo -n "Max open files=102400:102400" > /proc/${PID}/limits
 
# CentOS 7
prlimit --pid ${PID} --nofile=102400:102400

注意，CentOS 6 的调整方式在 CentOS 7 中不适用，同样，CentOS 6 中也不支持 prlimit 命令。

七、主从复制相关

主从复制重点关注以下三个指标，

• Slave_IO_Running：IO 线程的状态。

• Slave_SQL_Running：SQL 线程的状态。

• Seconds_Behind_Master：主从延迟时间。

这三个指标均取自 SHOW SLAVE STATUS。

除此之外，还需关注从库 read_only（super_read_only） 的值。为了避免对从库的误操作，建议将从库设置为只读。

八、Buffer Pool 相关

Buffer Pool 中有三个比较核心的链表来维护管理数据页。

• Free List：管理空闲页。空闲页指的是未被使用的页。在 Buffer Pool 初始化时，会将 Buffer Pool 的所有页加入到 Free List 中。

• LRU List：管理 Clean 页和脏页。Clean 页指的是数据与磁盘中内容一致的数据页。脏页指的是被修改过，但还没有刷盘的数据页。

• FLU List：管理脏页。脏页按照 oldest_modification（最早修改时间）进行降序排序，最早修改的脏页会放到链表尾部。

当用户需要读取磁盘中的一个数据页时，会从 Free List 中获取空闲页。如果有，则直接返回。如果没有，则会尝试驱逐 LRU List 末尾的数据页。

以下是 Buffer Pool 数据页相关的状态变量，

• Innodb_buffer_pool_pages_data：Buffer Pool 中数据页的数量，包括 Clean 页和脏页。

• Innodb_buffer_pool_bytes_data：数据页的大小，单位 byte。

• Innodb_buffer_pool_pages_dirty：脏页的数量。

• Innodb_buffer_pool_bytes_dirty：脏页的大小，单位 byte。

• 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：等待空闲页的次数。

这几个变量之间的关系如下，

• Innodb_buffer_pool_pages_total =  Innodb_buffer_pool_pages_data + Innodb_buffer_pool_pages_free + Innodb_buffer_pool_pages_misc

• 在不使用表压缩的情况下，Innodb_buffer_pool_bytes_data = Innodb_buffer_pool_pages_data * 16k，其中，16k 为页的默认大小。

• 在不使用表压缩的情况下，innodb_buffer_pool_size = Innodb_buffer_pool_pages_total * 16k

Innodb_buffer_pool_read_requests，Innodb_buffer_pool_reads

前者是逻辑读的数量，后者是物理读的数量。

Innodb buffer pool 命中率 = ( Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests。

可根据 Innodb buffer pool 命中率来判断 innodb_buffer_pool_size 的设置是否合理，一般情况下，应保证该值大于 95%。

Innodb_rows_deleted，Innodb_rows_inserted，Innodb_rows_read，Innodb_rows_updated

InnoDB 表被删除、插入、读取，更新的行数。

九、Redo log 相关

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

• Innodb_log_waits：因 redo log buffer 过小，导致 redo log buffer 刷盘的次数。

• Innodb_log_write_requests：写 redo log buffer 的次数。

• Innodb_log_writes：写 redo log 的次数。

• Innodb_os_log_fsyncs：对 redo log 调用 fsync 操作的次数。

• Innodb_os_log_pending_fsyncs：fsync 操作等待的次数。

• Innodb_os_log_pending_writes：写 redo log 等待的次数。

• Innodb_os_log_written：redo log 的写入量，单位 byte。

注意，不要使用 Innodb_os_log_written 来反映 redo log 的写入量。看下面这个示例。

1. 事务 1 写入了 100 个字节到 redo log buffer 中，相应地，LSN（日志序列号）会增加 100。

2. 事务 1 提交，redo log buffer 会刷新到 redo log 中。因为 redo log 的基本存储单位是块（block），且块的大小是 512 字节。所以，在刷新完毕后，Innodb_os_log_written 会增加 512。

3. 接下来，事务 2 写入了 200 个字节到 redo log buffer 中，相应地，LSN 会增加 200。

4. 事务 2 提交，同样地，Innodb_os_log_written 会增加 512。

Redo log buffer 实际写入了 300 个字节，但对应的 Innodb_os_log_written 却增加了 1024。

如果要评估 redo log 的写入量，推荐使用下面这种方式，

mysql> pager grep sequence
PAGER set to 'grep sequence'
 
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 88396793310905
1 row in set (0.01 sec)
 
1 row in set (1 min 0.00 sec)
 
Log sequence number 88396813216350
1 row in set (0.01 sec)
 
mysql> nopager
PAGER set to stdout
 
mysql> select 88396813216350 - 88396793310905;
+---------------------------------+
| 88396813216350 - 88396793310905 |
+---------------------------------+
|                        19905445 |
+---------------------------------+
1 row in set (0.00 sec)

十、锁相关

以下是行锁相关的状态变量，

• Innodb_row_lock_current_waits：当前正在等待行锁的操作数。

• Innodb_row_lock_time，Innodb_row_lock_time_avg，Innodb_row_lock_time_max：获取行锁花费的总时间，平均时间，最大时间。单位 ms。

• Innodb_row_lock_waits：等待行锁的次数。

以下是表锁相关的状态变量，

• Table_locks_immediate：能立即获得表锁的次数。

• Table_locks_waited：等待表锁的次数。

在 MySQL 中，表锁只用于 MyISAM，MEMORY，MERGE 引擎，读写相互阻塞。

看下面这个示例，

session1> create table t1(id int primary key) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
 
session1> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
session1> select id,sleep(100) from t1;
执行中。。。
 
session2> flush status;
Query OK, 0 rows affected (0.00 sec)
 
session2> show global status like 'Table_locks_waited';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Table_locks_waited | 0     |
+--------------------+-------+
1 row in set (0.00 sec)
 
session2> delete from t1 where id=1;
阻塞中。。。
 
session3> show global status like 'Table_locks_waited';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Table_locks_waited | 1     |
+--------------------+-------+
1 row in set (0.00 sec)
 
session3> show processlist;
+----+-----------------+-----------+----------+---------+------+------------------------------+------------------------------+
| Id | User            | Host      | db       | Command | Time | State                        | Info                         |
+----+-----------------+-----------+----------+---------+------+------------------------------+------------------------------+
|  5 | event_scheduler | localhost | NULL     | Daemon  | 1539 | Waiting on empty queue       | NULL                         |
| 18 | root            | localhost | slowtech | Query   |   39 | User sleep                   | select id,sleep(100) from t1 |
| 19 | root            | localhost | slowtech | Query   |   17 | Waiting for table level lock | delete from t1 where id=1    |
| 20 | root            | localhost | NULL     | Query   |    0 | init                         | show processlist             |
+----+-----------------+-----------+----------+---------+------+------------------------------+------------------------------+
4 rows in set (0.00 sec)

十一、排序相关

Sort_merge_passes，Sort_range，Sort_rows，Sort_scan

看下面这个示例，

结合实例，来看看这四个变量的含义，

• Sort_merge_passes：在涉及到排序（ORDER BY，GROUP BY，DISTINCT）操作时，如果无法使用索引，则会使用 filesort 。使用 filesort 进行排序时，执行计划的 Extra 列会显示 Using filesort。

  在使用 filesort 时，MySQL 会分配单独的排序缓存区（sort buffer）。排序缓存区是需要时才分配，且按需分配。它的最大大小由 sort_buffer_size 决定，默认是 256 K。如果要排序的记录数比较少，只用排序缓存区就能完成排序操作，这个时候处理的效率就会非常高。

  如果记录数较多，MySQL 会分批处理，每一批首先会在 sort buffer 中排序，排序后的结果会存储到磁盘的临时文件中。每个 sort buffer 对应临时文件中的一个 block。处理完毕后，最后再对临时文件中的 block 进行归并排序。

  Sort_merge_passes 反映的是归并操作的次数。如果 Sort_merge_passes 较大，可适当调大 sort_buffer_size 的值。

• Sort_range：对索引范围扫描的结果进行排序的次数。

• Sort_scan：对全表扫描的结果进行排序的次数。

• Sort_rows：排序的记录数。

十二、查询相关

查询相关的变量有 Select_full_join，Select_full_range_join，Select_range，Select_range_check，Select_scan。

这些变量的具体含义如下，

• Select_scan：全表扫描。如果是多表关联查询，指的是最外层的驱动表执行了全表扫描。

• Select_full_join：同样是全表扫描，不过针对的是被驱动表。

• Select_range：范围查询。如果是多表关联查询，指的是最外层的驱动表执行了范围查询。

• Select_full_range_join：同样是范围查询，不过针对的是被驱动表。

• Select_range_check：常用在非等值的关联查询中。

十三、其它重要指标

Uptime

数据库的运行时间，单位是秒。可基于 Uptime 来判断数据库是否发生过重启。

Queries，Questions

两者都是统计 Server 端执行的操作的数量。两者的关系如下，

SUM(Com_xxx) + Qcache_hits
= Questions + statements executed within stored programs
= Queries
‍

我们常用的 QPS 通常是基于 Questions 来计算的：QPS = Questions / Uptime。同 TPS 一样，建议取每秒增量。

Slow_queries

慢查询的数量。注意，无论是否开启慢日志，只要操作的执行时间超过 long_query_time，就会导致 Slow_queries 增加。

Bytes_received，Bytes_sent

• Bytes_received：从客户端接受的流量大小，单位 byte。

• Bytes_sent：发送给客户端的流量大小，单位 byte。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：

《一招解决MySQL中DDL语句被阻塞的问题场景》

《MySQL的列非空和自增属性》

《如何将印象笔记文件转换成无需授权的md文件？》

《MySQL的DDL成本为何高？》

《What is SQL?》

近期的热文：

《推荐一篇Oracle RAC Cache Fusion的经典论文》

《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：

《公众号1300篇文章分类和索引》