EmergencyEMERGENCY? Get 24/7 Help Now!

Beware: key_buffer_size larger than 4G does not work

 | February 14, 2007 |  Posted In: Insight for DBAs

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
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

17 Comments

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • 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……

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

  • 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

  • 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.

  • 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

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.