Quite commonly I get a question similar to this – “My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?”
This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
How to figure out if it is time for upgrade when ?
Look at number of misses per second Check number of innodb file reads and writes per second and see how high are these. Decent drive can do some 150-200 IOs/sec this is how you can guess about the load. If you just get couple of reads per second your working set fits to the memory very well if it is hundreds you’re likely to be IO bound or becoming one.
Look at Iowait iostat -dx 10 will show disk utilization. Low (less than 50%) means there are rarely anyone waiting from disk to service requests.
Look at Trends Really it is hard to give advice without trending data. So you have 50 reads/sec is it problem waiting to happen ? You can hardly tell unless you have a trending. If it was 5 reads/sec a week ago 20 reads/sec couple of days ago and 50 today I would be worried. Trending database size number of queries etc is also very helpful – for example growth from 20 to 50 reads/sec may be because load is getting more IO bound or may be just because amount of queries increased dramatically or may be because queries changed their plans.
Do the sizing This especially works well in sharding environment w Master-Master replication or something else which allows you to do light tests in production and which has relatively uniform database content. In cases like this you can often do some experiments with different innodb_buffer_pool_size (while having innodb_flush_method=O_DIRECT) and see how performance depends on buffer size so you get and understanding what Memory-To-Disk ratio is optimal for your application, or at which point performance drops dramatically. It is even better if you have Benchmark relevant for your application setup (I do not put it first because few people do have this setup) so the matter of becomes problem of benchmarking. Once you found out your system starts to degrade quickly as database size reaches say 3x of innodb_buffer_pool_size you can use it as guidance to add more memory or more servers.
Of course this is all oversimplifications – you’ve also got to look at CPU scalability in particular if you have many cores, consider how much IO bandwidth you have etc but these factors already should allow you to make an informed decision.
