devbox
Cpp五条
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql内存会持续上涨，每天增加一点，一直到100%_mysql内存占用一直增高不释放

作者：Cpp五条 | 2024-03-22 12:15:53

赞

踩

mysql内存占用一直增高不释放

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释放内存，运行一段时间，问题依然存在

通过脚本查看内存：

  1. ​
  2. # cat mem.sh 
  3. #!/bin/sh
  4.  
  5. # you might want to add some user authentication here
  6. /usr/local/mysql/bin/mysql -uroot -pxxxx -e "show variables; show status" | awk '
  7. {undefined
  8. VAR[$1]=$2
  9. }
  11. END {undefined
  12. MAX_CONN = VAR["max_connections"]
  13. MAX_USED_CONN = VAR["Max_used_connections"]
  14. 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"]
  15. 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"]
  16. MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
  17. MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
  19. printf "+------------------------------------------+--------------------+\n"
  20. printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
  21. printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
  22. printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
  23. printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
  24. printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
  25. printf "+------------------------------------------+--------------------+\n"
  26. printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
  27. printf "+------------------------------------------+--------------------+\n"
  28. printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
  29. printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
  30. printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
  31. printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
  32. printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
  33. printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
  34. printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
  35. printf "+------------------------------------------+--------------------+\n"
  36. printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
  37. printf "+------------------------------------------+--------------------+\n"
  39. printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
  40. printf "| %40s | %18d |\n", "max_connections", MAX_CONN
  42. printf "+------------------------------------------+--------------------+\n"
  44. printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
  45. printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
  47. printf "+------------------------------------------+--------------------+\n"
  49. }'
  50.  
  51. # ./mem.sh 
  52. mysql: [Warning] Using a password on the command line interface can be insecure.
  53. +------------------------------------------+--------------------+
  54. |                          key_buffer_size |          32.000 MB |
  55. |                         query_cache_size |           0.000 MB |
  56. |                  innodb_buffer_pool_size |       16384.000 MB |
  57. |          innodb_additional_mem_pool_size |           0.000 MB |
  58. |                   innodb_log_buffer_size |          32.000 MB |
  59. +------------------------------------------+--------------------+
  60. |                              BASE MEMORY |       16448.000 MB |
  61. +------------------------------------------+--------------------+
  62. |                         sort_buffer_size |           1.000 MB |
  63. |                         read_buffer_size |           2.000 MB |
  64. |                     read_rnd_buffer_size |           2.000 MB |
  65. |                         join_buffer_size |           1.000 MB |
  66. |                             thread_stack |           0.500 MB |
  67. |                        binlog_cache_size |           4.000 MB |
  68. |                           tmp_table_size |          16.000 MB |
  69. |                        net_buffer_length |           0.016 MB |
  70. +------------------------------------------+--------------------+
  71. |                    MEMORY PER CONNECTION |          26.516 MB |
  72. +------------------------------------------+--------------------+
  73. |                     Max_used_connections |                840 |
  74. |                          max_connections |               2048 |
  75. +------------------------------------------+--------------------+
  76. |                              TOTAL (MIN) |       38721.125 MB |
  77. |                              TOTAL (MAX) |       70752.000 MB |
  78. +------------------------------------------+--------------------+
  79. ​

按照脚本计算我最大的连接数为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;

  1. mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
  2.     ->        code_area, FORMAT_BYTES(SUM(current_alloc))
  3.     ->        AS current_alloc
  4.     ->        FROM sys.x$memory_global_by_current_bytes
  5.     ->        GROUP BY SUBSTRING_INDEX(event_name,'/',2)
  6.     ->        ORDER BY SUM(current_alloc) DESC;
  7. +---------------------------+---------------+
  8. | code_area                 | current_alloc |
  9. +---------------------------+---------------+
  10. | memory/innodb             | 18.72 GiB     |
  11. | memory/sql                | 3.68 GiB      |
  12. | memory/performance_schema | 1.41 GiB      |
  13. | memory/mysys              | 1.31 GiB      |
  14. | memory/temptable          | 846.00 MiB    |
  15. | memory/myisam             | 6.43 MiB      |
  16. | memory/mysqld_openssl     | 6.26 MiB      |
  17. | memory/csv                | 25.79 KiB     |
  18. | memory/mysqlx             | 3.44 KiB      |
  19. | memory/blackhole          |   88 bytes    |
  20. | memory/vio                |   16 bytes    |
  21. +---------------------------+---------------+
  22. 发现各个部分内存使用正常，加起来才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插件去管理内存

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

  1. [root@bss-mysql-master logs]# gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
  2. [New LWP 32146]
  3. [New LWP 32145]
  4. [New LWP 32144]
  5. [New LWP 32143]
  6. [New LWP 32142]
  7. [New LWP 32141]
  8. [New LWP 32140]
  9. [New LWP 32139]
  10. [New LWP 31937]
  11. [New LWP 31936]
  12. [New LWP 31935]
  13. ......
  14. ......
  15. ......
  16. 执行后发现内存使用率明显降下来了
  17. [root@bss-mysql-master logs]# free -m
  18.               total        used        free      shared  buff/cache   available
  19. Mem:          64258       46199         539         672       17519       16710
  20. Swap:             0           0           0
  21.  
  22.   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                                                                  
  23. 42022 mysql     20   0   57.9g  44.3g   9480 S 535.8 70.6 359805:36 mysqld                                                                                                                                                                                                   
  24.  1194 root      20   0  725044  26364   5176 S   7.0  0.0   7948:05 node_exporter                                                                                                                                                                                            
  25.     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)'

原文链接：数据库运行一段时间mysqld占用内存越来越高达到90% - 腾讯云开发者社区-腾讯云

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/288105
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号