The problem with broken group commit was discusses many times, bug report was reported 3.5Â years ago and still not fixed in MySQLÂ 5.0/5.1 (and most likely will not be in MySQLÂ 5.1). Although the rough truth is this bug is very hard (if possible) to fix properly. In short words if you enable replication (log-bin) on server without BBU (battery backup unit) your InnoDB write performance in concurrent load drops down significantly.
We wrote also about it before, see “Group commit and real fsync” and “Group commit and XA“.
The problem is the InnoDB tries to keep the same order of transactions in binary logs and in transaction logs and acquires mutex to serialize writes to both logs. We basically propose to break this serialization – in XtraDB release3 (will be announced soon, you can take current version for testing from Launchpad) we introduce –innodb-unsafe-group-commit mode. There are results with this options vs without (results are in transactions per second, more is better, this is sysbench OLTP load).
I tested it on Dell PowerEdge R900 with RAID 10 in WriteThrough mode to emulate absence of BBU. With BBU you will not see this problem (all results will scale well) as internal RAID cache will accumulate changes and return fsync() call immediately without real syncing data in disk.
So what can be wrong if you run –innodb-unsafe-group-commit â€” as I said there is possibility that transactions in binary-logs will be in different order than in InnoDB transactional log. Why this is bad? For example if box crashes and InnoDB does recovery: transactions on slaves may be executed in different order â€” that is you MAY get slaves unsynchronized with master. Is performance benefit worth it? It’s up to you, but I think better to have this choice then do not have.
I do not urge to use –innodb-unsafe-group-commit, I propose to have BBU on your RAID. But if it appears you don’t have it, and write load on server is significant â€” it may worth to try –innodb-unsafe-group-commit.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.