Bug #77094 Reduce log_sys->mutex contention by allowing concurrent mtr commit and log write
Submitted: 19 May 2015 13:13 Modified: 23 Aug 2016 1:58
Reporter: zhai weixiang (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.7 OS:Any
Assigned to: CPU Architecture:Any

[19 May 2015 13:13] zhai weixiang
Currently, log_sys->mutex is requested by log_write_up_to and mtr_commit. I think both operations can be executed concurrently by adding a extra buffer.

When we allocate two buffer, while one is occupied by log_write_up_to, another one can be used by mtr_commit. I also added a mutex to protect redo log writing.

I observed obvious performance improvement with the patch attached (based on 5.7.7-rc).

It seems TPS is not changed if setting innodb_flush_log_at_trx_commit to 1 because the bottleneck is fsync whick didn't acquire log_sys->mutex.

How to repeat:
My test is simple.

sysbench,  50 tables with 100,000 records,  update_non_index.lua,  all data fits in memory.

innodb_flush_log_at_trx_commit  = 2
sync_binlog = 1000

threads,    original (update/sec),  patched version (update/sec)
16,            32000,                        33000
32,            36000,                        40400
64,            38000,                        43500
128,          39000,                        46500
256,          37500,                        44500

Suggested fix:
I'll attach a patch later. This patch is not fully tested. It's only used to vefify the performance change.
[19 May 2015 13:14] zhai weixiang
patch to prove the performance improvement, not fully tested. based on 5.7.7

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: double_redo_buffer.diff (application/octet-stream, text), 17.99 KiB.

[1 Jul 2015 5:01] MySQL Verification Team
Hello Zhai ,

Thank you for the report and contribution.

[10 Mar 2016 13:24] Daniel Price
Posted by developer:
Fixed as of the upcoming 5.8.0 release, and here's the changelog entry:

Log buffer contention was reduced with the addition of a second buffer,
allowing for concurrent log buffer writing and flushing. A new mutex was
added to protect log buffer flushing. 

Thanks to Zhai Weixiang for the patch.
[23 Aug 2016 1:58] zhai weixiang
I found this bug was fixed in 5.7 but not appeared on release note.

Relevant commit:

commit aa47808311914c2d0bbaa9f3ae44379d59089122
Author: Bin Su <bin.x.su@oracle.com>
Date:   Wed Apr 13 11:45:41 2016 +0800

[9 Oct 2018 14:11] haochen he
My result:

innodb_flush_log_at_trx_commit=2 (avg latency)
16		2.12ms
64		10.19ms
128		22.36ms

innodb_flush_log_at_trx_commit=1 (avg latency)
16		2.28ms
64		11.05ms
128		22.5ms

innodb_flush_log_at_trx_commit=2 (avg latency)
16		2.27ms
64		11.02ms
128		23.73ms

innodb_flush_log_at_trx_commit=1 (avg latency)
16		2.48ms
64		11.18ms
128		22.03ms

The result seems consistent with you. Wonder why should I increase the number of threads to reveal this degration. Shouldn't 'mtr commit' and 'log write' happen no matter how many concurrent client is online?