Bug #73018 improve performance when binlog_order_commits=0 and sync_binlog >1

• Submitted: 17 Jun 2014 3:20
• Modified: 22 Jun 2015 10:02
• Reporter: zhai weixiang (OCA)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S5 (Performance)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[17 Jun 2014 3:20] zhai weixiang

Description:

If sync_binlog is setting to a non-zero value (for example,  1000), then every 1000  commit group, we need to sync the binlog file. 
The fsync operation is very slow. If binlog_order_commits is turned off, then we can let the following commit group skip  the sync stage (don't wait for LOCK_sync). This will bring some performance improvements. 

I slightly changed the code and make s simple test.

test case:
use sysbench, 32threads, 20 tables with 30,000 records, update_index.lua

set global sync_binlog = 1000;
set global binlog_order_commits = 0;

TPS: 3.2w => 3.5w (binlog_skip_sync_stage= 1)

How to repeat:
test with the attached patch

Suggested fix:
I will attach a simple patch to prove the performance improvement ( not a clean patch , dirty read/modify sync_counter)

[17 Jun 2014 3:28] zhai weixiang

based on 5.6.19

Attachment: bug73018.diff (application/octet-stream, text), 4.17 KiB.

[17 Jun 2014 5:12] zhai weixiang

edit synopsis

[18 Jul 2014 15:48] zhai weixiang

patch based on 5.7.4

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

Contribution: bug73018.diff (application/octet-stream, text), 5.00 KiB.

[14 Aug 2014 23:41] zhai weixiang

any comment ?