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.conf used 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..

SUMMARY

  • 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 !

Rgds, -Dimitri

APPENDIX

my.conf :

[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