| Bug #85979 | System memory not released after mysqldump completes | ||
|---|---|---|---|
| Submitted: | 18 Apr 2017 8:52 | Modified: | 8 Apr 2021 12:29 |
| Reporter: | Machiel Richards | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S3 (Non-critical) |
| Version: | 5.7.12 | OS: | Ubuntu (14.04) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqldump memory | ||
[20 Apr 2017 16:03]
MySQL Verification Team
Hi! What you describe is very well known behavior of GNU malloc on the LInux OS. GNU malloc simply does not deallocate memory from the process, but it flags it as available, so that next malloc() gets memory very fast. You can decrease the memory by running other programs which use lot's of RAM or by writing the small program that would calloc() more then the available memory. Then GNU malloc will detach some memory from our server. Please, do note that this has nothing to do with our code. This is entirely up to the functioning of GNU malloc, which is a part of Linux C library.
[24 Apr 2017 10:21]
Machiel Richards
The problem with that though is that I have setup larger databases with MySQL 5.6 which do not seem to have this issue. When running backups, the memory gets freed once backup completes. However, when using the mysql 5.7, this issue does happen.
[24 Apr 2017 13:37]
MySQL Verification Team
Hi! This is expected behavior. MySQL 5.7 has many more features, it is larger, but what is more important is that it has many more transient buffers. All those buffers are duly released, as it is checked on daily basis but several different specialized tests. Hence, make a test such as I recommended and you will see for yourself.
[26 Apr 2017 8:06]
Machiel Richards
I have done some tests by running other memory intensive processes as well and the system memory just keeps on increasing up to the point where mysql database is killed by OOM and eventually the whole system becomes unresponsive and needs to be restarted. This to me however seems that the theory that running other memory intensive programs will re-use the memory does not seem valid as the memory is still being used by mysqld and the rest of the processes simply uses the last bit that is left before everything crashes. We need to understand what exactly has changed between mysql 5.6 and 5.7 in terms of memory that is now causing this issue. I have a mysql 5.6 database and creating a backup of a 1Tb table on a system with only 32Gb of memory , besides taking long, does not seem to be an issue in terms of memory. However, to create a backup of 3 x 60Gb databases on 5.7 , one after the other (i.e. they are started about an hour apart from the previous completing), seems to spike the memory and eventually causing the memory and swap to be fully utilized and crashes mysql server with OOM errors. So this to me does in fact seem like a type of bug or at least a problem that needs to be addresses either by configuration or system changes, as setting up production servers with this type of memory requirements is a problem for most people as I can only imagine. So any assistance to try and trace why this happens on 5.7 but not on 5.6 would be greatly appreciated by many people.
[26 Apr 2017 14:40]
MySQL Verification Team
Hi! I have tried repeating the behavior that you describe on Oracle Linux, which is quite similar to Red Hat, with both 5.6 and 5.7 and with all of my test databases (quite large) backed up with mysqldump. It all worked fine. Single, well written program with calloc() frees memory that GNU malloc kept in the user space available (for the performance reasons). I have not been able to achieve OOM in a single instance, but memory was released. This slowed down further server operations, as GNU malloc had to make kernel calls to get memory. I can not test it on Ubuntu, since we do not use it. You should try some other, alternative malloc library, like jmalloc etc ... or see if other users of Ubuntu share your problems. Can't repeat !!!
[8 Apr 2021 12:15]
karthick thangavel
we are also facing the same problem in MySQL 5.7.14 if anyone had a solution please share it with us Thanks and Advance Karthick
[8 Apr 2021 12:29]
Machiel Richards
@ karthick thangavel Send me your email address then I will assist you offline.
[9 Apr 2021 13:10]
MySQL Verification Team
A small advice. The releases that you are using are ancient. Please try latest 5.7, which is 5.7.34. It has 2500 improvements and bugs fixed since 5.7.1*.
[18 May 2021 15:54]
karthick thangavel
Hi , This is karthick, sorry for delay response , this is my mail id karthikeyan.t@interlaceindia.com Regards, Karthick
[18 May 2021 15:55]
karthick thangavel
@ Machiel Richards karthikeyan.t@interlaceindia.com
Description: Good day After several tests in terms of server memory running out on a daily basis resulting in a database restart , the following were found. Mysql and system memory usage seems to be stable for most part of the day, until the point where mysqldump is run in order to backups the databases. The server currently have 128Gb of memory with 90Gb allocated to innodb_buffer pool and 32Gb swap. Memory usage on the server is at 60-70Gb before backups start, however the moment the backups start, this goes up to 125Gb memory used and then eventually starts to swap. The mysqldump processes complete the backups, however the memory is never released and just keeps on increasing as the system is used until the OS fails due to lack of memory. At the moment our workaround is to restart the DB via cron immediately after backups which then releases the memory, however have not found a permanent fix for this as yet. Based on the testing done thus far, it seems to only spike that much when mysqldump is being run. mysqldump command : mysqldump -u root --single-transaction --set-gtid-purged=OFF --quick --triggers --routines --events --databases DB | gzip -c > /var/lib/backup/DB/mysql-DB-backup.data.gz We have also tried to run the backups by excluding the compressions option (gzip) , however this had the same results. Database sizes vary from 30Gb to about 300Gb per database with a total of around 18 databases. Backups are spread out to not run any backup concurrently and from our graphs we can see the memory clearly increasing the whole time since start of backups but only decreasing after restart. How to repeat: Setup Mysql version 5.7 create database of substantial size Create a mysqldump backup of the database and monitor memory usage before and after the backups processes.