MySQL Bugs: #115328: Poor write performance due to new default value of innodb_doublewrite

Bug #115328	Poor write performance due to new default value of innodb_doublewrite_pages
Submitted:	14 Jun 2:13	Modified:	18 Jun 7:00
Reporter:	SeWoong Jeon	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	8.4.0	OS:	Red Hat (Rocky Linux 8.8)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	doublewrite, innodb

Description:
In version 8.4 LTS, the default value of the innodb_doublewrite_pages parameter changed to 128. 
This seems to be causing poor write performance.

Tested using sysbench, and the spec of the test machine are as follows.

HW : DELL R640
CPU : Intel(R) Xeon(R) Silver 4310 CPU @ 2.10GHz x 24core
RAM : 128GB
Strage : NVME 3.2TB
OS : Rocky Linux 8.8

The innodb configuration is as follows.

innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 64M
innodb_data_file_path = ibdata1:512M:autoextend
innodb_temp_data_file_path = ibtmp1:512M:autoextend
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 60
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_strict_mode=1
innodb_numa_interleave = ON
innodb_adaptive_hash_index = OFF
innodb_flush_neighbors = 0
innodb_purge_threads = 4
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_parallel_read_threads = 4
innodb_ddl_buffer_size = 1G
innodb_ddl_threads = 4
innodb_io_capacity = 20000
innodb_lru_scan_depth = 20000
innodb_redo_log_capacity = 8G
innodb_max_dirty_pages_pct = 90

=== Result ===
sysbench 16thd
 innodb_doublewrite_pages = 128 - queries: 22852.44 per sec. / avg latency: 4.20ms
 innodb_doublewrite_pages = 16  - queries: 24259.34 per sec. / avg latency: 3.96ms

sysbench 64thd
 innodb_doublewrite_pages = 128 - queries: 35491.13 per sec. / avg latency: 10.82ms
 innodb_doublewrite_pages = 16  - queries: 40553.01 per sec. / avg latency: 9.47ms

Looking at the data collected in performance_schema.file_summary_by_event_name, 
the SUM_TIMER_WRITE value is higher when the innodb_doublewrite_pages is 128.

<8.4.0 / innodb_doublewrite_pages=128>
EVENT_NAME                              COUNT_WRITE     SUM_TIMER_WRITE    AVG_TIMER_WRITE    truncate(SUM_NUMBER_OF_BYTES_WRITE/1024/1024, 0)
wait/io/file/innodb/innodb_dblwr_file   474846          9133864376751562   19235424298        924514

<8.4.0 / innodb_doublewrite_pages=16>
EVENT_NAME                              COUNT_WRITE     SUM_TIMER_WRITE    AVG_TIMER_WRITE    truncate(SUM_NUMBER_OF_BYTES_WRITE/1024/1024, 0)
wait/io/file/innodb/innodb_dblwr_file   3928403         7809780900060042   1988029146         980760

How to repeat:
sysbench --db-driver=mysql --table-size=140000000 --tables=8 /usr/share/sysbench/oltp_write_only.lua prepare
sleep 5 min.
CALL sys.ps_truncate_all_tables(FALSE)

sysbench --db-driver=mysql --table-size=140000000 --tables=8 --time=1500 --threads=16 /usr/share/sysbench/oltp_write_only.lua run 
sleep 3 min.
sysbench --db-driver=mysql --table-size=140000000 --tables=8 --time=1500 --threads=64 /usr/share/sysbench/oltp_write_only.lua run

Could you try, please, to replay the same tests on your system, but just using most of 8.4 defaults like just the following :

innodb_buffer_pool_size = 64G
innodb_dedicated_server = ON

(BP size remains 64GB to respect your IO-bound test conditions)

Thanks for the advice!!

As you suggested, I removed all innodb related settings from my.cnf and left only the "innodb_buffer_pool_size = 64G & innodb_dedicated_server = ON".
Although throughput has increased compared to the last test, changes in redo log capacity and background thread settings seem to have had an effect.

So, I compared the results of a test run with only the "dedicated_server = on" setting and the test results with the "innodb_doublewrite_pages = 16" setting added.

The gap has narrowed since the previous test, but write performance was still good when innodb_doublewrite_pages = 16 was set.
I took the test 3 times and the results were similar.

=== Previous Test Result (dedicated_server = off) ===
sysbench 16thd
 innodb_doublewrite_pages = 128 - queries: 22852.44 per sec. / avg latency: 4.20ms
 innodb_doublewrite_pages = 16  - queries: 24259.34 per sec. / avg latency: 3.96ms

sysbench 64thd
 innodb_doublewrite_pages = 128 - queries: 35491.13 per sec. / avg latency: 10.82ms
 innodb_doublewrite_pages = 16  - queries: 40553.01 per sec. / avg latency: 9.47ms

=== New Test Result (dedicated_server = on) ===
sysbench 16thd
 dedicated_server = on                                  - queries: 46993.39 per sec. / avg latency: 2.04ms
 dedicated_server = on + innodb_doublewrite_pages = 16  - queries: 47976.09 per sec. / avg latency: 2.00ms

sysbench 64thd
 dedicated_server = on                                  - queries: 64287.17 per sec. / avg latency: 5.97ms
 dedicated_server = on + innodb_doublewrite_pages = 16  - queries: 68369.34 per sec. / avg latency: 5.61ms

I'd rather expect to see you excited to obtain mostly *twice* better performance on your test workload with the new 8.4 defaults, but not ;-))

Now, regarding DBLWR tuning and any other :

- mind that defaults are not supposed to be the best choice for any workload !
- the main point is to make them to be "good enough" in the most cases
- additionally, it's not only about performance, but also about overall data safety as the first priority !
- so, you can always fine a better tuning for a given workload
- but this will not mean it'll be also good in another workload, or on another system, etc.