MySQL Bugs: #92207: mysqldump in MySQL 5.7 running on Ubuntu 18.04 does not release memory

Bug #92207	mysqldump in MySQL 5.7 running on Ubuntu 18.04 does not release memory
Submitted:	28 Aug 2018 5:05	Modified:	4 Oct 2018 13:06
Reporter:	Prakyath Raj	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S1 (Critical)
Version:		OS:	Ubuntu (18.04)
Assigned to:	MySQL Verification Team	CPU Architecture:	Other (x64)

Description:
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 backup using for loop and mysqldump 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 completely. 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

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

Issue detected on Ubuntu 18.04 64 bit

Enginee InnoDB Status

Attachment: innodb_status_aug29.txt (text/plain), 9.06 KiB.

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 .

Hi,

When you say "official repo" you are talking but "default what came with system" or you are talking about Oracle binaries from oracle repo ( https://dev.mysql.com/downloads/repo/apt/)  ?

'cause with Oracle binaries I cannot reproduce this.

thanks
Bogdan

We are using MySQL Community edition v5.7.23 available in Ubuntu 18.04 repository.

Most of our mysql servers running v5.7.23 are working fine. We were able to replicate this issue only in servers which contain more than 500-600 databases. Theses databases are about 2MB and contains stored procedures & views. I have tried tuning the InnoDB cache settings which seems to help. However, there is still small memory leak. Please let me know if you need any additional data.

Hi,
I can't reproduce this.
Using latest 5.7 on both centos, fedora and ubuntu, with huge data dictionary (1000 tables, 5000 stored procedures..) mysqldump works. mysqldump use a lot of ram to make this dump and it might be written "better" to use less but it's how it works, I don't see any leak here, everything is freed properly when mysql is done with it.

You can try using mysqlpump https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
it's bit different and uses ram differently, you might find it much faster for a huge number of tables you have.

kind regards
bogdan