MySQL 中的状态变量

文若4个月前技术文章135

前言

本篇文章介绍一些 MySQL 中常用的监控指标,常见的监控工具都是采集 MySQL 中的状态变量(status variables)理解这些状态变量,可以更好的帮助我们理解 MySQL 监控的含义及配置有效完备的监控,从而游刃有余的定位数据库的性能问题。

1. 连接相关

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

1.1 连接线程

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


Variable NameVariable ScopeVariable Meaning
Threads_connectedGlobal当前连接(线程)数,该值等于 SHOW PROCESSLIST 的总数。
Threads_runningGlobal当前处于活跃状态的连接(线程)数,如果该值过大,会导致系统频繁地切换上下文,CPU 使用率也会比较高。
Threads_cachedGlobalThreads cache 缓存的线程数。在创建新的连接时,会首先检查 Threads cache 中是否有缓存的线程。如果有则复用,如果没有则创建新的线程。在线程池的场景中,会禁用 Threads cache 此时该值为 0。
Threads_createdGlobal已创建的线程数。反应的是累加值,如果该值过大,说明 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 NameVariable ScopeVariable Meaning
Max_used_connectionsGlobal数据库历史最大的连接数。
Max_used_connections_timeGlobal连接数达到历史最大的时间。
Connection_errors_max_connectionsGlobal连接数占满后,应用有新的连接后返回 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 NameVariable ScopeVariable Meaning
Com_insertBothinsert 语句执行次数。
Com_selectBothselect 语句执行次数。
Com_updateBothupdate 语句执行次数。
Com_deleteBothdelete 语句执行次数。
Com_commitBothcommit 语句执行次数。
Com_rollbackBothrollback 语句执行次数。
Com_replaceBothreplace 语句执行次数。


Tips:此类变量可以使用 flush status 命令归零,重新累加统计。每秒执行事务的次数 TPS 可通过 Com_commit + Com_rollback 每秒增量来计算。 这里只列出了部分常见操作,完整的可以使用下方 SQL 查看。

show status like 'Com%';

3. 临时表相关

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


Variable NameVariable ScopeVariable Meaning
Created_tmp_disk_tablesBothMySQL 内部临时表转化为磁盘表的数量。
Created_tmp_filesGlobalMySQL 创建临时文件的数量。
Created_tmp_tablesBothMySQL 创建在内存临时表的数量。


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

show status like 'Created%';

4. Table Cache 相关

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


Variable NameVariable ScopeVariable Meaning
Open_tablesBoth当前打开表的数量。
Open_table_definitionsGlobal当前缓存的 frm 文件的数量。
Opened_tablesBoth打开过的表的数量。
Open_table_definitionsGlobal缓存过的 frm 文件的数量。
Table_open_cache_hitsBothTable Cache 的命中次数。
Table_open_cache_missesBothTable Cache 没有命中的次数。
Table_open_cache_overflowsBoth表缓存被删除的次数。


当 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 NameVariable ScopeVariable Meaning
innodb_buffer_pool_pages_dataGlobal缓冲池中数据页的数量,包括干净页和脏页。
innodb_buffer_pool_bytes_dataGlobal数据页的大小,单位是字节。
innodb_buffer_pool_pages_dirtyGlobal脏页的数量。
innodb_buffer_pool_bytes_dirtyGlobal脏页的大小,单位是字节。
innodb_buffer_pool_pages_freeGlobal空闲页的数量。
innodb_buffer_pool_pages_miscGlobal用于管理开销而分配的页的数量,比如行锁、自适应哈希索引等。
innodb_buffer_pool_pages_totalGlobal页的总数量。
innodb_buffer_pool_pages_flushedGlobal脏页被刷盘的次数。
innodb_buffer_pool_wait_freeGlobal等待空闲页的次数。


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 NameVariable ScopeVariable Meaning
Innodb_log_waitsGlobal因 redo buffer 过小,导致 redo log buffer 刷盘的次数。
Innodb_log_write_requestsGlobal写 redo log buffer 的次数。
Innodb_log_writesGlobal写 redo log 次数。
Innodb_os_log_fsyncsGlobal对 redo log 调用 fsync 操作的次数。
Innodb_os_log_pending_fsyncsGlobalfsync 操作等待的次数。
Innodb_os_log_pending_writesGlobal写 redo log 等待次数。
Innodb_os_log_writtenGlobalredo 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 1Session 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。

相关文章

Mysql备份及恢复-XtraBackup

Mysql备份及恢复-XtraBackup

1、下载备份软件#不能使用8.0的版本,不支持mysql8.0之前的备份wget -c https://downloads.percona.com/downloads/Percona-XtraBack...

Hive调优

1.Fetch抓取Fetch抓取是指,Hive中对某些情况的查询可以不必使用MapReduce计算。例如:SELECT * FROM students;在这种情况下,Hive可以简单地读取studen...

Kubernetes源码解读(二)--DeltaFIFO源码分析

Kubernetes源码解读(二)--DeltaFIFO源码分析

1、Queue接口与DeltaFIFO的实现1.1、Queue和Store接口接口和结构体先相关代码类似 workqueue 里的队列概念,这里也有一个队列,Queue 接口定义在 client-go...

Kubernetes 因令牌过期新增节点报错问题处理

1、背景Kubernetes 集群初始化完成之后,间隔一天发现 Worker 节点无法正常加入集群。2、报错信息$ kubeadm join api.k8s.com:9443 --token mmvh...

docker安装及常用操作

docker安装及常用操作

一、安装docker1、移除以前docker相关包sudo yum remove docker \      ...

kubernetes job和cronjob

kubernetes job和cronjob

一、JobJob 负责批处理任务,即仅执行一次的任务,它保证批处理任务的一个或多个 Pod 成功结束。特殊说明:1、spec.template 格式同 Pod2、RestartPolicy 仅支持 N...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。