MySQL壓測--非同步與半同步複製

標籤：mit   can   first   mys   any   get   CM   class   軟體   

  最近在看MySQL5.7 Manual，有關Semisynchronous Replication這一塊的內容，我們知道，MySQL預設的Replication是非同步，何為非同步？何為半同步？廢話不多說，直接看官方解釋吧：

1.背景知識

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.

--主庫只管把events寫入binlog中，不管從庫有沒有收到。

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.

--主庫提交一個事物，需要等待所有從庫先提交才能返回結果，執行這個事物。這樣會造成一個事物延時。

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.

--介於非同步複製和全複製之間，主庫僅僅只要等待至少一個從庫收到和記錄events。它不需要等待所有的從庫告訴它收到events,也不需要從庫執行和提交事物，從庫只是收到events就會告訴主庫，這樣主庫就可以提前提交事物了。

此外，半同步也分兩種，有參數rpl_semi_sync_master_wait_point控制，這裡我就不多做解釋了，我們使用預設設定after_sync,這種資料零丟失

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.

2.測試環境

Role	Hostname	IP	CPU	Memory	MySQL Version
TPCC	sht-sgmhadoopcm-01	172.16.101.54	2Core	8G	NO
master	sht-sgmhadoopdn-01	172.16.101.58	2Core	6G	5.7.21
slave1	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

一個master,slave1和slave2都是master的直接從庫。

分兩種情況測試：

（1）當slave1和slave2都是非同步複製的時候

（2）當slave1是半同步複製，slave2是非同步複製的時候

3.壓力測試

使用TPCC壓力測試軟體，比較TPS和QPS來判斷非同步和半同步複製的效能差異到底有多大。

具體如何測試，可以參考之前的部落格：MySQL壓測--TPCC安裝，測試

參考連結：

半同步複製安裝配置

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

MySQL壓測--非同步與半同步複製