[MYSQL] 业务反馈由于升级导致SQL突然变慢, 让我瞅瞅是怎么回个事

背景

业务反馈: 有个系统升级之后突然变慢了?

反馈的系统是2个月前升级的, 而测试环境则是在更早之前升级的, 测试没得问题之后才会升级生产. 现在却来突然反馈测试环境是升级导致变慢了. 感觉有点幺蛾子.

分析过程

测试环境也不方便截图, 也不好模拟, 就简单描述下分析过程.

使用top查看, 未发现有使用大量cpu的进程, mysql也才使用10%左右; load也就1左右; iowait有点,但也不多(不到5). 当然这个时候已经没有相关SQL执行了, 所以看这个没啥意义, 但还是得确认下服务器上没得其它进程在跑.

登录数据库,使用show processlist确认当前连接数不多, 也没得一直在跑的sql之类的,毕竟top看到都很闲了, 查看慢日志路径.

登录数据库的时候发现版本并没有升级, 也就是这并不是之前升级的那个测试环境. 幺蛾子+1

查看慢日志, 发现确实存在大量的慢SQL, SQL就那么几种, 但是量很多, 而且每条执行时间超过10s. sql比较简单, 大概是select x from tblename where col in ('xx'), 看了下执行计划, col字段没得索引. 走的全表扫描. 查看了下表数量,也才几千条. 全表扫描也不至于10几秒啊, 而且这种sql还是连续执行的(某小段时间几乎每秒都执行1次).

既然慢日志是10+秒, 数据量不大(虽然是全表扫描), 那就人工跑一下确认下. 人工执行发现确实耗费10+秒. 执行的时候观察了下cpu和IO, cpu很闲(毕竟就简单的select), io有一定的延迟但不算多(毕竟数据量就那么点). 啊, 这...

再次跑了下类似的sql, 发现执行时间很快(1秒内), 嗯? 也就是说那张表的数据刚才不在内存里面! 在我手动select之后才被换到内存.(swap使用很低, 也不在swap里面, 那就是在磁盘上了)

使用select POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,HIT_RATE from information_schema.INNODB_BUFFER_POOL_STATS; 查看缓存命中率发现是998, 这命中率很高啊(通常99.5%算正常). 不至于刚才查询那么慢啊.

欸, 业务现在没有跑了, 就我刚才手动跑了几条sql,这几条都到内存里来了, 那么这段时间的命中率当然高啊. 于是分别跑了不同表的sql之后, 发现命中率确实下降了(950),下降得还很明显.

到这基本上就确定确实是要查询的数据未加载到内存的原因了, 由于这种sql经常查询, 居然还不算热数据, 故猜测innodb_buffer_pool_size给小了. 服务器32GB内存, 通常会给16GB给innodb_buffer_pool. 查询了下相关参数和配置文件,发现innodb_buffer_pool_size=1GB.

明显配置得有问题, 而且不符合基线. 幺蛾子+1

解决办法

调整innodb_buffer_pool_size=16GB之后重启,应用测试正常.