[root@BJDB-01 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=BJDB-02 --mysql-port=3306 --mysql-user=root --mysql-password=*** --mysql-db=test --table-size=100000 --tables=5 --threads=100 --db-ps-mode=disable --auto_inc=off --report-interval=3 --max-requests=0 --time=360 --percentile=95 --skip_trx=off --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 --create_secondary=off run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 100
Report intermediate results every 3 second(s)
Initializing random number generator from current time
Initializing worker threads...
FATAL: mysql_store_result() returned error 5(Out of memory (Needed 48944 bytes))
FATAL: 'thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error，errno = 5, state = 'HY000': Out of memory (Needed 48944 bytes)
FATAL: mysql_store_result() returned error 5(Out of memory (Needed 48944 bytes))
FATAL: 'thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error，errno = 5, state = 'HY000': Out of memory (Needed 48944 bytes)

······
2021-03-16T09:13:00.692622+08:00 343 [ERROR] [MY-010934] [Server] Out of memory (Needed 708628 bytes)
2021-03-16T09:13:00.692702+08:00 343 [ERROR] [MY-010934] [Server] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
2021-03-16T09:13:59.832037+08:00 374 [ERROR] [MY-000000] [InnoDB] InnoDB: Assertion failure: ut0ut.cc:678:!m_fatal
InnoDB: thread 140375101384448
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
01:13:59 UTC - mysqld got signal 6 ;
分析
对 MySQL OOM 我们一步步进行分析，首先应该查看 ulimit 限制，
[root@BJDB-02 ~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 23045
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 23045
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
复制
系统并没有对 ulimit 进行限制，100 个并发量在我们的配置之内，那就不是 ulimit 配置的问题，接下来分析下内存的使用情况，
在复现 MySQL OOM 的过程中，查看对应内存使用，通过 top 和 free 命令进行监控，得到以下信息，
[root@BJDB-02 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          16047        1958        8956         8        5132       13920
Swap:          5119           0        5119
[root@BJDB-02 ~]# top
top - 17:21:30 up 32 min,  4 users,  load average: 0.00, 0.04, 0.11
Tasks: 226 total,   2 running, 224 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.2 us,  0.6 sy,  0.0 ni, 97.2 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  16432236 total,  9164596 free,  2012156 used,   5255484 buff/cache
KiB Swap:        5242876 total,    5242876 free,        0 used.  14247508 avail Mem
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 7654 root      20   0   13.7g   1.7g  14312 S   1.3 10.3   0:00.27 mysqld
复制
这里看我们的内存使用很正常，free 和 available 都很多，swap 都没有使用，唯一存在异常的是虚拟内存有点高，我们接着分析：
进一步查看一下 /proc/meminfo，具体分析一下内存的使用情况，其中以下两个参数引起了注意：
[root@BJDB-02 ~]# cat /proc/meminfo | grep Commit
CommitLimit:     13458992 kB
Committed_AS:    13244484 kB
复制
一般来说 CommitLimit 的值是要比 Committed_AS 的值要小的，结合现在内存的使用，我们应该注意到一个 OS kernel 参数。
[root@BJDB-02 ~]# cat /etc/sysctl.conf |grep vm.overcommit_memory
vm.overcommit_memory=2
复制
将 vm.overcommit_memory 调整为 0，压测时 MySQL OOM 消失了。
这三个参数值是什么意思呢？它和内存使用的关系是什么？内存真的够用吗？通过翻看 Linux 的内核文档我们来进行详细说明。
分析 vm.overcommit_memory 的使用
首先解释下 overcommit 的意思是指操作系统承诺给进程的内存大小超过了实际可用的内存。
从内核版本 2.5.30 开始，这个参数的解释为：
overcommit_memory：This value contains a flag that enables memory overcommitment.
When this flag is 0, Heuristic overcommit handling, the kernel attempts to estimate the amount of free memory left when userspace requests more memory. Obvious overcommits of address space are refused. Used for a typical system. It ensures a seriously wild allocation fails while allowing overcommit to reduce swap usage. root is allowed to allocate slightly more memory in this mode. This is the default.
When this flag is 1, Always overcommit, the kernel pretends there is always enough memory until it actually runs out. Appropriate for some scientific applications. Classic example is code using sparse arrays and just relying on the virtual memory consisting almost entirely of zero pages.
When this flag is 2, Don't overcommit, the kernel uses a "never overcommit" policy that attempts to prevent any overcommit of memory. The total address space commit for the system is not permitted to exceed swap + a configurable amount (default is 50%) of physical RAM. Depending on the amount you use, in most situations this means a process will not be killed while accessing pages but will receive errors on memory allocation as appropriate.
中文释义：
当这个标志为 0 时，表示试探性的 overcommit，当用户空间请求更多内存时，OS kernel 会预估剩余的空闲内存量，如果内存申请特别大就会被拒绝。例如 malloc() 一次性申请的内存大小就超过了 swap 和 physical RAM 的和，就会遭到 kernel 拒绝 overcommit。
当这个标志为 1 时，kernel 会假装一直有足够的内存，直到实际用完为止。
当这个标志为 2 时，kernel 使用“永不过度提交”的策略，试图阻止任何内存的过度提交。