Loss-less Semi-Synchronous Replication on MySQL 5.7.2

Loss-less Semi-Synchronous Replication on MySQL 5.7.2 Semi-synchronous replication is used by many users who want improved data integrity. Today I would like to introduce a s...

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.

attachments-2017-08-uss6tcv759a017017548


With this feature, semi-synchronous replication is able to guarantee:
  • All committed transaction are already replicated to at least one slave in case of a master crash.
That is obvious, because it cannot commit to storage engine unless the slave acknowledgement is received(or timeout).

It brings a couple of benefits to users:
  • Strong Data Integrity with no phantom read.
  • Ease recovery process of crashed semi-sync master servers.
Strong Data Integrity Eliminates Phantom Read On MySQL 5.5 and MySQL 5.6 transaction sessions need to wait for their slave acknowledgements (when semisync is on) just after the server commits to storage engine. And then they return a status to the clients after the slave acknowledgements are received or timeout.

attachments-2017-08-D6BVIHCr59a01737eae6

"Engine Commit" makes data permanent and release locks on the data. So other sessions can reach the data since then(引擎层提交后,数据改变已经落盘,其他用户可以看到,即使提交变更事务的session还在等待“确认消息,收不到这个消息,这个session的用户一直hold,看不到数据返回)), even if the session is still waiting for the acknowledgement. It will cause phantom read if master crashes and slave takes work over.(引擎先提交,然后session等待返回ack的信息)

attachments-2017-08-OrVVLpwr59a018766425

With this feature, the phantom read is impossible. User2 cannot get value 3 in the first SELECT, because it is not replicated to slave yet.
Ease recovery process of crashed semi-sync master
To make the crashed master server before MySQL 5.7.2 to work again, users need to:
  1. Manually truncate the binlog events which are not replicated.
  2. Manually rollback the transactions which are committed by not replicated.
Since this feature guarantees all committed transactions are replicated already, so 2nd step is not
needed any more.
How to Configure the Feature Configuration is pretty simple. Users can get its benefit even without any configuration. The feature is on by default on MySQL 5.7.2. A system variable is provided for switching back the original wait behavior.
  • rpl_semi_sync_master_wait_point
It is a global variable and can be changed dynamically. By default on MySQL 5.7.2, it is AFTER_SYNC which makes transaction sessions wait just after flushing its binlog events to disk(surely before committing to storage engine).

Set it to AFTER_COMMIT if you want to switch back the original wait behavior. But I didn't see any strong reason that users should switch back. There are no side effects and no performance impact.

For details please check MySQL Manual.
Optimizations on Dump Thread  While developing this feature we made some optimizations in the dump thread. We refactored its code base, but more importantly, dump thread no longer take binlog lock when read binlog events. That improved master throughput and reduced delay on replication. For detail please check the blog "Dump Thread Enhancement".
At the End To Conclude, this feature guarantees strong data integrity and consistency between master and slave without any side effects and performance impact. So I strongly recommend you to try it.

A special thanks to Mr. Zhenxing Zhou a community user who contributed a patch implementing a similar idea based on MySQL 5.5. Even though we did not take his patch (the 5.7 codebase changed quite a lot compared to 5.5), his feature request was yet another source of motivation and inspiration. 


  • 发表于 2017-08-25 20:24
  • 阅读 ( 48 )

你可能感兴趣的文章

相关问题

0 条评论

请先 登录 后评论
石天
石天

437 篇文章

作家榜 »

  1. shitian 662 文章
  2. 石天 437 文章
  3. 每天惠23 33 文章
  4. 小A 29 文章