InnoDB Plugin 1.1 doesn’t add any recovery specific improvements on top of what we already have in Plugin 1.0.7. The details on the latter are available in this blog. Yet, when I tried to recover another big recovery dataset I created, I got the following results for total recovery time:
- Plugin 1.0.7: 46min 21s
- Plugin 1.1: 32min 41s
Plugin 1.1 recovery is 1.5 times faster. Why would that happen? The numerous concurrency improvements in Plugin 1.1 and MySQL 5.5 can’t really affect the recovery. The honor goes to Native Asynchronous IO on Linux. Let’s try without it:
- Plugin 1.1 with –innodb-use-native-aio=0: 49min 07s
which is about the same as 1.0.7 time. My numerous other recovery runs showed that the random fluctuations account for 2-3min of a 30-45min test.
Why is native AIO good for you? Why is it better the than the simulated AIO we already have? Here’s what Inaam Rana, our IO expert and the author of the AIO patch, says:
- During recovery typically redo log application is performed by the IO helper threads in the completion routine.
- With simulated aio the thread waits for IO to complete and then calls the completion routine.
- With native aio the thread doesn’t have to wait for io to complete, instead it picks a completed request and applies redo to it.
Read more about native AIO here.
You don’t have to do anything to take advantage of this feature. It is enabled by default and is used where available as determined by configure.
Here are some details about the test environment:
Hardware: HP DL480, 32G RAM, 2×4 core Intel(R) Xeon(R) CPU E5450 @ 3.00GHz, RAID5, about 1T total storage
Dataset: 1757549 dirty pages, 2808364565 bytes of redo. For the curious, it was a sysbench table with 400 million rows and the workload I used was random row update by a simple perl script. Note that this is over 28G worth of dirty pages which means I had to use a very abusive settings of innodb_buffer_pool=29G and innodb_max_dirty_pages_pct=99, given only 32G of RAM. The recovery was done using the same settings and in the first few attempts the recovery would fail because of what was eventually diagnosed as bug 53122. As it happens, InnoDB recovery uses some memory outside of the buffer pool and it wanted more of it that was really necessary.
InnoDB configuration parameters:
–innodb-buffer-pool-size=28g
–innodb-log-file-size=2047m
–innodb-adaptive-flushing=0
–innodb-io-capacity=100
–innodb-additional-mem-pool-size=16m
–innodb-log-buffer-size=16m
–innodb-adaptive-hash-index=0
–innodb-flush-log-at-trx-commit=2
–innodb-max-dirty-pages-pct=99
This is highly artificial setup that targets maximizing the generation of dirty pages and redo, and using as much memory as possible for those dirty pages.
May 19th, 2010 at 2:29 am
innodb-io-capacity=100
Is it too small, even for the recovery ?
[Reply]
Michael Izioumtchenko Reply:
May 19th, 2010 at 10:55 pm
@David,
innodb-io-capacity=100 is an entirely artificial setting used
to quicker dirty the pages by throttling the flushing in
InnoDB master thread.
[Reply]
May 25th, 2010 at 1:39 am
I was under the impression that InnoDB 1.1 was built-in (not “plugin”)? http://bugs.mysql.com/bug.php?id=53771
[Reply]
Michael Izioumtchenko Reply:
May 25th, 2010 at 5:58 pm
@Roel,
By now the word Plugin refers more to the codebase than the distribution
mode.
In 5.5 there’s only one InnoDB which is Plugin code based.
In fact InnoDB Plugin 1.0.7GA is also
‘builtin’ in a sense that it is now distributed as part of MySQL,
not separately, however what was known as ‘builtin InnoDB’ is also distributed.
The two are still referred to as ‘builtin’ and ‘plugin’ in 5.1.47.
[Reply]
Roel Reply:
May 26th, 2010 at 2:11 am
@Michael Izioumtchenko,
Yes for 5.1.47 there would still be a pluginbuiltin differentiation. What I would propose for 1.1 though is that it is called “InnoDB 1.1″, not InnoDB plugin 1.1, similar to the bug I quoted and the updated documentation – this may create less confusion?
[Reply]
July 2nd, 2010 at 4:50 pm
Thank you very good
[Reply]