MySQL Bugs: #92648: MySQL process consuming 100% memory

Bug #92648	MySQL process consuming 100% memory
Submitted:	3 Oct 2018 4:30	Modified:	4 Oct 2018 12:12
Reporter:	akshaya srinivasan	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	5.6	OS:	Linux
Assigned to:		CPU Architecture:	Any (10GB RAM size)
Tags:	memory leak, MySQL, mysqldump

Description:
Hi,

I have mysql databases, with myisam tables in it. I had to take dump of those databases. For consistency purpose I had used lock-tables while taking dump, but this leads to memory consumption reaching 100% and the mysql server crashes. When I eliminate the lock-tables option , dump goes fine and there is memory spike seen. My database size is 120GB and RAM size is 10GB.

Is there any other way to achieve consistency while backing up MyISAM databases without using lock-tables? If  not please can this be fixed.

How to repeat:
Have a MySQL database with only MyISAM tables. Let the database size be 120GB. Run mysqldump command to take a backup of the database using lock-tables option. On monitoring the memory, we can see a spike in its usage.

/usr/bin/mysqldump --defaults-file=<mysql password file> --lock-tables   --flush-logs --create-options -B --default-character-set=utf8  --routines  --max_allowed_packet=33554432  -u <mySQL user> --socket=<socket file> --databases "<DB name>"

Hi,

Thank you for your report.

I am afraid it is not possible to backup MyISAM tables without flushing tables with the read lock. This is because MyISAM SE is neither ACID nor does it have isolation levels.

There are only two things that you can do. One is to plan to move all your tables to InnoDB. The other possibility is to dump each database separately.

Also, for your information, this is not a memory leak, since after the backup is finished, all memory is released to the operating system.