MySQL Voltage test--asynchronous vs. semi-synchronous replication

recently looking at MySQL5.7 Manual, about semisynchronous replication this piece of content, we know that MySQL default replication is asynchronous, what is asynchronous? What is semi-synchronous? Don't say much nonsense, just look at the official explanation:

1. Background knowledge

Asynchronous Replication

The master writes events to their binary log and slaves request them when they is ready. There is no guarantee the any event would ever reach any slave.

--The main library just writes events to Binlog, regardless of whether they are received from the library or not.

Fully Synchronous Replication

When a master commits a transaction, all slaves also would have committed the transaction before the master returns to the session that performed the transaction. The drawback of this was that there might was a lot of delay to complete a transaction.

--The main library commits a thing, waits for all to be submitted from the library before returning the result, executing the thing. This can cause a thing to delay.

semisynchronous Replication

Falls between asynchronous and fully synchronous replication. The master waits only until at least one slave have received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not the events has been fully Executed and committed on the slave side.

-Between asynchronous replication and full replication, the main library simply waits for at least one receipt from the library and logs events. It does not have to wait for all the libraries to tell it to receive events, to execute and commit things from the library, to just receive events from the library and tell the main library so that the main library can submit things ahead of time.

In addition, there are two kinds of semi-synchronous, parameter Rpl_semi_sync_master_wait_point control, here I do not explain more, we use the default settings after_sync, this data 0 lost

After_sync (the default): The master writes each transaction to its binary logs and the slave, and syncs the binary log to Disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client , which then can proceed.

After_commit:the Master writes each transaction to its binary log and the slave, syncs the binary log, and commits the TR Ansaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

2. Test environment

R OLE	hostname	ip	CPU	memory	mysql Version
TPC C	sht-sgmhadoopcm-01	172.16.101.54	2core	8g	no
Master	Sht-sgmhadoopdn-01	172.16.101.58	2Core	6G	5.7.21
sla Ve1	sht-sgmhadoopdn-02	172.16.101.59	2core	6g	5.7.21
Slave2	sht-sgmhadoopdn-03	172.16.101.60	2Core	6G	5.7.21

A master,slave1 and slave2 are master direct from the library.

Test in two different situations:

(1) When slave1 and Slave2 are both asynchronous copies

(2) When SLAVE1 is a semi-synchronous replication, Slave2 is asynchronous

3. Stress testing

Using the TPCC pressure test software, compare TPS and QPS to determine how much the performance difference between asynchronous and quasi-synchronous replication is.

specific how to test, you can refer to the previous blog: mysql pressure test--TPCC installation, testing

Reference Links:

Semi-synchronous replication installation configuration

Https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html

MySQL Voltage test--asynchronous vs. semi-synchronous replication