MySQL 性能监控4大指标——第一部分

时间：2023-01-22 06:58:55

【编者按】本文作者为 John Matson，主要介绍 mysql 性能监控应该关注的4大指标。 第一部分将详细介绍前两个指标： 查询吞吐量与查询执行性能。文章系国内 ITOM 管理平台 OneAPM 编译呈现。

MySQL 是什么？

MySQL 是现而今最流行的开源关系型数据库服务器。由 Oracle 所有，MySQL 提供了可以免费下载的社区版及包含更多特性与支持的商业版。从1995年首发以来，MySQL 衍生出多款备受瞩目的分支，诸如具有相当竞争力的 MariaDB 及 Percona。

关键 MySQL 统计指标

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

  • 查询吞吐量

  • 查询执行性能

  • 连接情况

  • 缓冲池使用情况

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

本文参考了我们在监控入门系列文章中介绍的指标术语，后者为指标收集与告警提供了基础框架。

不同版本与技术的兼容性

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

本文列出的大多数指标与监控策略同样适用于与 MySQL 兼容的技术，诸如 MariaDB 与 Percona 服务器，不过带有一些明显的差别。例如，MySQL Workbench(工作台)中的一些特性（在本系列第二篇中有详细介绍）就与当下的一些 MariaDB 版本不兼容。

Amazon RDS 用户应该查看我们专门制作的 MySQL 在 RDS 以及与 MySQL 兼容的 Amazon Aurora 监控手册。

查询吞吐量

MySQL 性能监控4大指标——第一部分

名称 描述 指标类型 可用性
Questions 已执行语句（由客户端发出）计数 Work：吞吐量 服务器状态变量
Com_select SELECT 语句 Work：吞吐量 服务器状态变量
Writes 插入，更新或删除 Work：吞吐量 根据服务器状态变量计算得到

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

MySQL 有一个名为 Questions 的内部计数器（根据 MySQL 用语，这是一个服务器状态变量），客户端每发送一个查询语句，其值就会加一。由 Questions 指标带来的以客户端为中心的视角常常比相关的 Queries 计数器更容易解释。作为存储程序的一部分，后者也会计算已执行语句的数量，以及诸如 PREPARE 和 DEALLOCATE PREPARE 指令运行的次数，作为服务器端预处理语句的一部分。

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

SHOW GLOBAL STATUS LIKE "Questions";

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| Questions     | 254408 |

+---------------+--------+

你也可以监控读、写指令的分解情况，从而更好地理解数据库的工作负载、找到可能的瓶颈。通常，读取查询会由 Com_select 指标抓取，而写入查询则可能增加三个状态变量中某一个的值，这取决于具体的指令：

Writes = Com_insert + Com_update + Com_delete

应该设置告警的指标：Questions

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

查询性能

MySQL 性能监控4大指标——第一部分

名称 描述 指标类型 可用性
查询运行时间 每种模式下的平均运行时间 Work：性能 性能模式查询
查询错误 出现错误的 SQL 语句数量 Work：错误 性能模式查询
Slow_queries 超过可配置的long_query_time 限制的查询数量 Work：性能 服务器状态变量

MySQL 用户监控查询延迟的方式有很多，既可以通过 MySQL 内置的指标，也可以通过查询性能模式。从 MySQL 5.6.6 版本开始默认启用，MySQL 的 performance_schema 数据库中的表格存储着服务器事件与查询执行的低水平统计数据。

性能模式语句摘要

性能模式的 events_statements_summary_by_digest 表格中保存着许多关键指标，抓取了与每条标准化语句有关的延迟、错误和查询量信息。从该表截取的一行样例显示，某条语句被执行了两次，平均执行用时为 325 毫秒（所有计时器的测量值都以微微秒为单位）：

*************************** 1. row ***************************

               SCHEMA_NAME: employees

                    DIGEST: 0c6318da9de53353a3a1bacea70b4fce

               DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ?

                COUNT_STAR: 2

            SUM_TIMER_WAIT: 650358383000

            MIN_TIMER_WAIT: 292045159000

            AVG_TIMER_WAIT: 325179191000

            MAX_TIMER_WAIT: 358313224000

             SUM_LOCK_TIME: 520000000

                SUM_ERRORS: 0

              SUM_WARNINGS: 0

         SUM_ROWS_AFFECTED: 0

             SUM_ROWS_SENT: 520048

          SUM_ROWS_EXAMINED: 520048

          ...          

          SUM_NO_INDEX_USED: 0

     SUM_NO_GOOD_INDEX_USED: 0

                 FIRST_SEEN: 2016-03-24 14:25:32

                  LAST_SEEN: 2016-03-24 14:25:55

摘要表会标准化所有语句（如上面的 DIGEST_TEXT 一栏所示），忽略数据值，规范化空格与大小写，因此，下面的两条查询会被认为是相同的：

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

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

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;

+--------------------+-------+--------------+

| schema_name        | count | avg_microsec |

+--------------------+-------+--------------+

| employees          |   223 |       171940 |

| performance_schema |    37 |        20761 |

| sys                |     4 |          748 |

+--------------------+-------+--------------+

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

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;

+--------------------+-----------+

| schema_name        | err_count |

+--------------------+-----------+

| employees          |         8 |

| performance_schema |         1 |

| sys                |         3 |

+--------------------+-----------+

sys 模式

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

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

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

SELECT * FROM sys.statements_with_errors_or_warnings;

在 sys 模式的文档中，详细介绍了许多有用的例子。sys 模式在 MySQL 5.7.7 版本中是默认包含的。不过，MySQL 5.6 用户通过简单的几个指令就能安装它。

慢查询

除了性能模式与 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;

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

调查查询性能问题

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

为了提高读取与写入操作的速度，许多用户会想通过调整 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 文档。

敬请期待本文第二部分，主要介绍 MySQL 连接与缓冲池。

本文系 OneAPM 工程师编译整理。OneAPM Cloud Insight 集监控、管理、计算、协作、可视化于一身，帮助所有 IT 公司，减少在系统监控上的人力和时间成本投入，让运维工作更加高效、简单。想阅读更多技术文章，请访问 OneAPM 官方技术博客。

本文转自 OneAPM 官方博客

相关文章