MySQL Bugs: #47991: InnoDB Dictionary Cache memory usage increases indefinitely when renaming tables

Bug #47991	InnoDB Dictionary Cache memory usage increases indefinitely when renaming tables
Submitted:	12 Oct 12:29	Modified:	16 Oct 15:58
Reporter:	Brice Figureau
Status:	Verified
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.0.77, 5.1.31, 5.1.39, 5.1.41-bzr	OS:	Any
Assigned to:	Vasil Dimov	Target Version:	5.0+
Tags:	Memory, innodb, dictionnary cache
Triage:	Triaged: D2 (Serious)

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.

Executable mimicing the sql load that generate the bug.

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

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);

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

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.

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

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.