Bug #26773 Possible Memory Leak with Archive Tables

• Submitted: 1 Mar 2007 22:16
• Modified: 15 Apr 2010 12:24
• Reporter: Chris Coyle
• Status: Duplicate
• Category: MySQL Server: Archive storage engine
• Severity: S1 (Critical)
• Version: 5.0.27-max
• OS: Linux (Debian 3.1 (kernel 2.6.14.2))
• CPU Architecture: Any

[1 Mar 2007 22:16] Chris Coyle

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

[1 Mar 2007 22:26] Chris Coyle

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.

[1 Mar 2007 22:42] Chris Coyle

Error Log

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

[15 Mar 2007 12:40] MySQL Verification Team

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.

[17 Nov 2007 14:13] MySQL Verification Team

test of 10K rows for 20K tables.

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

[17 Nov 2007 14:17] MySQL Verification Team

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.

[17 Nov 2007 14:27] MySQL Verification Team

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.

[18 Nov 2007 9:44] MySQL Verification Team

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.

[19 Nov 2007 14:36] Chris Coyle

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

[15 Apr 2010 12:24] MySQL Verification Team

ok, i am tentatively marking this as a duplicate of the "general archive problems" bug: bug #51252