MySQL Bugs: #20391: possible memory leak with replication

Bug #20391	possible memory leak with replication
Submitted:	12 Jun 2006 12:14	Modified:	23 Jul 2006 8:58
Reporter:	PJ Ninnim	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Windows (windows server 2003)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using replication in the 'A -> B -> C -> A' setup our servers loose available memory which is only recoverable after a restart of the machine(s). This was also the case with version 4.1 so we upgraded to 5 in the hope that the problem would be cured.

How to repeat:
Setup replication with 'A -> B -> C -> A'  or 'A -> B -> A' setup.

my.ini attached for server B

Attachment: my.ini (application/octet-stream, text), 9.23 KiB.

I forgot to mention that you have to run this with two quite heavy load database over 24/48hrs to see the memory loss occuring.

Thank you for the report. But we need in additional information.

How much physical and virtual memory are on your machine?
How large the database?
A and B servers are on the different machines?
Is your server x64 or x86?

Thank you for your reply.

Physical: 1047832K
Virtual: 1536MB
All machines are identical: 2.80Ghz - x86 - Windows Server 2003 Standard - 140GB Hard Disk

A list of our database statistics are below:

Database,     Collation,        Tables,    Data,     Indexes,       Total       
**********,   utf8_general_ci,     3,     6.4  KB,   19.0  KB,   25.4  KB   
**********,   utf8_general_ci,    23,     6.5  MB,   87.0  KB,    6.5  MB    
**********,   latin1_swedish_ci,   7,     6.8  KB,   18.0  KB,   24.8  KB    
**********,   utf8_general_ci,     3,    70.3  KB,    6.0  KB,   76.3  KB    
**********,   utf8_general_ci,     2,    11.5  KB,   10.0  KB,   21.5  KB    
**********,   latin1_swedish_ci,   2,   232.3  KB,    9.0  KB,  241.3  KB    
**********,   latin1_swedish_ci,   8,     5.7  MB,  108.0  KB,    5.8  MB    
**********,   latin1_swedish_ci,   8,     5.8  MB,  100.0  KB,    5.9  MB    
**********,   latin1_swedish_ci,   7,   299.7  KB,   18.0  KB,  317.7  KB    
**********,   utf8_general_ci,    16,    0  Bytes,    4.0  KB,    4.0  KB    
**********,   utf8_general_ci,     9,    26.9  KB,   35.0  KB,   61.9  KB    
**********,   latin1_swedish_ci,  15,   203.6  KB,   57.0  KB,  260.6  KB    
**********,   utf8_general_ci,    10,   284.2  MB,  233.6  MB,  517.8  MB    
**********,   latin1_swedish_ci,  19,   131.8  KB,   55.0  KB,  186.8  KB    
**********,   latin1_swedish_ci,  33,   366.6  MB,  601.4  MB,    0.9  GB    
**********,   latin1_swedish_ci,  33,   359.1  MB,  590.4  MB,  949.5  MB    
**********,   latin1_swedish_ci,  14,    22.1  MB,    4.5  MB,   26.6  MB    
**********,   latin1_swedish_ci,  14,    22.1  MB,    4.5  MB,   26.6  MB    
**********,   latin1_swedish_ci,  13,    23.3  MB,   10.1  MB,   33.3  MB    
**********,   latin1_swedish_ci,  13,    22.7  MB,    9.8  MB,   32.6  MB    
**********,   latin1_swedish_ci,   0,    0  Bytes,   0  Bytes,   0  Bytes    
**********,   latin1_swedish_ci,  30,   382.3  KB,  129.0  KB,   511.3  KB    
Total: 22,                               , 282,     1.1  GB,    1.4  GB,     2.5  GB 

I hope this helps.

Please, provide content of binlogs, created at the moment you notice possible memory leak. You can read how to do it here: http://dev.mysql.com/doc/refman/5.0/en/replication-bugs.html

I would provide you binlogs when this starts occuring but the problem is that I do not know when it starts. It seems to do something over-night... the memory available is fine all day, you then go in the next day and you will find that you have lost up to 200MB of memory. Should I setup the binlogs anyway and just start them off when the system starts?

>Should I setup the binlogs anyway and just start them off when the system starts?
Binlogs already have turned on in your my.ini file.
>the problem is that I do not know when it starts
You can catch it using Windows built-in tools. For example, replace in below script path to log and server name, save it as get_mem_usage.bat and run:
@echo off
REM log file
set OUTPUT="path\to\get_mem_usage.log"

if "%STDOUT_REDIRECTED%" == "" (
    set STDOUT_REDIRECTED=yes
    cmd.exe /c %0 %* >%OUTPUT%
    exit /b %ERRORLEVEL%
)

:LOOP1
	echo %TIME%
REM path to server
	tasklist /fi "Imagename eq mysqld.exe" /nh /fo CSV
REM wait one minute
	sleep 60
goto LOOP1

When you will notice lost of memory, open log file and find accurate time when memory lost occured. Then separate in binlogs parts happen in this time and send they to us.

Will be good if you can start server with --debug option and log stack trace too. Please, read howto here: http://dev.mysql.com/doc/refman/5.0/en/making-trace-files.html If you will do it, you can separate part of trace log when memory lost occurs by searching queries sended to server which you already found in binlogs.

Thanks for supplying the script, by using this I managed to locate the query that was causing the error and resolve it. Everything is now working fine, thank you for all your help.

> I managed to locate the query that was causing the error and resolve it.
If query causes repeatable memory leak it can be bug. So, please, provide us this query and structure of all tables, views and custom functions used in this query.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".