解决MySQL OOM（内存溢出）的问题

2022-11-12 09:12:30

内容摘要

这篇文章主要为大家详细介绍了解决MySQL OOM（内存溢出）的问题，具有一定的参考价值，可以用来参考一下。感兴趣的小伙伴，下面一起跟随512笔记的小玲来看看吧！ OOM全称"Out Of Memo

文章正文

这篇文章主要为大家详细介绍了解决MySQL OOM（内存溢出）的问题，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随数据库教程的小编来看看吧！

OOM全称"Out Of Memory"，即内存溢出。

内存溢出已经是软件开发历史上存在了近40年的“老大难”问题。在操作系统上运行各种软件时，软件所需申请的内存远远超出了物理内存所承受的大小，就叫内存溢出。

内存溢出产生原因多种多样，当内存严重不足时，内核有两种选择：

直接panic
杀掉部分进程，释放一些内核。

大部分情况下，会杀掉导致OOM的进程，然后系统恢复。通常我们会添加对内存的监控报警，例如：当memory或swap使用超过90%时，触发报警通知，需要及时介入排查。

如果已经出现OOM，则可以通过dmesg命令查看，CentOS7版本以上支持 -T选项，能将时间戳转成时间格式，方便查看具体时间：

代码如下：


[root@localhost ~]# free -m    total  used  free  shared buffers  cachedMem:  128937  128527  409   1  166  1279-/+ buffers/cache:  127081  1855Swap:  16383  16252  131

MySQL OOM（内存溢出）的解决思路

通过日志可以看出哪些进程、占用多少内存等信息，并会Kill掉占用内存较大的进程。

内存问题的排查思路

一、操作系统内存检查

已MySQL为例，OOM后，mysqld进程被Killed，内存会被释放。mysqld_safe安全进程会将mysqld拉起，此时查看到的系统内存会是一个正常值。如果内存使用很高，但还未OOM，系统内存使用情况可能为下面情况：

代码如下：


[root@localhost ~]# free -m    total  used  free  shared buffers  cachedMem:  128937  128527  409   1  166  1279-/+ buffers/cache:  127081  1855Swap:  16383  16252  131

MySQL OOM（内存溢出）的解决思路

可以看出此时的内存使用已经很高了，物理内存和swap虚拟内存几乎都被用完，buffers和cached也不多，随时可能出现OOM的情况。

首先，通过top命名查看占用内存最大的进程：

shift+o可以选择排序方式，n代表%MEM。

代码如下：


[root@localhost ~]# topMem: 132031556k total, 131418864k used, 612692k free, 212104k buffersSwap: 16777212k total,  0k used, 16777212k free, 14648144k cached
 PID USER  PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND14920 mysql  20 0 125g 109g 6164 S 6.6 87.0 27357:08 mysqld

MySQL OOM（内存溢出）的解决思路

可以看出mysqld进程占用内存最大，也可以这样查：

代码如下：


[root@localhost ~]# ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | grep -E 'PID|mysql' |grep -v grep PID COMMAND   COMMAND      %CPU RSZ VSZ STIME USER  UID25339 mysqld   /export/servers/mysql/bin/m 9.4 115001324 130738976 2017 mysql 50032070 mysqld_safe  /bin/sh /export/servers/mys 0.0 296 106308 2017 root   0

MySQL OOM（内存溢出）的解决思路

RSZ为进程占用私有内存大小，单位Kb。VSZ为映射的虚拟内存大小，单位Kb。

通过RSZ/total 也可以算出占用总内存比例。

二、查看给mysql分配的内存

mysql内部主要内存可通过下面语句查出：

代码如下：


MYSQL >SET @giga_bytes = 1024*1024*1024;SELECT (@@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + (select count(HOST) from information_schema.processlist)/*@@max_connections*/*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)) / @giga_bytes AS MAX_MEMORY_GB;

MySQL OOM（内存溢出）的解决思路

每个参数配置大小：

代码如下：


*************************** 1. row ***************************    @@key_buffer_size: 67108864    @@query_cache_size: 0     @@tmp_table_size: 268435456  @@innodb_buffer_pool_size: 38654705664@@innodb_additional_mem_pool_size: 134217728   @@innodb_log_buffer_size: 8388608    @@max_connections: 3000    @@read_buffer_size: 4194304   @@read_rnd_buffer_size: 4194304    @@sort_buffer_size: 2097152    @@join_buffer_size: 2097152    @@binlog_cache_size: 32768     @@thread_stack: 262144

MySQL OOM（内存溢出）的解决思路

每个参数配置说明：

innodb_buffer_pool_size	占用内存最大的参数
innodb_additional_mem_pool_size	额外内存，mysql5.7以后移除
innodb_log_buffer_size	重做日志缓存大小
key_buffer_size	只用于MyISAM引擎，不需要太大
tmp_table_size‍	临时表缓存大小
query_cache_size	查询缓存，建议关闭
max_connections	最大连接数
read_buffer_size read_rnd_buffer_size sort_buffer_size join_buffer_size binlog_cache_size thread_stack	这些参数都跟线程有关，所占内存为这些参数的和*最大连接数。连接数越多占用内存也就越多，建议不超过512K，binlog_cache_size采用系统默认32K，thread_stack默认256K即可

需要给mysql分配多大内存，直接跟以上参数有关。太大会导致内存不足，太小会影响性能，如何分配合理值，还需根据业务情况来定。但业务场景较多，每个业务配置都不一样，会造成运维成本较高。所以能定制出一套适用于绝大多数场景的配置模板就可以了。

1、如果mysql分配的内存比系统内存大

比如系统内存128G，mysql分配的内存已经大于128G，但是系统本身和其它程序也需要内存，甚至mysqldump同样需要内存，所以这样就很容易造成系统内存不足，从而导致OOM。这时我们要查出哪些参数设置比较大，适当降低内存分配。

innodb_buffer_pool在mysql中占有最大内存，将innodb_buffer_pool_size调小可以有效降低OOM问题。但如果设置太小会导致内存刷脏页频率增加，IO增多，从而降低性能。通常我们认为innodb_buffer_pool_size为系统内存的60%~75%最优。

查看buffer_pool的使用情况：

代码如下：


MYSQL >select POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG from information_schema.INNODB_BUFFER_POOL_STATS;+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+| POOL_ID | POOL_SIZE | FREE_BUFFERS | DATABASE_PAGES | OLD_DATABASE_PAGES | MODIFIED_DATABASE_PAGES | PAGES_MADE_YOUNG | PAGES_NOT_MADE_YOUNG |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+|  0 | 611669 |   1024 |   610644 |    225393 |      0 |   309881 |     0 ||  1 | 611669 |   1024 |   610645 |    225393 |      0 |   309816 |     0 ||  2 | 611669 |   1024 |   610645 |    225393 |      0 |   309756 |     0 |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+

MySQL OOM（内存溢出）的解决思路

可以看出buffer_pool分了3个实例，POOL_SIZE为每个实例大小，这里为页个数，我们知道mysql页的默认大小为16K，所以单个实例的真正大小为611669*16K，5.6以后要求FREE_BUFFERS至少保留1024个页，少于1024个页时会强制刷脏数据，后面的值可以看出脏页情况。另外如果PAGES_MADE_YOUNG远大于PAGES_NOT_MADE_YOUNG页数，那么此时内存使用就可能比较大，可以适当降低innodb_buffer_pool_size大小。

另一篇文章也有对buffer_pool的介绍：一条命令解读InnoDB存储引擎—show engine innodb status

如果innodb_buffer_pool_size不是很大，但内存还是很高，也可能是由于并发线程太多导致，需要确认是不是应用异常，还是需要调整max_connections最大连接。如果连接太多，每个连接也会占用独立的内存，read、sort、join缓存都是session级别，连接越多需要内存就越多，所以这些参数也不能设置太大。

需要注意的是一些参数不支持动态修改，只能先修改配置文件然后重启mysql才能生效，所以在mysql启动之前，一定要把参数值确认好。

2、如果mysql分配的内存比系统内存小

如果mysql参数设置都比较合理，但是仍然出现oom，那么可能是由于mysql在系统层面所需内存不足导致，因为mysql读取表时，如果同时有多个session引用一个表则会创建多个表对象，这样虽然减少了内部表锁的争用，但是会加大内存使用量。

首先，可以通过lsof -p pid查看进程打开的系统文件数，pid为mysqld的进程号。

代码如下：


[root@localhost ~]# ps -ef | grep mysqld[root@localhost ~]# lsof -p 3455COMMAND PID USER FD TYPE DEVICE SIZE/OFF  NODE NAMEmysqld 30012 mysql cwd DIR  8,3  12288 58982404 /mysql/datamysqld 30012 mysql mem REG  8,1  599392 272082 /lib64/libm-2.12.somysqld 30012 mysql mem REG  8,1  91096 272089 /lib64/libz.so.1.2.3mysqld 30012 mysql mem REG  8,1  93320 272083 /lib64/libgcc_s-4.4.7-20120601.so.1mysqld 30012 mysql mem REG  8,1  43392 272095 /lib64/libcrypt-2.12.somysqld 30012 mysql 10uW REG  8,3 536870912 59015176 /mysql/data/ib_logfile0mysqld 30012 mysql 11uW REG  8,3 536870912 59015177 /mysql/data/ib_logfile1mysqld 30012 mysql 12uW REG  8,3 536870912 59015178 /mysql/data/ib_logfile2mysqld 30012 mysql 13uW REG  8,3 675282944 59001816 /mysql/data/test/table6.ibdmysqld 30012 mysql 14uW REG  8,3 2155872256 58985613 /mysql/data/test/table487.ibdmysqld 30012 mysql 15u REG  8,3   0 58982414 /mysql/tmp/ibhNDzPM (deleted)mysqld 30012 mysql 16uW REG  8,3 2306867200 58983861 /mysql/data/test/table327.ibdmysqld 30012 mysql 17uW REG  8,3 4169138176 58985467 /mysql/data/test/table615.ibdmysqld 30012 mysql 18uW REG  8,3 79691776 59020641 /mysql/data/test/table_v199_20170920.ibdmysqld 30012 mysql 19uW REG  8,3 67108864 59015043 /mysql/data/test/table_v39_20170920.ibdmysqld 30012 mysql 20uW REG  8,3 75497472 59014992 /mysql/data/test/table_v7_20170920.ibdmysqld 30012 mysql 21uW REG  8,3 83886080 59019735 /mysql/data/test/table_v167_20170920.ibdmysqld 30012 mysql 22uW REG  8,3 1367343104 58997684 /mysql/data/popfin6/table_uuid6.ibdmysqld 30012 mysql 23uW REG  8,3 1275068416 58984491 /mysql/data/test/table_uuid7.ibd...[root@localhost ~]# lsof -p 3455 |grep ibd|wc -l54869

MySQL OOM（内存溢出）的解决思路

查看mysql服务打开文件数限制：

代码如下：


MySQL >show global variables like 'open_files_limit';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 65535 |+------------------+-------+

MySQL OOM（内存溢出）的解决思路

查看操作系统打开文件数限制：

代码如下：


[root@localhost ~]# ulimit -amax memory size   (kbytes, -m) unlimitedopen files      (-n) 65535

MySQL OOM（内存溢出）的解决思路

如果此时打开的文件很多，内存也会占用很多。

其次，还需看一下table_open_cache，当打开一个表后会把这个表的文件描述符缓存下来。

代码如下：


MYSQL >show global variables like 'table_open_cache';+------------------+-------+| Variable_name | Value |+------------------+-------+| table_open_cache | 16384 |+------------------+-------+MYSQL >show global status like '%open%tables%';+------------------------+--------+| Variable_name   | Value |+------------------------+--------+| Open_tables   | 16384 || Opened_tables   | 401374 |+------------------------+--------+

MySQL OOM（内存溢出）的解决思路

通过以上两个值来判断 table_open_cache 是否到达瓶颈。当缓存中的值open_tables 临近到了 table_open_cache 值的时候，说明表缓存池快要满了，但Opened_tables 还在一直有新的增长，这说明还有很多未被缓存的表。

用show open tables from schema命令，可以查看table_open_cache中缓存的表，重复打开的表仅显示一个

代码如下：


MYSQL >show open tables from sysbenchtest;+--------------+----------+--------+-------------+| Database  | Table | In_use | Name_locked |+--------------+----------+--------+-------------+| sysbenchtest | sbtest1 |  1 |   0 || sysbenchtest | sbtest2 |  0 |   0 || sysbenchtest | sbtest3 |  0 |   0 || sysbenchtest | sbtest4 |  0 |   0 || sysbenchtest | sbtest5 |  0 |   0 |

MySQL OOM（内存溢出）的解决思路

In_use显示当前正在使用此表的线程数，如果大于0也意味着此表被锁。

Name_locked只适用于DROP和RENAME，在执行DROP或RENAME时，table_open_cache中的表文件描述符会被移除，所以不会看到除0以外的其他值。

一般在库表比较多的情况下（分库分表）很容易出现内存占用较大的情况。如果要解决根源，还是需要对库表进行拆分。

3、MYSQL内部其他内存

information_schema下的表都使用的都是MEMORY存储引擎，数据只在内存中保留，启动时加载，关闭后释放。

查看除系统库外是否有MEMORY引擎表：

代码如下：


MySQL >select * from information_schema.tables where engine='MEMORY' and TABLE_SCHEMA !='information_schema';

MySQL OOM（内存溢出）的解决思路

如果业务有使用MEMORY存储引擎的，尽量改成innodb引擎。

4、MYSQL事件内存指标

从MySQL5.7开始，在performance_schema中会记录内存分配。

查看哪些指标启动了内存收集功能：

代码如下：


MySQL >select * from performance_schema.setup_instruments where NAME LIKE 'memory/%';

MySQL OOM（内存溢出）的解决思路

启动需要收集内存的指标：

代码如下：


MySQL >UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

MySQL OOM（内存溢出）的解决思路

指标的内存收集结果会汇总到到sys库下的视图中：

代码如下：


MySQL root@[sys]>show tables like 'memory%';+-----------------------------------+| Tables_in_sys (memory%)   |+-----------------------------------+| memory_by_host_by_current_bytes || memory_by_thread_by_current_bytes || memory_by_user_by_current_bytes || memory_global_by_current_bytes || memory_global_total    |+-----------------------------------+

MySQL OOM（内存溢出）的解决思路

这些视图总结了内存使用情况，按事件类型分组，默认降序排列：

代码如下：


MySQL >select event_name,current_count,current_alloc,high_alloc from sys.memory_global_by_current_bytes where current_count > 0;+--------------------------------------------------------------------------------+---------------+---------------+-------------+| event_name                  | current_count | current_alloc | high_alloc |+--------------------------------------------------------------------------------+---------------+---------------+-------------+| memory/performance_schema/table_handles          |   10 | 90.62 MiB  | 90.62 MiB || memory/performance_schema/events_statements_summary_by_thread_by_event_name |    3 | 26.01 MiB  | 26.01 MiB || memory/performance_schema/memory_summary_by_thread_by_event_name    |    3 | 16.88 MiB  | 16.88 MiB || memory/performance_schema/events_statements_history_long      |    1 | 13.66 MiB  | 13.66 MiB || memory/performance_schema/events_statements_history       |    3 | 10.49 MiB  | 10.49 MiB || memory/performance_schema/events_statements_current       |    3 | 10.49 MiB  | 10.49 MiB |...

MySQL OOM（内存溢出）的解决思路

总结：

通过以上排查能大体知道哪些占用内存较多，针对内存占用较多的地方再做具体优化。正像文章开头所说的，内存溢出已经是软件开发历史上存在了近40年的“老大难”问题，更何况数据库环境更加复杂，SQL语法、数据类型、数据大小等这些因素都与内存有关，所以在设计使用上更要多想内存溢出问题。

以上就是MySQL OOM（内存溢出）的解决思路的详细内容，更多关于MySQL OOM（内存溢出）的解决的资料请关注512笔记其它相关文章！

注：关于解决MySQL OOM（内存溢出）的问题的内容就先介绍到这里，更多相关文章的可以留意

代码注释

上一篇 MySQL编码不一致可能引起的一些问题解决办法下一篇 MySQL ddl语句用法详细说明

作者：喵哥笔记

学的不仅是技术，更是梦想！