The new MySQL-8.0.20 release is coming with re-designed InnoDB Double Write Buffer (DBLWR), and, indeed, it's one huge historical PITA less.. -- why it was so painful and cost us much blood in the past, I could not better explain than already done it in the following article yet from 2018 about MySQL on IO-bound workloads.. The story is not complete, as it's missing the 2019's chapter (will tell it later, np) -- but if you'll (re)read the mentioned above article first, you'll better understand the next ;-))
But at least the current post is only about good news now -- the new DBLWR and how it helps to solve historical MySQL performance problems ! -- and as one picture is better than million words, I'll try to save 3M words here (as there are 3 pictures in this article ;-))
Well, I'll also skip all new design details (I think Sunny will better explain them all himself "from the first hands") -- I'll only mention the following :
- the DBLWR is no more part of "system tablespace", and can be placed anywhere you like (so, if you have a possibility to use a different storage for DBLWR files, you can totally get a rid of DBLWR impact on your main storage) -- but by default, DBLWR is stored in the same directory as your DATA
- you can configure how many DBLWR files you want to use
- and also how many pages per DBLWR files to have (which is also directly related to final DBLWR file size)
For more details about config options you can check MySQL 8.0 doc about DBLWR explaining this all in details.
Enough words, let's go to the test results..
Test Workload Scenario
- a simple Sysbench OLTP_RW test is used here
- 8 tables, each of 50M rows (~100GB data)
- InnoDB Buffer Pool (BP) is sized first to :
- 128GB (in-memory workload)
- then 64GB (partially IO-bound)
- then 32GB (heavy IO-bound)
- load level is progressively growing from 1, 2, 4 .. 1024 concurrent users
- same test is executed on MySQL-8.0.19, then MySQL-8.0.20
HW/SW Platform / Config
- Server : 48cores-HT Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz, 192GB RAM
- Storage : Intel Optane NVMe, XFS filesystem
- OS : OL7.6, kernel-4.14.35-1902.301.1.el7uek.x86_64
- MySQL config : full
my.confused during the testing is attached at the end
- Note : DBLWR files are configured by default and stored in the same directory as DATA and REDO (also by default as well)
And few words about the following graphs.. -- they are not really "user friendly" (as marketing guys would love ;-)) but they are simply reflecting real TPS levels obtained over growing load levels. And TPS is growing as the load level is growing (1 user first, then 2, then 4, and so on, from the left to right) -- well, feel free to ping me if you're finding something unclear, etc. ;-))
Using 128GB Buffer Pool
- as expected, DBLWR is not impacting on in-memory workloads as long as you're able to setup your MySQL instance to hide doubled latency of page O write..
- (this can be achieved by using faster storage or bigger REDO size, etc.)
- page flushing is going in background, and as soon it'll go fast enough to follow your REDO writes, you're safe ;-))
Using 64GB Buffer Pool
- however, once your workload becomes IO-bound, the game is changing..
- any delay on page writes will delay your IO reads !
- and the old DBLWR design was not made for high concurrency..
- (but, as you can see, up to 64 users load level it was still ok ;-))
Using 32GB Buffer Pool
- with 32GB BP only 1/3 part of data can be kept "cached"
- and with "uniform" access pattern we're heavy IO-bound here..
- so, overall impact on TPS is yet more bigger
- but DBLWR impact is very similar to previous test result -- able to resist up to 64 users, then totally loosing performance..
- so far, one historical huge PITA is less.. -- KUDOS Sunny !! ;-))
- but mind that with MySQL 8.0 we're in "continuous release" model ;-))
- which simply means -- yet more fun stuff is coming ;-))
- so, stay tuned.. ;-))
Thank you for using MySQL !
[mysqld] # general max_connections=4000 back_log=4000 ssl=0 table_open_cache=8000 table_open_cache_instances=16 default_authentication_plugin=mysql_native_password default_password_lifetime=0 max_prepared_stmt_count=512000 skip_log_bin=1 character_set_server=latin1 collation_server=latin1_swedish_ci skip-character-set-client-handshake transaction_isolation=REPEATABLE-READ # files innodb_file_per_table innodb_log_file_size=1024M innodb_log_files_in_group=16 innodb_open_files=4000 # buffers innodb_buffer_pool_size=128000M / 64000M / 32000M innodb_buffer_pool_instances=24 innodb_log_buffer_size=64M # tune innodb_doublewrite=1 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=1 innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 join_buffer_size=32K sort_buffer_size=32K innodb_use_native_aio=1 innodb_stats_persistent=1 innodb_spin_wait_delay=6 innodb_max_purge_lag_delay=300000 innodb_max_purge_lag=0 innodb_flush_method=O_DIRECT innodb_checksum_algorithm=crc32 innodb_io_capacity=20000 innodb_io_capacity_max=40000 innodb_lru_scan_depth=1000 innodb_change_buffering=none innodb_read_only=0 innodb_page_cleaners=24 innodb_undo_log_truncate=off # perf special innodb_adaptive_flushing=1 innodb_flush_neighbors=0 innodb_read_io_threads=16 innodb_write_io_threads=4 innodb_purge_threads=4 innodb_adaptive_hash_index=0 # monitoring innodb_monitor_enable='%' performance_schema=ON # etc. loose_log_error_verbosity=3 secure_file_priv= core_file innodb_buffer_pool_in_core_file=off