MySQL Bugs: #72885: MySQL 5.6 memory leak

Bug #72885	MySQL 5.6 memory leak
Submitted:	5 Jun 2014 7:50	Modified:	1 Apr 2015 10:29
Reporter:	Ivan Stoykov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.6.17	OS:	Linux (Red Hat Enterprise Linux Server release 6.3)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	repository=table

Description:
Hello guys, 

There is a case of MySQL 5.6.17 mysql-5.6.17-linux-glibc2.5-x86_64
based replication, with the following settings:
==================================================
master_info_repository = 'TABLE';
relay_log_info_repository = 'TABLE';
slave_parallel_workers = 0 ;
sync_master_info = 1;
sync_relay_log_info = 1;
==================================================

The slave does not release the memory used, even after the SLAVE SQL thread has been stopped, same with restarting the slave as STOP SLAVE; START SLAVE;
===============================================
mysql> STOP SLAVE SQL_THREAD ;
mysql> SELECT SLEEP(5);
mysql> START SLAVE SQL_THREAD;

-----------memory----------
swpd free inact active
0 352768 5202008 26866196
0 344816 5201664 26867924
0 344196 5201352 26869540
0 341096 5201244 26870676
0 341212 5201256 26871592

0 339476 5202368 26872588 <-- stop
0 338624 5202788 26873560 <-- sleep 1
0 338372 5202700 26874900 <-- sleep 2
0 338372 5202744 26875936 <-- sleep 3
0 336884 5202644 26876816 <-- sleep 4
0 334396 5202644 26878228 <-- sleep 5
0 333528 5202012 26880108
0 333040 5202012 26881236 <-- finish

0 332296 5202820 26882892
0 342420 5203756 26878820
===============================================

The memory usage grow very slowly. not suddenly.

After changing the the master_info_repository and relay_log_info_repository to 'FILE', the memory issue was gone.

How to repeat:
MySQL config
==================================================
master_info_repository = 'TABLE';
relay_log_info_repository = 'TABLE';
slave_parallel_workers = 0 ;
sync_master_info = 1;
sync_relay_log_info = 1;
==================================================

master slave  version mysql-5.6.17-linux-glibc2.5-x86_64

two parallel sysbench tests run as :
./sysbench --test=tests/db/oltp.lua --mysql-user=root --mysql-db=test --mysql-table-engine=innodb --mysql-ignore-duplicates=on --num-threads=16 --oltp-table-size=20000000 --oltp-read-only=off --oltp-test-mode=complex --max-requests=0 --report-interval=5 --max-time=43200 run
./sysbench --test=tests/db/oltp.lua --mysql-user=root --mysql-db=sysb2 --mysql-table-engine=innodb --mysql-ignore-duplicates=on --num-threads=16 --oltp-table-size=20000000 --oltp-read-only=off --oltp-test-mode=complex --max-requests=0 --report-interval=5 --max-time=43200 run

Suggested fix:
Check the fix of the #71197 as that bug seems similar, even in our case the workers are set to 0

The original bug was reported for MySQL 5.6.17; I was able to reproduce it 
in MySQL 5.6.20.

I got to a point where I had to try something on my production servers because of the memory usage, and I just did a stop and start of replication. Memory wasn't instantly released, but memory usage has been trickling down ever since.

Hi,

Please see if memory leaks after changing the slave parameter.  In my case it stopped leaking.

SET GLOBAL sync_master_info=0;

Also, a simpler testcase, run this on master:

drop table if exists t1;
drop view if exists t1;
create table t1(a int auto_increment primary key, b int, c int) engine=innodb;
drop procedure if exists p1;
delimiter $
create procedure p1()
begin
  set @a:=1;
  repeat
    start transaction;
    insert into t1(b,c) values (@a:=@a+1,@a);
	delete from t1 where a=@a-4;
	commit;
  until 1=2 end repeat;
end $

delimiter ;

call p1();

flush_master_info calls and leaks. heap profile.

Attachment: mybin.hprof.3277.heap.pdf (application/pdf, text), 8.51 KiB.

My version is 5.6.21, We are seeing the leak when 

relay-log-info-repository = TABLE 
master-info-repository = TABLE 
sync-master-info = 1 

We changed to 
relay-log-info-repository = FILE 
master-info-repository = FILE 

The situation is improved. seems this is releated to 
relay-log-info-repository = TABLE 
master-info-repository = TABLE 
no matter RBR OR SBR

This bug is marked as duplicate of bug#69848

This is fixed in 5.6.25 where the release note at http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-25.html describes the fix as:

"Replication: When master_info_repository=TABLE the receiver thread stores received event information in a table. The memory used in the process of updating the table was not being freed correctly and this could lead to an out of memory error. The fix ensures that after an event is flushed to the relay log file by a receiver thread, the memory used is freed. (Bug #72885, Bug #19390463, Bug #69848, Bug #20124342)"

James Day, MySQL Senior Principal Support Engineer, Oracle