最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

MySQL OOM(内存溢出)的排查思路及优化方法

XAMPP下载 中文小张 115浏览 0评论

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

  1. 直接panic
  2. 杀掉部分进程,释放一些内核。

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

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

[root@localhost ~]# dmesg -T[Tue Mar 26 10:23:41 2019] memory: usage 25164884kB, limit 25165824kB, failcnt 172713[Tue Mar 26 10:23:41 2019] memory+swap: usage 25165824kB, limit 25165824kB, failcnt 6632[Tue Mar 26 10:23:41 2019] kmem: usage 0kB, limit 9007199254740988kB, failcnt 0[Tue Mar 26 10:23:41 2019] Memory cgroup stats for host: cache:4184KB rss:25160700KB rss_huge:0KB mapped_file:1968KB swap:940KB inactive_anon:1744836KB active_anon:23417824KB inactive_file:76KB active_file:1112KB unevictable:0KB[Tue Mar 26 10:23:41 2019] [ pid ] uid  tgid total_vm      rss nr_ptes swapents oom_score_adj name[Tue Mar 26 10:23:41 2019] [12910] 997 12910 16489842  6282421   28321        0           809 mysqld[Tue Mar 26 10:23:41 2019] Memory cgroup out of memory: Kill process 548216 (ParalInputsProc) score 1812 or sacrifice child[Tue Mar 26 10:23:41 2019] Killed process 12910 (mysqld) total-vm:65959368kB, anon-rss:25129684kB, file-rss:0kB, shmem-rss:0kB

通过日志可以看出哪些进程、占用多少内存等信息,并会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

可以看出此时的内存使用已经很高了,物理内存和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 

可以看出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

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;

每个参数配置大小:

*************************** 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

每个参数配置说明:

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 |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+

可以看出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服务打开文件数限制:

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

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

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

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

其次,还需看一下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 |+------------------------+--------+

通过以上两个值来判断 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 |

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';

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

4、MYSQL事件内存指标

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

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

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

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

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

指标的内存收集结果会汇总到到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 >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   |...

总结:

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

转载请注明:XAMPP中文组官网 » MySQL OOM(内存溢出)的排查思路及优化方法