Kevin@灼华
mysqld业务数据库运行占用内存越来越高峰值达88%
编辑
2024-06-06
数据相关
00
请注意，本文编写于 322 天前，最后修改于 322 天前，其中某些信息可能已经过时。

目录

最初处理思路：
通过脚本查看内存
运行脚本，查看分配的内存大小
通过Performance Schema and sys schema to monitor MySQL memory usage进行计算
glibc的内存管理器自身缺陷导致
小结：

背景：

环境及系统集群及版本配置
数据库环境3节点MGR集群primary节点
操作系统CentOS Linux release 7.9.2009 (Core)8核16G

目前主库数据库使用内存达到88%左右，其他的2从库内存使用率40%左右，通过观察主库内存使用率每天都有小幅度上升趋势。但是，innodb_buffer_pool_size才设置8G, 怀疑mysql客户端连接的线程断开后，内存不能正常释放.

最初处理思路：

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

mysql
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
binlog_cache_size
tmp_table_size

重启mysql实例后，释放内存，运行一段时间，问题依然存在，没有得到解决。

通过脚本查看内存

编写查看内存脚本如下：

bash
[root@wtj1vpdbmgnt01 shell]# cat check_mem_alloc.sh
#!/bin/bash

# ----------------------------------------------------------------
#       Copyright © 2024 Openserv Ltd. All rights reserved.
#       FileName   : check_mem_alloc.sh
#       Author     : dba
#       Mail       : [email protected]
#       Version    : v1.0.0
#       CreateTime : 2024-06-06 10:55:19
#       COPYRIGHT  : Copyright ©2024 - 开源服务
#       Description: Welcome Use The Script.
# ----------------------------------------------------------------

# 修改为自已数据库实例的账号和密码等信息 
/acdata/data/mysqlsoft3306/bin/mysql -h 10.3.8.112 -u root -p'xxxxxx' -P 3306 -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"  
}'

运行脚本，查看分配的内存大小

按照脚本计算实例当前会话的最大的连接数为80，最大内存使用应该是8G，而现在mysqld占用的内存达到10G.

通过Performance Schema and sys schema to monitor MySQL memory usage进行计算

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;

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             | 6.45 GiB      |
| memory/group_rpl          | 1024.00 MiB   |
| memory/performance_schema | 278.82 MiB    |
| memory/temptable          | 71.00 MiB     |
| memory/sql                | 63.23 MiB     |
| memory/mysys              | 12.23 MiB     |
| memory/mysqld_openssl     | 2.87 MiB      |
| memory/mysqlx             | 3.44 KiB      |
| memory/myisam             |  696 bytes    |
| memory/csv                |   88 bytes    |
| memory/blackhole          |   88 bytes    |
| memory/vio                |   16 bytes    |
+---------------------------+---------------+
12 rows in set (0.01 sec)

注意

有些怀疑是不是有内存泄漏的问题，去官方也没有找到此版本内存泄漏的相关问题

最后，使用大绝招，逼mysql就范。

glibc的内存管理器自身缺陷导致

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

bash

# 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重建表的做法。

bash
[root@wyvpjhsql01 ~]# gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
[New LWP 12846]
[New LWP 32443]
[New LWP 15459]
[New LWP 32563]
[New LWP 14120]
[New LWP 24868]
[New LWP 2599]
[New LWP 1819]
[New LWP 32141]
[New LWP 20832]
[New LWP 20730]
[New LWP 20581]
[New LWP 30140]
[New LWP 29796]
[New LWP 29630]
[New LWP 17314]
[New LWP 16350]
[New LWP 14404]
[New LWP 14402]
[New LWP 2256]
[New LWP 2254]
[New LWP 1712]
[New LWP 1710]
[New LWP 1631]
[New LWP 1599]
[New LWP 1597]
[New LWP 1596]
[New LWP 1595]
[New LWP 1110]
[New LWP 12170]
[New LWP 31006]
[New LWP 12831]
[New LWP 12766]
[New LWP 12196]
[New LWP 429]
[New LWP 31775]
[New LWP 30259]
[New LWP 28717]
[New LWP 28519]
[New LWP 28353]
[New LWP 28352]
[New LWP 28257]
[New LWP 27899]
[New LWP 27662]
[New LWP 27575]
[New LWP 27545]
[New LWP 27514]
[New LWP 27498]
[New LWP 27492]
[New LWP 27491]
[New LWP 27490]
[New LWP 27461]
[New LWP 27452]
[New LWP 27451]
[New LWP 27449]
[New LWP 27438]
[New LWP 27437]
[New LWP 27435]
[New LWP 27434]
[New LWP 27433]
[New LWP 27430]
[New LWP 27429]
[New LWP 27428]
[New LWP 27424]
[New LWP 27422]
[New LWP 27421]
[New LWP 27420]
[New LWP 27419]
[New LWP 27417]
[New LWP 27409]
[New LWP 27408]
[New LWP 18138]
[New LWP 18136]
[New LWP 18135]
[New LWP 18134]
[New LWP 18133]
[New LWP 18132]
[New LWP 18131]
[New LWP 11498]
[New LWP 11444]
[New LWP 11441]
[New LWP 11440]
[New LWP 11439]
[New LWP 11435]
[New LWP 11434]
[New LWP 11433]
[New LWP 11432]
[New LWP 11429]
[New LWP 11428]
[New LWP 11427]
[New LWP 11426]
[New LWP 11425]
[New LWP 11424]
[New LWP 11423]
[New LWP 11422]
[New LWP 11421]
[New LWP 11420]
[New LWP 11419]
[New LWP 11418]
[New LWP 11417]
[New LWP 11416]
[New LWP 11415]
[New LWP 11414]
[New LWP 11413]
[New LWP 11412]
[New LWP 11411]
[New LWP 11410]
[New LWP 11409]
[New LWP 11408]
[New LWP 11407]
[New LWP 11406]
[New LWP 11405]
[New LWP 11404]
[New LWP 11403]
[New LWP 11402]
[New LWP 11401]
[New LWP 11400]
[New LWP 11399]
[New LWP 11398]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00007fc1a23bdddd in poll () from /lib64/libc.so.6
$1 = 1
[Inferior 1 (process 11385) detached]

执行后发现内存使用率明显降下来了

js
[root@wyvpjhsql01 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          15884       11831         719          63        3334        3659
Swap:          8187         585        7602

小结：

至此问题告一段落，后续继续进行跟踪观察。

本文作者:Kevin@灼华

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！