How to determine if I have an undersized innodb_buffer_pool for MySQL

Question

We have several MySQL 5.7/Mariadb databases running on Ec2 instances on CentOS. Our current logic for allocating memory to the DB is based off total memory available on the OS and a percentage is allocated to the MySQL DB. The appserver also resides along with the DB, so we have to be cognizant of that. My question is, how can I tell if the current memory setting is adequate ? Am I overallocated/underallocated in terms of memory to MySQL DB ? Unfortunately we don't have any 3rd party UI or performance schema enabled to gather any metrics over time. Any help is appreciated.

• mustaccio: "How to determine if I have an undersized innodb_buffer_pool" -- check the buffer pool hit ratio. If it's below 90%, it's likely undersized. Also you should seriously consider not collocating application and database servers.

Answer 1

Run SHOW ENGINE INNODB STATUS\G in the mysql client.

Eventually you see a section on the buffer pool:

...
----------------------
BUFFER POOL AND MEMORY
----------------------
...
Buffer pool hit rate 997 / 1000
...

This shows out of 1000 requests for pages, 997 were served from pages that were already in the buffer pool. The other 3 had to read the page from the tablespace on storage into the buffer pool before it could be used to satisfy queries. That's a hit ratio of 99.7%.

I try to keep the ratio at 99.0% or higher on average. Occasionally it might drop, like if you do an atypical query that needs to examine a lot of rows. But most of the time, this ratio should be very high.

This is why it's important to use some monitoring solution so you can watch the value of this ratio over time, and not worry that you happen to view SHOW ENGINE INNODB STATUS at a time when the ratio was not close to its average.

You don't necessarily need to make the buffer pool as large as 99% of your data to get that 99% hit ratio. The reason for this is that it's usually the case in most apps that some small subset of data is requested more frequently than the rest. For example in a chat app, the data for the most recent few minutes is likely to be read far more than older data. So you can achieve at least 99% average hit ratio using a buffer pool that is much smaller than the total data on disk.

Answer 2

If the app is taking a lot of RAM, you must reduce the "available" RAM by that amount before taking the percentage.

So, simply make it as big as that allows.

Meanwhile... Swapping says that the buffer_pool is oversized.

An undersized buffer_pool should be considered as a symptom of a performance problem; increasing the buffer_pool_size should not be the main cure.

If there are performance issues, then look for the "worst" queries (preferably with the slowlog) and determine whether they can be sped up (usually by indexing or reformulating). That is likely to both improve the hit ratio and improve the overall system performance.