Friends of Pythian Referral Program - Earn up to $5000!

Experiments with MySQL 5.7’s Online Buffer Pool Resize

Posted in: MySQL, Technical Track

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.

 

The Environment

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 Experiments

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:

 

The Results

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:

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is unchanged. It shows some periodic drops that reduce over time as the cache warms up

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:

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is reduced from 4 to 2 GBs. It shows a noticeable drop in tps around second 120, which is when the change is made. After that, tps recovers, but is less than before, which is expected.

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:

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is increased from 4 to 8 GB. It shows a very short drop around second 120, which is when the change is made.

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:

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is increased from 2 to 8 GB. It shows a very short drop around second 120, which is when the change is made, and then an improvement in tps.

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:

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is decreased from 4 to 2GB, but with a small data set. It shows a very short drop around second 120, which is when the change is made.

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?

Scatter plot showing throughput in tps on the Y axis, and time in seconds on the X one. This shows tps for sysbench's oltp workload when the buffer pool's size is decreased from 4 to 2GB, and then increased to 4 again after 20 seconds. It shows a drop in throughput at second 120, which is when the change is made, some poor tps while the change is in place, and then a recovery to values prior to the change.

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.

Conclusion

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.

email

Interested in working with Fernando? Schedule a tech call.

About the Author

Fernando Ipar is a MySQL Principal Consultant who works remotely from his family home in Montevideo, Uruguay. Initially, Fernando began using GNU/Linux in 1998 and MySQL in 2000, and his journey led him to become active in the technical community. Fernando is currently the founder of the Montevideo MySQL Meetup group and has also been the lead on the MySQL-ha/high base project for many years. Before coming to Pythian, he spent seven years at Percona, where he contributed to the Percona Toolkit and TokuMX Toolkit, among other endeavors. Fernando helped scale and troubleshoot the back-ends for businesses of all sizes including financial institutions, telcos, and other IT companies while also supporting their full stack needs.

3 Comments. Leave new

Hello,this article helps me a lot.I am wondering how you draw the graph with the sysbench’s result.

Thank you.

Reply
Fernando Ipar
March 31, 2017 9:15 am

Hello, glad you found it useful. I use R and ggplot2, with a Tufte inspired theme. You can find an example of the code used to generate them here: https://github.com/fipar/benchmark_automation/blob/master/data_presentation_scripts/csv_to_png.sh

Reply

Thank you for reply!

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *