Shulou(Shulou.com)06/01 Report--

In the preface, the basic principle and configuration of MySQL semi sync version 5.6 are introduced. With the release of MySQL 5.7, the new version of MySQL fixes some bug of semi sync and enhances its functions. Asynchronization of sending binlog and accepting ack is supported

Support waiting for ACK before transaction commit

Determine whether the standby library requires semi-synchronization at the server layer to reduce Plugin lock conflicts

Resolve the conflict between binlog dump thread and lock_log, and so on. This paper focuses on the first and second improvements, because the original mode does affect the tps of the system, and the new asynchronous mode can improve the transaction processing ability of the system in semi-synchronous mode.

Second optimization

1 support asynchronization of sending binlog and accepting ack

From the previous introduction, we know that in Semisynchronous Replication mode, app commits a transaction on the main library / event,MySQL writes each transaction to binary and synchronizes each transaction to slave, and master waits for at least one slave notification: slave has received the transmitted events and written to relay log before returning to the callback layer to write successfully, or until the delivery log times out, the system will automatically be in asynchronous replication mode.

Logical diagram of the overall process

Disadvantages of version 5.5 semi sync design:

In principle and in the figure above, the old version of semi sync is limited to dump thread because dump thread takes on two different and frequent tasks: sending binlog to slave and waiting for slave feedback, and the two tasks are serial, and dump thread must wait for slave to return before sending the next events transaction. Dump thread has become the bottleneck of improving the performance of the whole semi-synchronous. In high concurrency business scenarios, this mechanism will affect the overall TPS of the database.

In order to solve the above problems, in the 5.7.4 version of the semi sync framework, an independent ack collector thread is created to receive feedback from slave. In this way, two processes on the master work independently, sending binlog to slave and receiving feedback from slave at the same time. Logical diagram of the overall process

The general idea of implementation is:

The slave IO thread uses the TCP protocol to interact with the main library, and the socket can be read and written at the same time. When the main library semisync is opened, start a background thread and use select to listen to the standby library to connect to the socket.

The dump thread no longer waits for the slave ACK;. While the ack reciver thread waits for ACK, the group commit thread can continue to send the next set of DumpThread, thereby promoting TPS.

2 support waiting for the new version of ACK; before the transaction commit adds the rpl_semi_sync_master_wait_point parameter to control how the main library commits the transaction before returning to the session transaction successfully in semi-synchronous mode.

This parameter has two values:

AFTER_SYNC (default): master writes each transaction to binlog, passes it to slave, and flushes it to disk. Master waits for slave feedback to receive the transaction and flush it to disk. Upon receiving feedback from slave, master commits the transaction in the main library and returns the result to the session. In AFTER_SYNC mode, all clients view submitted data at the same time. If the main library crash occurs, all transactions that have been committed on the main library have been synchronized to slave and logged to relay log. Switching to the slave library at this time can guarantee the minimum data loss.

AFTER_COMMIT: master writes each transaction to binlog, passes it to slave, flushes it to disk (relay log), and then commits the transaction in the main library. Master waits for slave feedback to receive the transaction and flush it to disk after committing the transaction. Once the slave feedback is received, the master feeds back the results to the client.

In AFTER_COMMIT mode, if slave does not apply logs, at this time master crash, the system failover to slave,app will find that the data is inconsistent, submitted in master but not applied by slave.

Three recommended reading notes: the last three blog from the MySQL replication development team, need to climb the wall, please bring your own ladder.

[1] 5.7 Semisynchronous Replication

[2] loss-less-semi-synchronous-replication MySQL 5.7modifies the order of transactions committed by the main library in semi-synchronization, and after sync mode avoids the occurrence of phantom reads.

[3] enforced-semi-synchronous-replication

MySQL 5.7semi-synchronous enhancement, adding rpl_semi_sync_master_wait_slave_count parameters to control how many slave write transaction successful feedback received by the main database before returning the success to the client.

[4] faster-semisync-replication

Modify the original dump thread sending event and receiving slave ack mode, separate the process of receiving slave and returning ack separately, and improve the tps of semi-synchronous mode.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.