Hello,
I’m trying to find a reliable method to see if MySQL would benefit with more memory?
My current approach is to use the output from show engine innodb status. i.e.
Buffer pool hit rate 1000 / 1000
If it is 1000 / 1000 things are good.
Is this too simplistic? What approaches do other people use?
Thanks.
@Jamie_Downs, you need to look at buffer pool reads vs buffer pool read requests. The former indicates a buffer pool “miss” which must go to disk. If the ratio of misses to cached reads is < 90%, then you could probably benefit from more memory to the buffer pool.
Thanks Matthew.
So the following would be pretty good for a box that’s been up for a while?
show global status like ‘%Innodb_buffer_pool_read%’;
±--------------------------------------±--------------+
| Variable_name | Value |
±--------------------------------------±--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 64304068 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 4180641185206 |
| Innodb_buffer_pool_reads | 599335740 |
±--------------------------------------±--------------+
select (599335740/4180641185206)*100 as bpoolhitrate;
±-------------+
| bpoolhitrate |
±-------------+
| 0.0143 |
±-------------+
My apologies. I had it backwards. I’ve edited my post above.
Innodb_buffer_pool_read_requests - logical reads from buffer pool
Innodb_buffer_pool_reads - disk reads / miss from pool
So do this:
_read_requests / (_read_requests + _reads) * 100 = hit ratio
You have a 99.98% hit rate. You are good 
If things still feel slow, run EXPLAIN on all queries and verify they are using proper indexes. More often than not, poor performance in MySQL is a result from bad queries, not from improper configuration.
Perfect thanks for the further explanation and clarification. Really useful.