Implementation Details: Improved InnoDB I/O Scalability (fix_broken_group_commit)

(The followings are just my current understanding about broken group commit)

Why group commit is broken after 5.0 with binlog?

As you know, the group commit of InnoDB doesn't work after MySQL 5.0 with binlog. And as the bug report (http://bugs.mysql.com/bug.php?id=38501) describes, the key point is in innobase_xa_prepare() function.


This mutex doesn't allow concurrent execution of commit.

Before the discussion about how to fix it, we must understand the intention of the mutex. The comment before the mutex says, to keep the order of binlog and InnoDB transaction log same.

… It is not depend on which we use xa_support or not. (moving the mutex to the after of

if (!thd->variables.innodb_support_xa) {}

may not make sence anymore…(patch of Bug#38501)) So, if we want to keep the record order of binlog and InnoDB transaction log same, we must give up “group commit”.

MySQL 4.1 allows group commit with binlog?

Yes, I think 4.1 has possibility that the order of binlog and innodb_log is not kept same…

Why we need to keep the order of binlog and innodb_log?

If the order of binlog is different from innodb_log, slave database may have moment of unreal state for master database, or we cannot use binlog for exact recovery (We cannot choose position of binlog for such “unreal state” in strict meaning)…

However, many of users may not have problem with this “unreal state”, because InnoDB manages transactions in correct order. (Conflicted transactions cannot be committed as same group, and applying binlog may not have problem… Hmm…)

Do you want nearly 100% exact recovery?

I suggest to add “innodb_enable_unsafe_group_commit=[0|1]” parameter and the parameter allows to skip the prepare_commit_mutex. So, we can choose,

* group commit (exactness of binlog may be the same as 4.1?)


* exact binlog

(This parameter have been implemented as innodb_io)

percona-server/features/implementation_details/fix_broken_group_commit.txt · Last modified: 2015/08/05 15:37 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054


Share This
]]> ]]>