Bug #27560 Memory usage of mysqld grows while doing nothing
Submitted: 31 Mar 2007 11:54 Modified: 12 Jul 2007 13:33
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.0-bk, 5.1-bk OS:Linux (Linux)
Assigned to: Kristian Nielsen
Tags: Memory, mysqld, ndb, sr5_1

[31 Mar 2007 11:54] Geert Vanderkelen
Description:
Starting up a cluster, when opening a connection to the MySQL server using the mysql CLI (without the -A option) the mysqld will continuously grow in.

Timestamp       | User PID   VSZ    RSZ
------------------------------------------------
# Start mysqld
20070331T115615 | 8930 10255 316596 188328
20070331T115645 | 8930 10255 316596 188328
20070331T115715 | 8930 10255 316596 188328
# Connect with mysql CLI with -A
20070331T115745 | 8930 10255 317000 188740
20070331T115815 | 8930 10255 317000 188740
20070331T115845 | 8930 10255 317000 188756
20070331T115915 | 8930 10255 317000 188756
# Doing a SHOW TABLES
20070331T115945 | 8930 10255 317112 189184
20070331T120015 | 8930 10255 317112 189184
20070331T120045 | 8930 10255 317112 189184
20070331T120115 | 8930 10255 317112 189184
20070331T120145 | 8930 10255 317112 189200
# Start
20070331T120240 | 8930 10404 316596 188328
20070331T120310 | 8930 10404 316596 188328
20070331T120340 | 8930 10404 316596 188328
20070331T120410 | 8930 10404 316596 188328
# Connect with mysql CLI without -A and close
20070331T120440 | 8930 10404 319424 191848
20070331T120510 | 8930 10404 320448 193376
20070331T120540 | 8930 10404 320448 194772
20070331T120610 | 8930 10404 321472 196052
20070331T120640 | 8930 10404 321472 197276
20070331T120710 | 8930 10404 322496 198456
20070331T120740 | 8930 10404 322496 199620
20070331T120810 | 8930 10404 323520 200740
# Connect with mysql CLI without -A and close
20070331T120840 | 8930 10404 323520 201972
20070331T120910 | 8930 10404 323784 202864
20070331T120940 | 8930 10404 324180 203840
20070331T121010 | 8930 10404 324444 204392
20070331T121040 | 8930 10404 324840 204760
20070331T121110 | 8930 10404 325236 205120
20070331T121210 | 8930 10404 325896 205824
20070331T121240 | 8930 10404 326292 206156

How to repeat:
- Using 5.0.30
- My setup was a 4 data node cluster (config.ini attached)
- MySQL started, connected to cluster
- use mysql CLI to connect to a database having quite some tables (had 70 of them, schema available if needed..). Maybe number of tables is not really important.
  shell> mysql -uroot dbname
  mysql> -- do absolutely nothing
  msyql> exit

Now watch the mysqld memory usage grow.
[31 Mar 2007 11:57] Geert Vanderkelen
Cluster config.ini

Attachment: bug27560_config.ini (text/plain), 1.36 KiB.

[31 Mar 2007 11:59] Geert Vanderkelen
Verified using 5.0.30.

Also with 5.0.36 though it didn't grow that much.
[2 Apr 2007 11:07] Kristian Nielsen
Managed to reproduce it as described (running on a single machine and with reduced
memory parameters in config.ini).

The following non-standard options in my.cnf seems to be needed to be able to
see the leak:

ndb-use-exact-count=0
ndb-force-send=1
ndb-cache-check-time=200
[2 Apr 2007 13:08] Kristian Nielsen
Here is how to reproduce this from a recent bitkeeper source build tree:

Create an empty test case ndb_kn.test:

    -- source include/have_ndb.inc
    -- source include/not_embedded.inc

And create an options file ndb_kn-master.opt:

    --ndb-use-exact-count=0 --ndb-force-send=1 --ndb-cache-check-time=200

Then start the server/cluster:

    perl mysql-test-run.pl --start-and-exit ndb_kn

Connect and create some tables:

    client/mysql --socket=mysql-test/var/tmp/master.sock -uroot test
    mysql> CREATE TABLE t1(a INT, PRIMARY KEY USING hash(a)) ENGINE=ndb;
    ...
    mysql> CREATE TABLE t49(a INT, PRIMARY KEY USING hash(a)) ENGINE=ndb;
    mysql> quit

Connect again, exit immediately:

    client/mysql --socket=mysql-test/var/tmp/master.sock -uroot test
    mysql> quit

Now the growing process can be observed with this:

    while(true); do DATA=`ps --no-headers -p <MYSQLD PID> -o euser,pid,vsz,rsz`; DT=`date +%Y%m%dT%H%M%S`; echo "$DT | $DATA"; sleep 5; done
[2 Apr 2007 13:54] Bugs System
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/23551

ChangeSet@1.2428, 2007-04-02 15:50:57+02:00, knielsen@ymer.(none) +1 -0
  BUG#27560: Memory usage of mysqld grows while doing nothing
  
  The query-cache watch thread was continually allocating new thread entries on the
  THD MEM_ROOT, not freed until server exit.
  
  Fixed by using a simple array, auto-expanded as necessary.
[2 Apr 2007 16:07] Bugs System
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/23561

ChangeSet@1.2401, 2007-04-02 18:21:05+02:00, tomas@whalegate.ndb.mysql.com +1 -0
  BUG#27560: Memory usage of mysqld grows while doing nothing
    
  The query-cache watch thread was continually allocating new thread entries on the
  THD MEM_ROOT, not freed until server exit.
    
  Fixed by using a simple array, auto-expanded as necessary.
[4 Apr 2007 15:26] Bugs System
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/23814

ChangeSet@1.2496, 2007-04-04 17:26:30+02:00, jbruehe@mysql.com +2 -0
  BUG#27560: Memory usage of mysqld grows while doing nothing
  
  The query-cache watch thread was continually allocating new thread entries on the
  THD MEM_ROOT, not freed until server exit.
  
  Fixed by using a simple array, auto-expanded as necessary.
  
  (Originally done by Tomas on 2007-04-02 18:21:05+02:00;
  applied to the custom build BR#14007 by Joerg)
[7 Apr 2007 7:00] Bugs System
Pushed into 5.0.40
[7 Apr 2007 7:00] Bugs System
Pushed into 5.1.18-beta
[10 Apr 2007 6:37] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bug fix in 5.0.40, 5.1.18, telco-6.1.7, and telco-6.2.1 changelogs.
[11 Jul 2007 9:20] Geert Vanderkelen
Re-opening as bug pops up again.

Using 5.0.44 data nodes, I'm able to reproduce the problem using versions 5.0.44, 5.0.40, 5.0.42 and 5.0bk on the SQL node.
Also, able to reproduce with all nodes being 5.0.44.

Memory and CPU usage go high doing absolutely nothing but change to a database with NDB tables.
[12 Jul 2007 13:33] Geert Vanderkelen
It's expected that memory and CPU is growing having query cache on and --ndb-cache-check-time pretty low.
What happens is that each tables needs to be checked if the data in the query cache is still valid or not. These are round trips to the cluster. If the check time is low, this happens quite a lot. When there are lots of NDB tables, this can show.

Re-closing.