The best key_buffer size

[ahbruinsma]

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

[vgatto]

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.

[ahbruinsma]

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

[MarkRose]

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.