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.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
15 Comments
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.
Frank
Comment :: February 14, 2007 @ 8:07 am
Well, it was fixed. http://bugs.mysql.com/bug.php?id=5731 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.
Comment :: February 14, 2007 @ 9:24 am
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.
Comment :: February 14, 2007 @ 9:43 am
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.
Comment :: February 14, 2007 @ 9:51 am
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
Comment :: February 14, 2007 @ 10:26 am
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.
Comment :: February 14, 2007 @ 10:37 am
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……
Trackback :: February 16, 2007 @ 9:56 am
Peter,
Though I haven’t tried it, can one create multiple key caches up to 4 GB of memory as a work around?
Comment :: February 25, 2007 @ 1:22 pm
My bad Peter, didn’t see your comment above
Comment :: February 25, 2007 @ 1:23 pm
Sean, As you found out yourself, yes this is the workaround you can use
Comment :: February 25, 2007 @ 1:48 pm
Peter,
Is there a way to monitor the usage and efficency of multiple key caches? Perhaps something similar ‘key_reads’ and ‘key_read_requests’.
Comment :: March 14, 2007 @ 4:48 pm
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
Comment :: March 19, 2007 @ 3:26 pm
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.
Comment :: April 17, 2007 @ 9:08 am
Currently all version of MySQL have a limit to the max size of the key_buffer_size, which is set to 4G.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
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:
http://dev.mysql.com/doc/refman/5.0/en/multiple-key-caches.html
Later!
–Dave
Comment :: April 25, 2007 @ 9:58 pm
As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows).
Comment :: September 22, 2008 @ 3:11 am