As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.
In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.
We suggested the “adaptive_checkpoint” option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option “adaptive_flushing” as native.
Let’s check the adaptive flushing options at this post.
HOW THEY WORK
< adaptive_checkpoint=reflex (older method)>
| oldest modified age %/max | behavior |
| 0 ~ 50% | nothing |
| 50% ~ 75% | constant flushing (weak) |
| 75% ~ 87.5% | constant flushing (strong) |
| 87.5% ~ | (flushing storm) |
< adaptive_checkpoint=estimate (newer method)>
| oldest modified age %/max | behavior |
| 0 ~ 50% | nothing |
| 50% ~ 87.5% | estimate flushing as bellow * |
| 87.5% ~ | (flushing storm) |
* estimate blocks to flush based on…
< adaptive_flushing (default “true” at 1.0.4)>
Its behavior is not based on the oldest modified age %.
* the how many blocks to flush based on…
(and adjust along with another flushing)
And it doesn’t exceed the equivalent to “constant flushing (strong)”
of the “adaptive_checkpoint=reflex”
RESULTS
TPC-C like workload (100WH: 16 session full)
innodb_buffer_pool_size = 16G
innodb_max_dirty_pages_pct = 90
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_io_capacity = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_ibuf_active_contract = 1
<none>
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = none
<flushing>
innodb_adaptive_flushing = true
<reflex>
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = reflex
<estimate>
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = estimate
case1: “innodb_doublewrite = false”
[0~1800 sec.]
[1200~1800 sec.]
averages (1200~1800 sec.)
none:Â Â Â Â 6868.92
flushing: 6655.92
reflex:Â Â Â Â 6481
estimate: 6575.88
case2: “innodb_doublewrite = true”
[0~1800 sec.]
[1200~1800 sec.]
averages (1200~1800 sec.)
none:Â Â Â Â 6569.48
flushing: 5090.12
reflex:Â Â Â 6871.9
estimate: 6609.9
CONSIDERINGS
The new “adaptive_flushing” seems to be tuned for “innodb_doublewrite = false” only.
(cause too much flushing for “innodb_doublewrite = true”)
“innodb_adaptive_checkpoint = reflex” and “adaptive_flushing” need tuning innodb_io_capacity properly.
(The result is based on proper value “innodb_io_capacity = 4000”)
“innodb_adaptive_checkpoint = estimate” is not depend on innodb_io_capacity, and it seems more “soft” result than the other methods.
So,
The “adaptive_flushing” seems good when we use “innodb_doublewrite = false”.
“innodb_adaptive_checkpoint = estimate” may be safe for “innodb_doublewrite = true” for now.
I should adjust “adaptive_flushing” for “innodb_doublewrite = true”. 🙂
IMPROVEMENTS (16 Sep.)
I have tried to improve the performance. But I couldn’t adjust performance of adaptive_flushing with doublewrite… So, instead of that, estimate is adjusted more.
case1: “innodb_doublewrite = false”
[0~1800 sec.]
case2: “innodb_doublewrite = true”
[0~1800 sec.]
The new “innodb_adaptive_checkpoint = estimate” seems to be more stable than before. The next question is, “Why is estimate + doublewrite better than the other no doublewrite settings?”… 🙂
ADDITIONAL TEST IN ANOTHER BALANCE
Also tested in another configuration to check the behaviors in another balance of IO bound.
<changes>
innodb_buffer_pool_size = 2GÂ Â (1/8)
innodb_log_file_size = 128MÂ Â Â (1/4)
case1: “innodb_doublewrite = false”
[0~1200 sec.]
case2: “innodb_doublewrite = true”
[0~1200 sec.]
“innodb_adaptive_checkpoint = estimate” seems stable but seems to flush much in its initial phase. The adaptive_flushing seems to be more soft in this cases.
Hmmm….
We should choose suitable adaptive_xxx for each workload for now… 🙂
Resources
RELATED POSTS
