MySQL Bugs: #69726: MySQL is using up all Memory, then it gets restarted

Bug #69726	MySQL is using up all Memory, then it gets restarted
Submitted:	11 Jul 2013 17:23	Modified:	14 Aug 2013 12:51
Reporter:	Frederic Steinfels	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.6.12	OS:	Linux (Fedora)
Assigned to:		CPU Architecture:	Any
Tags:	crash, Memory, restart

Description:
My MySQL server running on highdefinition.ch will use up all memory until the task scheduler is going to kill the task. Then MySQL will start fresh and use up about 16 GB of memory. The database is about 16 GB in size. This is very annoying because data is being lost. 

With 64 GB Ram, my MySQL installation is getting restarted about every 12 hours. With 64 GB Ram + 128 GB, it will get very slow (while swapping) but it takes 36 hours till restart.

I have disabled all swap memory therefore two days ago.

I am using around 200 tables and various stored procedures and triggers. The problem got more intense when I switched over to innodb recently.

130506 18:01:13 mysqld_safe Number of processes running now: 0
130507 19:57:29 mysqld_safe Number of processes running now: 0
130509 02:35:37 mysqld_safe Number of processes running now: 0
130510 22:18:22 mysqld_safe Number of processes running now: 0
130512 20:25:45 mysqld_safe Number of processes running now: 0
130514 11:16:43 mysqld_safe Number of processes running now: 0
130516 03:00:18 mysqld_safe Number of processes running now: 0
130517 17:00:26 mysqld_safe Number of processes running now: 0
130519 20:14:57 mysqld_safe Number of processes running now: 0
130521 14:18:39 mysqld_safe Number of processes running now: 0
130523 03:36:37 mysqld_safe Number of processes running now: 0
130524 18:02:05 mysqld_safe Number of processes running now: 0
130526 15:36:21 mysqld_safe Number of processes running now: 0
130528 08:01:07 mysqld_safe Number of processes running now: 0
130529 21:00:34 mysqld_safe Number of processes running now: 0
130531 13:25:30 mysqld_safe Number of processes running now: 0
130602 09:13:06 mysqld_safe Number of processes running now: 0
130604 03:24:16 mysqld_safe Number of processes running now: 0
130605 14:28:06 mysqld_safe Number of processes running now: 0
130607 23:10:11 mysqld_safe Number of processes running now: 0
130610 00:01:56 mysqld_safe Number of processes running now: 0
130611 13:45:23 mysqld_safe Number of processes running now: 0
130613 06:50:48 mysqld_safe Number of processes running now: 0
130615 01:00:29 mysqld_safe Number of processes running now: 0
130617 02:15:17 mysqld_safe Number of processes running now: 0
130617 17:07:51 mysqld_safe Number of processes running now: 0
130617 17:11:30 mysqld_safe Number of processes running now: 0
130619 10:22:24 mysqld_safe Number of processes running now: 0
130621 00:01:48 mysqld_safe Number of processes running now: 0
130622 16:27:32 mysqld_safe Number of processes running now: 0
130624 14:48:10 mysqld_safe Number of processes running now: 0
130626 05:55:31 mysqld_safe Number of processes running now: 0
130627 23:03:27 mysqld_safe Number of processes running now: 0
130629 12:08:47 mysqld_safe Number of processes running now: 0
130701 10:40:18 mysqld_safe Number of processes running now: 0
130703 01:01:12 mysqld_safe Number of processes running now: 0
130704 12:20:28 mysqld_safe Number of processes running now: 0
130705 23:22:09 mysqld_safe Number of processes running now: 0
130708 00:01:34 mysqld_safe Number of processes running now: 0
130710 14:20:16 mysqld_safe Number of processes running now: 0
130711 04:50:18 mysqld_safe Number of processes running now: 0
130711 16:40:31 mysqld_safe Number of processes running now: 0

How to repeat:
Uhhh... need help on that.

Suggested fix:
I think this bug exists on all platforms and versions. It just got very annoying with innodb. I had restarts before but I have no logs anymore.

Hi Frederic,  how many of the innodb tables have FULLTEXT indexes?

There is a known and fixed memory consumption bug in innodb fulltext.
Until 5.6.13 is released, I'd suggest this and see if things improve

o) run FLUSH TABLES every x minutes

o) convert all fulltext tables to MyISAM

FLUSH TABLES is releasing some of the memory but I can never return to the amount that was used when mysql was fresh. So even with FLUSH TABLES, memory consumption seems to be growing.

If you gave me that patch for the 5.6.12, I would recompile and check if the bug was gone.

I will try MyISAM later.

MySQL is crashing two times a day - plotted memory usage

Attachment: mysql_memory_usage.png (image/png, text), 60.31 KiB.

Most MySQL Memory Leaks have been fixed with 5.6.13, some smaller ones are still remaining

Attachment: mysql_memory_usage2.png (image/png, text), 90.63 KiB.

It seems memory usage has leveld out

Attachment: mysql_memory_usage3.png (image/png, text), 86.30 KiB.

As of my last measurement, the memory usage has leveled out. I will continue measuring and come back to this bug if there should be a long time upward trend. In the meantime, you might consider this bug resolved.

Let us consider this bug to have been the known innodb Fulltext leak bug.
If any further slower leaks are happening, it is a new bug that needs opening..