MySQL Bugs: #18160: Memory-/HEAP Table endless growing indexes

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

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.

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.

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.

verified with 5.0bk source, test case attached (shows 5 bytes being leaked per BTREE update)

test case

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

also verifeid on 3.23.58, 4.0bk, 4.1bk, 5.1bk

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

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.

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

Fixed in 5.0.21, 5.1.10.

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)

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

Sorry, the fix went into 5.0.23. I'll move the changelog
accordingly.

Will this ever be fixed in 4.1 tree? Or should I submit a new bug?

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?