高性能MySQL：一个诊断案例（3）

一个诊断案例（ ）

我们看到了两种可能性 要么是数据库导致了I/O（如果能找到源头的话 那么可能就找到了问题的原因） 要么不是数据库导致了所有的I/O 而是其他什么导致的 而系统因为缺少I/O 资源影响了数据库性能 我们也很小心地尽力避免引入另外一个隐式的假设 磁盘很忙并不一定意味着MySQL 会有问题 要记住 这个服务器主要的压力是内存读取 所以也很可能出现磁盘长时间无法响应但没有造成严重问题的现象

如果你一直跟随我们的推理逻辑 就可以发现还需要回头检查一下另外一个假设 我们已经知道磁盘设备很忙 因为其等待时间很高 对于固态硬盘来说 其I/O 平均等待时间一般不会超过 / 秒 实际上 从iostat 的输出结果也可以发现磁盘本身的响应还是很快的 但请求在块设备队列中等待很长的时间才能进入到磁盘设备 但要记住 这只是iostat 的输出结果 也可能是错误的信息

究竟是什么导致了性能低下？

当一个资源变得效率低下时 应该了解一下为什么会这样 有如下可能的原因

资源被过度使用 余量已经不足以正常工作

资源没有被正确配置

资源已经损坏或者失灵

回到上面的例子中 iostat 的输出显示可能是磁盘的工作负载太大 也可能是配置不正确（在磁盘响应很快的情况下 为什么I/O 请求需要排队这么长时间才能进入到磁盘？） 然而 比较系统的需求和现有容量对于确定问题在哪里是很重要的一部分 大量的基准测试证明这个客户使用的这种SSD 是无法支撑几百MB/s 的写 *** 作的 所以 尽管iostat 的结果表明磁盘的响应是正常的 也不一定是完全正确的 在这个案例中 我们没有办法证明磁盘的响应比iostat 的结果中所说的要慢 但这种情况还是有可能的 所以这不能改变我们的看法 可能是磁盘被滥用注 或者是错误的配置 或者两者兼而有之 是性能低下的罪魁祸首

在检查过所有诊断数据之后 接下来的任务就很明显了 测量出什么导致了I/O 消耗 不幸的是 客户当前使用的GNU/Linux 版本对此的支持不力 通过一些工作我们可以做一些相对准确的猜测 但首先还是需要探索一下其他的可能性 我们可以测量有多少I/O来自MySQL 但客户使用的MySQL 版本较低以致缺乏一些诊断功能 所以也无法提供确切有利的支持

作为替代 基于我们已经知道MySQL 如何使用磁盘 我们来观察MySQL 的I/O 情况 通常来说 MySQL 只会写数据 日志 排序文件和临时表到磁盘 从前面的状态计数器和其他信息来看 首先可以排除数据和日志的写入问题 那么 只能假设MySQL 突然写入大量数据到临时表或者排序文件 如何来观察这种情况呢？有两个简单的方法 一是观察磁盘的可用空间 二是通过lsof 命令观察服务器打开的文件句柄 这两个方法我们都采用了 结果也足以满足我们的需求 下面是问题期间每秒运行df–h 的结果

下面则是lsof 的数据 因为某些原因我们每五秒才收集一次 我们简单地将mysqld 在/tmp 中打开的文件大小做了加总 并且把总大小和采样时的时间戳一起输出到结果文件中

$ awk

/mysqld *tmp/ {

total += $

}

/^Sun Mar / &&total {

printf %s % f MB\n $ total/ /

total =

} lsof txt

: : MB

: : MB

: : MB

: : MB

: : MB

从这个数据可以看出 在问题之初MySQL 大约写了 GB 的数据到临时表 这和之前在SHOW PROCESSLIST 中有大量的 Copying to tmp table 相吻合 这个证据表明可能是某些效率低下的查询风暴耗尽了磁盘资源 根据我们的工作直觉 出现这种情况比较普遍的一个原因是缓存失效 当memcached 中所有缓存的条目同时失效 而又有很多应用需要同时访问的时候 就会出现这种情况 我们给开发人员出示了部分采样到的查询 并讨论这些查询的作用 实际情况是 缓存同时失效就是罪魁祸首（这验证了我们的直觉） 一方面开发人员在应用层面解决缓存失效的问题 另一方面我们也修改了查询 避免使用磁盘临时表 这两个方法的任何一个都可以解决问题 当然最好是两个都实施

       返回目录 高性能MySQL

       编辑推荐

       ASP NET开发培训视频教程

数据仓库与数据挖掘培训视频教程

lishixinzhi/Article/program/MySQL/201311/29695

前言

案例取自极客时间《mysql45讲》

案例

模拟执行器分析查询语句

场景复现

奇了怪了，此时没用索引，进行了全表扫描

虽然使用了索引，但是还是扫描了37116行，不妨结合之前的知识分析一下：

1.另一个事务未提交，需要保存之前的数据的数据版本，因此delete10万行数据实际是标记数据，这样每一行数据就有两个数据版本，旧的是delete之前的，新的是标记为delete的，索引a上的数据有两份

2.那还多出来的1万7呢，之前介绍过索引树的叶子节点存的是主键，select * 还要进行回表查询，这里将回表的扫描行数一并算上

为什么会选错索引

选择索引是优化器的工作，优化器要找到最优的执行方案并选择最小的代价去执行，扫描行数是影响执行代价之一（扫描越小，访问磁盘次数越少，消耗CPU资源越少）

mysql执行语句之前需要通过根据信息来统计记录数

这个统计信息就是索引的区分度，即索引上不同的值越多，区分度越高越好（show index t 的 cardinality字段查看），索引的区分度是利用采样统计得到的即取小部分统计信息再乘以整体。

除了使用统计信息，还会计算回表代价(主键不需要回表)

如果是统计信息不对那就修正

另一种场景复现

按理说这是个空集，利用索引a只扫描1000行，利用索引b要扫描50000行，这里优化器竟然选择了索引b！！

mysql又选错了索引

解决办法

2.引导使用a索引

我们知道索引树上的数据是有序的，优化器使用b索引，一方面是认为索引b可以避免排序 ,order by a,b强制按照a,b排序意味着两个都需要排序，因此扫描行数成了影响决策的主要条件

3.删掉索引b

解决mysql选错索引主要有两大方向

1.强制指定索引

2.干涉优化器选择（比如增大limit数量，增加order by ，写成子查询）

MySQL选错索引导致的线上慢查询事故

mysql中走与不走索引的情况汇集(待全量实验)

---