MySQL 中的状态变量

2024-01-05 08:12
文章标签 mysql database 状态变量

本文主要是介绍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 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。

9. 查询相关

以下是查询相关的状态变量:

Variable NameVariable ScopeVariable Meaning
Select_scanBoth全表扫描的次数,如果是关联查询,指的是驱动表执行了全表扫描。
Select_full_joinBoth同样是全表扫描,不过只包含关联场景,驱动表全表扫描的次数。
Select_rangeBoth范围查询次数。如果是关联查询,指的是驱动表执行了范围查询。
Select_full_range_joinBoth同样是范围查询,不过只包含关联场景,驱动表全表扫描的次数。
Select_range_checkBoth常用于非等值的关联查询中。
Slow_queriesBoth慢查询的数量,无论是否开启了慢查询,只要 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 中的状态变量的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/572223

相关文章

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们