性能分析--数据库--MySQL--内存泄露分析
随着数据量的增加，MySQL服务的OOM问题也愈加频繁。这不禁引发一个疑问：MySQL内存究竟去了哪里？常规的内存分析方法难以定位问题根源。因此，亟需新的方法查因。
本文，提炼了一种内存分析方法，不但适用于MySQL内存分析，也适用于PostgreSQL、Java等进程的内存分析。感兴趣就往下看吧！
1 问题与现象
随着客户应用的不断增多，MySQL服务经常出现Out of Memory的问题，数据库服务每隔9个小时就会OOM，被操作系统杀死。
在尝试过常见的参数优化和SQL调优手段后，症状会有所缓解，重启的间隔时间变为2-3天重启一次，但内存泄漏仍然存在，无法定位问题根源，因此，不得不寻找新的方法来查因。
OOM问题频现
2 前提条件
2.1 MySQL内存增量由执行的SQL使用
由于SQL调用是操作数据的唯一途径，如果没有SQL执行，则MySQL进程的内存使用量应保持稳定。
因此，根据这个前提，内存增量可以通过分析SQL来确定调用者的业务。
2.2 MySQL内存中innodb_buffer_pool保持用量稳定
innodb_buffer_pool保留了innodb存储引擎的数据缓存（数据页）、索引缓存、脏页缓冲、内部数据（哈希索引等），是占用MySQL进程内存的最大部分。
根据持续的内存观察，我们发现这部分内存使用量保持稳定，LRU(Least Recently Used)淘汰算法运行正常，其最终物理内存使用量不会超过innodb_buffer_pool_size分配的值。
innodb_buffer_pool内存分析
如上图所示，innodb_buffer_pool_size=23092724 KB，这些虚拟内存在mysqld进程启动时就已经分配好，那么其物理内存使用量最大也不会超过虚拟内存23092724 KB。
因此，根据上述观察现象，内存泄漏分析需要关注Server层内存消耗。
2.3 MySQL总内存耗用量计算公式
除了innodb_buffer_pool外，MySQL Server也会耗用很多内存，理论总耗用量计算公式如下：
select ifnull(@@key_buffer_size,0) + ifnull(@@tmp_table_size,0)
+ ifnull(@@innodb_buffer_pool_size,0)+ifnull(@@innodb_log_buffer_size,0)
+ ifnull(@@max_connections,0) *(
ifnull(@@read_buffer_size,0) + ifnull(@@read_rnd_buffer_size,0)
+ ifnull(@@sort_buffer_size,0) + ifnull(@@join_buffer_size,0)
+ ifnull(@@binlog_cache_size,0) + ifnull(@@thread_stack,0))
以上公式中，理论总内存耗用依次为：MyISAM存储引擎占用量、内部临时表、innodb_buffer_pool、redolog缓冲，再加上连接相关的内存占用：表扫描、无序读、排序、JOIN、binlog缓冲、线程栈。
另外，再加上Engine=Memory临时表耗用、performance_schema内存占用等，一般来说，performance_schema会占用内存几百MB。
根据这个计算公式，如果连接没有激增，那么计算得到的理论内存总耗用应该可以控制在整机物理内存之下。
3 常规内存分析方法
3.1 performance_schema分析
建立时间序列表，跟踪每个事件的内存分配的总量，相关代码如下：
select now(),event_name,sum(tt.USED) from (
select event_name
,(SUM_NUMBER_OF_BYTES_ALLOC*1.0 -SUM_NUMBER_OF_BYTES_FREE*1.0) AS USED
from performance_schema.memory_summary_global_by_event_name
) tt
group by event_name;
经过跟踪发现，PFS（performance_schema）合计的内存用量与mysqld进程的虚拟内存耗用量并不一致。因此，通过PFS判断内存泄漏问题时，数据不准，无法准确定位。
但是，通过跟踪事件的时间序列的内存占用量，可以判断某类事件的内存增量趋势，如下图所示：
某个事件的内存增量趋势
3.2 系统调用分析
内存分配会调用内核方法进行处理，因此可通过strace来查看内存的系统调用，同时也可以明确是否存在内存不释放的问题。相关代码如下：
# strace -tt -T -f -e "brk,mmap,munmap" -p `pidof mysqld`
经过分析，MySQL进程申请的内存会正常释放一部分，但是从长期累积的过程看，内存又存在一个缓慢泄漏的过程，因此无法从一个长时间的序列中发现具体哪一块内存没有及时释放。
需要注意的是，MySQL代码中申请内存的方法是malloc，超过128KB的内存则需要系统调用mmap，较小块内存的申请需要系统调用brk，释放内存的系统调用是munmap。
系统调用分析
例如，上图的内存地址0x7ef584000000，申请了64MB的内存，但是仅仅存在了2ms就被释放了。
4 内存dump分析
由于以上常规分析方法很难确定内存的内容，甚至连分析的方向都无法明确，因此内存core dump分析方法就成了最后的手段，但是修改MySQL参数core_file或者kill 11方法均会导致进程重启，不利于生产环境的持续运行。
因此，我们提炼出如下的内存分析方法，这个方法不但适用于MySQL内存分析，也适用于PostgreSQL、Java等进程的内存分析。
4.1 内存core dump与处理
步骤一：生成core dump与内存地址，相关命令如下：
# gcore -a -o dump文件名 `pidof mysqld` && pmap -x `pidof mysqld` > my.log
此处pmap的作用是记录虚拟内存地址与内存块的大小，相当于是提供了一张地图，只有根据地图上记录的虚拟内存地址和块大小才能正常通过gdb导出内存。否则，一个进程的虚拟内存地址空间是128T，磁盘根本无法承受。
步骤二：提取重点关注的内存地址，输出到内存段文件heap.log，相关命令如下：
# grep 000 my.log | grep -v mysqld | awk '{if ($2>10000 && $3>10000 && $2<175189) print $1 " " $2}' >heap.log
这里我们关注的内存段要存在物理内存耗用，否则只能看到一个空文件。
步骤三：遍历内存段文件，将内存段导出并生成文本日志，相关命令如下：
#cat heap.log | while read ads size; do gdb -q --batch --core=dump文件名
-ex="dump memory $ads.bin 0x$ads 0x$ads+$size*1024";strings $ads.bin>$ads.log;done;
导出内存段并生成文本日志
如上图所示，00007ef264000000.bin就是地址0x00007ef264000000中的内存内容，大小为67084288 B，00007ef264000000.log就是这段地址的内存文本日志，这部分日志是可以人工阅读的，是后续分析的重点。
步骤四：遍历内存文本日志，统计各类SQL出现的频率。通常而言，频率异常高的SQL就是我们业务分析的重点。
步骤五：分析重点SQL所在原始内存文件，评估每个SQL的内存占用量。
4.2 客户分析案例
1）SQL频率分析
我们在MySQL出现OOM之前分别进行了三次内存dump，分别整理出如下出现频率较高的SQL：
日常报销
SELECT b.fentrycostcompanyid, a.FAUDITDATE FROM t_er_dailyreimbursebill AS a INNER JOIN t_er_expensedetail AS b
ON a.fid=b.fid WHERE (b.FDetailID IN (SELECT fitementryid FROM t_er_invoiceitem WHERE finvoiceheadentryid
IN (SELECT FEntryID FROM t_er_invoiceinfo WHERE fserialno=?)) AND (a.FAUDITDATE >= ?))
ORDER BY a.FAUDITDATE DESC, b.fseq ASC
LIMIT 0, 1
人员查询
SELECT C.FId AS "INNER_ID"
FROM t_SEC_User AS C INNER JOIN t_SEC_User_L AS B ON (((B.FId=C.FId AND B.FLocaleId='zh_CN') AND 1=?) AND B.ftruename=?)
WHERE 1=?
应付单查询
SELECT a.forgid, b.fk_cico_responsibleunit, a.FAUDITDATE, e.fisexpensealloc, a.fk_cico_checkboxfield
FROM t_ap_finapbill AS a INNER JOIN t_ap_finapbill_e AS e ON a.fid=e.fid
INNER JOIN t_ap_finapbilldetailentry AS b ON a.fid=b.fid
WHERE (fk_cico_entry_invoiceno IN (SELECT fk_cico_invoiceno FROM tk_cico_finapinvoicentity WHERE fk_cico_serialno=?)
AND (a.FAUDITDATE >= ?))
ORDER BY a.FAUDITDATE DESC, b.fseq ASC
LIMIT 0, 1
差旅报销
SELECT b.fentrycostcompanyid, a.FAUDITDATE
FROM T_ER_ReimburseBill AS a INNER JOIN T_ER_ReimburseTripEntry AS b ON a.fid=b.fid
INNER JOIN T_ER_ReimburseEntry AS c ON b.FEntryID=c.FEntryID
WHERE (c.FDetailID
IN (SELECT fitementryid FROM t_er_invoiceitem WHERE finvoiceheadentryid
IN (SELECT FEntryID FROM t_er_invoiceinfo WHERE fserialno=?)
) AND (a.FAUDITDATE >= ?))
ORDER BY a.FAUDITDATE DESC, b.fseq ASC
LIMIT 0, 1
根据以上SQL语句，统计相应的行数，结果如下：
高频率SQL语句行数统计
注：VSZ（Virtual Memory Size），进程占用的虚拟内存的大小，也即进程能够访问到的内存空间大小（实际可能尚未在物理内存中分配）。
虽然存在很多其他类型的SQL语句，但上述4条SQL语句的出现频率最高，因此上述四条SQL语句就是我们分析的重点。
2）重点SQL分析
日常报销
日常报销的内存耗用主要为SQL解析、子查询、中间结果的缓存、查询结果等，建议降低调用频率，优化查询计划。其内存如下图所示：
日常报销的内存耗用分析
人员查询
人员查询的SQL语句中，t_SEC_User_L存在全表扫描，建议在字段FID，ftruename，FLocaleId建立组合索引。
应付单查询
应付单查询的SQL语句中，t_ap_finapbilldetailentry. fk_cico_entry_invoiceno为Text字段，无法使用索引，且此类型字段无法在innodb_buffer_pool中存储，完全在Server中处理，建议将字段类型修改为varchar(50)。
差旅报销
差旅报销的内存耗用主要为SQL解析、子查询、中间结果的缓存、查询结果等，处理方式同日常报销。
综上，针对上述四类SQL语句，相应的优化措施如下：
优化措施
以上即为内存dump分析方法的全过程，后续我们会基于这个方法分享更多的性能案例，敬请关注~
5 划重点
1. 本文提出的内存dump分析方法，不但可以用于分析MySQL内存占用，也可以分析PostgreSQL、Java进程的内存耗用，尤其是对于Java进程的堆外内存分析。
2. 内存dump分析方法的主要操作步骤如下：
#往期推荐#
更多精彩内容，“码”上了解！↓
性能分析--数据库--MySQL--内存泄露分析
本文2024-09-23 01:12:15发表“云苍穹知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-cangqiong-144437.html
