mysql内存会持续上涨，每天增加一点，一直到100%

使用sysbench 对数据库进行压测，压测一段时间mysqld内存使用上去了，停止压测，内存使用没有降低，也就是说线程关闭内存没有释放，使用gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'发现内存有明显的释放。简言之，就是调用glibc申请的内存使用完毕后，归还给OS时没有被正常回收，而变成了碎片，随着碎片的不断增长，就能看到

皮不卡秋秋_

6773人浏览 · 2022-11-17 14:40:53

皮不卡秋秋_ · 2022-11-17 14:40:53 发布

mysql：Server version: 8.0.25 MySQL Community Server - GPL

目前主库数据库使用内存达到80%左右，通过观察主库内存使用率每天都有小幅度上升趋势

而innodb_buffer_pool_size才设置16G, 怀疑mysql客户端连接的线程断开后，内存不能正常释放

一开始处理：

把线程相关的内存参数调整小一些

sort_buffer_size

read_buffer_size

read_rnd_buffer_size

join_buffer_size

binlog_cache_size

tmp_table_size

重启mysql释放内存，运行一段时间，问题依然存在

通过脚本查看内存：


# cat mem.sh 
#!/bin/sh

# you might want to add some user authentication here
/usr/local/mysql/bin/mysql -uroot -pxxxx -e "show variables; show status" | awk '
{undefined
VAR[$1]=$2
}

END {undefined
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN

printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"

printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN

printf "+------------------------------------------+--------------------+\n"

printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576

printf "+------------------------------------------+--------------------+\n"

}'

# ./mem.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------------------------+--------------------+
|                          key_buffer_size |          32.000 MB |
|                         query_cache_size |           0.000 MB |
|                  innodb_buffer_pool_size |       16384.000 MB |
|          innodb_additional_mem_pool_size |           0.000 MB |
|                   innodb_log_buffer_size |          32.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |       16448.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           1.000 MB |
|                         read_buffer_size |           2.000 MB |
|                     read_rnd_buffer_size |           2.000 MB |
|                         join_buffer_size |           1.000 MB |
|                             thread_stack |           0.500 MB |
|                        binlog_cache_size |           4.000 MB |
|                           tmp_table_size |          16.000 MB |
|                        net_buffer_length |           0.016 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          26.516 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                840 |
|                          max_connections |               2048 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |       38721.125 MB |
|                              TOTAL (MAX) |       70752.000 MB |
+------------------------------------------+--------------------+

按照脚本计算我最大的连接数为840 最大内存使用应该是38G（平时正常情况的下的连接数只有400左右），而现在mysqld占用的内存达到56G

参照官方文档再次把内存使用情况计算一遍（8.12.3.1 How MySQL Uses Memory）

MySQL :: MySQL 8.0 Reference Manual :: 8.12.3.1 How MySQL Uses Memory

通过Performance Schemaandsys schemato monitor MySQL memory usage

SELECTSUBSTRING_INDEX(event_name,'/',2)AS code_area, FORMAT_BYTES(SUM(current_alloc))AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUPBYSUBSTRING_INDEX(event_name,'/',2)ORDERBYSUM(current_alloc)DESC;

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
    ->        code_area, 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;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 18.72 GiB     |
| memory/sql                | 3.68 GiB      |
| memory/performance_schema | 1.41 GiB      |
| memory/mysys              | 1.31 GiB      |
| memory/temptable          | 846.00 MiB    |
| memory/myisam             | 6.43 MiB      |
| memory/mysqld_openssl     | 6.26 MiB      |
| memory/csv                | 25.79 KiB     |
| memory/mysqlx             | 3.44 KiB      |
| memory/blackhole          |   88 bytes    |
| memory/vio                |   16 bytes    |
+---------------------------+---------------+
发现各个部分内存使用正常，加起来才25.85 GiB

猜测怀疑是不是有内存泄漏的问题，去官方也没有找到此版本内存泄漏的相关问题，一时间感觉到十分困扰，通过网络搜索参见这篇文章：mysqld进程占用内存过高怎么排查？

mysqld进程占用内存过高怎么排查？_51CTO博客_mysqlcpu占用过高排查

前面排查都是一样的，后面有一步骤：

5、glibc的内存管理器自身缺陷导致。简言之，就是调用glibc申请的内存使用完毕后，归还给OS时没有被正常回收，而变成了碎片，随着碎片的不断增长，就能看到mysqld进程占用的内存不断上升。这时，可以调用函数主动回收释放这些碎片。

[root@mysql#] gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)' PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 45305 mysql 20 0 28.4g 5.2g 8288 S 2.7 17.0 64:56.82 mysqld 这就像是在InnoDB表中产生太多碎片后，主动执行OPTIMIZE TABLE重建表的做法。

为了验证这一步，现在测试环境上实验：

使用sysbench 对数据库进行压测，压测一段时间mysqld内存使用上去了，停止压测，内存使用没有降低，也就是说线程关闭内存没有释放，使用gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'发现内存有明显的释放，也可以通过安装jemalloc插件去管理内存

于是确定在晚上业务低峰的时候在生产环境执行一下：

[root@bss-mysql-master logs]# gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
[New LWP 32146]
[New LWP 32145]
[New LWP 32144]
[New LWP 32143]
[New LWP 32142]
[New LWP 32141]
[New LWP 32140]
[New LWP 32139]
[New LWP 31937]
[New LWP 31936]
[New LWP 31935]
......
......
......
执行后发现内存使用率明显降下来了
[root@bss-mysql-master logs]# free -m
              total        used        free      shared  buff/cache   available
Mem:          64258       46199         539         672       17519       16710
Swap:             0           0           0

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                                                                  
42022 mysql     20   0   57.9g  44.3g   9480 S 535.8 70.6 359805:36 mysqld                                                                                                                                                                                                   
 1194 root      20   0  725044  26364   5176 S   7.0  0.0   7948:05 node_exporter                                                                                                                                                                                            
    1 root      20   0  195944   7560   1324 S   0.3  0.0 330:13.06 systemd

至此问题告一段落，继续跟踪观察

gdb安装方法：

# yum -y install gcc wget texinfo

# wget https://mirrors.tuna.tsinghua.edu.cn/gnu/gdb/gdb-8.1.tar.gz --no-check-certificate

# tar -zxf gdb-8.1.tar.gz

# cd gdb-8.1

# mkdir builddir

# cd builddir

# ../configure

# make && make install

# gdb --version

收回内存命令：gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'