This sub-task has been created in order to allow the main issue to remain under Sujatha all the time. While it's blocking it, there will be a link between them to say so.

axel I've added 3 days of estimation to avoid to have 0. But please update it with what you think.

I test a recent 10.6 build with loads with increasing write rate (90:10, read/write, write-only). Configurations are

• binlog enabled, all defaults (sync_binlog = 0)
• sync_binlog = 1
• sync_binlog = 1, commit_wait_count = 1
• sync_binlog = 1, commit_wait_count = 8

In a first test run I have seen quite some impact from binlog_commit_wait_count = 8. I have however not seen any impact from binlog_commit_wait_usec.

I see no point in testing with innodb_flush_log_at_trx_commit = 0. Or read-only workload.

Up to 64 threads (sysbench1.pdf) there is a clear trend. Latency is about double and throughput about half for the configurations with sync_binlog=1.

For commit_wait_count=8 there is clearly a penalty for less then 8 benchmark threads and a performace benefit from 8 threads onward. This is expected. The commit_wait_count=1 line is identical to the one with only sync_binlog=1. Hence not shown in the diagrams.

At 128 and 256 benchmark threads (sysbench2.pdf, sysbench3.pdf) we have some saturation effects, causing the sync_binlog=1 variants to get up.

Conclusion: the effect of sync_binlog=1 on performance is about what would be expected. The extra disk write is visible by double the latency / half the throughput. Setting commit_wait_count to something higher than 1 helps a bit, but at the cost of througput at lower thread counts.

sujatha.sivakumar is that enough information for you?

Hello Axel,

Thank you for the benchmark results. My observations also seem to match with yours.

MDEV_16589_SysBench.ods

This information sufficient for us as we could observe that, with sync_binlog=1, latency is doubled and throughput is half.

Thank you.

Add a series with innodb_flush_log_at_trx_commit=0 to see what MDEV-18959 could do.

Uploaded a new result file: sysbench4.pdf. It shows 2 new results:

1. blue line: now with commit_wait_usec=5000 instead of using the default
2. magenta line: with flush_log_at_trx_commit=0

I left out the data points where it doesnt scale any more.