MySQL内存逐渐增长导致数据库挂起-解决方案？

MySQL内存逐渐增长导致数据库挂起-解决方案？

原创   2018-03-17   徐春阳   数据库随笔

      Memory usage gradually increases and brings server to halt。

      前几天提到发现某系统MySQL数据库内存疑似泄露， 见链接：   在MySQL 5.7.18或以上版本发现疑似内存泄露的现象 。收到一些网友的反馈，他们也都说在使用5.7.18或以上版本，但目前还是发现内存泄露的现象。收到这些信息，内心的感觉有点说不出来，是自己倒霉，还是哪里搞错了？ 连续跟踪了一段时间，目前也没有发现mysql在内存分配上面有严重的异样， 甚至有点怀疑是不是操作系统的某些原因？ 

       实在找不到啥原因，于是今天在mysql的bugs系统里去碰碰运气，看会不会发现些什么线索，或者找到答案。 

       仔细翻了翻，找了一个跟我们遇到的现象非常类似的bug:

Bug #83047	Memory usage gradually increases and brings server to halt

       把它review完，原来有这些多人遇到这个问题，抱怨的小心理有点安慰了，原来不是一个案例，间接证明了跟我们自身没有关系。 

      下面是遇到该bug的人反馈的一些内容：

  [20 Sep 2016 7:40] Machiel Richards

How to repeat:Install mysql 5.7 and run for about a week or so with medium amount of traffic (mostly write traffic). 

[10 Aug 2017 8:34] Peter Bukaowski

Hello

I am observing the same situation on multiple mysql installations running mysql 5.7.19 on ubuntu server 16.04.

[25 Sep 2017 11:01] Athi Nivas

Hi,

We're facing a similar issue like this while using MySQL 5.7.18 Community edition in our CentOS 7 systems(CentOS Linux release 7.3.1611 (Core), Linux 172.20.19.194 3.10.0-514.26.2.el7.x86_64).

After many concurrent connections to the server, there seems a substantial increase in the amount of memory being used by mysql process & eventually results in OS crash due to memory overflow. Think, the memory allocated for each connection(s) are not set to free properly.

So, we tried using the TCMalloc(http://goog-perftools.sourceforge.net/doc/tcmalloc.html) for memory allocation. We found the memory is under control & everything seems normal after that.

As both innodb_use_sys_malloc and innodb_additional_mem_pool_size were deprecated in MySQL 5.6 and removed in MySQL 5.7. I'm not sure which allocator went awful.

Does this a bug in MySQL innodb memory allocator (or) the system allocation went wrong? Shall we proceed with TCMalloc? I believe MySQL itself recommends it as an optional memory-allocator in its documentation.https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_safe_malloc-libThanks,
Athi

[20 Nov 2017 13:27] Van Stokes

Ubuntu 16.04.03
MySQL: 5.7.20-log (CE; MySQL apt repo)
Replication: 4xMasters Circular, 6xSlaves
Server type: Dedicated; Non-VM
Server physical RAM: 32GB

We hve the same problem. Excessive memory usage over-time (according to TOP, PS, etc) causing SWAP to be utilized. However, I notice that it happens on my backup/slave servers the most. We backup using "mysqldump" and we dump large databases with a large packet size. We do not have performance schema enabled on the slaves.

And, according to vmstat, we are swapping in/out large number of pages.

--- mysqld.cnf ---

    遇到的人还真不少。 不过最重要的，上面还提到了解决方案。

[28 Sep 2017 7:53] Athi Nivas

Hi Bogdan,

Yes. And changing the memory allocator used for memory operations solved the issue. We've tried with TCMalloc as mentioned here ( https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_safe_malloc-lib ).

Kindly share your thoughts on whether we can proceed with this memory allocator change in our production environment?? is it a safe bet on relying external memory allocator? (or) we should downgrade to a MySQL 5.7 minor version, which doesn't reproduce this issue. 

Much Thanks, 
Athi

[10 Aug 2017 8:45] Machiel Richards

Hi Peter

    I managed to solve this issue for us by enabling a different memory allocation library called jemmalloc. 

    When using the default libraries, the issue comes up, however when enabling jemmalloc libraries,everything became stable again. 

   Steps to follow for this on ubuntu was as follows : 

       - apt-get install libjemalloc1
       - restart mysql service

    You can then use percona toolkit (latest version) to verify that the new memory allocation library is being used. 

    This resolved the issue for me, even though the original bug does not get fixed, but at least a fix to resolve the issues. 

     I have done a whole writeup on the issue for myself, and you are welcome to pm me and I will forward it to you. 

Regards

       靠换了内存分配方法了解决？ 如果真的是这样，之前作者想通过跟踪内存的分配跟释放的方法来定位，所有的辛苦全部都呵呵了。 目前上面的方案作者还没有验证， 也希望就是这个解决方案。更希望官方能够尽快修复该bug. 

      

在MySQL 5.7.18或以上版本发现疑似内存泄露的现象  

当在mysql5.7上发现这个bug，小心脏不好受了

MySQL 5.7.20发布--带来两个重要的有关MGR功能

mysql group relication 原理解析

mysql group replication内部工作机制解析

2017数据库大会实录-MySQL核心参数含义的源码解析

       

戳原文链接，去bug网站看该bug详情。