MySQL Bugs: #26773: Possible Memory Leak with Archive Tables

Bug #26773	Possible Memory Leak with Archive Tables
Submitted:	1 Mar 2007 23:16	Modified:	19 Nov 2007 15:36
Reporter:	Chris Coyle
Status:	Analyzing
Category:	Server: Archive	Severity:	S1 (Critical)
Version:	5.0.27-max	OS:	Linux (Debian 3.1 (kernel 2.6.14.2))
Assigned to:	Shane Bester	Target Version:

Description:
Possible memory leak with Archive Engine Tables and large datasets spanning mulitple
tables.

When doing a mass import of table data across multiple tables with the archive engine
memory allocation continues to increase until exhuasting memory and crashing.

When it crashes, tables are damaged and missing chunks of data, even when using the
--flush arg with mysqld.  This makes me think that some portion of the buffer isn't ever
being sync'd to disk and deallocated.

How to repeat:
## Add command args as necessary for your setup ##

 - create a scratch database (for this example "test_archive")

 - Find a database with a couple hundred large tables (~20 fields and ~10,000 rows each)

 - for x in `mysql <db> -e "show tables"`; do mysqldump <db> $x | sed -e
's/ENGINE=MYISAM/ENGINE=ARCHIVE/g' | gzip -c >> <db>.$x.gz

 - In another terminal: watch -n 2 "free -m && ps -eo comm,pid,size,rss,%mem | grep
mysqld"

 - for x in `ls *.gz`; do echo $x && zcat $x | mysql -q -A -f test_archive

If you did it with a big enough database, mysql will eventually run out of memory.  If
not, let it sit for hours, days, weeks.  The memory utilization will never go down unless
you drop the test_archive database or shutdown mysql.  My environment is ~20,000 tables
across multiple databases for a point of reference.

Suggested fix:
None

Whoops, forgot to inlcude that you'd have to strip out the keys/indexs.. 

Also, I thought maybe since archive tables are compressed it was holding them open in
memory, but flush tables only decreased the memory usage by a miniscule fraction.

Error Log

Attachment: mysql.err.log (application/octet-stream, text), 2.17 KiB.

chris, please upload a compressed file containing output from mysqldump --no-data
--all-databases
I tried using 20000 simple tables with int's but saw no visible memory leak.

I will try complex tables next and check if it's a certain field type that makes the
problem more apparent.

test of 10K rows for 20K tables.

Attachment: bug26773.php (application/octet-stream, text), 2.68 KiB.

Chris, sorry for a long silence.  Yes, I now notice the gradual memory consumption, when
using the decimal columns.

At least I lose 100kb of memory per second on 5.1.21 with the above script.  I'll test
for a while long and see how it fairs, then update here again.

Chris, how large is your table_cache configured to be?  does FLUSH TABLES reduce memory? 
It helped here - so I'm not sure there's a genuine leak.

I did some tests now with 5.0.50 and found a File handle leak.
After flush tables and drop database, the mysqld process still had open the file handle
to the <table>.ARZ file.  Looking into this closer now.

Shane, 

From what I recall (It's been awhile) flush tables did not free any memory back into the
pool, and the table_cache was set to 30000

Thanks, 
 - Chris