MySQL很慢怎么办如果MySQL很慢，您的第一印象是什么？如果MySQL数据库性能不好，怎么处理？咨询了一些同事，得到的反馈如下：***答案是重试，第二个答案是优化SQL，第三个答案是增加缓冲池，然后开始换硬件，改SSD***实在不行，找个搜索引擎搜索“MySQL慢了怎么办”。如果用国内的搜索引擎，搜索引擎会推荐XX或者XX，推荐一些MySQL调优经验，增加参数A，减少参数B，等等。类似的网站可以告诉你MySQL慢了怎么办。下面我们来分析一下这些现象背后隐藏的含义：如果重试成功，说明可能遇到了不可重现的外部因素，从而导致MySQL变慢。如果能解决优化过的SQL，就意味着SQL的执行复杂度远大于它的需求复杂度。如果解决方案可以通过增加缓冲池来解决，那就意味着MySQL遇到了一些自身的局限性。如果更换SSD可以解决问题，说明服务器资源有限。如果你需要一个搜索引擎，那就意味着调优变成了玄学。本文与大家分享我对MySQL慢诊断的思考，并向大家介绍系统观察工具。MySQL慢的诊断MySQL慢的诊断一般从三个方向进行：MySQL内部的观察外部资源的观察外部需求的转化让我们依次来看看这些思路。MySQL内部观察常用的MySQL内部观察方法如下：第一步是Processlist，看哪些SQL压力不正常；第二步是explain，解释它的执行计划；第三步做Profiling，如果SQL可以再执行一次，做一个profilling；高级DBA会直接使用performance_schema，MySQL5.7以后会直接使用sys_schema，sys_schema是一个视图，里面包含了各种方便的信息，可以帮助你诊断性能；更高级的，将使用innodb_metrics来诊断引擎。除了这些方法，还有一些乱七八糟的方法就不一一列举了。以上是观察MySQL内部状态的大致思路。外部资源观察这里有一篇国外高手写的文章，题目是《60 秒的快速巡检》（参考链接在文末）。让我们看看它在60秒内对服务器进行了什么样的检查。一共有十条命令，我们一一来看。正常运行时间，正常运行时间告诉我们这台机器存活了多长时间，它的平均负载是多少。dmesg-T|tail，告诉我们系统日志中是否有错误。vmstat1，告诉我们虚拟内存的状态，页面换入换出是否有问题，是否使用swap。mpstat-PALL1，告诉我们CPU压力是否均匀分布在各个内核上。pidstat1，告诉我们每个进程的资源使用情况。iostat-xz1、检查IO问题。free-m内存使用率；sar-nDVE1、sar-nTCP、ETCP1、8、9，根据设备网卡设备的维度，看网络的消耗情况，一般看TCP的使用率和错误率如何许多。顶一下，看看一般的进程和线程问题。这是外部资源的诊断。这十个命令揭示了应该诊断哪些外部资源。外需转型的第三个诊断思路是外需转型。这里引用MySQL官方文档中的一个章节，《Examples of Common Queries 》（https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html），文档介绍了如何写正则SQL并给出了一些例子。我们来看一个里面提到的例子。这个表有三列，article，dealer，price。它所做的是从这个表中选择每个作者最昂贵的产品，并将其列在结果集中。这是它最原始的SQL，很符合业务，但是是关联子查询。关联子查询的代价是非常昂贵的，所以上面的文档会教你快速将其转换成非关联子查询。可以看到中间子查询和外层查询没有关联。第三步，教大家直接把子查询去掉，再转成这样的SQL。这称为业务转型。前后三个SQL的开销是不一样的。会跑的很好，但是这个SQL已经不能很好的表达了。建议只有在诊断出SQL的开销比较大时才使用该方法。为什么它能够拆除相关子查询？这背后的原理是关系代数。所有SQL都可以表示为等价的关系代数公式。关系代数公式之间存在等价关系。这种等价关系可以通过转换去掉关联的子查询。综上所述，诊断MySQL慢的方法如下：***，MySQL本身提供了很多命令来观察MySQL自身的各种状态。从上往下检查一般可以查出SQL问题或者服务器问题。其次，从服务器的角度，我们从检查脚本的角度出发。服务器资源只有几种，观察方法也只有几种。观察服务器的所有资源就足够了。第三，如果真的不确定，需求方一定要用数据库容易接受的方式来写SQL。这个成本会下降的很快。这是诊断慢速MySQL的常用方法。下面重点为大家介绍系统观察工具。系统观察工具的介绍首先从诊断思路的讨论切换到系统观察工具，先了解什么是系统观察工具并看它的例子，再回到诊断思路，看看新工具的介绍能为我们做些什么思想怎么变。什么是系统观察工具这里也引用一个老外写的文档：https://jvns.ca/blog/2017/07/05/linux-tracing-systems/把这个文档拆开，描述三件事中间的Things：系统观察工具的数据源来自哪里；数据采集??过程，因为采集的是系统的运行状态，所以如何采集是一个难点；怎么看数据，用图还是用表，就叫数据处理前端。第一步，我们来看数据源。Linux提供的数据源包括操作系统内核态提供的观察点和用户态提供的观察点。MySQL很早就提供了用户态的观察点。第二步是如何提取数据。下面这些工具中，大家应该最熟悉perf和ftrace了。有些人正在使用sysdig。你可能听说过其他人。这是从操作系统中提取数据的方法。第三步是数据处理前端。Perf和ftrace是前端常用的。如果你熟悉perf，你就会知道perf产生的数据是一个树状的数据，你可以和这棵树进行交互，比如：查看一个函数运行了多长时间，哪个函数耗时最长，这个是数据处理前端。下面我们来对比一下常规的四类系统观察工具：ftrace、perf_events、eBPF和Systemtap，这四种工具有什么区别，看看Linux为什么提供这么多观察工具。ftrace：ftrace是sysfs中的存根。通过这个存根，内核提供了一种观察的形式——把你要观察的函数的签名放到这个存根中，然后操作系统就会提供这个函数的运行状态。ftrace的结构如左图所示。数据处理前端和采集端是ftrace，数据源是下面这几堆。perf：常用的perf原理是操作系统提供系统调用将数据写入缓存，然后客户端提取数据呈现在显示器上。eBPF：这就是我想在这篇文章中推荐的。以上两种解决方案，一种是操作系统提供的文件系统上的存根，另一种是操作系统提供的系统调用，而eBPF是一种直接将一段代码插入到文件的某个位置的机制操作系统内核。Systemtap：它的原理是将一段C代码编译成一个内核模块，然后将这个模块嵌入到内核中。它不是内核提供的机制，而是内核的模块机制提供的功能。介绍这四种观察工具的区别，让大家在选择观察工具的时候就知道如何选择了。四种观察工具中哪一种对系统危害最小？对系统危害最小的是系统调用，是系统承诺的服务。然后是ftrace，它是系统在文件系统层面提供的一个端口，告诉你可以通过这个端口与系统进行交互。谁侵入了系统？对系统侵入性最大的应该是eBPF，因为它直接将一段代码嵌入到系统中，而最不稳定的应该是SystemTap，因为它是系统的一个模块，提供了非常复杂的功能。上图是eBPF的架构图。eBPF首先将一个程序编译成二进制代码，然后将其插入到操作系统中。操作系统在运行这段代码的时候，会把收集到的数据吐到操作系统自己的空间里，然后做统一的返回。eBPF结构的核心部分是将代码插入到操作系统中运行。它需要各种安全保护来完成这个，所以这就是机制复杂的地方。让我们通过引用bcc来快速了解一下eBPF可以做什么，bcc是一个开箱即用的开源eBPF脚本集。bcc（eBPF脚本合集）用MySQL请求延迟分析的例子：一个MySQL承担了很多业务，几千个并发中，哪个SQL最慢，哪个SQL超过一秒，除了日志慢，你也可以这样看。该命令的结果分为三列。它的第一列是请求的延迟，呈指数增长，单位是微秒。中间一列是它的最大数量。如果有请求，则延迟在64-127微秒的区间内，第一个数字会加一，最后一列是它的分布图。在同一份报告中提供数值方法和图形方法，结果一目了然。对于这个服务器，我选择了性能压力，它的大部分请求都集中在64到127微秒之间。这个数据库的性能可能还不错。再来看另一种压力。我在一个数据库里放了一个select+insert混合压，它的图又变了。它呈现了一个非常好的双峰图。我为两个峰使用了另一种颜色。表示这两个峰值意味着一个数据库中很可能存在混合压力，或者压力的上半部分是因为安装了一些缓存，而下半部分的压力是因为没有安装缓存活动缓存。这部分请求比较慢，形成了另一个高峰，所以通过这个高峰分析，可以看出数据库大概的运行状态。如果你能做得更好，你可以采样自己的数据库并制作链条对比图。比如分析数据库在今天和昨天同一时间在相同业务压力下的延迟。如果数据库的延迟峰值已经延迟，则可以意味着数据库的状态越来越差。这是bcc可以做的第一件事，需要再次强调的是，下载开箱后可以直接使用。MySQL的慢查询：MySQL本身就提供了很好的慢查询，为什么要用另外一种机制来获取MySQL的慢查询呢？MySQL的慢查询可能比较难做，相对于MySQL的慢日志，可以用较低的成本做到：获取少量的慢查询获取某个模式的慢查询获取某个用户的慢查询比如获取一个小的慢查询的数量，为什么是少量的？因为不确定现在的在线延迟是多少，如果慢查询只打开一秒，可能瞬间日志就堆起来了，性能会下降。但是，如果慢查询开启了十秒左右，这段时间没有请求***，所以需要一点一点的调整这个值。比如在线上最慢的1%的查询可以优化，但是在这个脚本中，你可以提取一定范围内的几个最慢的查询。通过脚本，你还可以在某种模式下安装慢查询。比如我们只关心update的慢查询，那么获取select的结果意义不大，或者我必须获取某些特定表的相关查询，我可以通过脚本来完成这两项工作。第三种情况，你想获取某个用户的慢查询。这个一般是针对多租户系统的，因为当多租户系统只想分析某个用户的慢查询时，这个脚本比较有用。VFSLatencyAnalysis：VFS的Latency分析，也就是对数据库的写压力，可以清楚的看到一个双峰图，也就是写的两个峰值，这是数据库对内核写压力的反馈。这是什么意思？这可能意味着因为这部分写入是操作系统文件系统的缓存，而后面的部分写入实际上是写入设备，所以两者的延迟是不同的。这是典型的双峰。如图所示，您需要将两个峰分开才能进行此类分析。换句话说，如果写压力集中在这里，没有第二个峰值，我们是否需要更换物理设备？它可能不需要，因为所有内容都会刷新操作系统的缓存。生命周期短的临时文件检测：这不一定常见。MySQL在某些情况下会使用临时表。如果不写SQL，会创建一个临时表。这些临时表的生命周期很短，但是量很大，所以一定要写到文件中，而不是写到内存中。在这种情况下，会对操作系统造成一定的压力，而这种压力不容易诊断，因为临时文件的生命周期很短，所以这个脚本可以帮你提供一个解决方案，这个解决方案的结果是像这样。我做了一个临时表，这个临时表存活了大约5.3秒，所以在脚本的结果中显示出来了。如果你扫描自己的在线MySQL，发现这里有很多东西，说明大量的临时表被使用了。如果此时IO压力比较大，可能是临时表的影响。短连接分析：比较好的应用会使用连接池，但是我们往往没有这么好的运气，经常遇到这么好的应用，所以往往业务会抛出大量的短连接。本例中sysbench的并发量很大，但是只持续了300多毫秒。这些连接仅存在300多毫秒。反复运行这个sysbench，可以杀掉数据库，建立一千个连接，300毫秒后，它会被销毁，又会建立一千个连接，你的业务就上上下下了。通过这个脚本，可以找出压力来自于哪个服务器和端口，然后搞定。长期联系分析：除了短期联系分析，还有长期联系分析。哪个业务端老是和我的数据打交道，老是写进去，老是读进去，网速很慢。它可以帮你提供这样一个视角，它有阅读和写作。以上几个bcc相关的例子都是现成的脚本。bcc可以观察操作系统的方方面面。比如OOMkill掉了什么东西，也可以看看有没有内存泄露。它基本上是我们近年来发现的宝库。可以直接调用这些脚本来完成很多其他人无法完成的分析，其技术使用的是eBPF，直接在github上搜索即可。eBPF使用/限制如果这里的脚本不能满足要求，可以自己写。这里介绍一下脚本的编写方法和eBPF的局限性。以上面的MySQL延迟分析为例。MySQL上有一千个查询。这些查询属于哪个延迟时间？图中，为了完成这个需求，我需要写两个程序，其中***段程序是运行在内核内部的程序。这个程序的逻辑是这样的：在查询开始的时候拦截，让它记录一个时间戳；在请求结束时拦截并记录一个时间戳；减去两个时间戳以获得延迟；丢进结果集中，程序就完成了。我用结束时间减去开始时间，减去得到一个延迟，然后把这个延迟扔进一个统计容器，这件事就结束了。这是我要编写的第一个程序。它是一个嵌入内核的程序，但是需要一个shell程序来负责嵌入。这个shell程序的逻辑也很简单。将刚才的内核程序嵌入到MySQL的观察点，嵌入到内核中，然后取出结果集，打印出来，就结束了。这就是如何编写一个eBPF脚本，你唯一需要做的就是这两个程序，然后运行它。这段程序的核心只有45行，中间忽略了负责错误处理的部分。你只需要把当前的脚本拿下来复制，改了之后就可以完成很多功能了。为什么这么好的方法很多人不知道呢？操作系统内核的限制，这个功能是Linux4.4引入的，但是在Linux4.4上有统计bug，我们推荐Linux4.9+，一些有用的功能只有4.13+才有，这是eBPF***limits。怎么做？我只能祝你长寿！活到Linux4.x内核可以用于生产环境的那一天。它的第二大限制是MySQL的编译参数。MySQL虽然很早就提供了dtrace观察点，但是这些观察点是公开的，但是在官方发布的包中是默认编译的，是没有观察点编译的，所以直接在官方发布的二进制包中是不能使用这个功能的，你需要自己编译。编译的时候需要包含这个参数，可能也是一个比较大的限制。所以如果你受限，我推荐另一个工具：systemtap。Linux2.6已经有了，但是它的机制是写一个内核模块。这种机制不是特别稳定。它增加了一些限制来解决不是特别稳定的问题。例如，内核中可以使用的内存大小是有限的，采集频率也有限，对整个内核性能的影响百分比也有限。这些限制参数全部开启时，相对安全。但是许多观察功能必须关闭这些限制。一旦关闭内核，就不会很稳定，所以这个工具，我不敢写它的缺点，因为它确实是一个好工具，我们很难说这个缺点是致命的缺陷，但是不建议在生产环境中使用，但是在测试环境中确实是一个非常有趣的工具。如果你不能使用eBPF，你可以使用systemtap来做一些诊断。还有很多其他的工具：至于如何选择，可以直接google，有专门的文章教你如何选择这些观察工具。但总的来说，没有科学的思维方式，只有尝试，不断尝试。黄岩，Acson研发总监，深入研究分布式数据库相关技术，擅长行业相关的MySQL中间件产品及开发，以及分布式中间件在企业中的应用实践。
