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

This article mainly introduces how to build asynchronous replication with MySQL default Replication. The content of the article is carefully selected and edited by the author. It has certain pertinence and is of great significance to everyone's reference. Let's learn how to build asynchronous replication with MySQL default Replication.

1. Background knowledge

Asynchronous replication

The master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.

The main library just writes events to binlog, regardless of whether it is received from the library or not.

Fully synchronous replication

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

The master library commits a thing and waits for all slave libraries to submit before the result can be returned. Execute this thing. This will cause a delay in a thing.

Semisynchronous replication

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

Between asynchronous replication and full replication, the master library simply waits for at least one events to be received and recorded from the library. It doesn't need to wait for all the slave libraries to tell it to receive the events, and it doesn't need to execute and commit things from the library. It just receives the events from the library and tells the master library so that the master library can submit things in advance.

In addition, there are two types of semi-synchronization, there are parameter rpl_semi_sync_master_wait_point control, I am not going to explain here, we use the default setting after_sync, this kind of data loss is zero

AFTER_SYNC (the default): The master writes each transaction to its binary log 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 transaction 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.

two。 Test environment

RoleHostnameIPCPUMemoryMySQL VersionTPCCsht-sgmhadoopcm-01172.16.101.542Core8GNOmastersht-sgmhadoopdn-01172.16.101.582Core6G5.7.21slave1sht-sgmhadoopdn-02172.16.101.592Core6G5.7.21slave2sht-sgmhadoopdn-03172.16.101.602Core6G5.7.21

Both master,slave1 and slave2 are direct slave libraries for master.

The test is divided into two situations:

(1) when both slave1 and slave2 are replicated asynchronously

(2) when slave1 is semi-synchronous replication and slave2 is asynchronous replication

3. Pressure testing

Use TPCC stress test software to compare how much TPS determines the performance difference between asynchronous and semi-synchronous replication.

For more information on how to test, please refer to the previous blog: MySQL stress testing-TPCC installation, testing

Database TpmC data obtained through TPCC test

Thread CountAsynchronousSemisynchronous41145.9001081.13381966.6331687.933163202.3332538.233324363.5333302.167645112.9003638.6671285257.2673738.1002565199.7333823.8335124753.7003538.63310243268.6332769.66715362294.0002215.733

Draw a broken line image

4. Summary

From the test results, we can see that:

Whether it is semi-synchronous replication or asynchronous replication, the TpmC of the database begins to increase with the increase of concurrent threads, reaching about 200, and the TpmC begins to decline gradually.

When the number of threads is between 16 and 1024, the TpmC of asynchronous replication is much larger than that of semi-synchronous replication, with a difference of about 1000-2000, so the performance of asynchronous replication is much lower than that of semi-synchronous replication.

If you do not have much concurrency in your database and have high requirements for data security, you can give priority to using semi-synchronous replication

After reading the above about MySQL default Replication how to build asynchronous replication, many readers must have some understanding, if you need more industry knowledge and information, you can continue to follow our industry information section.

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.