MySQL Bugs: #36701: memory leak in mysqld when executing simple join on clustered tables

Bug #36701	memory leak in mysqld when executing simple join on clustered tables
Submitted:	13 May 2008 21:34	Modified:	14 Sep 2009 9:58
Reporter:	Yong Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	mysql-5.0	OS:	Linux (redhat es/ws 4.0)
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	5.0.45, 5.0.51a

Description:
not too sure if this is with mysqld or with ndbd but the problem is that mysqld starts to eat up ram as seen through top.  The resident memory size continues to grow and eventually slows down all processing on the box.  On our production servers, mysql eats up about 11gb before it has to be manually killed.

Note that mysql never relinquishes all of the RAM it takes up, even after the connection is dropped.

I've confirmed this problem on a 5.0.45 32 bit system (redhat ws 4) and on redhat es 5.0 64 bit systems.  The problem is consistent and i have managed to recreate this.  I've also confirmed this problem on a 5.0.51a system.

i've also created this problem on systems where mysql and ndbd live on the same server and in cases where they live on different servers.

How to repeat:
run top and filter for mysql to see just the mysql process.  

create the following temporary tables and load in about 600 000 records into them.  I've found that there appears to be a minimum size associated with the table structure for the problem to occur.  I am not too sure if the text column is important or not :

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `f1` varchar(100) default NULL,
  `did` int(11) default NULL,
  `txt_field` text,
  PRIMARY KEY  (`id`),
  KEY `did_dex` (`did`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `f1` varchar(100) default NULL,
  `did` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `did_dex` (`did`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

ensure that some records in t2 have did = 3, eg:
update t2 set did=3 limit 50000;

now, restart mysql (to see the problem better).

while monitoring top, run the sql query :

select t1.* from t1 left join t2 on t1.f1 = t2.f1 and t2.did=3 order by t1.f1 desc;

and watch the resident memory size of mysql start to go up.

doing this with bigger tables we can see mysql growing much faster.

the key seems to be the t2.did=3 statment in the join clause.  It doesn't seem to matter if it is an outer join or not.

Suggested fix:
no idea.  just wish it would stop leaking.

it looks like the text/blob field is required in the select portion of the statement to create the problem.

Thank you for the report.

I beieve this is because large join, not result of a bug.

Please provide output of EXPLAIN EXTENDED select t1.* from t1 left join t2 on t1.f1 = t2.f1 and t2.did=3 order by t1.f1 desc; and SHOW WARNINGS issued right after this query and your cluster and mysqld configuration files.

also note that the exact same query run against myisam tables do not have any impact on the virutal or resident memory sizes (ie: i created a copy of the tables using the same create table syntax but with myisam storage engines and then ran the same query, monitoring memory through top).

This problem seems specific to joins involving ndbcluster tables.

i'll add in some more comments i sent to Martin on the cluster list.  I find this problem is very easy to recreate and it is happening consistently on our systems causing us a lot of grief:

first create the test dbs:

use test;
CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `f1` varchar(100) default NULL,
  `did` int(11) default NULL,
  `txt_field` text,
  PRIMARY KEY  (`id`),
  KEY `dex1` (`did`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

 CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `f1` varchar(100) default NULL,
  `did` int(11) default NULL,
  `txt_field` text,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

I then created a simple perl script to load in both tables with 600000 records:
=================================================================================
#!/usr/bin/perl

use DBI;
$user = 'test';
$pwd = 'test';

# Used to generate test
$dbi = DBI->connect("DBI:mysql:database=test;host=localhost", $user, $pwd, {PrintError => 1, AutoCommit => 1}); if (!$dbi) {
  print "Couldn't connect\n";
  exit;
}

$i = 0;
my $sql = '';
print "Doing inserts\n";
while ($i <= 600000) {
  print "inserted $i\n" if $i % 100000 == 0;
  $sql = "insert into t1 (id, f1, did) values (null, 'testing testing', 3)";
  if (!$dbi->do($sql)) {
    print "Problems doing insert on t1\n";
    die $dbh->errstr . "\n";
  }
  $sql = "insert into t2 (id, f1, did) values (null, 'testing testing', 3)";
  if (!$dbi->do($sql)) {
    print "Problems doing insert on t2\n";
    die $dbh->errstr . "\n";
  }
  $i++;
}

======================================================================================
I ran the script to load up my test tables (yah, it's a brute force script that takes a while to run).

Then to generate the problem, just do the following on the server running an api node (use 2 windows) :

in window 1 run :

	top

Then monitor the mysql processes

in window 2 run :

mysql -u test -ptest

use test;
select t1.*, t2.* from t1 left join t2 on t1.id=t2.id and t1.did=2;

Then watch in the top window as the resident memory size of the mysqld process keeps on increasing.  I've redid this test after restaring api and ndb nodes as well as restarting the server.

Using MySQL Cluster 6.2 from bazaar, (I know this report is about MySQL 5.0..), everything seems to be normal.

Doing the join, one sees indeed the memory usage increasing. This is perfectly normal as a join requires all data to be buffered, in memory.
Exiting the session, releases the memory again.

(timestamp ; userid; pid; vsz; rsz; pcpu)
20080722T155341 | 8930     18278 147512 39876 39.5
20080722T155343 | 8930     18278 147512 39876 39.5
20080722T155345 | 8930     18278 147512 39876 39.5
20080722T155347 | 8930     18278 147512 39876 39.4
# starting the join
20080722T155349 | 8930     18278 148040 40352 39.4
20080722T155351 | 8930     18278 150284 42536 39.5
20080722T155353 | 8930     18278 153056 45372 39.5
# join has done, result returned.
20080722T155629 | 8930     18278 331100 223348 41.8
20080722T155632 | 8930     18278 331496 223844 41.9
20080722T155634 | 8930     18278 331496 223844 41.9
20080722T155636 | 8930     18278 331496 223844 41.9
20080722T155638 | 8930     18278 331496 223844 41.8
..
| 24439 | testing testing |    3 | NULL      | NULL | NULL | NULL | NULL      |
|  5755 | testing testing |    3 | NULL      | NULL | NULL | NULL | NULL      |
+-------+-----------------+------+-----------+------+------+------+-----------+
41888 rows in set (2 min 55.77 sec)

20080722T155716 | 8930     18278 331496 223844 41.3
20080722T155718 | 8930     18278 331496 223844 41.3
# we exit the session
20080722T155720 | 8930     18278 147512 39876 41.3
20080722T155722 | 8930     18278 147512 39876 41.3
# memory cleaned up

is the memory not returned as long as the session is open ?  If the same query is run, but say with slightly different where clause, will it just claim more memory rather than re-using the memory it has claimed ?  For memory requirements, does it need to hold the complete cartesian product in memory ?

in production where we use a long lived connection, we're seeing memory being claimed and not released.  After it gets to a certain point, it just starts claiming more memory very quickly and then becomes unusable.  In the test case that i created, i found that there appears to be some threshold that has to be crossed.  The behaviour is normal for smaller data sets and i needed to ensure that the test tables had enough records in them to create the problem.

Is this a production system already running on 5.0.
Otherwise we recommend to use a later 5.1 release.

on our test system, i've updated to 5.1 (mysql cluster 6.2.15-0). The problem is still there and it is very easily recreated.

Using the test tables i provided, the resident ram goes up n keeps going up.  After i stop the query n disconnect the session, the ram is never recovered by the process.  I think it has something to do with the join involving text fields which is causing the issue.  On our production system we have a table holding 2 million rows with 2 text fields.  Any join involving this table where the text fields are part of the select statement will cause mysql to leak.

if u need any more info from me, please let me know.

also note that there seems to be some limit/threshold that has to be crossed for the leaks to occur.  I know in Geert's test he used ~40k rows and this was okay, but the problem appears to be associated with a query involving a large number of rows in the order of a few 100 k (in my test case) (or a couple of million in our production case).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77767

2952 Martin Skold	2009-07-02
      Bug#36701 memory leak in mysqld when executing simple join on clustered tables
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp

I'd like to add to this. Running on MySQL 5.0, and came across the same problem. I do a lot of inserts and memory is eaten up in minutes.

I use only VARCHAR and INTEGER columns.

Any help? This is very frustrating. I have to find a completely different storage medium just for these inserts because of this bug.

Would also like to specify, I am NOT using JOINs. Just simple Insert queries. Connection / query resultset is closed on each insert.

Matt, FYI:

We generally recommend that anyone thinking of using MySQL Cluster with MySQL 5.0 should use the latest MySQL Cluster NDB 6.3 or NDB 7.0 release instead. You may find that your issues have already been fixed in these newer releases.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82559

2972 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82564

3035 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82566

2988 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82568

2987 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82669

2985 Martin Skold	2009-09-08
      Bug#36701 memory leak in mysqld when executing simple join on clustered tables
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82673

3036 Martin Skold	2009-09-08 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_restore.result
        mysql-test/suite/ndb/t/ndb_restore.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82680

2989 Martin Skold	2009-09-08 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/t/ndb_restore.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82684

2988 Martin Skold	2009-09-08 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/t/ndb_restore.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        storage/ndb/include/ndbapi/NdbBlob.hpp
        storage/ndb/src/ndbapi/NdbBlob.cpp
        storage/ndb/tools/restore/Restore.cpp

Pushed into 5.1.35-ndb-7.1.0 (revid:martin.skold@mysql.com-20090908124652-0bd68u3ku34xqg8f) (version source revid:martin.skold@mysql.com-20090908124652-0bd68u3ku34xqg8f) (merge vers: 5.1.35-ndb-7.1.0) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:martin.skold@mysql.com-20090908113951-flbnamy45l5dscw1) (version source revid:martin.skold@mysql.com-20090908112440-q5msan771s6z8xzm) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:martin.skold@mysql.com-20090908121053-yst2g625jf8v1kms) (version source revid:martin.skold@mysql.com-20090908121053-yst2g625jf8v1kms) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:martin.skold@mysql.com-20090908112403-4md7hwxrp8rm2qx4) (version source revid:martin.skold@mysql.com-20090908110248-eykk666ykkixgy0r) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Documented bugfix in the NDB-6.2.19, 6.3.27, and 7.0.8 changelogs, as follows:

        Some joins on large NDB tables having TEXT or BLOB columns
        could cause mysqld processes to leak memory. The joins did not
        need to reference the TEXT or BLOB columns directly for this
        issue to occur.