慢查询堆积导致从库不可用

现象

收到报警，提示某个端口的某几个从库连接超时，偶尔连接失败。凭经验，猜测应该是慢查造成的（如果是机器挂了，应该一直报连接失败，并且 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 能解决一些重复查询的问题。