MySQL 5.7 Enhanced Semisync Replication performance optimization

This article mainly introduced the MySQL 5.7 Enhancement edition semisync replication performance optimization, this article emphatically explained supports sends the BINLOG and accepts the ACK the Asynchrony, the support waits for the ACK before the transaction commits two items, needs the friend to be possible to refer to under

A preface

The previous article describes the 5.5/5.6 version of the MySQL semi sync fundamentals and configuration, with the release of MySQL 5.7, the new version of MySQL fixes some bugs in semi sync and enhances functionality.

Support the asynchronous of sending Binlog and accepting ACK;

Supports waiting for an ACK before a transaction commits;

The server layer is used to determine whether the standby requires half synchronization to reduce the plugin lock conflict;

Unblock Binlog dump threads and lock_log conflicts, and so on.

This article focuses on the 1th, 2 improvements, because the original model does affect the system TPS, the new asynchronous mode can improve the system transaction capacity in the semi synchronous mode.

Two optimization

1. Support the asynchronous of sending Binlog and accepting ACK

With the previous introduction, we know that in semisynchronous replication mode, the app commits a transaction on the main library/event,mysql writes each transaction to binary and synchronizes to slave. Master waits for at least one slave notification: Slave has received the event passed and written to relay log to return to the reply-layer write success, or until the transfer log times out, the system will automatically be asynchronous replication mode.

A logical diagram of the overall process

The disadvantages of the 5.5 version semi sync design:

In principle as well as above, the old version of semi sync is limited to dump thread because dump thread takes on two different and very frequent tasks: transferring Binlog to Slave and waiting for slave feedback, and the two tasks are serial, The dump thread must wait for the slave to return before the next events transaction is routed. Dump thread has become the bottleneck in the performance of the whole half synchronization in a high concurrency business scenario, such a mechanism would affect the TPS of the database as a whole.

In order to solve the above problem, in the 5.7.4 version of the Semi Sync Framework, an ACK collector thread is independent, dedicated to receive slave feedback information. So master has two processes working independently, sending Binlog to slave at the same time, and receiving slave feedback. A logical diagram of the overall process

The general realization of the idea is:

The standby IO thread uses the TCP protocol and the main library to interact, the read and write sockets can be simultaneously, when the main library Semisync is opened, a background thread is started, and a select listener is used to connect the socket;

The dump thread no longer waits for a standby ack, and when the ACK Reciver thread waits for an ACK, the dump thread can continue to send the next set of group commit Binlog to promote the TPS.

2 support waits for an ACK before a transaction commits;

The new version of Semi sync adds the Rpl_semi_sync_master_wait_point parameter to control how the primary library submits the transaction before returning to the session transaction success in the semi-synchronous mode.

This parameter has two values:

After_sync (default): Master writes each transaction to Binlog, passes it to slave, and flushes to disk. Master waits for the slave feedback to receive the transaction and flushes to disk. Once the slave feedback is received, master commits the transaction in the main library and returns the result to the session. In After_sync mode, all clients view the submitted data at the same time. If a primary library crash occurs, all transactions that have been committed on the main library have been synchronized to slave and logged to relay log. Switch to from the library at this time, can protect the smallest data loss.

After_commit:master writes each transaction to Binlog, passes it to slave to disk (relay log), and submits the transaction in the main library. Master waits for slave feedback to receive transactions and flushes to disk after committing the transaction. Once the slave feedback is received, master feeds the results to the client.

In after_commit mode, if slave does not have an application log, at this point master crash, the system failover to Slave,app will find inconsistencies in the data being submitted in master and slave not applied.