MySQL Bugs: #69301: MySQL does not free memory when Application stops executing queries.

Bug #69301	MySQL does not free memory when Application stops executing queries.
Submitted:	22 May 2013 9:19	Modified:	25 Jun 2013 8:07
Reporter:	Ashvini Deshpande	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S5 (Performance)
Version:	mysql-enterprise-5.0.66a-win32	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	memory leak

Description:
Description:
Stress: Memory not freed after query execution is comleted for an application.

When we execute queries, the memory utilization by the mysqld process continually increases.
Each query execution seems to increase the memory load by about 4-6 MB.

Mysql is not releasing memory on system when executing the same query multiple times.

It seems if we keep the application running for 2 or 3 days, it uses a lot of memory which is not freed quickly

How to repeat:
How to repeat:
Setup:
The setup used has 4 gb of RAM and 80 gb of hard disk WiNDOWS OS.

Repro:
1. Check memory utilized by mysqld process using top or system monitor.
2. Create table with blob data type which ocupies 1 gb of memory.
3. Check the memory utilized after table creation and population is complete.
4. Insert lots of data into the database of data type 'BLOB'
5. Execute the select query for multiple times.
6. Check the memory utilized after query exection
7. Stop mysqld service.
8. Check the memory utilized. 

Expected Result:
Memory utilized by mysqld after step 5 should be less than memory utilized after step 8. Alternatively, the memory after executing the query should be 

shared for other applications to use.
Memory should be released quicky after query execution is completed.

Actual Result:
Memory utilized by mysqld after step 5 is more than the memory utilized after step 8. 
It is not released by mysql.After 2 days, memory is released to normal stage.

Additional information
create table Temp(
"field1","tinyint(20) unsigned",
"field1","bigint(20) unsigned",
"field1","smallint(6) unsigned",
"field1","smallint(6) unsigned",
"field1","smallint(6) unsigned",
"field1","smallint(6) unsigned",
"field1","smallint(6) unsigned",
"field1","smallint(6) unsigned",
"field1","tinyint(4) unsigned",
"field1","bigint(20)",
"field1","bigint(20)",
"field1","bigint(20)",
"field1","char(33)",
"field1","char(17)",
"field1","char(33)",
"field1","tinyint(20) unsigned",
"field1","char(33)",
"field1","char(33)",
"field1","tinyint(4) unsigned",
"field1","bigint(20)",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","tinyint(4) unsigned",
"field1","blob"};

This table populated with 50000 records creates a table.
At least 3 tables used with same schema.
It takes more time to insert data one by one.

Hello Ashvini,

Thanks for your report, I can't reproduce your problem, can you please provide more info regarding your environment - my.ini,table schema, sample data as well as periodic outputs of how much the mysqld process consumes over time..

Btw, active development for MySQL Database Server version 5.0 has ended and the reported version MySQL version 5.0.66 is >5 years old and many bugs were fixed since. Any specific reason that you are still using older version? Please upgrade to current version 5.1.xx/5.5.x/5.6.x

Thanks,
Umesh

Hi,
Thanks for your reply.

Our application runs on a computer with 4GB RAM on windows.
Table Schema as posted above.

Sample data:

INSERT IGNORE INTO Temp VALUES (1,1234567,31121,3,1,31121,111,12,0,1111111100,1111111100,0,' ',' ',' ',0,' ',' ',5,900,0,1,0,0,0,0,0,'ÿÿÿÿÿÿÿÿÿÿÿÿÿÿIÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ')

We are using mysql-enterprise-5.0.66a-win32 version as our application is very old. so we do not want to change it.
Mysqlld-nt.exe uses abot 95,000 K memory over a period of time.

Our application uses lots of queries and around more than 5 tables for this application. It took lots of time to release memory automatically.

We need to release the memory quickly, as other modules are dependant on that.
Hope these details will help you.
Thanks once again.

Hello Ashvini,

I can not repeat described behavior with dummy data and default config(I asked for my.ini to you in previous note but seems you missed it). Additionally version 5.0.66a is very old and many bugs were fixed since. please upgrade to current version 5.0.xx, try with it and inform us if you can repeat this memory leak issue.

Also, we do not fix bugs that are not repeatable with current versions.So, atleast try with latest version in 5.0.x series.

I suspect you may be affected by Bug #38693

Thanks,
Umesh