Bug #47991 InnoDB Dictionary Cache memory usage increases indefinitely when renaming tables

• Submitted: 12 Oct 2009 10:29
• Modified: 14 Oct 2010 13:40
• Reporter: Brice Figureau
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.77, 5.1.31, 5.1.39, 5.1.41-bzr
• OS: Any
• Assigned to: Vasil Dimov
• CPU Architecture: Any
• Tags: dictionnary cache, innodb, Memory

[12 Oct 2009 10:29] Brice Figureau

Description:
I'm running a process that computes player ranks for an online game every 10s.
It does this by:
1) populating player ranks into the rank_tmp table
2) renaming rank to rank_old, rank_tmp to rank and rank_old to rank_tmp
3) wait 10s
4) go to 1.

The memory used by the mysql server process keeps increasing and is never freed. Ultimately I have to restart the mysql server every weeks, or it is killed by the kernel OOM.

How to repeat:
Please run the attached test.pl file against a mysql server:

$ perl test.pl -u root -h localhost -d test

This script mimics what is done by my ranking computation system.

Wait for a couple of minutes, it should print something like this:
Dictionnary Cache mem increased since last run: 8112

Watch the Dictionary Cache additional memory increasing, and the global memory increasing.

Suggested fix:
In innobase/dict/dict0dict.c when we rename a table we strdup the old name and the new name in the current table heap. 

Since my process never drops the tables, the heap can only grow and the memory is never freed and about every minutes or so (depending on the move frequency and table name length), a new block of size 8112 bytes is added to the table heap.

Of course, one possible work-around in my code would be to drop the tables instead of renaming them, but rename has some good properties (ie rename being atomic, performance is good ...).

My suggestion would be:
 * to check that we can't reuse the memory of the old name to store the new name
 * or that the table heap supports freeing memory
 * or that the table name is not stored in the table heap but is allocated with malloc and freed upon rename.

[12 Oct 2009 10:30] Brice Figureau

Executable mimicing the sql load that generate the bug.

Attachment: test.pl (text/x-perl), 3.30 KiB.

[16 Oct 2009 5:16] MySQL Verification Team

here's a simple testcase. show innodb status will prove that 'Dictionary memory allocated' keeps increasing.

delimiter ;
drop database if exists test;
create database test;
use test;
create table t0(a int)engine=innodb;
drop procedure if exists p1;
delimiter $

create procedure p1(num int)
begin
  declare i int default '0';
  repeat
    set @sql=concat("rename table t",i," to t",(i+1));
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
    if(i mod 100 =0) then 
      select concat("renamed ",i) as status;
    end if;  
    set i:=i+1;
  until i>num end repeat;
end $

delimiter ;

call p1(1000000);

[16 Oct 2009 5:32] Valeriy Kravchuk

Verified just as described b Shane with recent 5.1.41 from bzr on Mac OS X.

[16 Oct 2009 5:42] Valeriy Kravchuk

After 100000 renames:

...
+----------------+
| status         |
+----------------+
| renamed 100000 |
+----------------+
1 row in set (10 min 45.87 sec)

Query OK, 0 rows affected (10 min 45.87 sec)

I've got:

Dictionary memory allocated 3257544

while initially it was:

Dictionary memory allocated 27208. So, we have a "leak" of 32 bytes or so per rename.

[16 Oct 2009 13:43] MySQL Verification Team

I ran the same code for 1M renames and got 32.4 bytes/rename. The amount leaked looks linear.

[16 Oct 2009 13:58] Brice Figureau

The size of the leak per rename completely depends on the length of the new and old names of the tables. Make your tables longer, and you'll leak faster.
The leak is in incremement of 8112 bytes on my system (which certainly correspond to a heap block, see below).

See in dict0dict.c, in dict_table_rename_in_cache, in my 5.0.77 version around line 1012 the code does:

old_name = mem_heap_strdup(table->heap, table->name);
table->name = mem_heap_strdup(table->heap, new_name);

Basically the code allocates two brand new strings from the table heap to store the old and new name. We can certainly ask why it allocates the old name since it is already in the heap, but that's another story (I think this was fixed in 5.1).

Unfortunately, the heap is never freed (because that's the whole principle of it to be fast), except when you drop the table. So this isn't a memory leak per-se. The heap is a simple chained block allocator, where nothing can be freed. As soon a block is full, a new block is malloc'ed.

I don't see an immediate fix, but it should at least be possible to:
 * not duplicate the old_name
 * fit the new name in the old_name space if strlen(new_name) <= strlen(old_name)
 * state this in the documentation.

[22 Jun 2010 16:33] 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/111791

3517 Vasil Dimov	2010-06-22
      Fix Bug#47991 InnoDB Dictionary Cache memory usage increases indefinitely
      when renaming tables
      
      Allocate the table name using ut_malloc() instead of table->heap because
      the latter cannot be freed.
      
      Adjust dict_sys->size calculations all over the code.
      
      Change dict_table_t::name from const char* to char* because we need to
      ut_malloc()/ut_free() it.
      
      Reviewed by:	Inaam, Marko, Heikki (rb://384)
      Approved by:	Heikki (rb://384)

[22 Jun 2010 17:05] 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/111793

3518 Vasil Dimov	2010-06-22
      Add ChangeLog entry for the fix of Bug#47991

[23 Jun 2010 7:20] 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/111858

3114 Vasil Dimov	2010-06-23
      Merge Bug#47991 fix from mysql-5.1-innodb
      
        ------------------------------------------------------------
        revno: 3517
        revision-id: [e-mail]-20100622163043-dc0lxy0byg74viet
        parent: [e-mail]-20100621095148-8g73k8k68dpj080u
        committer: Vasil Dimov <[e-mail]>
        branch nick: mysql-5.1-innodb
        timestamp: Tue 2010-06-22 19:30:43 +0300
        message:
          Fix Bug#47991 InnoDB Dictionary Cache memory usage increases indefinitely
          when renaming tables
          
          Allocate the table name using ut_malloc() instead of table->heap because
          the latter cannot be freed.
          
          Adjust dict_sys->size calculations all over the code.
          
          Change dict_table_t::name from const char* to char* because we need to
          ut_malloc()/ut_free() it.
          
          Reviewed by:	Inaam, Marko, Heikki (rb://384)
          Approved by:	Heikki (rb://384)
        ------------------------------------------------------------

[19 Jul 2010 14:33] Bugs System

Pushed into 5.1.49 (revid:[e-mail]-20100719143034-omcma40sblwmay3x) (version source revid:[e-mail]-20100704071244-3lo4okzels3kvy1p) (merge vers: 5.1.49) (pib:16)

[20 Jul 2010 22:25] John Russell

Added this text to the 5.1.49 change log:

Performing large numbers of RENAME TABLE statements caused excessive
memory use.

[23 Jul 2010 12:27] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (version source revid:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (merge vers: 5.5.6-m3) (pib:18)

[23 Jul 2010 12:34] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100723121929-90e9zemk3jkr2ocy) (version source revid:[e-mail]-20100723121827-3bsh51m5sj6g4oma) (pib:18)

[4 Aug 2010 7:51] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100624104620-pklunowaigv7quu9) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:09] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100624104620-pklunowaigv7quu9) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:25] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100624104620-pklunowaigv7quu9) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:04] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100624104620-pklunowaigv7quu9) (pib:20)

[4 Aug 2010 22:58] Paul DuBois

Noted in 5.5.6 changelog.
Bug does not appear in any released 5.6.x version.

[14 Oct 2010 8:38] Bugs System

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

[14 Oct 2010 8:53] Bugs System

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

[14 Oct 2010 9:10] Bugs System

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

[14 Oct 2010 13:40] Jon Stephens

Already documented in the 5.1.49 changelog; no additional changelog entries required. Set back to Closed state.