前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >数据库运行一段时间mysqld占用内存越来越高达到90%

数据库运行一段时间mysqld占用内存越来越高达到90%

原创
作者头像
wangwei-dba
修改2021-07-27 14:28:41
17.5K0
修改2021-07-27 14:28:41
举报
文章被收录于专栏:mysql-dba

数据库环境:一主一从

操作系统:CentOS Linux release 7.5.1804 (Core) 64核64G

mysql:Server version: 8.0.25 MySQL Community Server - GPL

目前主库数据库使用内存达到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

代码语言:javascript
复制
[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;

代码语言:javascript
复制
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)'发现内存有明显的释放

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

代码语言:javascript
复制
[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内存使用情况的脚本:

代码语言:javascript
复制
[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"  
}'

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档