加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程 > 正文

MySQL太慢?试试这些诊断思路和工具

发布时间:2018-09-09 17:14:30 所属栏目:编程 来源:黄炎
导读:9月15日技术沙龙 | 与东华软件、AWS、京东金融、饿了么四位大咖探讨精准运维! MySQL 慢怎么办 如果遇到 MySQL 慢的话,你的第一印象是什么,MySQL 数据库如果性能不行,你是如何处理的? 我咨询了一些同行, 得到了以下反馈: 第一反应是再试一次 第二个反
副标题[/!--empirenews.page--] 9月15日技术沙龙 | 与东华软件、AWS、京东金融、饿了么四位大咖探讨精准运维!

MySQL太慢?试试这些诊断思路和工具

MySQL 慢怎么办

如果遇到 MySQL 慢的话,你的第一印象是什么,MySQL 数据库如果性能不行,你是如何处理的?

我咨询了一些同行, 得到了以下反馈:

  • 第一反应是再试一次
  • 第二个反应是优化一下 SQL
  • 第三个反应是调大 buffer pool,然后开始换硬件了,换一下 SSD
  • 最后实在不行了找个搜索引擎搜索一下“MySQL 慢怎么办”。

如果大家用的是国内的搜索引擎的话,搜索引擎会推荐某某知道或者某某乎, 推荐一些 MySQL 调优经验, 调大参数 A, 调低参数 B, 诸如此类,类似的网站能告诉你 MySQL 慢怎么办。

我们来分析一下这些现象背后隐藏的意义:

  • 如果再试一次能够成功的话, 意味着你可能碰到了不可复现的外界因素的影响,导致 MySQL 会慢。
  • 如果优化 SQL 能解决,就意味着 SQL 的执行复杂度远远大于它的需求复杂度。
  • 如果调大 buffer pool 能解决,就意味着 MySQL 碰到了自身的某些限制。
  • 如果换 SSD 能解决,那么意味着服务器资源受到了一定的限制。
  • 如果需要搜索引擎,意味着调优这事已经变成了玄学。

本文向大家分享我对 MySQL 慢的诊断思路,以及向大家介绍系统观测工具。

MySQL 慢的诊断思路

MySQL 慢的诊断思路,一般会从三个方向来做:

  1. MySQL 内部的观测
  2. 外部资源的观测
  3. 外部需求的改造

下面依次看一下这几个思路。

MySQL 内部观测

常用的 MySQL 内部观测手段是这样的:

  • 第一步是 Processlist,看一下哪个 SQL 压力不太正常;
  • 第二步是 explain,解释一下它的执行计划;
  • 第三步要做 Profilling,如果这个 SQL 能再执行一次的话, 就做一个 Profilling;
  • 高级的 DBA 会直接动用 performance_schema ,MySQL 5.7 以后直接动用 sys_schema,sys_schema 是一个视图,里面有便捷的各类信息,帮助大家来诊断性能;
  • 再高级一点,会动用 innodb_metrics 进行一个对引擎的诊断。

除了这些手段以外,还有一些乱七八糟的手段就不列在这了,这些是常规的 MySQL 内部状态观测的思路。

外部资源观测

这里引用国外一个大神写的文章,标题是《60 秒的快速巡检》(参考链接在文末)。我们来看一下它在 60 秒之内对服务器到底做了一个什么样的巡检。一共十条命令,下面一条一条来看一下。

  1. uptime,uptime 告诉我们这个机器活了多久,以及它的平均负载是多少。
  2. dmesg -T | tail,告诉我们系统日志里边有没有什么报错。
  3. vmstat 1,告诉我们虚拟内存的状态,页的换进换出有没有问题,swap 有没有使用。
  4. mpstat -P ALL 1,告诉我们 CPU 压力在各个核上是不是均匀的。
  5. pidstat 1,告诉我们各个进程的对资源的占用大概是什么样子。
  6. iostat-xz 1,查看 IO 的问题。
  7. free-m 内存使用率;
  8. sar-n DVE 1,
  9. sar-n TCP, ETCP 1,8 和 9 两条按设备网卡设备的维度,看一下网络的消耗状态,以及总体看 TCP 的使用率和错误率是多少。
  10. top,看一下大概的进程和线程的问题。

这个就是对于外部资源的诊断,这十条命令揭示了应该去诊断哪些外部资源。

外部需求改造

第三个诊断思路是外部的需求改造,在这里引用了 MySQL 官方文档中的一章,《Examples of Common Queries 》( https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html),文档中介绍了常规的 SQL 怎么写, 给出了一些例子。

下面看一下它其中提到的一个例子。

MySQL太慢?试试这些诊断思路和工具

这张表有三列,article、dealer、price。它做的事情是从这个表里选取每个作者最贵的商品列在结果集中,这是它最原始的 SQL,非常符合业务的写法,但是它是个关联子查询。

MySQL太慢?试试这些诊断思路和工具

关联子查询成本是很贵的,所以上面的文档会教你快速地把它转成一个非关联子查询,大家可以看到中间的子查询和外边的查询之间是没有关联性的。

MySQL太慢?试试这些诊断思路和工具

第三步,会教大家直接把子查询拿掉,然后转成这样一个 SQL,这个就叫业务改造,前后三个 SQL 的成本都不一样,把关联子查询拆掉的成本,拆掉以后 SQL 会跑得非常好,但这个 SQL 已经不能良好表义了,只有在诊断到 SQL 成本比较高的情况下才建议大家使用这种方式。

为什么它能够把一个关联子查询拆掉?

这背后的原理是关系代数,所有的 SQL 都可以被表达成等价的关系代数式,关系代数式之间有等价关系,这个等价关系通过变换可以把关联子查询拆掉。

总结一下,对于 MySQL 慢的诊断思路如下:

第一,MySQL 本身提供了很多命令来观察 MySQL 自身的各类状态,从上往下检一般能检到 SQL 的问题或者服务器的问题。

第二,从服务器的角度,我们从巡检的脚本角度入手,服务器的资源就这几种,观测手法也就那么几种,把服务器的资源全部都观察一圈就可以了。

第三,如果实在搞不定,需求方一定要按照数据库容易接受的方式去写 SQL,这个成本会下降的非常快,这个是常规的 MySQL 慢的诊断思路。

下面重点介绍为大家介绍系统观测工具。

系统观测工具介绍

先从诊断思路的讨论切换到系统的观测工具,首先了解什么叫系统观测工具并且看一下它的举例,然后再回到诊断思路上,看看新的工具的引入能为我们的思路到底带来怎样的改变。

什么叫系统观测工具

这里也参考了一篇外国人写的文档:

https://jvns.ca/blog/2017/07/05/linux-tracing-systems/

把这个文档拆开,中间描述了三件事情:

系统观测工具的数据源来自于哪里;

数据采集过程,因为采集的是系统的运行状况,所以到底如何采集这是一个难点;

应该怎么看数据,是用图来看,还是用表来看,它就叫数据处理前端。

(编辑:核心网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读