MySQL 5.5.8 – in search of stability

shawn53

A couple of days ago, Dimitri published a blog post, Analyzing Percona's TPCC-like Workload on MySQL 5.5, which was a response to my post, MySQL 5.5.8 and Percona Server: being adaptive. I will refer to Dimitri's article as article [1]. As always, Dimitri has provided a very detailed and thoughtful article, and I strongly recommend reading if you want to understand how InnoDB works. In his post, Dimitri questioned some of my conclusions, so I decided to take a more detailed look at my findings. Let me show you my results.

Article [1] recommends using the innodb_max_dirty_pages_pct and innodb_io_capacity parameters to get stable throughput in MySQL 5.5.8. Let's see what we can do with them. Article [1] also advises that innodb_log_file_size is not important for stable throughput.


For my tests, I again used the Cisco UCS C250 box with 346GB of RAM , and I ran the tpcc-mysql benchmark with 500W (about 50GB of data) on the FusionIO 160GB SLC card. For innodb_buffer_pool_size I used 26GB to represent about a 1/2 ratio of buffer_pool_size to data.

For the initial tests, I used MySQL 5.5.8 (the tar.gz binary from dev.mysql.com), and for the other tests I used Percona Server based on 5.5.8. Addressing a complaint to my previous post, I am sharing the percona-server-5.5.8.tar.gz I used for testing, but please note: It is very pre-beta and should not be used in production. You can download it from our TESTING area.

In order to test different settings in a short period of time, I used 30-minute runs, which may not be long enough to see the long-term trend, but we will see the effects anyway. The full command line to run the test is: tpcc_start localhost tpcc500w root "" 500 32 10 1800. For better understanding the results for each run, I will show different graphs:
benchmark throughput - This is New Order Transactions per 10 seconds.
dirty page - This graph will contain the percentage of dirty pages in the InnoDB buffer pool. This value is calculated from the output of mysqladmin ext -i10 using this formula: (100*Innodb_buffer_pool_pages_dirty)/(1+Innodb_buffer_pool_pages_data+Innodb_buffer_pool_pages_free). This is the exact formula that InnoDB uses internally to estimate current innodb_dirty_pages_pct.
checkpoint age - This is a value in MB or GB and shows what amount of the space in innodb_log_file corresponds to changed pages in the buffer pool. You can compute this value as Log sequence number - Last checkpoint at from SHOW ENGINE INNODB STATUS.

Here are the InnoDB settings for the initial run. Later I will change them in searching for optimal values.
PLAIN TEXT
CODE:
innodb_file_per_table = true
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M

innodb_buffer_pool_size = 26G

innodb_buffer_pool_instances=16

innodb_log_file_size = 2000M
innodb_log_files_in_group = 2

innodb_read_io_threads = 16
innodb_write_io_threads = 16

innodb_purge_threads=1
innodb_adaptive_flushing=1
innodb_doublewrite=1


Please note that initially I used the default value for innodb_max_dirty_pages_pct, which is 75, and the default value for innodb_io_capacity, which is 200. I also enabled innodb_doublewrite. As will appear later, it is quite a critical parameter.

So, the results for the initial run, using MySQL 5.5.8:





Let me explain the second graph a little. I put checkpoint age and dirty pages percentage on the same graph to show the relationship between them. Checkpoint age is shown by the red line, using the left Y-axis. Dirty pages are shown by the blue line, using the right Y-axis.

As expected, throughput jumps up and down. Checkpoint age is stable and is about 2854.02 MB. Checkpoint age is the limiting factor here, as InnoDB tries to keep the checkpoint age within 3/4 of the limit of the total log size (total size is 2000MB*2).

The 15-minute average throughput is 59922.8 NOTPM.

Okay, now following the advice in article [1], we will try to limit the percentage of dirty pages and increase I/O capacity.
So, I will set innodb_max_dirty_pages_pct=50 and innodb_io_capacity=20000.

Here are the results:




As we see, throughput is getting into better shape, but is far from being a straight line.
If we look at the checkpoint age/dirty pages graph, we see that the dirty pages percentage is not respected, and is getting up to 70%. And again we see the limiting factor is checkpoint age, which is getting up to 3000MB during the run.

The 15-minute average result for this test is 41257.6 NOTPM.

So, it seems we are not getting the stable result of article [1], and the difference is the doublewrite area. Doublewrite activity actually adds significant I/O activity. Basically, it doubles the amount of writes , as you see from its name. So, let's see what result we have when we disable doublewrite; that is, set innodb_doublewrite=0.




Now, although throughput is not a perfect line, we see a totally different picture for dirty pages and checkpoint age.
The dirty page maximum of 50% is still not respected by InnoDB, but the checkpoint age drops far below the 3000MB line. It is now on about the 1500MB line.

The 15-minute average result for this test is 63898.13 NOTPM. That is, by disabling the doublewrite area, we improved the result 1.55x times.

As it seems hard for InnoDB to keep 50% dirty pages, let's try 60%.

Here is the run with innodb_max_dirty_pages_pct=60.



Okay, now we finally see throughput more or less flat. The dirty page percentage is kept at the 60% level, and checkpoint age is at the 2000MB level; that is, not bounded by innodb_log_file_size.

The 15-minute average result for this test is 64501.33 NOTPM.

But we still have DOUBLEWRITE=OFF.

Since now we are limited by innodb_max_dirty_pages_pct, what will be the result if we try to increase it to 70% ?




It seems 70% is too big, and now we again hit the limit set by innodb_log_file_size.

The 15-minute average result for this test is 57620.6 NOTPM.

Let me summarize so far. With innodb_doublewrite disabled, we have stable throughput only with innodb_max_dirty_pages_pct=60. Setting this value to 50 or 70 gives us dips in throughput, though for different reasons. In the first case, InnoDB is unable to maintain the 50% level; in the second we are limited by the capacity of REDO logs.

So, what do we get if we again enable innodb_doublewrite, but we now set innodb_max_dirty_pages_pct=60?





This is a bummer. Throughput again jumps up and down. The dirty pages percentage is not respected, and InnoDB is not able to maintain it. And checkpoint age is back to 3000MB and again limited by innodb_log_file_size.

The 15-minute average result is 37509.73 NOTPM.

Okay, so what if we try an even smaller innodb_max_dirty_pages_pct, setting it to 30? (I use a 1-hour run in this case.)

The results:



I can't say if the resullt should be considered stable. There are still a lot of variations.

The 15-minute average result is 37039.73 NOTPM.

Let's try an even larger decrease, setting innodb_max_dirty_pages_pct=15.




This seems to be the most stable line I can get with MySQL 5.5.8.

The 15-minute average result is 37235.06 NOTPM.

This allows me to draw a conclusion which partially concurs with the conclusion in article [1]. My conclusion is: With doublewrite enabled, you can get a more or less stable line in MySQL 5.5.8 by tuning innodb_max_dirty_pages_pct and innodb_io_capacity; but the limiting factor is still innodb_log_file_size.

To prove it, I took Percona Server based on 5.5.8 and ran it in MySQL mode (that is, using adaptive_flushing from InnoDB and with the adaptive_checkpoint algorithm disabled), but with giant log files. I used a log file of 8000MB*2, just to see what the maximum checkpoint age is.

Okay, here are the results:




Success! With a big log file, we are getting stable throughput. Checkpoint age jumps up to 3900MB line, but the dirty page percentage is not kept within the 60% line, going instead up to the 70% line limit. That is, to get this stable throughput, we need a total log file size of about 3900MB + 25% = 5300MB.

The 15-minute average result for this test is 48983 NOTPM.

But what about innodb_max_dirty_pages_pct; can we get better results if we increase it? It's not respected anyway.
Let's try the previous run, but with innodb_max_dirty_pages_pct=75.



The 75% dirty pages line is at a stable level now, but something happened with throughput. It doesn't have holes, but there is still oscillating. Checkpoint age is quite significant, reaching 7000MB in the stable area, meaning you need
about 9000MB of log space.

The 15-minute average result for this test is 55073.06 NOTPM.

What can be the reason? Let's try a guess: flushing neighborhood pages.
Let's repeat the last run, but with innodb_flush_neighbor_pages=0.




Okay, we are back to a stable level. Checkpoint age is also back to 3000MB, and dirty pages are stable as well, but getting to 77%. I am not sure why it is more than 75%. It is a point for further research, but you are probably tired from all these graphs, as am I.

The 15-minute average result for this test is 52679.93 NOTPM. This is 1.4x better than we have with the stable line in MySQL 5.5.8.

But, finally, let me show the result I got running Percona Server in optimized mode:
PLAIN TEXT
CODE:
innodb_buffer_pool_size = 26G
innodb_buffer_pool_instances=1
innodb_log_file_size = 8000M
innodb_log_files_in_group = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity=500

innodb_max_dirty_pages_pct = 60
innodb_purge_threads=1
innodb_adaptive_flushing=0
innodb_doublewrite=1
innodb_flush_neighbor_pages=0
innodb_adaptive_checkpoint=keep_average





The 15-minute average result is 73529.73 NOTPM.

The throughput is about 1.33x better than in "MySQL compatible mode", though it requires 10500MB for checkpoint age; that is, 14000MB of log space. And, the Percona Server result is ~2x better than the best result I received with MySQL 5.5.8 (with innodb_doublewrite enabled).

In summary, my conclusion is: You can try to get stable throughput in MySQL 5.5.8 by playing with innodb_max_dirty_pages_pct and innodb_io_capacity and having innodb_doublewrite enabled. But you must have the support of big log files (>4GB) to help increase throughput.

Basically, by lowering innodb_max_dirty_pages_pct, you are killing your throughput. When you disable innodb_doublewrite, you can get stable throughput if you are lucky enough to find a magic innodb_max_dirty_pages_pct value. As you saw in the results above, 50 and 70 are not good enough, and only 60 gives stable throughput.

(Post edited by Fred Linhoss)