MySQL – Best Cache Settings for 8GB RAM Dedicated MySQL Server Using InnoDB

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

I can easily believe a 95% key cache hit rate. Listen to your statistics:

You stated you have 50GB of Indexes for MyISAM tables

The key cache is designed to cache index pages for MyISAM tables. This option is set by the key_buffer_size option

You said it was set at 2G. No wonder the hit rate is 95%. Index blocks are being paged in and out of the key cache constantly. Dozens of queries needing a specific row gets loaded into the keycache after MySQL finds out the index (.MYI) info by the first query needed was not initially there. All subsequent queries requiring the same .MYI info to lookup data (.MYD) will already be cached after the first query loaded it. MySQL DOES NOT CACHE MyISAM Data in its own caches.

You should be able to set key_buffer_size to 8G.

Here is an excerpt from http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size:

You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

This same URL explains that the max key_buffer_size for 32-bit OS is 4G. You should not go past 8G for key_buffer_size with your given server config.

As for your data reads, the only status variable you need is Key_reads. This gives an indication how often an index page has to be fetched from a .MYI file.

What you need to monitor for you key cache hit ratio is the following

KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC

KeyCache Hit Ratio (KHR) is therefore this formula

KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA

You want a KeyCache Hit Ratio 99+%

Now on the flipsize of things, let discuss InnoDB.

Your innodb_buffer_pool_size should be set to 4G. Thi is because the InnoDB buffer pool caches both data and index pages. You can give innodb_buffer_pool a more specific figure rounded up using the following:

SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;