Percona Server 8.0内存使用分析

DBA入坑指南 2021-04-18

1918

点击上方蓝字关注我们

一、背景

上周收到CVM内存使用超过85%的告警，上面运行Percona Server 8.0，详细版本为8.0.19，存储引擎为TokuDB，CVM配置为2c 4G，收到这个告警觉得还是有点问题的，因为笔者设置my.cnf的配置不太可能会超过85%这个阈值，InnoDB buffer设置了10MB，TokuDB Cache也只设置了512MB，其他的session级变量设置也在合理范围内，归档任务一跑起来，内存占用就会上涨比较快，内存使用监控图如下所示

二、原因分析

1、查看mysqld进程的内存使用情况

# pmap -d `pidof mysqld`
19725:   opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/data/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/mysql_error.log --pid-file=/data/mysql/data/mysql_run.pid --socket=/tmp/mysql.sock --port=3306
Address           Kbytes Mode  Offset           Device    Mapping
0000000000400000   52216 r-x-- 0000000000000000 0fd:00001 mysqld
00000000036fe000    1372 r---- 00000000032fd000 0fd:00001 mysqld
0000000003855000    3596 rw--- 0000000003454000 0fd:00001 mysqld
0000000003bd8000    1304 rw--- 0000000000000000 000:00000   [ anon ]
00007f879b800000 4456448 rw--- 0000000000000000 000:00000   [ anon ]
00007f88abbf6000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88abbf7000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88ac3f7000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88ac3f8000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88acbf8000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88acbf9000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88ad3f9000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88ad3fa000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88adbfa000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88adbfb000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88ae3fb000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88ae3fc000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88aebfc000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88aebfd000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88af3fd000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88af3fe000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88afbfe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88afbff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b03ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b0400000   53248 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b3bf3000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b3bf4000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b43f4000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b43f5000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b4bf5000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b4bf6000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b53f6000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b53f7000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b5bf7000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b5bf8000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b63f8000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b63f9000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b6bf9000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b6bfa000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b73fa000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b73fb000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b7bfb000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b7bfc000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b83fc000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b83fd000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b8bfd000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b8bfe000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b93fe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b93ff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88b9bff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88b9c00000  118784 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c13fc000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c13fd000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c1bfd000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c1bfe000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c23fe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c23ff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c2bff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c2c00000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c3bf8000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c3bf9000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c43f9000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c43fa000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c4bfa000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c4bfb000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c53fb000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c53fc000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c5bfc000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c5bfd000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c63fd000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c63fe000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c6bfe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c6bff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c73ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c7400000   20480 rw--- 0000000000000000 000:00000   [ anon ]
00007f88c8bff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88c8c00000   69632 rw--- 0000000000000000 000:00000   [ anon ]
00007f88cd3fb000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88cd3fc000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88cdbfc000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88cdbfd000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88ce3fd000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88ce3fe000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88cebfe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88cebff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88cf3ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88cf400000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d03ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d0400000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d13ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d1400000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d207d000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d207e000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d23ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d2400000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d3075000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d3076000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d30f7000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d30f8000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d3179000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d317a000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d31fb000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d31fc000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d327d000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d327e000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d33ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d3400000   12288 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d407c000    1024 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d417c000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d417d000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d42fe000    1024 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d43fe000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d43ff000    8192 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d4bff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d4c00000   16384 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d5c3e000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d5c3f000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d5cc0000    1024 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d5dc0000   14592 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d6c3f000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d6c40000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d6cc1000    1024 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d6dc1000     236 r-x-- 0000000000000000 0fd:00001 tokudb_backup.so
00007f88d6dfc000    2048 ----- 000000000003b000 0fd:00001 tokudb_backup.so
00007f88d6ffc000       4 r---- 000000000003b000 0fd:00001 tokudb_backup.so
00007f88d6ffd000       8 rw--- 000000000003c000 0fd:00001 tokudb_backup.so
00007f88d6fff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d7000000   32768 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d9022000    1836 r-x-- 0000000000000000 0fd:00001 ha_tokudb.so
00007f88d91ed000    2044 ----- 00000000001cb000 0fd:00001 ha_tokudb.so
00007f88d93ec000      12 r---- 00000000001ca000 0fd:00001 ha_tokudb.so
00007f88d93ef000      28 rw--- 00000000001cd000 0fd:00001 ha_tokudb.so
00007f88d93f6000      36 rw--- 0000000000000000 000:00000   [ anon ]
00007f88d93ff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88d9400000   16384 rw--- 0000000000000000 000:00000   [ anon ]
00007f88da475000    1024 rw--- 0000000000000000 000:00000   [ anon ]
00007f88da575000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88da576000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88da5f7000      32 r-x-- 0000000000000000 0fd:00001 libnss_sss.so.2
00007f88da5ff000    2044 ----- 0000000000008000 0fd:00001 libnss_sss.so.2
00007f88da7fe000       4 r---- 0000000000007000 0fd:00001 libnss_sss.so.2
00007f88da7ff000       4 rw--- 0000000000008000 0fd:00001 libnss_sss.so.2
00007f88da800000    4096 rw--- 0000000000000000 000:00000   [ anon ]
00007f88dac66000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88dac67000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88dace8000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88dace9000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88dad6a000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88dad6b000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88dadec000      48 r-x-- 0000000000000000 0fd:00001 libnss_files-2.17.so
00007f88dadf8000    2044 ----- 000000000000c000 0fd:00001 libnss_files-2.17.so
00007f88daff7000       4 r---- 000000000000b000 0fd:00001 libnss_files-2.17.so
00007f88daff8000       4 rw--- 000000000000c000 0fd:00001 libnss_files-2.17.so
00007f88daff9000      24 rw--- 0000000000000000 000:00000   [ anon ]
00007f88dafff000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88db000000  339968 rw--- 0000000000000000 000:00000   [ anon ]
00007f88efc33000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88efc34000     512 rw--- 0000000000000000 000:00000   [ anon ]
00007f88efcb4000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88efcb5000     516 rw--- 0000000000000000 000:00000   [ anon ]
00007f88efd36000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88efd37000     512 rw--- 0000000000000000 000:00000   [ anon ]
00007f88efdb7000     384 r-x-- 0000000000000000 0fd:00001 libpcre.so.1.2.0
00007f88efe17000    2048 ----- 0000000000060000 0fd:00001 libpcre.so.1.2.0
00007f88f0017000       4 r---- 0000000000060000 0fd:00001 libpcre.so.1.2.0
00007f88f0018000       4 rw--- 0000000000061000 0fd:00001 libpcre.so.1.2.0
00007f88f0019000     144 r-x-- 0000000000000000 0fd:00001 libselinux.so.1
00007f88f003d000    2044 ----- 0000000000024000 0fd:00001 libselinux.so.1
00007f88f023c000       4 r---- 0000000000023000 0fd:00001 libselinux.so.1
00007f88f023d000       4 rw--- 0000000000024000 0fd:00001 libselinux.so.1
00007f88f023e000       8 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f0240000      88 r-x-- 0000000000000000 0fd:00001 libresolv-2.17.so
00007f88f0256000    2044 ----- 0000000000016000 0fd:00001 libresolv-2.17.so
00007f88f0455000       4 r---- 0000000000015000 0fd:00001 libresolv-2.17.so
00007f88f0456000       4 rw--- 0000000000016000 0fd:00001 libresolv-2.17.so
00007f88f0457000       8 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f0459000      12 r-x-- 0000000000000000 0fd:00001 libkeyutils.so.1.5
00007f88f045c000    2044 ----- 0000000000003000 0fd:00001 libkeyutils.so.1.5
00007f88f065b000       4 r---- 0000000000002000 0fd:00001 libkeyutils.so.1.5
00007f88f065c000       4 rw--- 0000000000003000 0fd:00001 libkeyutils.so.1.5
00007f88f065d000      56 r-x-- 0000000000000000 0fd:00001 libkrb5support.so.0.1
00007f88f066b000    2048 ----- 000000000000e000 0fd:00001 libkrb5support.so.0.1
00007f88f086b000       4 r---- 000000000000e000 0fd:00001 libkrb5support.so.0.1
00007f88f086c000       4 rw--- 000000000000f000 0fd:00001 libkrb5support.so.0.1
00007f88f086d000     196 r-x-- 0000000000000000 0fd:00001 libk5crypto.so.3.1
00007f88f089e000    2044 ----- 0000000000031000 0fd:00001 libk5crypto.so.3.1
00007f88f0a9d000       8 r---- 0000000000030000 0fd:00001 libk5crypto.so.3.1
00007f88f0a9f000       4 rw--- 0000000000032000 0fd:00001 libk5crypto.so.3.1
00007f88f0aa0000      12 r-x-- 0000000000000000 0fd:00001 libcom_err.so.2.1
00007f88f0aa3000    2044 ----- 0000000000003000 0fd:00001 libcom_err.so.2.1
00007f88f0ca2000       4 r---- 0000000000002000 0fd:00001 libcom_err.so.2.1
00007f88f0ca3000       4 rw--- 0000000000003000 0fd:00001 libcom_err.so.2.1
00007f88f0ca4000     868 r-x-- 0000000000000000 0fd:00001 libkrb5.so.3.3
00007f88f0d7d000    2044 ----- 00000000000d9000 0fd:00001 libkrb5.so.3.3
00007f88f0f7c000      56 r---- 00000000000d8000 0fd:00001 libkrb5.so.3.3
00007f88f0f8a000      12 rw--- 00000000000e6000 0fd:00001 libkrb5.so.3.3
00007f88f0f8d000     296 r-x-- 0000000000000000 0fd:00001 libgssapi_krb5.so.2.2
00007f88f0fd7000    2048 ----- 000000000004a000 0fd:00001 libgssapi_krb5.so.2.2
00007f88f11d7000       4 r---- 000000000004a000 0fd:00001 libgssapi_krb5.so.2.2
00007f88f11d8000       8 rw--- 000000000004b000 0fd:00001 libgssapi_krb5.so.2.2
00007f88f11da000       8 r-x-- 0000000000000000 0fd:00001 libfreebl3.so
00007f88f11dc000    2044 ----- 0000000000002000 0fd:00001 libfreebl3.so
00007f88f13db000       4 r---- 0000000000001000 0fd:00001 libfreebl3.so
00007f88f13dc000       4 rw--- 0000000000002000 0fd:00001 libfreebl3.so
00007f88f13dd000    1804 r-x-- 0000000000000000 0fd:00001 libc-2.17.so
00007f88f15a0000    2048 ----- 00000000001c3000 0fd:00001 libc-2.17.so
00007f88f17a0000      16 r---- 00000000001c3000 0fd:00001 libc-2.17.so
00007f88f17a4000       8 rw--- 00000000001c7000 0fd:00001 libc-2.17.so
00007f88f17a6000      20 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f17ab000      84 r-x-- 0000000000000000 0fd:00001 libgcc_s-4.8.5-20150702.so.1
00007f88f17c0000    2044 ----- 0000000000015000 0fd:00001 libgcc_s-4.8.5-20150702.so.1
00007f88f19bf000       4 r---- 0000000000014000 0fd:00001 libgcc_s-4.8.5-20150702.so.1
00007f88f19c0000       4 rw--- 0000000000015000 0fd:00001 libgcc_s-4.8.5-20150702.so.1
00007f88f19c1000    1028 r-x-- 0000000000000000 0fd:00001 libm-2.17.so
00007f88f1ac2000    2044 ----- 0000000000101000 0fd:00001 libm-2.17.so
00007f88f1cc1000       4 r---- 0000000000100000 0fd:00001 libm-2.17.so
00007f88f1cc2000       4 rw--- 0000000000101000 0fd:00001 libm-2.17.so
00007f88f1cc3000     932 r-x-- 0000000000000000 0fd:00001 libstdc++.so.6.0.19
00007f88f1dac000    2044 ----- 00000000000e9000 0fd:00001 libstdc++.so.6.0.19
00007f88f1fab000      32 r---- 00000000000e8000 0fd:00001 libstdc++.so.6.0.19
00007f88f1fb3000       8 rw--- 00000000000f0000 0fd:00001 libstdc++.so.6.0.19
00007f88f1fb5000      84 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f1fca000      40 r-x-- 0000000000000000 0fd:00001 libnuma.so.1.0.0
00007f88f1fd4000    2048 ----- 000000000000a000 0fd:00001 libnuma.so.1.0.0
00007f88f21d4000       4 r---- 000000000000a000 0fd:00001 libnuma.so.1.0.0
00007f88f21d5000       4 rw--- 000000000000b000 0fd:00001 libnuma.so.1.0.0
00007f88f21d6000      84 r-x-- 0000000000000000 0fd:00001 libz.so.1.2.7
00007f88f21eb000    2044 ----- 0000000000015000 0fd:00001 libz.so.1.2.7
00007f88f23ea000       4 r---- 0000000000014000 0fd:00001 libz.so.1.2.7
00007f88f23eb000       4 rw--- 0000000000015000 0fd:00001 libz.so.1.2.7
00007f88f23ec000       4 r-x-- 0000000000000000 0fd:00001 libaio.so.1.0.1
00007f88f23ed000    2044 ----- 0000000000001000 0fd:00001 libaio.so.1.0.1
00007f88f25ec000       4 r---- 0000000000000000 0fd:00001 libaio.so.1.0.1
00007f88f25ed000       4 rw--- 0000000000001000 0fd:00001 libaio.so.1.0.1
00007f88f25ee000       8 r-x-- 0000000000000000 0fd:00001 libdl-2.17.so
00007f88f25f0000    2048 ----- 0000000000002000 0fd:00001 libdl-2.17.so
00007f88f27f0000       4 r---- 0000000000002000 0fd:00001 libdl-2.17.so
00007f88f27f1000       4 rw--- 0000000000003000 0fd:00001 libdl-2.17.so
00007f88f27f2000    2264 r-x-- 0000000000000000 0fd:00001 libcrypto.so.1.0.2k
00007f88f2a28000    2048 ----- 0000000000236000 0fd:00001 libcrypto.so.1.0.2k
00007f88f2c28000     112 r---- 0000000000236000 0fd:00001 libcrypto.so.1.0.2k
00007f88f2c44000      52 rw--- 0000000000252000 0fd:00001 libcrypto.so.1.0.2k
00007f88f2c51000      16 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f2c55000     412 r-x-- 0000000000000000 0fd:00001 libssl.so.1.0.2k
00007f88f2cbc000    2048 ----- 0000000000067000 0fd:00001 libssl.so.1.0.2k
00007f88f2ebc000      16 r---- 0000000000067000 0fd:00001 libssl.so.1.0.2k
00007f88f2ec0000      28 rw--- 000000000006b000 0fd:00001 libssl.so.1.0.2k
00007f88f2ec7000      28 r-x-- 0000000000000000 0fd:00001 librt-2.17.so
00007f88f2ece000    2044 ----- 0000000000007000 0fd:00001 librt-2.17.so
00007f88f30cd000       4 r---- 0000000000006000 0fd:00001 librt-2.17.so
00007f88f30ce000       4 rw--- 0000000000007000 0fd:00001 librt-2.17.so
00007f88f30cf000      32 r-x-- 0000000000000000 0fd:00001 libcrypt-2.17.so
00007f88f30d7000    2044 ----- 0000000000008000 0fd:00001 libcrypt-2.17.so
00007f88f32d6000       4 r---- 0000000000007000 0fd:00001 libcrypt-2.17.so
00007f88f32d7000       4 rw--- 0000000000008000 0fd:00001 libcrypt-2.17.so
00007f88f32d8000     184 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3306000     360 r-x-- 0000000000000000 0fd:00001 libprotobuf-lite.so.3.6.1
00007f88f3360000    2044 ----- 000000000005a000 0fd:00001 libprotobuf-lite.so.3.6.1
00007f88f355f000       8 r---- 0000000000059000 0fd:00001 libprotobuf-lite.so.3.6.1
00007f88f3561000       4 rw--- 000000000005b000 0fd:00001 libprotobuf-lite.so.3.6.1
00007f88f3562000      92 r-x-- 0000000000000000 0fd:00001 libpthread-2.17.so
00007f88f3579000    2044 ----- 0000000000017000 0fd:00001 libpthread-2.17.so
00007f88f3778000       4 r---- 0000000000016000 0fd:00001 libpthread-2.17.so
00007f88f3779000       4 rw--- 0000000000017000 0fd:00001 libpthread-2.17.so
00007f88f377a000      16 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f377e000     124 r-x-- 0000000000000000 0fd:00001 libHotBackup.so
00007f88f379d000    2044 ----- 000000000001f000 0fd:00001 libHotBackup.so
00007f88f399c000       4 r---- 000000000001e000 0fd:00001 libHotBackup.so
00007f88f399d000       4 rw--- 000000000001f000 0fd:00001 libHotBackup.so
00007f88f399e000     272 r-x-- 0000000000000000 0fd:00001 libjemalloc.so.1
00007f88f39e2000    2044 ----- 0000000000044000 0fd:00001 libjemalloc.so.1
00007f88f3be1000       8 r---- 0000000000043000 0fd:00001 libjemalloc.so.1
00007f88f3be3000       4 rw--- 0000000000045000 0fd:00001 libjemalloc.so.1
00007f88f3be4000       4 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3be5000     136 r-x-- 0000000000000000 0fd:00001 ld-2.17.so
00007f88f3c66000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88f3c67000     512 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3ce7000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88f3ce8000     512 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3d68000       4 ----- 0000000000000000 000:00000   [ anon ]
00007f88f3d69000     572 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3e03000      12 rw--- 0000000000000000 000:00000   [ anon ]
00007f88f3e06000       4 r---- 0000000000021000 0fd:00001 ld-2.17.so
00007f88f3e07000       4 rw--- 0000000000022000 0fd:00001 ld-2.17.so
00007f88f3e08000       4 rw--- 0000000000000000 000:00000   [ anon ]
00007fff869d2000     132 rw--- 0000000000000000 000:00000   [ stack ]
00007fff869f9000       8 r-x-- 0000000000000000 000:00000   [ anon ]
ffffffffff600000       4 r-x-- 0000000000000000 000:00000   [ anon ]
mapped: 5659588K    writeable/private: 5532080K    shared: 0K

[anon]为已分配内存，[stack]为程序堆栈，pmap命令用于报告进程的内存映射关系，加-x选项信息更加详细，是Linux调试及运维一个很好的工具，从上面可以看出主要主进程使用的内存，但是还是无法知道内存到底用在哪里了。

2、开启performance_schema分析内存使用，操作如下

update performance_schema.setup_instruments set ENABLED = 'YES' where name like 'memory%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' where name like '%memory%';

生产上建议打开变量performance_schema = on ，方便后续排查问题，如果之前没有开启，那么就得需要重启mysql才能生效了。

查询全局内存统计表，操作如下

select EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

查询用户内存统计表，操作如下

select USER,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_user_by_event_name where EVENT_NAME = 'memory/sql/String::value';

查询主机内存统计表，操作如下

select HOST,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_host_by_event_name where EVENT_NAME = 'memory/sql/String::value';

查询主机内存统计表，操作如下

select USER,HOST,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_account_by_event_name where EVENT_NAME = 'memory/sql/String::value';

查询线程内存统计表，操作如下

select THREAD_ID,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME = 'memory/sql/String::value' order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

从上面的查询信息我们可以知道是这个事件memory/sql/String:value占用内存比较多，那这个事件又跟哪个线程产生关联呢？所以我们放大查询范围再次查询线程内存统计表

select THREAD_ID,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME like 'memory/sql%' order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

我们可以看出是事件memory/sql/NET::buff占用内存最多，我们看看net_buffer_length变量设置

然后拿COUNT_ALLOC的值计算一下

上面的值3112271872跟CURRENT_NUMBER_OF_BYTES_USED的值是差不多，而且通过持续查询表performance_schema.memory_summary_by_thread_by_event_name会发现内存在不断增大，但是COUNT_FREE的值却还是0，也就是说分配的内存从来没有释放过，这不就是内存泄露了吗？而且通过show full processlist查看是没有用户线程在跑的，因为归档程序一般在凌晨就跑完了，对net_buffer_length变量不了解的，可以查看下图信息

从上面掌握的信息，我们是大概知道分配线程的内存都没有得到释放，我们再看看Percona Server 8.0的内存分配器种类

# lsof -p `pidof mysqld` | grep -i jemalloc
mysqld  19725 mysql  mem       REG              253,1      2329056    345291 /opt/mysql/lib/mysql/libjemalloc.so.1

jemalloc还是比较稳定，由于内存bug导致mysqld产生内存泄露的几率是比较小的，另外从prometheus监控和现象上看，这个内存泄露bug很大概率是跟open tables操作有关的，从现象看内存的快速上涨和open_tables的上涨趋势是吻合的，其他监控指标并没有比较明显的变化，为了验证猜想，笔者执行flush tables;发现内存使用就会降下来，但是还是后续还是会慢慢上涨上去，所以这个操作只能缓解内存占用，因此治标不治本。

我们查看线程1的具体信息操作如下

select * from performance_schema.threads where THREAD_ID = 1\G

从上面我们可以看出线程对应的os线程id为3082，正是我们的主线程

mysql     3082  1168  1 10:26 ?        00:00:19 opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/data/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/mysql_error.log --pid-file=/data/mysql/data/mysql_run.pid --socket=/tmp/mysql.sock --port=3306

从上面可以看出是主线程存在内存泄露问题了，我们可以大胆猜想有可能是Percona Server 8.0的BUG导致连接关闭后线程资源没有回收导致的内存泄露，笔者去查看MySQL change log和Percona Server change log并没有发现相关bug，而且笔者还测试了TDSQL 8.0也没发现类似内存泄露问题（给TDSQL团队点赞，TDSQL确实很稳定，目前为止没有发现比较严重的BUG），为了验证高版本Percona Server新版本是否已经解决了相关BUG，笔者还是决定升级从库到Percona server 8.0.22版本观察下，发现问题并没有解决。

注意：生产环境升级不要直接升级主库，建议先升级从库观察一段时间后再升级主库，另外生产不允许直接对mysqld使用gdb、pstack进行分析，会导致mysql夯住，严重导致cvm卡住（开发四十米大砍刀已饥渴难耐）。

根据以上的现象、监控排查和版本升级，笔者猜测可能跟线程池内存泄露有关，因为相关线程的资源在连接关闭后相关资源并没有得到回收，所以关掉线程池，编辑配置文件my.cnf

#thread_handling = pool-of-threads
thread_handling = one-thread-per-connection

修改完配置文件后进行重启操作，观察两天，查询线程内存统计表

select THREAD_ID,EVENT_NAME,COUNT_ALLOC,COUNT_FREE,CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME = 'memory/sql/NET::buff' order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

SELECT SUBSTRING_INDEX(event_name,'/',2) AS  code_area, sys.format_bytes(SUM(current_alloc))  AS current_alloc  FROM sys.x$memory_global_by_current_bytes  GROUP BY SUBSTRING_INDEX(event_name,'/',2)  ORDER BY SUM(current_alloc) DESC;

从上面信息可以看出mysql内存泄露已经处理好了，之前Percona server 5.7也有小版本出现过由于打开线程池导致的内存泄露的BUG。分析完问题后，关闭setup_instruments，开启PS本身也会用掉几百兆内存的，而且mysql性能会有损失。

update performance_schema.setup_instruments set ENABLED = 'NO' where name like 'memory%';

三、总结

开启Percona Server 8.0的线程池而导致内存泄露的问题，之前可能由于归档的表比较少，后面随着并发跑的表越来越多，让这个问题逐步显现出来了。由于这个是我们的归档库所以对于重启和升级不是很敏感，但是如果是正在用的生产库，DBA在排查问题还是比较头疼的，我们能做的就是尽可能减少对业务的影响，同时缩小排查范围，根据现象定位可能的原因，由于笔者并不是研究源码或者内核开发人员，去全局分析源码还是比较吃力的，但是我们可以用其他方法去不断验证、尝试和观察，前提是首先要根据监控和现象做到有迹可循，总之大家共勉吧！

扫描二维码获取

更多知识

DBA入坑指南

点个在看你最好看

数据库

文章转载自DBA入坑指南，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Percona Server 8.0内存使用分析

评论