Beware: key_buffer_size larger than 4G does not work


I was working with customer today which has MySQL on a system with some 64GB or RAM running MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes.

Why ? Because key_buffer_size over 4GB in size is not really supported (checked with latest and greatest MySQL Enterprise 5.0.34). It works just fine until you have less than 4GB worth of key cache used and then it would crash.

If you do not know about this limitation it may be quite complicated to figure out what the problem is as MySQL will be crashing on random statements associated to random tables.

What I find interesting is this bug was known for years and not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be close to changing single line of code with variable values limits, might be a bit more than that but it should not be that bad.


Share this post

Comments (17)

  • Abhijeet Reply

    Hi Peter & *

    Hi got a error “out of memory” while running some SELECT query. This behavior is seen once in a blue moon. I just tried reproducing this issue. I think this is because of the less space in key_buffer_size. For this i changed the following variables as follows:
    SET GLOBAL key_buffer_size=100
    SET global sort_buffer_size = 100
    SET global max_heap_table_size=1000

    Then i ran SELECT , INSERT and UPDATE command in infinite loop hoping that key_buffer_size will get overflow and OUT OF MEMORY error will be thrown.

    However this did not help :(

    Could you pls help me reproduce this error?


    February 14, 2007 at 12:00 am
  • Farhan "Frank" Mashraqi Reply

    It seems like this part of code wasn’t ported over and optimized for 64-bit binaries.

    This is ofcourse a serious limitation on use of MyISAM in large environments.


    February 14, 2007 at 8:07 am
  • Sergei Golubchik Reply

    Well, it was fixed. has this comment:

    [18 Oct 2004 1:52] Dean Ellis

    Fixed in 4.0.22 (cset 1.2043).

    In 5.0 the bugfix was apparently removed, by mistake.

    February 14, 2007 at 9:24 am
  • peter Reply


    Yes this limit should not have where at the first place but I guess Monty did the same mistake Bill Gates did thinking 640K will be enough for everybody… 10 years ago 4GB sounded like large enough limit not to worry about 🙂

    As a workaround now you can create several key buffers and map different tables/indexes to them. This works OK in most cases, even though it is a hassle with exception of single giant table.

    February 14, 2007 at 9:43 am
  • peter Reply


    Well it is 4.0 only… I’m not sure if it was ever fixed in 4.1 or 5.0 (I’ve been running into this bug many times on my memory so I do not think it was)

    also note the bug report is still in Verified stage.

    Anyway, be positive I’m simply trying to attract some attention to this bug to make sure it is prioritized and fixed sooner.

    February 14, 2007 at 9:51 am
  • Christian Reply


    we’re running Mysql 5.0.30 (Debian-Package) on our Intel Xeon 5110 with 8 GB Ram. We’ve set
    key_buffer_size to 6GB ( 6291456000 Bytes) and actually we’re having no problems with this
    setup in our production enviroment. mysqld is running for about 10 days (since we installed that new server).

    How do I know if mysql is using the whole key-buffer? Top actually outputs

    21394 mysql 15 0 2444m 1.8g 6368 S 10 23.1 2654:23 mysqld

    February 14, 2007 at 10:26 am
  • peter Reply

    In your case at this point Virtual Size is less than 2.5GB so key buffer is not used fully.
    you also can see key_blocks_used from “SHOW STATUS” output to check which portion of key_buffer is used.

    February 14, 2007 at 10:37 am
  • An Expert's Guide to Oracle Technology Reply

    Log Buffer #32: a Carnival of the Vanities for DBAs…

    Welcome, to the 32nd edition of Log Buffer, the weekly survey of database blogs. What shall I talk about? Hmmm, maybe I’ll start with the Oracle blogosphere. Might as well get started with controversy by reading an entry by Dean……

    February 16, 2007 at 9:56 am
  • Sean Reply


    Though I haven’t tried it, can one create multiple key caches up to 4 GB of memory as a work around?

    February 25, 2007 at 1:22 pm
  • Sean Reply

    My bad Peter, didn’t see your comment above 🙁

    February 25, 2007 at 1:23 pm
  • peter Reply

    Sean, As you found out yourself, yes this is the workaround you can use 🙂

    February 25, 2007 at 1:48 pm
  • sean Reply

    Is there a way to monitor the usage and efficency of multiple key caches? Perhaps something similar ‘key_reads’ and ‘key_read_requests’.

    March 14, 2007 at 4:48 pm
  • David W. Juntgen Reply


    If you would like to force mysql to lock itself into memory you can. This is useful to avoid page swaping. The down fall is that you must run as root and it sucks up all the memory defined by your my.cnf file for MyISAM and Innodb buffer pools. So, if you have a 6G key buffer, then you top will show a RES of 6G.

    In your my.cnf file add these two lines under [mysqld]



    March 19, 2007 at 3:26 pm
  • Daniel Nuriyev Reply

    I have 8Gb RAM with MySQL 5.0.27. Mistakingly I set key_buffer_size to 20Gb instead of 2Gb. Then I opened MySQL Administrator Health tab and saw there 4Gb of key_buffer_size. It seems that MySQL has some code that makes sure that key_buffer_size will not be more than the maximum.
    Note: I set key_buffer_size through SET GLOBAL key_buffer_size = …; SQL command. I did not do it through a configuration file.

    April 17, 2007 at 9:08 am
  • David W. Juntgen Reply

    Currently all version of MySQL have a limit to the max size of the key_buffer_size, which is set to 4G.

    But…there are ways around this limitation, you can add additional key buffers or (CACHE INDEXs) for individual tables after the server starts. You can read about this more here:



    April 25, 2007 at 9:58 pm
  • Przemek Reply

    As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows).

    September 22, 2008 at 3:11 am
  • mysql-d Reply

    I think the issues is not with mysql but the file system that is been provided by the operating system.

    The EXT 2 file system just provides with 4 Gigs of max file size support. I think you should try with the EXT 3 file system
    which is more thatn lots and lots of terabites


    February 26, 2009 at 3:29 am

Leave a Reply