Bug #8930 Heap tables do not free ram after "rename table".

• Submitted: 3 Mar 2005 19:59
• Modified: 4 May 2005 18:47
• Reporter: Jeff C
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.10
• OS: FreeBSD (Freebsd 4.11-RELEASE)
• CPU Architecture: Any

[3 Mar 2005 19:59] Jeff C

Description:
Mysql holds on to ram after a rename table. 

Example:
   testing (HEAP 200MB)
   newtesting (HEAP 200MB)
 
   rename table testing to testingold, newtesting to testing;
   drop table testingold;

   Now table testing's "show table status" only shows 200MB of ram being used, whereas mysql is holding on to 400MB.   

   Should you throw an "alter table testing engine=heap" into the mix, table testing will still show 200MB under "show table status", whereas mysql will be holding on to 600MB of ram.

(We use very large heap tables.  Where mysql holds on to double the ram needed for heap tables, it crashes the system after swapping until exhausted.  In some cases, triple the ram if you do an alter table <table> engine=heap)

How to repeat:
set global max_heap_table_size = 600*1024*1024;

* find a table with a large Avg_row_length and more than 1M rows

show table status\G

Then: 

create table testing like <your_table_you_chose>;

Run this code 2 times:

create table testing2 like testing; 
alter table testing2 engine=heap;
insert ignore into testing2 select * from <your_table> limit 1000000; 
rename table testing to oldtesting, testing2 to testing; 
drop table oldtesting; 

After running this code twice, look at "top" or "ps" and mysql will be using double the ram than needed, compare it to "show table status like 'testing';"

You may also triple the amount of ram used by adding in another "alter table <your_table> engine=heap" after the insert ignore into testing2.

Suggested fix:
Do not use "rename table" on heap tables.

[4 Mar 2005 0:25] Jeff C

Easier to test...

console:

perl -le 'while($i++<1000000) { print "x" x 200; }' > /tmp/test.db

bug_8930.sql:

use test;
drop table if exists testing;
create table testing (id varchar(200) not null,index(id)) engine=heap;
load data infile '/tmp/test.db' ignore into table testing;
create table testing2 like testing;
alter table testing2 engine=heap;
insert ignore into testing2 select * from testing;
alter table testing2 engine=heap;
rename table testing to testingold, testing2 to testing;
drop table testingold;
create table testing2 like testing;
alter table testing2 engine=heap;
insert ignore into testing2 select * from testing;
alter table testing2 engine=heap;
rename table testing to testingold, testing2 to testing;
drop table testingold;

[4 Mar 2005 0:29] Jeff C

make sure you set max_heap_table_size and use -v -v -v to watch it go on the command line...

[4 May 2005 18:47] Hartmut Holzgraefe

Once memory has been allocated by a mysqld process (or by a process using malloc() in general) it is never returned to the operating system. It's only freed internaly and may be reused by subsequent malloc() calls.

So the actual process size reported by ps or top is the maximum memory ever used by the process, not the current amount of memory actualy being used.

As you have two heap tables in parallel until you drop the first one the memory consumption is twice the table size. On doing an ALTER TABLE the table is internally copied so you end up with a short timeframe where you even have three tables existing
which explains the max memory use reported by ps being even 3 times as big as the actual table.