Description:
After migrating a web forum from Ubuntu 8.04 (MySQL 5.0.96-0ubuntu3) to Ubuntu 12.04 (MySQL 5.5.37-0ubuntu0.12.04.1) I noticed that the memory allocation of MySQL was very high, the daemon grows about 6MB per forum page displayed and it grew to about 13 GB resident size in a day. With MySQL 5.0.96, I never noticed any problems.
The admin of the web forum was able to create a test case, see below. The circumstances of the bug apparently involve a certain kind of correlated subquery and a table with multiple indexes and a particular combination of row data, but the exact cause still remains mysterious to me.
The amount of memory leaked is proportional to the number of rows in table tab1; with 256 rows as created in the test case, it leaks about 1 MB per query.
How to repeat:
mysql -u root -p
create database test;
use test;
create table tab1 (col1 int) engine=MyISAM;
insert into tab1 (col1) values (1);
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
create table tab2 (col1 int, col2 int, key col1 (col1), key col2
(col2)) engine=MyISAM;
insert into tab2 (col1, col2) values (1, 1);
insert into tab2 (col1, col2) values (1, 2);
insert into tab2 (col1, col2) values (2, 1);
# And here's a query that trips the bug:
select curtime() as x from tab1 where (select 1 from tab2 where
col1=tab1.col1 and col2 in (3,4) order by col2 limit 1)=0;
Suggested fix:
Converting the database (or just tab2) to InnoDB seems to help, but I'm not sure how reliable that is as a workaround.