MySQL Bugs: #69525: memory lost control during search

Bug #69525	memory lost control during search
Submitted:	20 Jun 2013 13:59	Modified:	21 Jul 2013 19:19
Reporter:	qinglin zhang (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.61-log	OS:	Linux (2.6.32)
Assigned to:		CPU Architecture:	Any
Tags:	SELECT

Description:
the memory of mysql server use during a simple query can lead to 62G memory usage, which consume too much memory. 
Bufferpool is 10G,sort_buffer_size=32M,query_cache_size = 32M 

How to repeat:
create table t3(id int(11), name varchar(20), address varchar(50), info varchar(50)) engine = innodb;

create table t2(name varchar(20), shopdesc varchar(50), shopaddr varchar(50)) engine = innodb;

insert into t3 records as much of 140000000, 
insert into t2 records as much of 7000000,

when t3 has 140000000 pieces of record , t2 has 7000000 pieces of record
execute query:

select * from t3,t2 where t3.name = t2.name;

then execute:top command and check the  memory mysqld uses

PS:the tables of t2 and t3 have no index or primary key

How large would the result set be with your data set? 

And is it really mysqld that consumes the memory, or is it actually the command line client while buffering the result set?

the result set will be s single record, and the memory mysqld uses is 60G according the  top command, so I think the memory consumed is not used by buffering resultset, but used by the query itself.

Thank you for the report.

But version 5.1.61 is too old. Please try with current version 5.1.70, or, better, 5.6.12 and inform us if problem still exists. Please also send us output of SHOW VARIABLES LIKE '%tmp%'; and SHOW VARIABLES LIKE '%join%'

5.6 has solved problems like this , but 5.1 and 5.5 has the same problems, what should I do , should I try new version?

Thank you for the feedback.

Optimizer in version 5.6 was rewritten and contains a lot of fixes due to nature of new design. So, if this is not repeatable in version 5.6, I suggest you to upgrade.

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".