Bug #18160 Memory-/HEAP Table endless growing indexes
Submitted: 12 Mar 2006 1:22 Modified: 3 Jun 2006 13:35
Reporter: Holger S. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S3 (Non-critical)
Version:5.0.18, 4.1,x, 4.0.x OS:Linux (Debian Linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[12 Mar 2006 1:22] Holger S.
Description:
Hello,

I have a strange Problem with my  Memory-Table. The table has about 10 Attributes, 300,000 records, a PRIMARY KEY using HASH and 7 indexes using BTREE. Furthermore the Table is updated about 2000-3000 times per seconds (thats why I have chosen memory storage) also effecting indexed columns an although I do net insert that mush of data, the memory used by the table grows and grows.

For illustration just two snapshot shown in phpMyAdmin:

Status 1:

Space usage
Data 13,240 KB
Index 44,116 KB
Total 57,356 KB
Rows 249,252
Row length ø 51
Row size ø 236 Bytes

Some hours, millions of updates and ~50,000 inserts later:
Status 2:
Data 16,296 KB
Index 227,378 KB
Total 243,674 KB
Rows 305,838
Row length ø 51
Row size ø 816 Bytes

As soon as I now drop any Index, all Index structures seeme to be totally rebuilt and suddenly the table is back in an acceptable status:
Data 16,296 KB
Index 53,581 KB
Total 69,877 KB

Obvieoisly something goes wrong in the memory storage engine, RAM is not to be  wasted this way.

This problem was already mentioned in your forums: http://forums.mysql.com/read.php?92,47478,47478#msg-47478
But I couldn't find any bugreport concerning this.

How to repeat:
Not shure if BTREE Indexes or HASH indexes cause the problem - I'm using 7 BTREE and one HASH(=PRIMARY).

Suggested fix:
- A maintenance statement like OPTIMIZE TABLE that is supported by memory storage engine
OR
- any kind of fix that stops index management routines to waste memory.
[12 Mar 2006 4:02] Holger S.
sql-dump of a table for testing + perlscript writing 1.000.000 random update statements to STDOUT usable with this table

Attachment: bugtest.tar.bz2 (application/bzip2, text), 86.30 KiB.

[12 Mar 2006 11:36] Holger S.
Just testet it out with above atteched files for half an hour and the index size really keeps growing and growing by just updats and not adding any data. I finally had  a  table with 5MB of index and 129KB of  data.

./bugtest.pl | mysql -u test -D test

After dropping the BTREE-Indexes replacing them by HASH-Indexes and  doing above procedure again, memory usage did not further grow. So it must be a problem of BTREE only.
[24 Mar 2006 16:33] Hartmut Holzgraefe
verified with 5.0bk source, test case attached (shows 5 bytes being leaked per BTREE update)
[24 Mar 2006 16:34] Hartmut Holzgraefe
test case

Attachment: bug18160.tar.gz (application/x-gzip, text), 944 bytes.

[24 Mar 2006 16:37] Hartmut Holzgraefe
also verifeid on 3.23.58, 4.0bk, 4.1bk, 5.1bk
[13 Apr 2006 19:37] 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/4937
[18 Apr 2006 18:28] Sergei Golubchik
This bug will be fixed, of course.

But just to clarify - indexes or memory usage are not growing indefinitely, but the amount of memory is calculated wrong. It's like you add 100 byte key, you delete 100 byte key, and the "size of index" value is incremented, but not decremented. You can verify it, if you will be adding/removing  key values long enough - you can get "index size" much larger than the physical memory you have :)

Still a bug, that will be fixed.
[19 Apr 2006 10:14] 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/5135
[6 May 2006 9:11] Sergey Vojtovich
Fixed in 5.0.21, 5.1.10.
[8 May 2006 19:23] Paul Dubois
Noted in 5.0.21, 5.1.10 changelogs.

Updates to a <literal>MEMORY</literal> table caused the size
of <literal>BTREE</literal> indexes for the table to increase.
(Bug #18610)
[3 Jun 2006 13:35] Holger S.
When fixed, why is this bug still present in 5.0.21 and 5.0.22?

I'm using the "Linux x86 generic RPM" for installation.
http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-server-5.0.22-0.i386.rpm/from/pick
[5 Jun 2006 12:30] Paul Dubois
Sorry, the fix went into 5.0.23. I'll move the changelog
accordingly.
[3 May 2007 4:26] Michael K
Will this ever be fixed in 4.1 tree? Or should I submit a new bug?
[3 May 2007 6:04] Valeriy Kravchuk
Bug #19138 was marked as a duplicate of this one. Will this bug, verified on all versions, will ever be fixed on 4.0.x and/or 4.1.x?