# The best key\_buffer size **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [February 13, 2009, 2:01am UTC](https://forums.percona.com/t/the-best-key-buffer-size/1082 "2009-02-13T02:01:38Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![ahbruinsma](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@ahbruinsma](https://forums.percona.com/u/ahbruinsma)\ **Post date:** [February 13, 2009, 2:01am UTC](https://forums.percona.com/t/the-best-key-buffer-size/1082/1 "2009-02-13T02:01:38Z")
I’ve got a db-server, dual quad-core, MySQL 5, with 8Gb of memory (64bits CentOS). My db is arround 70Gb and some tables have indexes around 2Gb. I am playing arround with the mysql config-values, using the tuning-script, because performance is going down, for example it’s using all the swap memory of the OS and the load will jump up and the whole server will become unresponsive at some times. I guess it has to do with the key\_buffer size, which is setup pretty high right now. What is the best size for the key\_buffer in this scenario? Should it be big enough to hold more then one index or doesn’t it work that way? Thanks! P.S.: A quick summary of the config-values: key\_buffer = 5020Mmax\_allowed\_packet = 1024Mtable\_cache = 4096sort\_buffer\_size = 512Mread\_buffer\_size = 512Mmyisam\_sort\_buffer\_size = 512Mthread\_cache = 32query\_cache\_limit = 16Mquery\_cache\_size = 256Mthread\_concurrency = 8net\_read\_timeout = 3600net\_write\_timeout = 3600group\_concat\_max\_len = 10485760max\_heap\_table\_size = 128Mmax\_connections = 250tmp\_table\_size = 512Mjoin\_buffer\_size = 512Mopen\_files\_limit = 25000 ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [February 13, 2009, 1:01pm UTC](https://forums.percona.com/t/the-best-key-buffer-size/1082/2 "2009-02-13T13:01:02Z")
So I assume you’re only using MyISAM tables. If you’re using InnoDB, you’ll definitely need to use some of that memory for the InnoDB buffer pool. The key buffer is used to cache index pages. You can tell if its being effectively used by checking the ratio of Key\_reads to Key\_read\_requests from the SHOW GLOBAL STATUS command. The popular rule of thumb is that this ratio should be less than 0.01. When Key\_reads is much lower than Key\_read\_requests, it means you’re going to disk to fetch index pages less often, which is good. The problem with having a very large key buffer is that you’re not just accessing indexes, you need to read the actual rows that the indexes point to. MySQL doesn’t cache these data pages in memory for a MyISAM table, instead it relies on the OS file system cache to speed things up. This cache just uses memory not in use by other processes, so by decreasing MySQL’s memory footprint, you’ll make more memory available for caching data. So if your key\_reads:key\_read\_requests is very small, you can try lowering the key buffer size until you get to the 0.01 ratio. But keep in mind the first goal here should be to stop the system from swapping heavily. You need to find out why its happening and stop it. Also, your max\_allowed\_packet seems huge, though I don’t think that could cause that much of a problem. Really, your configuration depends on your workload. A tuning script can give you a good starting point, buts its no replacement for really understanding the configuration options and using your unique knowledge of your application needs to make intelligent configuration decisions. ---
**Author:** ![ahbruinsma](https://avatars.discourse-cdn.com/v4/letter/a/dc4da7/32.png) [@ahbruinsma](https://forums.percona.com/u/ahbruinsma)\ **Post date:** [February 13, 2009, 3:58pm UTC](https://forums.percona.com/t/the-best-key-buffer-size/1082/3 "2009-02-13T15:58:53Z")
| [B]Quote:[/B] | | So I assume you're only using MyISAM tables. If you're using InnoDB, you'll definitely need to use some of that memory for the InnoDB buffer pool. | Most of them are MyISAM tables, we got a few innoDB tables, but they aren't very big. | [B]Quote:[/B] | | The key buffer is used to cache index pages. You can tell if its being effectively used by checking the ratio of Key\_reads to Key\_read\_requests from the SHOW GLOBAL STATUS command. The popular rule of thumb is that this ratio should be less than 0.01. When Key\_reads is much lower than Key\_read\_requests, it means you're going to disk to fetch index pages less often, which is good. | Didn't know that, I've checked those values and they look like this: Key\_read\_requests 23474418144 Key\_reads 4997410 | [B]Quote:[/B] | | So if your key\_reads:key\_read\_requests is very small, you can try lowering the key buffer size until you get to the 0.01 ratio. But keep in mind the first goal here should be to stop the system from swapping heavily. You need to find out why its happening and stop it. | Whenever I look at top I see MySQl is taking all the swap memory in some cases. No other big processes run on the server, only MySQL. But I'm guessing lowering the key\_buffer is worth a try?! | [B]Quote:[/B] | | Also, your max\_allowed\_packet seems huge, though I don't think that could cause that much of a problem. | True, but I tried to lower that, but it did not help. The reason this is that high is because we are running two slaves which complained about this value being too low. ---
**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [March 21, 2009, 1:32pm UTC](https://forums.percona.com/t/the-best-key-buffer-size/1082/4 "2009-03-21T13:32:58Z")
You might benefit from reducing your key\_buffer size if your “hot” data is only a few GB in size. This would free-up RAM to keep the hot data in the OS’s filesystem cache. Anything over 25% or so of your RAM as a key\_buffer is likely pushing hot data out of the filesystem cache. Try setting it to 2G and see how that affects performance. I’d seriously look at increasing the RAM in your system. 2GB chips are cheap, and if you have 8 slots it’s well worth the money to upgrade.