MySQL的监控指标还是很多的,可能各使用这都有自己关注的方向,阈值这些都会有所不同,陈老师新书中的这篇文章《一文说透 MySQL 常用监控指标》介绍了监控指标,很全面,学习下。
在使用监控时,很多人都会遇到这样一个问题:监控项明明很多,但当线上实例出现问题时,还是两眼一抹黑,不知道该分析哪个监控项。
之所以会出现这样的问题,究其原因,还是对监控项不熟,不懂得监控项的具体含义。
基于此,本文会系统梳理 MySQL 中那些常用的监控指标,包括指标含义、导致指标发生变化的原因及使用过程中的注意事项。
MySQL 中绝大多数的监控指标都来源于状态变量(Status Variable),所以下面会从状态变量出发,来看看相关监控项的具体含义。
常用的状态变量主要包括以下几大类,
- 连接相关
- Com 相关
- Handler 相关
- 临时表相关
- Table Cache 相关
- 文件相关
- 主从复制相关
- Buffer Pool 相关
- Redo log 相关
- 锁相关
- 排序相关
- 查询相关
- 其它重要指标
一、连接相关
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 的限制,考虑两种场景,
-
缓存中存在未使用的表,这个时候,会基于 LRU 算法关闭未使用的表并将其从 Table Cache 中删除,同时,会增加Table_open_cache_overflows 的值。
-
缓存中的表都在使用,这个时候,会临时扩容 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 写入了 100 个字节到 redo log buffer 中,相应地,LSN(日志序列号)会增加 100。
-
事务 1 提交,redo log buffer 会刷新到 redo log 中。因为 redo log 的基本存储单位是块(block),且块的大小是 512 字节。所以,在刷新完毕后,Innodb_os_log_written 会增加 512。
-
接下来,事务 2 写入了 200 个字节到 redo log buffer 中,相应地,LSN 会增加 200。
-
事务 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 statusG select sleep(60); show engine innodb statusG
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篇文章分类和索引》