MySQL监控三部曲：四大性能指标 （一）
本文是MySQL监控三部曲的第1部分。第2部分是收集MySQL统计信息和指标，第3部分解释了如何使用Cloud Insight监控MySQL。
MySQL是世界上最流行的开源关系型数据库，在被Oracle收购后，推出了社区免费版本和具有附加功能支持的商业版本。自1995年以来，MySQL已经衍生出了MariaDB、Percona等优秀的分支。
MySQL关键指标
如果你的线上数据库运行缓慢，甚至无法进行查询，那么依赖于数据库的每个业务也会遇到各种问题。为了数据库可以保持平稳健康的运行，可以监控以下四大性能指标：
查询吞吐量
查询执行性能
数据库连接
缓冲池使用情况
用户可以从数据库中获取数百个指标，因此在本文中，我们将重点介绍一些关键指标，使你能够有针对性地了解数据库的运行状况和性能。
在本系列的第二部分中，我们将向你展示如何收集这些指标。本系列适用于MySQL版本5.6和5.7，版本之间的差异将在本文中指出。
查询吞吐量
为了确保用户在MySQL中执行的查询能正常返回结果，首先应确保MySQL按预期执行查询。
MySQL的服务器状态变量（在MySQL里被称为 "Server status variable"）与查询个数相关的计数器有 Questions 和 Queries，主要对客户端应用程序发送的所有语句进行递增记录。Questions 往往比 Queries 更容易理解，因为 Queries 计数器会算上存储过程执行的语句以及命令，如PREPARE与DEALLOCATE PREPARE 等运行在服务器端的准备语句。
获取 Questions 或 Com_select 指标：
SHOW GLOBAL STATUS LIKE "Questions";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Questions | 254408 |
+---------------+--------+
监控数据库读取和写入也是了解数据库负载并识别潜在瓶颈的有效措施。读取值通常由Com_select和Qcache_hits指标来获取，而写入值取决于三个指标：Writes = Com_insert+ Com_update+Com_delete
需要关注的指标
当前的查询吞吐量不是基于固定阈值的指标，所以需要注意查询吞吐量急剧升高或下降，这可能表明系统存在严重问题。
查询执行性能
和 performance schema 来监控查询延迟。自MySQL 5.6.6起默认启用，performance_schema 里的表存储着服务器事件和查询执行的统计信息。
Performance Schema
许多关键指标都包含在 performance_schema 的 events_statements_summary_by_digest 表中，该表会获取每个规范化SQL的延迟、错误和查询量的信息。下面的示例展示了一个已执行两次且平均执行时间为325毫秒的语句（所有计时器均以picoseconds为单位）：
*************************** 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
以微秒为单位查询每个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 非常适合以写SQL的方式从数据库中获取指标，但是对于即席查询来说，使用MySQL的sys schema 会更合适。Sys schema 以更易读的格式提供统计后的指标，例如要查找最慢的语句（运行时间在第95分位）：
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
或者查看哪些语句产生了错误：
SELECT * FROM sys.statements_with_errors_or_warnings;
Sys schema文档中详细介绍了许多其他有用的示例。从5.7.7版本开始，sys schema 内置在MySQL中，但MySQL 5.6用户也只需几个命令就可以安装它。有关说明，请参阅本系列的第2部分。
慢查询
除了 performance_schema 和 sys schema 中大量性能数据之外，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;
需要注意的是，你必须关闭会话并重新连接到数据库才能在会话级别应用更改。
如何判断查询性能问题
如果你的SQL执行速度比预期慢，很可能是应用系统最近更新过的SQL惹的祸。如果不能确定是SQL的原因，那么下一步要调查的是主机级指标（CPU、磁盘I/O、内存和网络），问题根因通常是CPU使用率过高或 I/O 达到瓶颈。除了主机你还需要检查 Innodb_row_lock_waits 指标，它记录了InnoDB存储引擎等待获取行级锁的频率。自MySQL版本5.5以来，InnoDB一直是默认的存储引擎，并且InnoDB表使用的是行级锁。
为了提高读写速度，许多用户会选择调整InnoDB的缓冲池大小来缓存更多表和索引数据。关于监控和调整缓冲池的信息请看下方。
需要关注的指标
Query run time：如果数据库中平均查询执行时间开始增加，请检查数据库实例的资源瓶颈、行锁或表锁的争用情况，或者修改客户端查询SQL。
Query errors：查询出错数的激增可能表示客户端应用或数据库本身存在问题。可以使用 sys schema 快速查找可能导致问题的SQL。例如，列出返回最多错误的10个SQL语句：
SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;
Slow_queries：如何定义慢查询取决于你的实际环境。无论如何，你都需要检查慢查询数量是否超过基线水平。如果想找到执行缓慢的SQL，可以查询 sys schema或查看MySQL的慢查询日志，默认情况下会禁用该日志（因为对性能会有影响）。有关启用和访问慢查询日志的更多信息，请参阅MySQL文档。
数据库连接
置连接数限制
监控客户端连接是非常重要的，因为一旦用尽可用连接，MySQL将拒绝新的客户端连接。连接数限制默认为151：
SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
一个健壮的MySQL服务器应该能处理成百上千的连接：
Linux或Solaris应该能够支持500到1000个并发连接，如果你有很多的内存可用，每个连接的工作负载很低或者对响应时间要求不高，应该能够支持多达1万个连接。由于Windows平台上使用的是Posix兼容层，所以（open table × 2 + open connections）必须小于 2048。
连接数限制可以动态调整：
SET GLOBAL max_connections = 200;
但是当服务器重新启动时，该设置将恢复为默认值。如果要永久生效，要在my.cnf 配置文件中添加下面的配置：
max_connections = 200
监控连接利用率
MySQL的 Threads_connected 指标可以当作打开线程数，因为每个连接会开启一个新线程。通过监控该指标和配置连接数限制，可以确保你有足够的容量来处理新连接。MySQL还有Threads_running 指标用来区分有多少活跃线程。
如果你的数据库达到了 max_connections 的限制，将开始拒绝连接，Connection_errors_max_connections 和 Aborted_connects 指标将递增。
MySQL还有更多有关连接错误的指标，其中 Connection_errors_internal 指标比较值得关注，因为它仅在服务器内部发生错误时才会增加。内部错误可能由于内存不足或服务器无法启动新线程而导致。
需要注意(告警)的指标
Threads_connected：如果客户端在所有可用连接用尽时还尝试连接到MySQL，MySQL将返回“Too many connections”错误并增加 Connection_errors_max_connections。此时应该监控打开的连接数 Threads_connected，并确保它小于 max_connections 。
Aborted_connects：如果该值正在不断增加，则代表你的客户端正在尝试并且无法连接到数据库。建议使用 Connection_errors_max_connections 和 Connection_errors_internal 来调查问题的原因。
缓冲池使用情况
MySQL的默认存储引擎InnoDB使用 Buffer pool 来缓存表和索引的数据。缓冲池指标主要用于排查性能问题，如果在磁盘 I/O 上升时数据库性能开始下滑，扩大缓冲池是个好选择。
调整缓冲池的大小
缓冲池大小默认是128 MiB，但官方建议可以将其增加到专用服务器物理内存的80％。需要注意的是如果物理内存耗尽，会采用Swap分区，性能将受到严重影响。
缓冲池也可以分成单独的区域，称为实例，使用多个实例可以提高缓冲池的并发度。
缓冲池大小以块为单位，必须设置为块大小的倍数乘以实例数：
innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size
* innodb_buffer_pool_instances
块大小默认为128 MiB，从MySQL 5.7.5开始可以进行设置。可以按如下方式查询两个参数的值：
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";
如果查询 innodb_buffer_pool_chunk_size 未返回任何结果，则该参数在当前MySQL版本中不可调，为固定的128 MiB。
在服务器启动时设置缓冲池大小和实例数：
$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16
从MySQL 5.7.5开始，还可以通过 SET 命令动态调整缓冲池的大小（以字节为单位）。例如，使用两个缓冲池实例，可以通过将总大小设置为8 GiB，从而动态的将每个实例设置为4 GiB：
SET GLOBAL innodb_buffer_pool_size=8589934592;
关键的InnoDB缓冲池指标
MySQL公开了一些关于缓冲池及其利用率的指标。最有用的是检查缓冲池总大小、使用量以及缓冲池读取效率的指标。
Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads 指标是了解缓冲池利用率的关键。Innodb_buffer_pool_read_requests 记录了逻辑读取请求的数量， Innodb_buffer_pool_reads 则记录了缓冲池无法满足而必须从磁盘读取的请求数。鉴于内存读取速度通常比磁盘读取速度快几个数量级，所以如果Innodb_buffer_pool_reads 指标开始上涨，性能将受到影响。
缓冲池利用率可以帮助你判断是否有必要调整缓冲池大小。利用率指标不是开箱即用的，可以按如下方式轻松计算：
Buffer pool utilization = (Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total
如果数据库正在从磁盘读取大量的数据，但缓冲池远未满，则可能是最近的缓存已被清除且仍处于预热状态。如果你的缓冲池没有填满并且逻辑读数量远大于磁盘读，那么你的内存大小非常匹配数据集大小。
另一方面，缓冲池利用率高不一定是坏事，因为旧数据在缓存内会使用LRU策略自动释放。但是磁盘读请求很多的话，可能就要扩大缓存了。
将缓冲池指标转换为字节数
大多数缓冲池指标都为内存页数，有些情况下需要转换为字节数，这样可以更轻松地将这些指标与缓冲池的实际大小相关联。例如，要获取缓冲池中的总页数，并且以字节为单位显示缓冲池的总大小：
Innodb_buffer_pool_pages_total * innodb_page_size
InnoDB内存页大小可调，默认为16 KiB或16,384 字节。可以使用SHOW VARIABLES查询其当前值：
SHOW VARIABLES LIKE "innodb_page_size";
结论
在这篇文章中，我们探讨了一些MySQL应该监控的最重要的指标，帮助你密切关注MySQL的运行情况和性能。如果要监控MySQL，收集下面列出的指标将使你对数据库更加了解。它们还可以帮助你确定什么时候需要扩展实例或将实例移动到更好的主机，以保持良好的应用程序性能。
查询吞吐量
查询执行性能
数据库连接
缓冲池使用情况
本系列的第2部分会讲到如何收集MySQL统计信息和指标，敬请期待。
本文系 Cloud Insight 工程师编译整理。
睿象云 Cloud Insight 集监控、管理、计算、协作、可视化于一身，帮助所有 IT 公司，减少在系统监控上的人力和时间成本投入，让运维工作更加高效、简单。想阅读更多技术文章，请访问睿象云官方技术博客。
