mysql内存不释放
【1】mysql内存
mysql：Server version: 8.0.25 MySQL Community Server - GPL
目前主库数据库使用内存达到80%左右，通过观察主库内存使用率每天都有小幅度上升趋势
而innodb_buffer_pool_size才设置16G, 怀疑mysql客户端连接的线程断开后，内存不能正常释放
（1.1）调整线程相关内存
一开始处理：
把线程相关的内存参数调整小一些
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
binlog_cache_size
tmp_table_size
重启mysql释放内存，运行一段时间，问题依然存在。
通过脚本查看内存：
（1.2）查看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
（1.3）参照官网内存使用情况计算
参照官方文档再次把内存使用情况计算一遍 （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进程占用内存过高怎么排查？
【2】mysqld进程占用内存过高怎么排查？
前面排查都是一样的，后面有一步骤：
（2.1）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
至此问题告一段落，继续跟踪观察
（2.2）gdb安装方法
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)'
