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.
About the Author
Peter ZaitsevPeter 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.
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
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.
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.
Peter,
Though I haven’t tried it, can one create multiple key caches up to 4 GB of memory as a work around?
My bad Peter, didn’t see your comment above 🙁
Sean, As you found out yourself, yes this is the workaround you can use 🙂
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.
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
As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows).
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
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