My Mysql server is heavily loaded, now 300 qps average.
It uses %50 Cpu in average and just 700MB of ram. My server has 8GB and it has over 3GB free. The slow query log seems fine. There are very few and not frequent ones.
I want to be sure that it is returning the cached results and do not touch the disk unnecessarily.
I think the linux OS caches the innodb file but can I trust on that? And is there any good practice to lower cpu usage through buffering or caching?
innodb_buffer_pool_size is set to default value. (8mb)
I have Innodb, MyIsam and Memory tables mixed.
Here is an output from a tuner script
INNODB STATUS
Current InnoDB index space = 238 M
Current InnoDB data space = 294 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
KEY BUFFER
Current MyISAM index space = 113 M
Current key_buffer_size = 192 M
Key cache miss rate is 1 : 63
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 7.64 %
Current query_cache_min_res_unit = 4 K
Query Cache is 28 % fragmented
Since you have 3GB free, boost your innodb_buffer_pool_size to hold your entire innodb dataset (data + index).
Give it a 1G so it has some breathing room. You won't regret it. :)