How to use most of memory available on MySQL

Posted by Zilvinas on Server Fault See other posts from Server Fault or by Zilvinas
Published on 2010-11-24T16:50:10Z Indexed on 2011/02/13 7:27 UTC
Read the original article Hit count: 540

Filed under:
|
|
|
|

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?

© Server Fault or respective owner

Related posts about mysql

Related posts about Performance