mysql常用性能指标监控

关键 MySQL 统计指标

如果你的数据库运行缓慢，或者出于某种原因无法响应查询，技术栈中每个依赖数据库的组件都会遭受性能问题。为了保证数据库的平稳运行，你可以主动监控以下四个与性能及资源利用率相关的指标：

• 查询吞吐量

• 查询执行性能

• 连接情况

• 缓冲池使用情况

MySQL 用户可以接触到数百个数据库指标，因此，在本文中我们将专注于能帮助我们实时了解数据库健康与性能的关键指标。

本系列文章讨论的一些监控策略只适用于 MySQL 5.6与5.7版本。这些版本间的差异将在后文中提及。

一、查询吞吐量(QPS)

查询吞吐量	名称	描述	指标类型	可用性	指标命令	指标分析
	Questions （应该设置告警）	已经发送给服务器的查询的个数	Work：吞吐量	服务器状态变量	show global status like "Questions";	当前的查询速率通常会有起伏，因此，如果基于固定的临界值，查询速率常常不是一个可操作的指标。但是，对于查询数量的突变设置告警非常重要——尤其是查询量的骤降，可能暗示着某个严重的问题。
	Com_select	select语句执行了多少次	Work：吞吐量	服务器状态变量	show status like 'Com_select';
	Writes	插入，更新或删除	Work：吞吐量	根据服务器状态变量计算得到	show global status like 'Com_insert'; show status like 'Com_update'; show status like 'Com_delete';	QPS=(com_update+com_insert+com_delete+com_select)/uptime

在监控任何系统时，你最关心的应该是确保系统能够高效地完成工作。数据库的工作是运行查询，因此在本例中，你的首要任务是确保 MySQL 能够如期执行查询。

 

通过以下指令，查询诸如 Questions 或 Com_select 服务器状态变量的值：

• show global status like "Questions";               
• show status like 'Com_select';          //读取查询指令
• show global status like 'Com_insert';   //写入查询可能增加以下三个状态变量中的某一个值
• show status like 'Com_update';
• show status like 'Com_delete';

备注：（show global,，表示本次MySQL启动后到现在的总请求数；show status  当前会话连接执行的参数状态）：

show global status like 'com_select';  //当前MySQL启动后所有连接执行的select语句总数

   show status like 'com_select'；      //仅仅表示当前会话连接执行的SELECT语句数量
 

MySQL查询的数量,可以用来统计qps,qps计算公式：

基于Questions计算：questions / uptime

或者基于com_%计算：（Com_select + Com_insert + Com_update + Com_delete）/ uptime

 

应该设置告警的指标：Questions

当前的查询速率通常会有起伏，因此，如果基于固定的临界值，查询速率常常不是一个可操作的指标。但是，对于查询数量的突变设置告警非常重要——尤其是查询量的骤降，可能暗示着某个严重的问题。

 

二、查询性能

性能模式语句摘要

性能模式的 events_statements_summary_by_digest 表格中保存着许多关键指标，抓取了与每条标准化语句有关的延迟、错误和查询量信息。

想要按模式抽取出以微秒为单位的平均运行时间，你可以这样查询性能模式：

SELECT schema_name
     , SUM(count_star) count     
     , ROUND(   (SUM(sum_timer_wait) / SUM(count_star))              
     / 1000000) AS avg_microsec  
     
     FROM performance_schema.events_statements_summary_by_digest 
     
 WHERE schema_name IS NOT NULL 
 GROUP BY schema_name;

相似地，按模式计算出现错误的语句总数，可以这么做：

SELECT schema_name
     , SUM(sum_errors) err_count
  FROM performance_schema.events_statements_summary_by_digest 
  WHERE schema_name IS NOT NULL 
  GROUP BY schema_name;

 

sys 模式

用上面的方式查询性能模式能以编程方式有效地从数据库中检索出指标。然而，对于特别查询或调查，使用 MySQL 的 sys 模式通常更为简单。sys 模式以人们更易读的格式提供了一个有条理的指标集合，使得对应的查询更加简单。例如，想要找出最慢的语句（运行时间在 95 名开外）：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者查看哪些标准化语句出现了错误：

SELECT * FROM sys.statements_with_errors_or_warnings;

 

慢查询

除了性能模式与 sys 模式中丰富的性能数据，MySQL 还提供了一个 Slow_queries 计数器，每当查询的执行时间超过 long_query_time 参数指定的值之后，该计数器就会增加。默认情况下，该临界值设置为 10 秒。

SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

long_query_time 参数的值可通过一条指令进行调整。例如，将慢查询临界值设置为 5 秒：

SET GLOBAL long_query_time = 5;

（请注意，你可能要关闭会话，再重新连接至数据库，这些更改才能在会话层生效。）

show status like 'Slow_queries';   

超过该值（--long-query-time）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。

调查查询性能问题

如果你的查询运行得比预期要慢，很可能是某条最近修改的查询在捣鬼。如果没有发现特别缓慢的查询，接下来就该评估系统级指标，寻找核心资源（CPU，磁盘 I/O，内存以及网络）的限制。CPU 饱和与 I/O 瓶颈是常见的问题根源。你可能还想检查 Innodb_row_lock_waits 指标，该指标记录着 InnoDB 存储引擎不得不停下来获得某行的锁定的次数。从 MySQL 5.5 版本起，InnoDB 就是默认的存储引擎，MySQL 对 InnoDB 表使用行级锁定。

 

应该设置告警的指标：

• 查询运行时间：管理关键数据库的延迟至关重要。如果生产环境中数据库的平均查询运行时间开始下降，应该寻找数据库实例的资源限制，行锁或表锁间可能的争夺，以及客户端查询模式的变化情况。

• 查询错误：查询错误的猛增可能暗示着客户端应用或数据库本身的问题。你可以使用 sys 模式快速查找可能导致问题的查询。例如，列举出返回错误数最多的 10 条标准化语句：

SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;

• Slow_queries：如何定义慢查询（并由此设置 long_query_time 参数）取决于你的用户案例。但是，无论你如何定义 “慢”，你都会想知道慢查询的数量是否超出了基准水平。为了找出真正执行缓慢的查询，你可以询问 sys 模式，或深入了解 MySQL 提供的慢查询日志（该功能默认是禁用的）。有关启用并读取慢查询日志的更多信心，请参考 MySQL 文档。

三、连接

 

连接使用率	名称	描述	指标类型	可用性	指标命令	指标分析
	Threads_connected （应该设置告警）	当前打开的连接数量	资源: 利用率	服务器状态变量	show status like 'Threads_connected';	当所有可用连接都被占用时，如果一个客户端试图连接至 MySQL，后者会返回 “Too many connections(连接数过多)” 错误，同时将Connection_errors_max_connections 的值增加。为了防止出现此类情况，你应该监控可用连接的数量，并确保其值保持在 max_connections 限制以内。
	Threads_running	当前运行的连接数量 （代表当前并发数，一般低于Threads_connected）	资源: 利用率	服务器状态变量	show status like 'Threads_running';	如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时， 要同时监视其他的一些值。
	Connection_errors_internal	由服务器错误导致的失败连接数	资源: 错误	服务器状态变量	show status like 'Connection_errors_internal';	该指标只会在错误源自服务器本身时增加。内部错误可能反映了内存不足状况，或者服务器无法开启新的线程。
	Aborted_connects （应该设置告警）	尝试与服务器进行连接结果失败的次数	资源: 错误	服务器状态变量	show status like 'Aborted_connects';	如果该计数器在不断增长，意味着用户尝试连接到数据库的努力全都失败了。此时，应该借助 Connection_errors_max_connections 与  Connection_errors_internal 之类细粒度高的指标调查该问题的根源。
	Connection_errors_max_connections	由 max_connections 限制导致的失败连接数	资源: 错误	服务器状态变量	show status like 'Connection_errors_max_connections';	如果服务器真的达到 max_connections 限制，它就会开始拒绝新的连接。在这种情况下，Connection_errors_max_connections 指标就会开始增加，同时，追踪所有失败连接尝试的Aborted_connects 指标也会开始增加。

 

 

mysql5.5之前默认数据库引擎是MyISAM，不过，5.5版本后，MySQL引入了InnoDB数据引擎。本文中使用的是mysql5.7，引擎为InnoDB，查询数据库引擎的方法如下：

SHOW VARIABLES LIKE 'default_storage_engine%';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

 

QPS（每秒查询数），根据数据引擎不同计算方式不同；

针对MyISAM引擎为主的DB：QPS=questions/uptime=5172，mysql自启动以来的平均QPS，如果要计算某一时间段内的QPS，可在高峰期间获取间隔时间t2-t1，然后分别计算出t2和t1时刻的q值，QPS=(q2-q1)/(t2-t1)

针对InnnoDB引擎为主的DB：QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076,某一时间段内的QPS查询方法同上。