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:Microsoft Windows (windows server 2003)
Assigned to: Assigned Account CPU Architecture:Any

[12 Jun 2006 12:14] PJ Ninnim
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.
[12 Jun 2006 12:15] PJ Ninnim
my.ini attached for server B

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

[12 Jun 2006 12:17] PJ Ninnim
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.
[13 Jun 2006 14:40] Sveta Smirnova
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?
[13 Jun 2006 15:14] PJ Ninnim
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.
[15 Jun 2006 20:35] Sveta Smirnova
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
[16 Jun 2006 7:40] PJ Ninnim
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?
[19 Jun 2006 6:42] Sveta Smirnova
>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.
[23 Jun 2006 8:34] PJ Ninnim
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.
[23 Jun 2006 8:58] Sveta Smirnova
> 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.
[23 Jul 2006 23:00] Bugs System
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".