• mysql内存会持续上涨，每天增加一点，一直到100%

    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% - 腾讯云开发者社区-腾讯云

  • 相关阅读:
    基于HSV空间的彩色图像分割技术
    【论文阅读】NEURAL MACHINE TRANSLATION BY JOINTLY LEARNING TO ALIGN AND TRANSLATE
    机器学习笔记之高斯分布(五)推断任务之边缘概率分布与条件概率分布
    2023年中国饲料酸化剂产量、需求量及市场规模分析[图]
    数据结构与算法(三) 深度优先搜索
    Google Universal Image Embedding比赛丨北大第一名方案总结
    若依前端部署后地址栏刷新后 出现 拦截提示
    【专栏】RPC系列（番外）-“土气”的IO实现
    PCL点云自定义裁剪的两种思路
    基于华为云IOT平台实现多节点温度采集(STM32+NBIOT)
  • 原文地址：https://blog.csdn.net/weixin_42272246/article/details/127902705