Bug #68879 Key Buffer Usage 235 MB with all keys disabled
Submitted: 7 Apr 2013 7:22 Modified: 8 May 2013 16:36
Reporter: Mike Jorgenstam Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Tests Severity:S5 (Performance)
Version:mySQL Community Server 5.1.48 32 bit OS:Other (Win 7 x64)
Assigned to: CPU Architecture:Any
Tags: buffer, key, Memory, performance

[7 Apr 2013 7:22] Mike Jorgenstam
Description:
When performing a migration and cleaning a huge table (2B rows) I noticed the following behavior by MySQL.

The code below still creates a huge section in the Key Buffer Usage stats of MySQL Administrator using some 235 MB RAM as well as ending up with 100% hit ratio on the key buffer.

I am uncertain if this is a bug, feature or some other underlying cause to this issue but it does eat up RAM fast.

How to repeat:

Requires a huge table like then applying the following code.

step 1. Disable MyISAM SWAP, Query Caching and other memory hogs

step 2. Generate a huge table of min 100 M rows

step 3: apply the following pseudo code model of migration and removal of large data sets all in one go.

-- -----------------------------------------
ALTER TABLE tblName DISABLE KEYS;
CREATE TABLE tblNameTemp LIKE tblName;
ALTER TABLE tblNameTemp DISABLE KEYS;
INSERT IGNORE INTO tableNewLayout (fld0, fld1, fld2)
 (SELECT  fld0, fld1, fld2 FROM tblName  WHERE fld1 <> fld2);
DROP TABLE tblName;
ALTER TABLE tblNameTemp RENAME tblName;
-- -----------------------------------------
[8 Apr 2013 16:36] MySQL Verification Team
Please try most recent version:

http://downloads.mysql.com/archives.php?p=mysql-5.1

Thanks.
[9 May 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".