mysqldump in MySQL 5.7 running on Ubuntu 18.04 does not release memory

[Prakyath Raj]

I have been using MySQL 5.7.23 in Ubuntu 18.04 installed from offical repo. It is working fine fine until we tried to take a dump of the databases. The mysql server contains around 500 small databases which I used to take individual DB dumps using for loop and mysqldump command in bash script. The script works fine with MySQL 5.7 installed in Ubuntu 14.04. However, when we start the dump in Ubuntu 18.04, it starts swapping aggressively and does not release the memory even after individual database dump is completed. After sometime, memory and swap becomes almost full, then also CPU starts increasing due to too much swapping. This caused the server to freeze due to high cpu or mysql server to crash due to OOM.

Note: I will keep adding more info as I get it.

How to repeat:
Setup MySQL 5.7 in Ubuntu 18.04 on AWS T2.Small.
Create about 400-500 small databases
Try to take backup of all databases using mysqldump command

[Prakyath Raj (prakyathr)]

I tried using jemmalloc and tcmalloc libraries for memory allocation. However, the memory leak is not fixed.

[Prakyath Raj (prakyathr)]

Command run to take backup of databases:

for db in $(mysql -e 'show databases;' | grep "DB_PREFIX"); do mysqldump --no-tablespaces --single-transaction $db > "$db_bk.sql"; done

I have added "--no-tablespaces" argument to avoid slowness and load during backup which seemed related to bug https://bugs.launchpad.net/percona-server/+bug/1589334 .

I have attached the output of "SHOW ENGINE INNODB STATUS" query when taking backup.

[Andreas Hasenack (ahasenack)]

Are you saying that the memory usage increases with eacy mysqldump command, or that perhaps one or a few databases are particularly big and the OOM happens when those are hit? A t2.small instance has just 2Gb of memory, isn't that a bit tight for 500 databases?

[Andreas Hasenack (ahasenack)]

If you have a simple reproducer script, that would help. "400-500 small databases" is a bit too generic.

[Launchpad Janitor (janitor)]

[Expired for mysql-5.7 (Ubuntu) because there has been no activity for 60 days.]

[Yaser (yasnad)]

I have the same problem but the Ubuntu version is 16 and MySQL 5.7

When I create the daily MySQL backup by mysqldump command the allocated memory by MySQL changed from 15% to 89% and not released.

It causes many problem on the server.

We have about 10 databases which has about 140 tables with many data.

[Prakyath Raj (prakyathr)]

@yaser I don't know if this will help. From what I found in my case,the issue seems to be caused due to INNODB Buffer pool settings. I have to make sure by adding these 3 innodb variables in my mysql conf and its value by following formula:

innodb_buffer_pool_size = innodb_buffer_pool_chunk_size x innodb_buffer_pool_instances

Example:

innodb_buffer_pool_size = 20G
innodb_buffer_pool_chunk_size = 1G
innodb_buffer_pool_instances = 20

Unless it is required, there is no need to add custom settings in mysql conf. You can also avoid the memory leak issue if you use default mysql conf.

[Paride Legovini (paride)]

Hi,

As this still seems to be an issue I moved the bug report status back to Incomplete. If this is actually a bug and not a configuration problem we'd really like to deliver a fix for it, or to get it properly filed upstream if it belongs tehre, however we first need to reproduce the issue locally in order to be able to investigate it and take actions.

As Andreas pointed out in comment #4 the provided steps are a bit too vague: we'd likely end in a frustrating "can't reproduce" scenario. What we need is a minimal set of commands (ideally a script) that brings from a fresh Ubuntu install to a OOM kill. Using a LXD container is probably the easiest way to experiment with reproducers.

[Launchpad Janitor (janitor)]

[Expired for mysql-5.7 (Ubuntu) because there has been no activity for 60 days.]