MySQL Bugs: #77094: Reduce log_sys->mutex contention by allowing concurrent mtr commit and log write

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

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.

Hello Zhai ,

Thank you for the report and contribution.

Thanks,
Umesh

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.

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

    Bug#21352937 - REDUCE LOG_SYS->MUTEX CONTENTION BY ALLOWING CONCURRENT
    MTR COMMIT AND LOG WRITE

My result:

v5.7.7-rc
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

v5.7.6-m16
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?