mysqld 内存持续变高
目前主库数据库使用内存达到90%左右，从库内存使用率40%左右，通过观察主库内存使用率每天都有小幅度上升趋势 而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释放内存，运行一段时间，问题依然存在 通过脚本查看内存： 按照脚本计算我最大的连接数为840 最大内存使用应该是38G（平时正常情况的下的连接数只有400左右），而现在mysqld占用的内存达到56G
[root@bss-mysql-master home]# ./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 | +------------------------------------------+--------------------+参照官方文档再次把内存使用情况计算一遍 （8.12.3.1 How MySQL Uses Memory） https://dev.mysql.com/doc/refman/8.0/en/memory-use.html 通过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进程占用内存过高怎么排查？
https://blog.51cto.com/u_15127586/2809954
前面排查都是一样的
后面有一步骤：
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)'发现内存有明显的释放 于是确定在晚上业务低峰的时候在生产环境执行一下：
[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
至此问题告一段落，继续跟踪观察
获取mysql内存使用情况的脚本：
[root@bss-mysql-master home]# cat mem.sh
#!/bin/sh
# you might want to add some user authentication here
/usr/local/mysql/bin/mysql -S /tmp/mysql3312.sock -uroot -pxxxxx -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
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"] + VAR["net_buffer_length"]
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 "| %40s | %15.3f MB |\n", "net_buffer_length", VAR["net_buffer_length"]/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"
}'
文章引用：https://cloud.tencent.com/developer/article/1852862 