Innodb_buffer_pool explanation The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process.

According to the mysql docs:


  On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.


Now, how does the percentage approach makes sense here when we are talking about actual numbers?

Lets say that currently my db uses 80 gb of ram and 20 gb of ram is left for other tasks.
After a period of time, my database grows bigger and i decide to increase the buffer pool size. To keep the 80% proportion, i have to increase the total ram of the server, even though other processes did not require more ram memory.
Why not just add the additional ram while keeping the same 20gb of ram for the other tasks?
The 80% is just a general rule of thumb for helping people start with a more sensible value than the default. 

Buffer pool size calculation can actually be more complicated. Ideally frequently accessed data pages should remain in the buffer pool and you don't want too much eviction to happen because of its impact on performance. This is again generic but a more realistic measurement to have the buffer pool large enough to accommodate hot data + enough space for avoiding unfrequent reads triggering too much eviction. 

Just because you have a database 1TB big you don't have to have 1Tb of buffer pool if you only read the same 100MB all the time. 

Keep in mind also that inserts and updates also affect buffer pool usage because the operation happens there. You should try to match the amount of data you can write into innodb logs (controlled by innodb_log_file_size) and the space required for this in buffer pool. Even if you have large innodb log files but you don't have enough space for insert than eviction will cause flushing data pages to tablespace which in essence have the same effect as checkpointing. 

One empirical way to correctly size buffer pool is to set as high as your datasize (or as large as possible) and run normal production load on it while decreasing the size constantly while measuring transaction rate, response time and threads_running. When you see performance starting to drop that's the size that you need at least.