The latest from the MySQL community

This article is also available in Chinese: 中文版. Browse all English articles.

Large transactions are a notorious problem in MySQL: they cause not only replication lag but also stability problems. A previous article, MySQL Large Transaction Commit Optimization, covered the problems a large transaction causes at commit time and the optimizations we made in AliSQL. This article looks at the problems a large transaction causes during semi-synchronous replication, and how AliSQL solves them.

In MySQL Large Transaction Commit …

On MySQL5.7.3, we released a new semi-synchronous feature which brings the advantage of stronger data integrity and durability. Please allow me to introduce you the feature.

I suppose you already know what semi-synchronous replication is. If that is not true, you can check Seim-Synchronous Replication chapter on MySQL reference manual. It describes semi-synchronous replication clearly, so I don't repeat it here again. Make Data Durable on More Than One Slaves The new feature allows users to set how many slaves the transaction(its binary events) should be replicated to before externalizing it to users. In other words, semisync master makes the transaction to wait until receiving acknowledgements from those many slaves. The simple process is demonstrated below. …

On the new milestone release MySQL 5.7.2, we did some optimizations  on binlog lock and dump thread. Major dump thread code was re-written: Some code never reached was removed; Complex
logic was simplified; Code became more readable. But I don't want to introduce the refactoring here. Today, I would like to introduce you the optimization on binlog lock which improved master throughput.
Background binlog lock(LOCK_log) is a mutex which is used to protect binlog read and write operations. With this mutex, binlog read and write operations are safe. But it has limited concurrency. Not only can dump threads and user sessions not read and write binlog simultaneously but even the dump threads themselves cannot read the binlog simultaneously.  All other sessions have to wait whenever one session …

Semi-synchronous replication is used by many users who want improved data integrity. Today I would like to introduce a semi-synchronous new feature  on MySQL 5.7.2 milestone release which enhances the data integrity between master and slave. 
Wait Slave Acknowledgement before Engine Commit As you know, with semi-synchronous replication enabled, user sessions will wait for acknowledgement from a slave before committing the transaction. To enhance data integrity, this feature makes transaction sessions wait a little bit earlier than MySQL 5.5 and MySQL 5.6. The wait happens just before the server commits to storage engine.

With this feature, semi-synchronous replication is able to guarantee:

• All committed transaction are already replicated to at least …