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

【编者按】本文作者为 John Matson,主要介绍 mysql 性能监控应该关注的4大指标. 第一部分介绍了前两个指标：查询吞吐量与查询执行性能.本文将继续介绍另两个指标：MySQL 连接与缓冲池.文章系国内ITOM 管理平台 OneAPM 编译呈现.

连接

检查并设置连接限制

监控客户端连接情况相当重要,因为一旦可用连接耗尽,新的客户端连接就会遭到拒绝.MySQL 默认的连接数限制为 151,可通过下面的查询加以验证：

SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+| Variable_name | Value |

+-----------------+-------+| max_connections | 151 |

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

MySQL 的文档指出,健壮的服务器应该能够处理成百上千的连接数.

“常规情况下,Linux 或 Solaris 应该能够支持500到1000个同时连接.如果可用的 RAM 较大,且每个连接的工作量较低或目标响应时间较为宽松,则最多可处理10000个连接.而 Windows 能处理的连接数一般不超过2048个,这是由于该平台上使用的 Posix 兼容层.”

连接数限制可以在系统运行时进行调整：

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,后者会返回 “Too many connections(连接数过多)”错误,同时将 Connection_errors_max_connections 的值增加.为了防止出现此类情况,你应该监控可用连接的数量,并确保其值保持在 max_connections 限制以内.

• Aborted_connects：如果该计数器在不断增长,意味着用户尝试连接到数据库的努力全都失败了.此时,应该借助 Connection_errors_max_connections 与 Connection_errors_internal 之类细粒度高的指标调查该问题的根源.

缓冲池使用情况

MySQL 默认的存储引擎 InnoDB 使用了一片称为缓冲池的内存区域,用于缓存数据表与索引的数据.缓冲池指标属于资源指标,而非工作指标,前者更多地用于调查(而非检测)性能问题.如果数据库性能开始下滑,而磁盘 I/O 在不断攀升,扩大缓冲池往往能带来性能回升.

检查缓冲池的大小

默认设置下,缓冲池的大小通常相对较小,为 128MiB.不过,MySQL 建议可将其扩大至专用数据库服务器物理内存的80%大小.然而,MySQL 也指出了一些注意事项：InnoDB 的内存开销可能提高超过缓冲池大小10%的内存占用.并且,如果你耗尽了物理内存,系统会求助于分页,导致数据库性能严重受损.

缓冲池也可以划分为不同的区域,称为实例.使用多个实例可以提高大容量(多 GiB)缓冲池的并发性.

缓冲池大小调整操作是分块进行的,缓冲池的大小必须为块的大小乘以实例的数目再乘以某个倍数.

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 的值开始增加,意味着数据库性能大有问题.

缓冲池利用率是在考虑扩大缓冲池之前应该检查的重要指标.利用率指标无法直接读取,但是可以通过下面的方式简单地计算得到：

(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 监控方案,抓取下面列出的指标能让你真正理解数据库的使用模式与可能的限制情况.这些指标也能帮助你发现,何时扩展服务器内存或将数据库移至更为强大的主机,从而保持良好的应用性能.

• 查询吞吐量

• 查询延迟与错误

• 客户端连接与错误

• 缓冲池利用率

鸣谢

非常感谢来自 Oracle 的 Dave Stokes 与 VividCortex 的 Ewen Fortune,他们在本文发布之前提供了许多宝贵的反馈意见.

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