Mysql – How to use most of memory available on MySQL

innodbmemorymyisamMySQLperformance

I've got a MySQL server which has both InnoDB and MyISAM tables. InnoDB tablespace is quite small under 4 GB. MyISAM is big ~250 GB in total of which 50 GB is for indexes.

Our server has 32 GB of RAM but it usually uses only ~8GB. Our key_buffer_size is only 2GB. But our key cache hit ratio is ~95%. I find it hard to believe..

Here's our key statistics:

| Key_blocks_not_flushed | 1868 |
| Key_blocks_unused | 109806 |
| Key_blocks_used | 1714736 |
| Key_read_requests | 19224818713 |
| Key_reads | 60742294 |
| Key_write_requests | 1607946768 |
| Key_writes | 64788819 |

key_cache_block_size is default at 1024.

We have 52 GB's of index data and 2GB key cache is enough to get a 95% hit ratio. Is that possible? On the other side data set is 200GB and since MyISAM uses OS (Centos) caching I would expect it to use a lot more memory to cache accessed myisam data. But at this stage I see that key_buffer is completely used, our buffer pool size for innodb is 4gb and is also completely used that adds up to 6GB. Which means data is cached using just 1 GB?

My question is how could I check where all the free memory could be used? How could I check if MyISAM hits OS cache for data reads instead of disk?

Best Answer

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;
Related Topic