Linux系统下mysql运行慢,请给出分析方案!!

Linux 进程通过 C 标准库中的内存分配函数 malloc 向系统申请内存，但是到真正与内核交互之间，其实还隔了一层，即内存分配管理器(memory allocator)。常见的内存分配器包括：ptmalloc(Glibc)、tcmalloc(Google)、jemalloc(FreeBSD)。MySQL 默认使用的是 glibc 的 ptmalloc 作为内存分配器。

内存分配器采用的是内存池的管理方式，处在用户程序层和内核层之间，它响应用户的分配请求，向 *** 作系统申请内存，然后将其返回给用户程序。

为了保持高效的分配，分配器通常会预先向 *** 作系统申请一块内存，当用户程序申请和释放内存的时候，分配器会将这些内存管理起来，并通过一些算法策略来判断是否将其返回给 *** 作系统。这样做的最大好处就是可以避免用户程序频繁的调用系统来进行内存分配，使用户程序在内存使用上更加高效快捷。

关于 ptmalloc 的内存分配原理，个人也不是非常了解，这里就不班门弄斧了，有兴趣的同学可以去看下华庭的《glibc 内存管理 ptmalloc 源代码分析》。

关于如何选择这三种内存分配器，网上资料大多都是推荐摒弃 glibc 原生的 ptmalloc，而改用 jemalloc 或者 tcmalloc 作为默认分配器。因为 ptmalloc 的主要问题其实是内存浪费、内存碎片、以及加锁导致的性能问题，而 jemalloc 与 tcmalloc 对于内存碎片、多线程处理优化的更好。

目前 jemalloc 应用于 Firefox、FaceBook 等，并且是 MariaDB、Redis、Tengine 默认推荐的内存分配器，而 tcmalloc 则应用于 WebKit、Chrome 等。

我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：

第一个方向是MySQL内部的观测

第二个方向是外部资源的观测

第三个方向是外部需求的改造

1.1 MySQL 内部观测

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

除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。

1.2 外部资源观测

外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。

1.uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。

2.dmesg -T | tail，告诉我们系统日志里边有没有什么报错。

3.vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。

4. mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。

5.pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。

我们来看一下后五条：

首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。

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

1.3 外部需求改造

第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。

我们来看一下它其中提到的一个例子。

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

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

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

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

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

上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。

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

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

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

MySQL 在崩溃恢复时，会遍历打开所有 ibd 文件的 header page 验证数据字典的准确性，如果 MySQL 中包含了大量表，这个校验过程就会比较耗时。 MySQL 下崩溃恢复确实和表数量有关，表总数越大，崩溃恢复时间越长。另外磁盘 IOPS 也会影响崩溃恢复时间，像这里开发库的 HDD IOPS 较低，因此面对大量的表空间，校验速度就非常缓慢。另外一个发现，MySQL 8 下正常启用时居然也会进行表空间校验，而故障恢复时则会额外再进行一次表空间校验，等于校验了 2 遍。不过 MySQL 8.0 里多了一个特性，即表数量超过 5W 时，会启用多线程扫描，加快表空间校验过程。

如何跳过校验MySQL 5.7 下有方法可以跳过崩溃恢复时的表空间校验过程嘛？查阅了资料，方法主要有两种：

1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ，那么 validate = false，即可以跳过表空间校验。实际测试的时候设置 innodb_force_recovery =1，也就是强制恢复跳过坏页，就可以跳过校验，然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程，快速启动 MySQL，个人目前暂时未发现有什么隐患。2. 使用共享表空间替代独立表空间这样就不需要打开 N 个 ibd 文件了，只需要打开一个 ibdata 文件即可，大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决 drop 大表时性能抖动的原理后，感觉共享表空间在很多业务环境下，反而更有优势。

临时冒出另外一种解决想法，即用 GDB 调试崩溃恢复，通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程，然后让 MySQL 正常关闭，重新启动就可以正常启动了。但是实际测试发现，如果以 debug 模式运行，确实可以临时修改 validate 变量，跳过表空间验证过程，但是 debug 模式下代码运行效率大打折扣，反而耗时更长。而以非 debug 模式运行，则无法修改 validate 变量，想法破灭。

---