MySQL监控概述

秦晓辉@快猫星云 2022-11-16 08:08:08

什么是 MySQL

MySQL 是世界上最流行的开源关系型数据库，隶属 Oracle，有开源版本和商业版本。最初是在 1995 年发布，后来催生了知名的分支版本，比如 MariaDB 和 Percona。

MySQL 关键统计数据

如果你的数据库运行缓慢，或因任何原因无法提供查询服务，你的堆栈中依赖该数据库的每一部分也会遭受性能问题。为了保持你的数据库顺利运行，你可以积极监测四类指标，这些指标涵盖性能和资源利用相关方面。

查询吞吐量
查询性能
连接数
缓冲池

MySQL用户可以从数据库中访问数以百计的指标，在本文中，我们将专注于少数几个关键指标，使你能够实时了解你的数据库的健康状况和性能情况。

版本和技术之间的兼容性

本系列中讨论的一些监控策略是针对MySQL 5.6和5.7版本的。这些版本之间的差异将在文章中被顺便指出。

这里概述的大多数指标和监控策略也适用于与MySQL兼容的技术，如MariaDB和Percona Server，但有一些明显的区别。例如，在本系列第二部分中详细介绍的MySQL工作台的一些功能与当前可用的MariaDB版本不兼容。

Amazon RDS 和 Aurora 的监控也会有些区别，会在其他文章中介绍。

查询吞吐量

Questions

Questions: 统计执行的语句(客户端发起的)数量，可以从"服务器状态变量"获取
Com_select: SELECT 数量，可以从"服务器状态变量"获取
Writes: Inserts、Updates、Deletes 数量，单个基础数据可以从"服务器状态变量"获取，最终相加就是 Writes 总量

MySQL有一个内部计数器(用MySQL的说法是"服务器状态变量")，称为Questions，它表示客户端应用程序发送的所有语句数量。除了Questions，还有一个指标可以统计语句数量，是Queries。不过一般来讲我们都是使用Questions，因为Questions是统计了客户端发起的语句数量，而Queries，还包含了存储过程执行时的语句，以及一些准备语句，比如PREPARE和DEALLOCATE PREPARE等命令。

具体如何从"服务器状态变量"中查询数据呢？比如查询Questions或Com_select，样例如下：

SHOW GLOBAL STATUS LIKE "Questions";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 254408 |
+---------------+--------+

你也可以分开监控读和写命令，以便更好的了解你的数据库工作负载，确定潜在瓶颈。读取数量一般由Com_select指标来拿到，写入数量则是 Com_insert、Com_update、Com_delete 的和，即：

Writes = Com_insert + Com_update + Com_delete

针对 Questions 的告警

当前的查询率自然会上升和下降，所以基于固定阈值并不好使。但值得警惕的是，查询量的突然变化，特别是吞吐量的急剧下降，可能表明有严重的问题。

查询性能

Query run time：每个 schema 的平均执行时间，来自 performance schema
Query errors：产生 errors 的 SQL 语句数量，来自 performance schema
Slow_queries：超过了 long_query_time 限制的慢查询数量，数据来自"服务器状态变量"

我们可以有多种方式来监测查询延迟，既可以利用MySQL的内置指标，也可以通过查询performance_schema。自MySQL 5.6.6以来默认启用，MySQL中的performance_schema数据库的表存储了关于服务器事件和查询执行的低级别统计数据。

Performance schema statement digest

有很多关键指标数据，在 performance schema的 events_statements_summary_by_digest 表，这个表捕获了SQL的很多关键信息，比如延迟、错误量、查询量。下面有一个例子，例子中的SQL执行了2次，平均执行时间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

这个表里的SQL，也就是DIGEST_TEXT字段标识的语句称为normalized statement，所谓的normalized，就是统一了大小写和空格，查询参数统一成问号，所以，下面两个语句经过归一化处理之后，会被看做相同的语句：

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

如果要按照 schema 的维度来计算平均执行时间，可以这样来查询(以毫秒为单位)：

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 |
+--------------------+-------+--------------+

类似的，按照 schema 来统计错误数量：

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 schema

使用上面的方式，通过 performance schema 来查询性能数据，是不错，不多针对这种即时查询、诊断问题的场景，还有一个更简单的方式，就是使用 sys schema。sys schema 提供了一种组织良好、人类易读的查询指标的方式，使得查询起来更为简单。比如，找到最慢的SQL(这个数据在statements_with_runtimes_in_95th_percentile表中)：

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

或者查看哪些归一化的语句包含错误：

SELECT * FROM sys.statements_with_errors_or_warnings;

更多有用的例子可以在 sys schema 的文档中找到。MySQL 从 5.7.7 版本开始，才包含 sys schema，不过从 5.6 开始，可以手工安装。具体如何安装，留待其他文章详解。

Slow queries

除了 performance schema 和 sys schema 里的这些数据，MySQL 还提供了 Slow_queries 这个指标，每当查询执行时间超过了 long_query_time， Slow_queries 就会 +1，long_query_time 默认配置是 10s：

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

long_query_time 可以通过命令调整，比如，我们设置为 5 秒：

SET GLOBAL long_query_time = 5;

注意：你可能必须关闭会话并重新连接到数据库才能在会话级别应用更改。

追查性能问题

如果发现查询执行速度比预期慢，很有可能是因为最近做了查询变更，比如因为业务功能迭代，增加了一些新的SQL查询，或者老的SQL查询语句做了调整之类的。如果没有哪个查询请求出奇的慢，下一个要排查的就是系统级别的指标了，比如CPU、硬盘I/O，内存，网络等系统核心资源。CPU饱和度和I/O瓶颈通常是罪魁祸首。另外你可能也会想要查看 Innodb_row_lock_waits 指标，这个指标表示 InnoDB 引擎在特定 row 上等待锁的频繁程度。从 MySQL 5.5 开始，InnoDB 就成为 MySQL 默认存储引擎了，对于 InnoDB 的 Table，MySQL 会使用行级锁。

为了提升读写操作的速度，很多用户会调整 InnoDB buffer pool 的大小，以此来缓存 table 和 index 的数据。如何监控和调整 buffer pool，会在下文继续介绍。

用于告警的指标

查询耗时：对一些关键数据库而言，查询延迟非常关键。如果生产数据库的查询延迟变高，需要看一下数据库资源是否有争抢，可能在行锁表锁上有竞争，另外也要考虑优化查询SQL
查询错误：如果查询错误突然变多，可能是客户端应用出问题了，当然也可能是数据库实例本身出问题了。你可以通过 sys schema 来快速查看一下是哪个查询导致的问题，比如，下面的语句可以罗列出产生问题最多的10条SQL：

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

Slow_queries: 如何定义慢速查询（以及如何配置 long_query_time 参数）取决于你的场景。无论你对“慢”的定义是什么，如果慢查询的数量超过基线水平，你肯定都想排查一下。想要找到具体是哪些查询慢了，你可以查询 sys schema 或深入了解 MySQL 的可选慢查询日志，默认情况下它是禁用的。更多有关启用和查看慢查询日志的知识，可以参考MySQL的文档

连接数

Connections

Threads_connected: 当前打开的连接数，可以从"服务器状态变量"获取
Threads_running: 当前正在运行查询的连接数，可以从"服务器状态变量"获取
Connection_errors_internal: 由于服务端错误导致的拒绝连接数量，可以从"服务器状态变量"获取
Aborted_connects: 尝试连接Server但是失败的连接数量，可以从"服务器状态变量"获取
Connection_errors_max_connections: 由于最大连接数限制导致的拒绝连接的数量，可以从"服务器状态变量"获取

检查和设置连接数限制

监控客户端连接数是非常关键的，因为一旦把可用连接用光，新的连接就会被拒绝。MySQL的最大连接数限制默认是151，以现在的硬件条件，说实话太小了，建议调大。我们可以使用下面的命令来查看：

SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

MySQL文档建议，一台高配机器理应支持成百上千的连接，引文如下：

Linux or Solaris should be able to support 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform.

连接数限制可以通过下面的命令即时调整：

SET GLOBAL max_connections = 200;

如果服务器重启，这个配置就又恢复默认配置了，为了永久修改这个配置，需要修改 my.cnf，增加下面的这一行内容：

max_connections = 200

监控连接数使用率

MySQL 暴露了 Threads_connected 指标来统计连接数，监控这个指标以及最大连接数指标，就可以知道我们是否有足够的容量来处理新请求。MySQL 还暴露了 Threads_running 指标，表示正在执行查询请求的连接有多少，因为现代程序都是连接池设计，有很多连接虽然连上来了，但是都没有在工作，是 idle 的连接。

如果数据库确实达到了最大连接数的限制，此时就开始拒绝连接，除了客户端会报错之外，数据库的 Connection_errors_max_connections 和 Aborted_connects 指标也会增长。

MySQL 针对连接失败暴露了一堆指标，可以让我们方便查看到底是什么原因导致的连接失败。其中，Connection_errors_internal 是一个很值得关注的指标，因为只要这个指标涨了，就说明数据库内部出错了，比如内存不够用了，或者没有办法启动一个新的线程了。

缓存池使用情况

Buffer pool usage

Innodb_buffer_pool_pages_total: Buffer pool 中的 page 总量，可以从"服务器状态变量"获取
Buffer pool utilization: Buffer pool 的 page 使用率，可以从"服务器状态变量"获取基本信息，然后通过计算得到使用率
Innodb_buffer_pool_read_requests: 向 Buffer pool 发起的查询数量，可以从"服务器状态变量"获取
Innodb_buffer_pool_reads: 向 Buffer pool 发起了查询，但是 Buffer pool 没有缓存，仍然需要读取硬盘的查询数量，可以从"服务器状态变量"获取

MySQL 的默认存储引擎，InnoDB，会使用一块内存(称为 Buffer pool)来缓存Table和Index的数据。Buffer pool是一种资源类的指标，和工作类指标不同，工作类指标可以直接发现问题，资源类指标在排查问题时非常有用。如果发现硬盘I/O上涨，数据库性能下跌，通常可以调大 Buffer pool 来解决。

调整 Buffer pool

Buffer pool 默认大小是 128MB，这个值相对有些小了，MySQL建议是调整为物理内存的80%，当然，前提是这个机器上只有MySQL，不会有其他进程争抢资源。MySQL也给了一些注意事项，InnoDB的内存开销，会使内存占用量增加约10%，超出分配的缓冲池大小。如果把物理内存耗光了，系统就会求助于交换分区，这会让性能极具下降。

Buffer pool 可以被分成多个分区，称为 instance。在内存较大的机器上，使用多个 instance 可以提升 Buffer pool 的并发性能。

Buffer pool 大小的调整是以块为单位来执行的，其大小是块大小乘以实例数的倍数：

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size
                            * innodb_buffer_pool_instances

块大小默认是128MB，也是可以配置的，这两个参数可以通过下面的命令来检查：

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

如果 innodb_buffer_pool_chunk_size 查不到内容，说明这个参数在你的 MySQL 版本中是不能调整的，在这样的实例上，姑且就假设为 128MB 即可。

可以通过命令行参数来调整这俩参数：

mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

在 MySQL 5.7.5 版本，你可以通过 SET 命令临时调整 Buffer pool 的大小(单位是byte)。举个例子，在有两个 Buffer pool instance 的数据库上，可以通过设置整体 Buffer pool 为 8GiB 来让每个 instance 变成 4GiB：

SET GLOBAL innodb_buffer_pool_size=8589934592;

Buffer pool 关键指标

MySQL 暴露了一些关于缓冲池及其利用率的指标。一些最有用的指标是跟踪缓冲池的总大小、使用量以及缓冲池的效率。

指标 Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads 是了解缓冲池利用率的关键指标。Innodb_buffer_pool_read_requests 跟踪了逻辑读的数量，Innodb_buffer_pool_reads 跟踪了Buffer pool无法满足导致从硬盘读取数据的数量。鉴于从内存读取通常比从磁盘读取快几个数量级，如果 Innodb_buffer_pool_reads 开始攀升，性能将受到影响。

缓冲池利用率是在考虑调整缓冲池大小之前检查的有用指标。利用率指标不是开箱即用的，但可以很容易地计算如下：

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) /
 Innodb_buffer_pool_pages_total

如果您的数据库正在处理大量磁盘读取，但缓冲池远未满，则可能是您的缓存最近已被清除并且仍在预热。如果您的缓冲池没有填满但有效地提供读取服务，则您的工作数据集可能很适合内存(相当于要查的数据用有限的内存就可以cache住，而且大部分请求都是走cache即可返回)。

另一方面，高缓冲池利用率不一定是坏事，因为旧数据或未使用的数据会使用 LRU 策略自动从缓存中老化清理。但是，如果缓冲池不能有效地服务于您的读取工作负载，可能是时候扩展您的缓存了。

转换 Buffer pool 指标成 byte 作为单位

大多数 Buffer pool 指标都使用内存页的个数作为单位，但这些指标可以转换为字节，这样可以更轻松地将这些指标与缓冲池的实际大小联系起来。比如，我们可以通过下面的算法得到Buffer pool的总大小(以byte为单位)：

Innodb_buffer_pool_pages_total * innodb_page_size

InnoDB page size 是可以调整的，默认是 16 KiB，或说 16384 byte。可以使用下面的命令拿到当前大小：

SHOW VARIABLES LIKE "innodb_page_size";

总结

在这篇文章中，我们探讨了一些你应该监控的最重要的指标，以保持对MySQL活动和性能的关注。如果你正在建立你的MySQL监控体系，捕获下面概述的指标将使你了解数据库的使用模式和潜在的限制。它们还将帮助你确定何时有必要扩大规模或将你的数据库实例移到更强大的主机上，以保持良好的应用性能。

查询吞吐量
查询延迟和错误数
客户端连接和错误数
缓冲池使用率

关于作者

本文是翻译自这篇文章，译者秦晓辉，Flashcat合伙人，文章内容是Flashcat技术团队共同沉淀的结晶，作者做了编辑整理，我们会持续输出监控、稳定性保障相关的技术文章，文章可转载，转载请注明出处，尊重技术人员的成果。