One key bottleneck, every software engineer who uses MySQL, fails to realize, is how to utilize MySQL cache. I have seen people believing the MySQL query cache as the MySQL cache. Sadly, it is not. The query cache is probably one of the worst cache attributes you may enable for your MySQL instance optimization for a high-traffic website.
MySQL Buffers
MySQL utilizes buffers for caching. These buffers are stored in RAM. The key benefit of these buffers is that the changes can also be stored in RAM before flushing the data to disk, along with sync to the replica. Let’s look at an example.
Let’s say, you have selected a post with id 1001
select * from posts where id = 1001
If you have a MySQL buffer, then the result of this query would be placed in the memory.
Now, the subsequent call, let’s say the following:
update posts set title = 'This is a new title' where id = 1001
Now, imagine, how the above query will be handled in MySQL? MySQL would write in the RAM, and keep the chunk as dirty to be flushed to disk. Now, that makes the above update super fast to the interactive queries. Because writing to disk would be super slow compared to writing to RAM.
If you live under a heavy load, this is an awe-inspiring tweak. Because this makes your MySQL instance a RAM-based database, with an IO player, which keeps track of your dirty bits, and flushes them when needed. While the con of the approach is that if the system is crashed, dirty bits are lost. Now, we can’t directly say lost, if you are using a system with journaling enabled, that keeps track of the changes, and rolls back.
What is the right Innodb Buffer Pool Size?
There is much misinformation about the size of your buffer pool. MySQL says you should use 80% of your RAM as the pool, which is significantly incorrect. If your database is of size 500MB and the RAM you hold is much larger, then using 80% is quite a waste. You could probably use them for other buffers like a join buffer or a temporary table or a heap table. But unwisely using that for Innodb Buffer is not worthy.
To start with, you can set the buffer pool size to the total size of your database first and let the system run for a day or two. Once done, then you may analyze a few MySQL status variables to find out whether should you increase or decrease. Let’s find out a few details
To get the MySQL status variables, run the following from your MySQL console:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
Now, this should give you a details like the following:
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; +---------------------------------------+--------------------------------------------------+ | Variable_name | Value | +---------------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_dump_status | | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 210915 13:30:05 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_load_incomplete | OFF | | Innodb_buffer_pool_pages_data | 117007 | | Innodb_buffer_pool_bytes_data | 1917042688 | | Innodb_buffer_pool_pages_dirty | 557 | | Innodb_buffer_pool_bytes_dirty | 9125888 | | Innodb_buffer_pool_pages_flushed | 80192071 | | Innodb_buffer_pool_pages_free | 7339 | | Innodb_buffer_pool_pages_misc | 6726 | | Innodb_buffer_pool_pages_total | 131072 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 7627222 | | Innodb_buffer_pool_read_ahead_evicted | 41991 | | Innodb_buffer_pool_read_requests | 480918989539 | | Innodb_buffer_pool_reads | 117010959 | | Innodb_buffer_pool_wait_free | 1206 | | Innodb_buffer_pool_write_requests | 290588732 | +---------------------------------------+--------------------------------------------------+ 19 rows in set (0.001 sec)
We can use a few metrics from this to determine whether we need a change in buffer or not.
- Free Pages
First, we look at the variable ‘Innodb_buffer_pool_pages_free’. It says the amount the free pages you have. If you have set a pretty large buffer pool, you should have a large number of pages in total, hence seeing the number ‘Innodb_buffer_pool_pages_free’ should not make you realize if you are going out of the pool page or not. You may calculate a ratio like the following:
free_page_ratio = ( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) * 100
free_page_ratio should not be less than 10% according to my experience until you are running out of RAM. If you have enough RAM, and the ratio is less than 10%, you have fewer free pages, you might want to increase the pool size.
Now, one thing to remember, the value, might stay below 10% all the time, it might not mean you are running out of pool space, instead, you are at the top of the optimization or near to cut off. To determine, if you are out of pool space, make sure to monitor the variable several times of the day and see how often does it change. If it gets lower too often, that suggests you are hitting the boundary and it is time to increase the buffer pool size. - Read Ratio
Next, you need to look at the number of read requests and the number of reads you have performed in total. If you are seeing a lot of read requests and the ratio to reads is higher, then the buffer isn’t enough to store your full database requests in the RAM, and you should increase the value.
read_ratio = (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
Remember, Innodb_buffer_pool_reads means the amount of requests Innodb had to query the disk to get the data from. The value should be less than 1% for maximum efficiency. In my above example, the value is less than .1%, which makes the Innodb performance the best. - Flushed & Wait Free
The other thing, we need to track is that, Innodb_buffer_pool_wait_free. The variable should be near 0 or 0. If the variable is higher, it suggests a situation has happened, where the flushing was essential to free pages. Even if you have a good read_ratio, but if the variable shows a large number, then you need to increase the buffer pool size to make sure we do not need to free pages at the peak times.
Continuous monitoring is essential to perfectly use Innodb Buffer Pool. It is an extremely essential part of MySQL-based software and should be used wisely. Proper usage can excessively decrease your server CPU cost, and maximize the overall performance.