MySQL Bugs: #81376: Single page flushing should be removed

Bug #81376	Single page flushing should be removed
Submitted:	11 May 2016 6:48	Modified:	12 May 2016 14:21
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	flushing, innodb, performance

Description:
If doublewrite buffer is enabled, there are only eight slots for single-page flushes. When there are no free pages, doublewrite immediately becomes the bottleneck on OLTP RW disk-bound due to a low number of slots and general design:

660 pthread_cond_wait,enter(ib0mutex.h:850),buf_dblwr_write_single_page(ib0mutex.h:850),buf_flush_write_block_low(buf0flu.cc:1096),buf_flush_page(buf0flu.cc:1096),buf_flush_single_page_from_LRU(buf0flu.cc:2217),buf_LRU_get_free_block(buf0lru.cc:1401),...

631 pthread_cond_wait,buf_dblwr_write_single_page(buf0dblwr.cc:1213),buf_flush_write_block_low(buf0flu.cc:10 ...

If doublewrite buffer is disabled, the single page flushes show that cleaner threads are not coping with the workload. If the reason for that is hot buffer pool mutexes, the single page flush will only make things worse since it must take the same mutexes for the LRU list scan. Otherwise this is an indication of suboptimal cleaner tuning / heuristics / or maybe I/O is already the bottleneck and issuing more will not help.

How to repeat:
Performance and code analysis. The above reasoning is attempted to explain in a bit more detail at

https://www.percona.com/blog/2016/05/03/mysql-5-7-initial-flushing-analysis-and-why-perfor...
https://www.percona.com/blog/2016/05/05/percona-server-5-7-multi-threaded-lru-flushing/
https://www.percona.com/blog/2016/05/09/percona-server-5-7-parallel-doublewrite/

Suggested fix:
- remove single page flushing and any associated code
- in the situation of empty buffer pool free list, wait for the cleaner threads to produce a free page
- improve cleaner thread design so that the empty free list situation only arises when it's unavoidable (i.e. when flushing is already saturating I/O)

Hi Laurynas,

I do have some additional questions, that were not covered in the articles that you posted.

What was the hardware that you were using? 24 core? 64 core? 128 core?

What was the mysqld configuration?

Were there any additional plugin settings being used?

What other information might help in reproducing the bug?

Maybe you can update the bug with this additional information.

Thanks in advance.

I prefer this remain. That makes it easier to show how much faster MyRocks is compared to InnoDB for write-heavy workloads.

The configuration is the same as in , and you can find server settings there:

innodb_log_file_size=10G
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_instances=8
innodb_change_buffering=none
innodb_adaptive_hash_index=OFF
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_lru_scan_depth=8192
innodb_io_capacity=15000
innodb_io_capacity_max=25000
loose-innodb-page-cleaners=4
table_open_cache_instances=64
table_open_cache=5000
loose-innodb-log_checksum-algorithm=crc32
loose-innodb-checksum-algorithm=strict_crc32
max_connections=50000
skip_name_resolve=ON
loose-performance_schema=ON
loose-performance-schema-instrument='wait/synch/%=ON',

I think it was 48 cores, but I am not too sure, will ask Alexey S to confirm.

*as in https://www.percona.com/blog/2016/03/17/percona-server-5-7-performance-improvements/

re: reproducibility - workload IO bound sysbench/OLTP_RW, symptoms clearly visible  on any type of hardware, 16cores+HT/28cores+HT/etc. There is a metric for single page flushing so it's very easy to see when it starts to affect InnoDB performance.

MarkC,

Thank you so much for your feedback !!!

I never heard about MyRocks, but your words inspired me to push on this subject. 

We shall be hearing on the subject, yet ....

Laurynis,

Thank you for your feedback.

Verified.

https://www.percona.com/blog/2018/07/13/on-mysql-and-intel-optane-performance/