Beware: key_buffer_size larger than 4G does not work

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Comments

  1. Abhijeet says

    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:
    #mysql
    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?

    -Abhijeet

  2. peter says

    Frank,

    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.

  3. peter says

    Serg,

    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.

  4. says

    Hi,

    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

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    21394 mysql 15 0 2444m 1.8g 6368 S 10 23.1 2654:23 mysqld

  5. peter says

    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.

  6. Sean says

    Peter,

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

  7. sean says

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

  8. says

    Peter,

    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]

    user=root
    memlock

    –David

  9. says

    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.

  10. mysql-d says

    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

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *