One of the interesting features introduced in MySQL 5.7 is that innodb_buffer_pool_size is a dynamic variable (since 5.7.5, to be more specific). Yet, past experience tells us that just because a variable is dynamic, it does not make it is safe to change it on the fly.
To find out how safe this new feature is, I measured throughput on a synthetic workload (sysbench 1.0 running the oltp script) as I made changes to this variable. In this post, I will show the results that came through.
For my tests, I used a Google Cloud Compute instance of type n1-standard-4 (that is 4 vCPUs and 15 GB of memory) with 25 GB of persistent ssd. The dataset was about 9.2 GB (on disk, Innodb, no compression, 40M rows), with a smaller version of almost 1 GB (160k rows) for a specific test.
As mentioned earlier, the workload was sysbench 1.0’s oltp script.
The goal of the experiment was to measure what impact (if any) changing innodb_buffer_pool_size dynamically has on the workload, measured in terms of throughput (transactions per second).
After some tests to find a suitable run time, I decided to do the following for each test:
- restore the data directory from a backup, so all runs had the same data and a cold buffer,
- run sysbench for 240 seconds, reporting stats every second, and
- change innodb_buffer_pool_size after 120 seconds.
Here’s how the variable was modified:
- The ‘normal’ configuration is 4GB
- For the ‘increased’ tests, it was modified to 8GB
- For the ‘decreased’ tests, to 2GB
Innodb’s log file size was set to 1 GB, and no other changes were made to the default configuration. I was not going for benchmark results here; I simply wanted to find out how safe it would be to do this in production.
Let me start by showing what happens when I left sysbench to run its oltp script for 240 seconds, with no changes made to the variable:
Let me start by showing what happens when I just left sysbench to run its oltp script for 240 seconds, with no changes made to the variable:
We can see some periodic drops in tps that improve with time, and which go away if sysbench is left to run for about 600 seconds, but, again, I just wanted to get an idea of the safety of changing the Buffer Pool’s size on a live system. In the end, this baseline was good enough to let me run several tests in a short amount of time.
Let’s see what happens now when, at second 120, the BP’s size is reduced from 4 to 2 GBs:
We see a very clear drop around the time of the change, and then an expected drop in tps. You may be wondering why I tested a change that I knew would result in poor performance, and that’s a valid question. In my experience, people make mistakes when tuning Innodb. I’ve witnessed this several times and know this to be an incredibly realistic scenario. I think it is interesting to know, besides the expected result of less tps, what happens when the change is actually made. Looking at sysbench’s output (you can find all the files, along with the scripts I used, here) we see that the drop started at second 121 and lasted until about 130, where tps started to stabilize again. I think that’s pretty good. Remember, we are talking about a variable that required a service restart in previous versions, and nothing is worse for throughput than mysqld not running at all. With that in mind, a few seconds of reduced performance seems like an improvement to me.
Here is what happens when, given the same start, the BP size is increased to 8GB at second 120:
There is another drop, but it seems shorter, and honestly, I probably wouldn’t have noticed it in the graph if it wasn’t for that lonely dot near the bottom. Looking at the raw output, we can see the impact is seen only on second 121. I think this is very good news. Again, compared with what we had before, this means that, at least on this controlled experiment, we were able to increase the BP’s size with very little production impact.
Another increase example, in this case, from 2 to 8 GB, which I have labelled as ‘increase needed’ in my scripts because titles are a kind of name, and naming things is one of the hardest problems in computing:
The drop is also measured just on second 121, and tps improves significantly starting on second 122, so this makes me even more optimistic about this feature.
Let’s now see what happens when we decrease the BP while running on the small dataset:
My goal here was to try and simulate what may happen when we dynamically reduced an oversized BP because, for example, someone copied the configuration from a standalone production MySQL to a shared hosted test instance with small datasets, (which is something I have also seen done). In this case, the drop is right at second 120, and then it goes back to normal.
Finally, what happens when the BP size is reduced, and then this change is rolled back after 20 seconds?
A Quick Look at the Drops in Throughput
We have seen that, in all cases, there is a drop in throughput when this variable is changed, with varying length depending on the circumstances. I took a quick look at what happens then via pt-pmp, and found out that, during the drops, most threads are waiting on the trx_commit_complete_for_mysql function. This function is found on file trx0trx.cc of Innodb and is described by a comment as flushing the log to disk (if required). So what happens if we change this on a read-only workload? It turns out there is still a short drop in throughput, and this time most threads are waiting at the buf_page_make_young function, which moves a page to the start of the BP’s LRU list. In both cases, the ‘root’ wait is for internal mutexes: one protecting writes to the log in the oltp workload’s case, and one protecting the buffer pool in the read only workload’s case.
I think this new feature was much needed. It’s a welcome addition to MySQL’s capabilities, and while, yes, it can have some impact in your workload (even if it is read-only), it must be compared to what happened before – a service restart was needed.