一个 Mysql 参数解决服务器磁盘负载过高问题

最近调整了一个 mysql 参数，解决了困扰我很久的服务高 IO 的问题，分享一下， 希望对各位有帮助

问题

我有一个服务，跑了有几年，随着时间的增长，数据量也越来越大。 随后我开始注意到一个问题，就是这个服务几乎吃光了所有的读写资源。请求活跃时， 磁盘队列长度长时间在 5 以上。

原因

自己写的服务，大概什么原因也很容易猜得到，比较耗资源的地方是几个刷新列表的地方， 这几个地方的特点都一样，先从对应表格中捞出匹配数据，但是这些数据不全， 需要从属性表中把原始属性捞出来，构建出对象，然后提取缺失信息，生成输出表格。 整个过程需要大量的数据库读操作。

随着时间的推移，属性表的数据越来越多，系统用户越来越多， 于是从属性表中读出的数据量也越来越多和频繁，就变成了现在的样子， 不过根据服务器的负载，CPU 的使用率并不高，说明其实瓶颈不在运算。 资源监视器显示数据库一直在读属性表文件，磁盘队列长度达到了 5。

所以根本原因在于，组装返回数据的过程中，程序需要读取大量的属性表数据。 所以这个问题的根本解决方案是改变程序的做法，将输出给用户的数据作为一个单独的表格保存， 这样后续返回数据时，直接读取这个表格，避免大量读取属性表重新组装数据。

分析

很显然我没有采用解决根本原因的做法，因为这样修改的地方还挺多的， 但是领导可能不会理解已有功能为什么要安排这么多时间做改造和优化， 一直又有新任务进来，所以一直也是凑合能用就行。近来， 有其他用户也开始回报相同问题了，我试了一下他的用户，打开页面需要 15 秒， 这个是有点过分了，所以我又开始头痛要不要排期整改了。

所在的服务器 CPU 还可以，内存也有大量剩余，所以经常运维也会抱怨， 资源都没有吃满，为啥还是慢，其实原因就是大家都在等磁盘 IO。

我的改造计划是把生成的输出资料存下来，保存到容器中。这样用户获取数据时， 直接返回容器中保存的数据即可，换句话说就是服务自己做一层缓存。既然是这样， 那有没有什么办法让数据库直接把表格读入内存呢？反正我的瓶颈不在 CPU。

解决

如果我们去搜索 InnoDB cache，很快我们就会知道 InnoDB 有一个 Buffer Pool， Buffer Pool 会把频繁使用的表数据和索引数据放入内存，这样我们读写数据时， 其实是在内存上操作，这样可以节省大量的磁盘 IO。

Pool size 的基数是由 chunk size 和 instance 的乘积决定的， 需要是后两者积数的整数倍，我这里的运行环境，chunk size 是 128m，instance 是 1， pool size 调整之前是 128m，而我经常读取的表格数据量已经达到了 1.8G。 所以缓冲区应该是不够用了。我们可以用下面命令去查看 pool size, chunk size 以及 pool instance:

-- 查看 pool size
SELECT @@innodb_buffer_pool_size;
-- 查看 chunk size
SELECT @@innodb_buffer_pool_chunk_size;
-- 查看 pool instance 数量
SELECT @@innodb_buffer_pool_instances;

三个参数里，pool size 的配置是即时生效无须重启的，所以我挺有试一下的冲动的。 接下来我们要考虑的问题就是这个值配多大合适了， 这个地址 给了一个简单的计算方法，我的环境，剩余内存还有 16G：

一下子用掉 13G 我觉得有点过于激进，于是我把 128m 放大了 50 倍，设成了 6G(6442450944)。 很快磁盘队列就降下来了，从 5 到 0，内存经过一段时间的使用稳定增长了 5G 多。 基本也符合我们的设定。

SET GLOBAL innodb_buffer_pool_size=6442450944;

总结

调大缓冲区确实显著降低了磁盘读写，那么对于用户的感知，又有什么变化呢？ 之前打开页面需要 15 秒的用户，现在只需要不到 3 秒就可以完成了。 这个参数是 MySQL 5.7 引入的，所以如果你的 MySQL 不太旧，同时也有磁盘 IO 的问题，不妨也试一试， 看来服务还可以再撑一阵子，代码优化的问题又可以放一放了，毕竟还有更多的工作……