MySQL OOM解决方案

在Linux系统中部署MySQL服务，经常会遇到MySQL因为OOM(out of memory)的原因被操作系统Kill，导致数据库服务异常。要分析该问题，首先我们需要了解一下Linux 内存分配的基本策略。

一、    Linux内存分配策略

一般写C语言程序，我们习惯使用malloc动态的申请内存空间（Java由JVM负责内存管理），malloc函数会向操作系统申请一段连续的内存单元，然后返回这段空间的起始地址。如果malloc函数返回为null则表示系统没有可分配的内存空间。这是我们的一般思维，当然这在某些操作系统中确实也是正确的（Solaris）。

但是Linux不是这样的，Linux的内存分配采取的是一种更加积极的分配策略，它假设应用申请了内存空间后并不会立即去使用它，所以允许一定量的超售，当应用真的需要使用它的时候，操作系统可能已经通过回收了其他应用的内存空间而变得有能力去满足这个应用的需求，简单的说，就是允许应用申请比实际可分配空间（包括物理内存和Swap）更多的内存，这个特性称为OverCommit。

 这个特性在Linux操作系统里面也是可配的，可以通过设置/proc/sys/overcommit_memory为不同的值来调整OverCommit策略。overcommit_memory可以取3个值：

0：默认值，由Linux内核通过一些启发式算法来决定是否超售和超售的大小，一般允许轻微的超售，拒绝一些明显不可能提供的请求，同时做一些规则限制，比如不同用户overcommit的大小也不一样。 

1：允许，不做限制的超售，当然这个也不是无限大，还受到寻址空间的限制，32位系统最大可能只有4G，64位系统大概16T左右。 

2：禁止，禁止超售，系统能够分配的内存不会超过swap+实际物理内存*overcommit_ratio，该值可以通过/proc/sys/vm/overcommit_ratio设置，默认50%。

我们在一个1G内存，400M Swap的虚拟机上进行一个小测试，实验一是一个程序不停通过malloc申请空间，实验二是程序在malloc后，即用1填充该空间，对比两个程序申请到的空间大小。

图1

图2

我们观测到实验二（图2）申请的空间与实际物理空间大体一致，而实验一（图1）却申请到远远大于实际物理空间的虚拟空间，这也验证了Linux的超售的内存分配策略。本身这是一个系统的优化，无可厚非。但是我们知道，但凡“超售”都是基于不会有大量程序同时使用资源的假设，这显然也是有风险的。所以Linux又使用了一种OOM Killer(Out Of Memory killer)的机制，在系统可用内存（包括Swap）即将使用完之前，选择性的Kill掉一些进程以求释放一些内存。

二、    Linux OOM Killer工作原理

这里就涉及到一个问题，到底Kill掉谁呢？一般稍微了解一些Linux内核的同学第一反应是谁用的最多，就Kill掉谁。这当然是Linux内核首先考虑的一种重要因素，但是也不完全是这样的，我们查一些Linux的内核方面的资料，可以知道其实Kill谁是由/proc//oom_score来决定的，这个值每个进程一个，是由Linux内核的oom_badness()函数负责计算的。那下面我们来仔细读一读badness()函数。   

在badness()函数的注释部分，写明了badness()函数的处理思路：

1) we lose the minimum amount of work done
       2) we recover a large amount of memory
         3) we don't kill anything innocent of eating tons of memory
       4) we want to kill the minimum amount of processes (one)
       5) we try to kill the process the user expects us to kill, this  algorithm has been meticulously tuned to meet the principle of least surprise ... (be careful when you change it)

总的来说就是Kill掉最小数量的进程来获取最大数量的内存，这与我们Kill掉占用内存最大的进程是吻合的。

/*
     * The memory size of the process is the basis for the badness.
     */
points = p->mm->total_vm;

分数的起始是进程实际使用的RAM内存，注意这里不包括SWAP，即OOM Killer只会与进程实际的物理内存有关，与Swap是没有关系的，并且我们可以看到，进程实际使用的物理内存越多，分数就越高，分数越高就越容易被牺牲掉。

/*
    * Processes which fork a lot of child processes are likely
    * a good choice. We add the vmsize of the childs if they
    * have an own mm. This prevents forking servers to flood the
    * machine with an endless amount of childs
    */
     ...
    if (chld->mm != p->mm && chld->mm)
         points += chld->mm->total_vm;

这段表示子进程占用的内存都会计算到父进程上。

s = int_sqrt(cpu_time);
        if (s)
                points /= s;
    s = int_sqrt(int_sqrt(run_time));
        if (s)
                points /= s;

这表明进程占用的CPU时间越长或者进程运行的时间越长，分数越低，越不容易被Kill掉。

/*
    * Niced processes are most likely less important, so double
    * their badness points.
    */
    if (task_nice(p) > 0)
              points *= 2;

如果进程优先级低（nice值，正值低优先级，负值高优先级），则Point翻倍。

/*
    * Superuser processes are usually more important, so we make it
    * less likely that we kill those.
    */
    if (cap_t(p->cap_effective) & CAP_TO_MASK(CAP_SYS_ADMIN) ||
                                p->uid == 0 || p->euid == 0)
                points /= 4;

super用户的进程优先级较低。

/*
    * We don't want to kill a process with direct hardware access.
    * Not only could that mess up the hardware, but usually users
    * tend to only have this flag set on applications they think
    * of as important.
    */
    if (cap_t(p->cap_effective) & CAP_TO_MASK(CAP_SYS_RAWIO))
                points /= 4;

直接可以访问原始设备的进程优先级较高.

/*
    * Adjust the score by oomkilladj.
    */
        if (p->oomkilladj) {
                if (p->oomkilladj > 0)
                        points <<= p->oomkilladj;
                else
                        points >>= -(p->oomkilladj);
}

每个进程有个oomkilladj 可以设置该进程被kill的优先级，这个参数看上去对Point影响还是比较大的，oomkilladj 最大+15，最小是-17，越大越容易被干掉，这个值由于是移位运算，所以影响还是比较大的。

三、    解决MySQL OOM的最佳实践

前面两章，我们分析了Linux内存分配的策略以及Linux通过使用 OOM_Killer的机制解决了“超售”引起的风险，MySQL同其他的应用程序一样，在操作系统允许的范围内也是可以超售的，一般人理解，Innodb_buffer_pool必须小于实际物理内存，否则MySQL会启动失败。其实这是一个误区，这个不是MySQL层控制的，这个是操作系统（OS）层控制的，就是前面提到的/proc/sys/overcommit_memory控制OS是否允许“超售”。如果允许“超售”，则Innodb_buffer_pool可以远远超过实际的内存空间大小，但是这部分空间是没有使用的。

从上面的分析可以得出，MySQL OOM的起因是内存不足，内存不足主要有两个方面原因：第一是MySQL自身内存的规划。第二是一般部署MySQL的服务器，都会部署很多的监控或者定时任务脚本，而这些脚本往往缺少必要的内存限制，导致在高峰期的时候占用大量的内存，导致触发Linux OOM_Killer机制，MySQL由于持有的内存最多往往成为Kill对象。

那如何才能让MySQL摆脱被Kill的厄运呢？ MySQL被Kill的根源在于Linux超售的内存分配机制，前面也提到了，只要存在这种超售的机制，就不可能完全避免某一个应用程序被Kill的风险。那要使得MySQL一定不会被Kill掉，只能禁止操作系统超出实际内存空间的分配内存。但是前面我们也提过，对于部署了MySQL的服务器，我们不建议这么做，因为MySQL的很多内存都是刚开始申请了，并不是立即使用的，OS一旦禁止超售，这不仅对MySQL自身内存规划提出更苛刻的要求，同时也存在内存无法充分利用的问题。同时，MySQL的每个连接的私有内存是动态分配的，如果分配不到，就会直接导致服务器Crash，这样也会增加MySQL Crash的风险。
既然受限于操作系统，无法完全做到避免被Kill，那只能尽量降低MySQL被Kill的几率。我觉得至少可以做下面3个事情：

合理的规划MySQL的内存使用。

调整OOM_adj参数，将MySQL被OOM_Killer锁定的优先级降低。

加强内存的监控和报警，一旦报警，DBA应该迅速介入，Kill掉一些占用较多内存的连接。

四、    MySQL参数优化

我们首先需要了解一下MySQL是如何使用内存的。MySQL使用的内存主要有2类，一类是共享内存，一类是连接私有内存。
共享内存相关参数:

innodb_buffer_pool_size：使用过Innodb的同学都知道，这块内存是Innodb存储引擎最重要的内存，直接关系到MySQL的读写性能。与Myisam表只缓存索引，数据寄望于OS系统缓存不同，Innodb一般都会关闭OS的缓存，所有读到数据页和索引都直接存在数据库层的innodb_buffer_pool中的，这与Innodb是事务存储引擎有关，所以这个值越大MySQL性能当然也越好，但是有个前提需要讲明白，innodb_buffer_pool越大，不仅预热慢，崩溃恢复的速度也会很慢。这个值一般在保证了其他用途的内存够用的前提下，应尽可能的大。我们来看一下AWS（亚马逊云计算）不同规格的RDS该参数的设置情况:

之所以AWS的各种规格的内存这么零碎，我觉得可能AWS已经抛掉了一些它自己部署的一些程序持有的内存，所以这里就已经是MySQL实际能够使用的内存 大小了，基本上规格越低，Innodb_buffer_pool_size的比例越小，这个主要是因为每个连接的私有内存是固定的，所以对于小规格的实 例，本身内存就比较小，为了保证一定的并发连接数，所以Innodb_buffer_pool_size也相对较小，这个是合理的。我们目前RDS一刀切 75%的Innodb_buffer_pool分配显然是不合理，应该按照实际内存大小，内存越小，比例越低的原则进行设定。

innodb_additional_mem_pool_size：主要用于存放MySQL内部的数据结构和Innodb的数据字典，所以大小主要与表的 数量有关，表越多值越大。庆幸的是这个值是可变的，如果不够用的话，MySQL会向操作系统申请的。该值默认8M，AWS所有规格都是统一的2M，由于这 个值可以动态申请，所以我觉得2M应该是满足需求的。

innodb_log_buffer_size：这个是redolog的缓冲区，为了提高性能，MySQL每次写日志都将日志先写到一个内存Buffer 中，然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。目前，我们所有实例的 innodb_flush_log_at_trx_commit设置为了1，即每次事务提交都会刷新Buffer到磁盘，保证已经提交的事务，redo是 不会丢的。AWS该值也设的是1（为了保证不丢数据），这个值的大小主要影响到刷磁盘的次数，设置的过小，Buffer容易满，就会增加fsync的次 数，设置过大，占用内存。该值默认是8M，AWS所有规格统一128M，我觉得目前每次提交都会刷buffer，所以除非有大事务的情况，一般 buffer不太可能被占满，所以没必要开的很大， 8M应该是满足需求的。

key_buffer_size：myisam表的键缓存，这个只对myisam存储引擎有效，所以对于我们绝大多数使用Innodb的应用，无需关心。

query_cache_size：MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的 Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用，对于绝大多数更新较多的库可能 不是很适用，比较受限于应用场景，所以AWS也把这个缓存给关了。我觉得这个值默认应该关闭，根据需求调整。

上面这些就是MySQL主要的共享内存空间，这些空间是在MySQL启动时就分配的，但是并不是立即使用的。MySQL还有一部分内存是在用户连接请求到达时动态分配的，即每个MySQL连接都单独一个缓存，这部分缓存主要包括：

read_buffer_size & read_rnd_buffer_size这两个值主要针对的都是Myisam表引擎，用来加快随机和顺序扫描表，对于innodb表，虽然在排序的时 候，可以缓存临时文件中的索引，缓存嵌套查询的结果等作用，但是重要性已经下降很多了。这两个值前者默认128K，AWS统一256K，我们采用默认值。 后者默认256K，AWS统一512K，我们也采用默认值。

sort_buffer_size：每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL 会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以 sort_buffer_size 对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的，如果是希望扩大的话，建议在会话层设置，默 认值2M，AWS也是2M。

thread_stack：默认256K，AWS256K，MySQL为每个线程分配的堆栈。

join_buffer_size：*每个连接的每次join都分配一个，默认值128K，AWS 128K；

binlog_cache_size：类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。默认32K，AWS 32K。

tmp_table_size： 默认16M，用户内存临时表的最大值，如果临时表超过该值，MySQL就会把临时表转换为一个磁盘上mysiam表。如果用户需要做一些大表的 groupby的操作，可能需要较大的该值，由于是与连接相关的，同样建议在会话层设置。AWS 16M

网易云新用户大礼包：https://www.163yun.com/gift

本文来自网易实践者社区，经作者郭忆授权发布。