现象
收到报警,提示某个端口的某几个从库连接超时,偶尔连接失败。凭经验,猜测应该是慢查造成的(如果是机器挂了,应该一直报连接失败,并且 MySQL 服务器千兆网卡很少会存在网络带宽打满的问题)。登上机器查看负载情况(由于机器负载较高,卡在了登录过程中,kill 了之后才能上去。因为 CPU 基本上都忙于处理 SQL 语句,影响其他比如 ssh 服务的连接处理。),看到如下状况:
CPU 基本占满,而且 us 部分占了 80% 多,wa 占了 10%,这基本可以判断 SQL 的问题,并且都在排序或者进行其它计算
进一步看机器的状态,内存基本用完,磁盘利用率飙升(其实 swap 也用完了,swap 总共 7G)
进一步分析
造成此问题的具体 SQL 是什么?有什么问题?
查看执行语句状态,
Copying to tmp table
表示正往临时表拷贝数据(没超过tmp_table_size
的大小),用的是内存临时表。分析 SQL 的执行计划,联表查询只用到了其中一个表的索引。 另外一个表进行了全表扫。 预估要对 35 万行数据进行排序筛选。
再看表结构定义,一个是 InnoDB 表,联表字段是 NOT NULL; 一个是 Myisam 表,联表字段是 default NULL。(联表字段结构必须一样么?)
再看慢日志的结果,引擎层实际扫描了 48 万行数据送到服务器,结果只取了其中 11 行,问题就在这里了,按照 <<高性能 MySQL>> 的说法就是 select 访问数据多于实际想要的数据。
为什么内存、swap 都用光了?
- 每来一个连接,服务器为其开一个线程,因为是慢查,线程使用到了 sort_buffer, join_buffer, myisam_sort_buffer, tmp_table, 这些加起来有 280 M 左右。
- 该端口的几个从库连接数维持在 100/s 的水平。
280MB * 100 / 1024 = 27GB, 加上 innodb_buffer_pool 8 G,基本上能达到第一张图该进程的占用水平。
也就是说,慢查询消耗了过多的 CPU 和内存空间,慢查堆积的越来越多,CPU、内存都到极限,造成机器卡死。
如何解决
优化 SQL。思路就是借助索引尽量少扫数据,当然怎么加索引还跟业务逻辑有关。
调整参数。鉴于几个从库都是 SSD 的机器,IO 还未饱和,在内存一定的情况下把 io_capacity 参数适当调大。
Qcache 能解决一些重复查询的问题。