MySQL Bugs: #49326: Support group commit for the binlog

Bug #49326	Support group commit for the binlog
Submitted:	2 Dec 2009 5:22	Modified:	4 Jul 2012 14:49
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.0,5.1	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	binlog, group.commit

Description:
The innodb plugin supports group commit. MySQL replication binlog does not. Supporting group commit for InnoDB but not the binlog doesn't help enough.

The WL for this is secret -- http://forge.mysql.com/worklog/task.php?id=2492

I have a server with battery backed RAID and a busy workload. With sync_binlog=1 the server gets hopelessly behind. With sync_binlog=10 the server keeps up. 

Alas, MySQL doesn't export performance stats from the fsync() call done for the binlog, so I don't know what it considers the fsync latency to be. It could be that fsync() is fast but not fast enough -- that is just the cost of the system call while holding the mutex on the binlog might be too slow.

Battery for RAID cache is OK and service times reported by iostat are good -- await and svctm are less than 1ms.

idle time is high and that is a sign of mutex contention:

avg-cpu:  %user   %nice    %sys %iowait   %idle
           7.06    0.00    5.00    6.31   81.64

How to repeat:
Create popular social game backed by MySQL.
Watch load on db server.

Suggested fix:
implement group commit for the binlog

Mark,

Please have a look at WL#4007 and WL#4832.

WL#4832 does some internal refactoring to improve the speed of binary logging, but is a pre-requisite for WL#4007. WL#4007 require all the events to be stored in the transaction cache to be possible to implement efficiently. Once WL#4007 is implemented, the binlog have a form of "group commit".

We already have a patch for WL#4832 but to truly work well even for DDL, it require meta-data locking (WL#4284) since DDL operations are synchronized using  LOCK_open, so it was put on hold awaiting MDL to be pushed.

Both of these worklogs are available publicly as well at:

http://forge.mysql.com/worklog/task.php?id=4007
http://forge.mysql.com/worklog/task.php?id=4832

I want to expand this request to include changes to storage engines to benefit from this change:
1) InnoDB locks prepare_commit_mutex during the prepare phase, until that is removed there will be not concurrent transactions when binlog writes/syncs are done
2) InnoDB does not release row locks until the commit phase. Two fsyncs are done prior to that: one in the prepare phase, one for the binlog. Workloads with dependent transactions won't benefit much from this change unless InnoDB row locks are release earlier

Fixed in 5.6.6. To be documented with WL#5223, Closed.