How to use most of memory available on MySQL
- by Zilvinas
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?
