MySQL 5.7 enhanced version Semisync Replication performance optimization, mysqlreplication

MySQL 5.7 enhanced version Semisync Replication performance optimization, mysqlreplication

Preface

The previous article introduced the basic principles and configurations of MySQL semi sync in MySQL 5.5/5.6. With the release of MySQL 5.7, the new version of MySQL fixes some bugs in semi sync and enhances functions.

Supports asynchronous sending of binlogs and ack reception;
Supports waiting for ACK before the transaction commit;
Determine whether the slave database requires semi-sync at the server layer to reduce plug-in lock conflicts;
Unbind binlog dump thread conflicts with lock_log.

This article focuses on the 1 and 2 improvement items, because the original mode does affect the system tps, and the new asynchronous mode can improve the system transaction processing capability in the semi-synchronous mode.

2. Optimization

1. support asynchronous sending of binlog and ack reception

Based on the previous introduction, we know that in Semisynchronous Replication mode, the app submits a transaction/event on the master database. MySQL writes each transaction to binary and synchronizes it to slave, the master will wait for at least one Server Load balancer notification: the Server Load balancer has received the passed events and written them into the relay log before returning the write success to the Session Layer, or until the log transmission times out, the system automatically enters the asynchronous replication mode.

Logic Diagram of the overall process

Disadvantages of semi sync 5.5:

From the principle and perspective, the old version of semi sync is limited by dump thread because dump thread undertakes two different and very frequent tasks: Send binlog to slave, you also need to wait for the slave feedback and the two tasks are serialized. dump thread must wait for the slave to return before transmitting the next events transaction. Dump thread has become the bottleneck for improving the performance of semi-synchronization in High-concurrency business scenarios. Such a mechanism will affect the overall TPS of the database.

To solve the above problem, in the semi sync framework of 5.7.4, an ack collector thread is developed independently to receive slave feedback. In this way, two processes on the master can work independently, send binlog to slave at the same time, and receive slave feedback. Logic Diagram of the overall process

The general implementation idea is:

The standby I/O thread uses the TCP protocol to interact with the primary database. The read/write socket can be performed at the same time. When the primary database semisync is enabled, a background thread is started and select is used to listen to the standby database to connect to the socket;
The dump thread no longer waits for the standby database ACK. When the ack reciver thread waits for the ACK, the dump thread can continue to send the binlog of the next group commit to improve the TPS.

2. Support waiting for ACK before the transaction commit;

In the new version of semi sync, The rpl_semi_sync_master_wait_point parameter is added to control the way in which the master database submits the transaction before returning the transaction to the session in semi-synchronous mode.

This parameter has two values:

AFTER_SYNC (default): The master writes each transaction to the binlog, passes it to the slave, and refreshes it to the disk. The master waits for the slave feedback to receive the transaction and refresh it to the disk. Once the slave feedback is received, the master submits the transaction in the master database and returns the result to the session. In AFTER_SYNC mode, all clients view submitted data at the same time. If the master database crash occurs, all transactions committed on the master database have been synchronized to the slave and recorded to the relay log. Switch to the slave database to minimize data loss.

AFTER_COMMIT: The master writes each transaction to the binlog, passes it to the slave to refresh the disk (relay log), and then submits the transaction in the master database. After the master commits the transaction, it waits for the slave feedback to receive the transaction and refresh it to the disk. Once the slave feedback is received, the master sends the result to the client.
In AFTER_COMMIT mode, if the slave does not apply logs, the master crash will be sent to the slave by the system failover, and the app will find that the data is inconsistent, and the Server Load balancer has no application.