MySQL :: MySQL 8.0 Reference Manual :: 19.4.10.2 Configuring Semisynchronous Replication (original) (raw)

19.4.10.2 Configuring Semisynchronous Replication

When you install the source and replica plugins for semisynchronous replication (seeSection 19.4.10.1, “Installing Semisynchronous Replication”), system variables become available to control plugin behavior.

To check the current values of the status variables for semisynchronous replication, use SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';

From MySQL 8.0.26, new versions of the source and replica plugins are supplied, which replace the terms“master” and “slave” with“source” and “replica” in system variables and status variables. If you install the newrpl_semi_sync_source andrpl_semi_sync_replica plugins, the new system variables and status variables are available but the old ones are not. If you install the oldrpl_semi_sync_master andrpl_semi_sync_slave plugins, the old system variables and status variables are available but the new ones are not. You cannot have both the new and the old version of the relevant plugin installed on an instance.

All therpl_semi_sync_ _`xxx`_ system variables are described atSection 19.1.6.2, “Replication Source Options and Variables” andSection 19.1.6.3, “Replica Server Options and Variables”. Some key system variables are:

Therpl_semi_sync_source_wait_point orrpl_semi_sync_master_wait_point system variable controls the point at which a semisynchronous source server waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

• AFTER_SYNC (the default): The source writes each transaction to its binary log and the replica, and syncs the binary log to disk. The source waits for replica acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the source commits the transaction to the storage engine and returns a result to the client, which then can proceed.
• AFTER_COMMIT: The source writes each transaction to its binary log and the replica, syncs the binary log, and commits the transaction to the storage engine. The source waits for replica acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the source returns a result to the client, which then can proceed.

The replication characteristics of these settings differ as follows:

• With AFTER_SYNC, all clients see the committed transaction at the same time, which is after it has been acknowledged by the replica and committed to the storage engine on the source. Thus, all clients see the same data on the source.
  In the event of source failure, all transactions committed on the source have been replicated to the replica (saved to its relay log). An unexpected exit of the source and failover to the replica is lossless because the replica is up to date. As noted above, the source should not be reused after the failover.
• With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives replica acknowledgment. After the commit and before replica acknowledgment, other clients can see the committed transaction before the committing client.
  If something goes wrong such that the replica does not process the transaction, then in the event of an unexpected source exit and failover to the replica, it is possible for such clients to see a loss of data relative to what they saw on the source.

From MySQL 8.0.23, you can improve the performance of semisynchronous replication by enabling the system variablesreplication_sender_observe_commit_only, which limits callbacks, andreplication_optimize_for_static_plugin_config, which adds shared locks and avoids unnecessary lock acquisitions. These settings help as the number of replicas increases, because contention for locks can slow down performance. Semisynchronous replication source servers can also get performance benefits from enabling these system variables, because they use the same locking mechanisms as the replicas.