MySQL semi-synchronous Replication

MySQL semi-synchronous Replication

MySQL supports semi-synchronous replication as a plug-in starting from MySQL5.5. How can we understand semi-sync? First, let's look at the concept of Asynchronization and full synchronization.

Asynchronous replication)

By default, MySQL replication is asynchronous. After the transaction committed by the client is executed, the master database immediately returns the result to the client, regardless of whether the slave database has been received and processed, in this case, there will be a problem. If the master crashes, the transactions committed on the master may not be uploaded to the slave database. If the master database is promoted to the master database, data on the new master may be incomplete.

Full synchronous replication (Fully synchronous replication)

When a transaction is executed in the master database, all slave databases execute the transaction before returning it to the client. Because it is necessary to wait for all slave databases to execute this transaction before returning, the performance of full synchronization replication will inevitably be seriously affected.

Semi-synchronous replication (Semisynchronous replication)

Between asynchronous replication and full synchronous replication, the master database does not return to the client immediately after the transaction committed by the client is executed, but waits for at least one slave database to receive the transaction and write it to the relay log before returning it to the client. Compared with asynchronous replication, semi-synchronous replication improves data security and also causes a certain degree of delay. This delay is at least the round-trip time of a TCP/IP. Therefore, semi-synchronous replication is best used in low-latency networks.

Let's take a look at the schematic diagram of semi-synchronous replication:

 

Potential problems of semi-synchronous Replication

After the client transaction is committed at the storage engine layer, the master database goes down when the slave database is confirmed. There are two possible situations:

The transaction has not been sent to the slave Database

At this time, the client will receive a message indicating that the transaction failed to be committed, and the client will re-submit the transaction to the new master. After the master database is restarted, when the slave database is added to the master-slave structure as the slave database, the transaction is committed twice in the slave database, the data is synchronized by the new master.

The transaction has been sent to the slave Database

At this time, the slave database has received and applied the transaction, but the client still receives the transaction commit Failure Information, resubmit the transaction to the new master.

Semi-synchronous replication without data loss

To address the above potential problems, MySQL 5.7 introduces a new semi-synchronous solution: Loss-Less semi-synchronous replication.

For the figure above, "Waiting Slave dump" is adjusted to "Storage Commit.

Of course, the previous semi-sync solution also supports this. MySQL 5.7.2 introduces a new parameter for control-rpl_semi_sync_master_wait_point

Rpl_semi_sync_master_wait_point has two values:

AFTER_SYNC

This is the new semi-sync solution. Waiting Slave dump is before Storage Commit.

AFTER_COMMIT

The old semi-sync solution ,.

Installation and deployment of semi-synchronous Replication

To use semi-synchronous replication, the following conditions must be met:

1. MySQL 5.5 and later versions

2. The variable have_dynamic_loading is YES.

3. asynchronous replication already exists

First load the plug-in

You must have the SUPER permission to install plugin, set global, stop slave, and start slave.

MASTER:

Mysql> install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';

From:

Mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';

Check whether the plug-in is successfully loaded.

There are two methods

1.

Mysql> show plugins;

rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL  

2.

Mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '% semi % ';

+----------------------+---------------+| PLUGIN_NAME          | PLUGIN_STATUS |+----------------------+---------------+| rpl_semi_sync_master | ACTIVE        |+----------------------+---------------+1 row in set (0.00 sec)

Start semi-synchronous Replication

After the plug-in is installed, semi-sync replication is disabled by default. You need to set parameters to enable semi-sync.

MASTER:

Mysql> set global rpl_semi_sync_master_enabled = 1;

From:

Mysql> set global rpl_semi_sync_slave_enabled = 1;

Restart the previous IO thread

Mysql> stop slave IO_THREAD;

Mysql> start slave IO_THREAD;

If the server Load balancer instance is not restarted, asynchronous replication is performed by default. After the instance is restarted, the Server Load balancer instance is registered as a semi-synchronous Replication Server Load balancer instance on the master node.

At this time, the main error. log will print the following information:

2016-08-05T10:03:40.104327Z 5 [Note] While initializing dump thread for slave with UUID <ce9aaf22-5af6-11e6-850b-000c2988bad2>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).2016-08-05T10:03:40.111175Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 2)2016-08-05T10:03:40.119037Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(2), pos(mysql-bin.000003, 621)2016-08-05T10:03:40.119099Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 621)

Check whether semi-sync is running

MASTER:

Mysql> show status like 'rpl _ semi_sync_master_status ';

+-----------------------------+-------+| Variable_name               | Value |+-----------------------------+-------+| Rpl_semi_sync_master_status | ON    |+-----------------------------+-------+1 row in set (0.00 sec)

From:

Mysql> show status like 'rpl _ semi_sync_slave_status ';

+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON    |+----------------------------+-------+1 row in set (0.20 sec)

These two variables are often used to monitor whether the master and slave nodes are running in semi-synchronous replication mode.

So far, MySQL semi-synchronous replication is complete ~

In fact, semi-synchronous replication is not strictly a semi-synchronous replication.

When the semi-synchronous replication times out (controlled by the rpl_semi_sync_master_timeout parameter, in milliseconds, the default value is 10000, that is, 10 s), semi-synchronous replication is temporarily disabled and switched to asynchronous replication. After the master dump thread sends all the events of a transaction, if the slave database response is received within rpl_semi_sync_master_timeout, the master and slave databases are restored to semi-synchronous replication.

Let's test it.

This verification is divided into three phases

1. Before Slave executes stop slave, the primary insert operation will be able to return quickly.

2. After Slave executes stop slave, the primary insert operation requires 10.01s to return, which is consistent with the time of the rpl_semi_sync_master_timeout parameter.

In this case, the values in both States are "OFF.

At the same time, the main error. log prints the following information:

2016-08-05T11:51:49.855452Z 6 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000003, pos: 1447), semi-sync up to file mysql-bin.000003, position 1196.2016-08-05T11:51:49.855742Z 6 [Note] Semi-sync replication switched OFF.

3. After Slave executes start slave, the primary insert operation will be able to return quickly. At this time, the values of the two statuses will also change to "ON.

At the same time, the main error. log will print the following information:

2016-08-05T11:52:40.477098Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(2), pos(mysql-bin.000003, 1196)2016-08-05T11:52:40.477168Z 7 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 1196)2016-08-05T11:52:40.523475Z 0 [Note] Semi-sync replication switched ON at (mysql-bin.000003, 1447)

Other variables

Environment Variable

mysql> show variables like '%Rpl%';+-------------------------------------------+------------+| Variable_name                             | Value      |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled              | ON         || rpl_semi_sync_master_timeout              | 10000      || rpl_semi_sync_master_trace_level          | 32         || rpl_semi_sync_master_wait_for_slave_count | 1          || rpl_semi_sync_master_wait_no_slave        | ON         || rpl_semi_sync_master_wait_point           | AFTER_SYNC || rpl_stop_slave_timeout                    | 31536000   |+-------------------------------------------+------------+7 rows in set (0.30 sec)

Rpl_semi_sync_master_wait_for_slave_count

Introduced in MySQL 5.7.3, this variable sets the number of slave responses that the master needs to wait for before returning to the client. The default value is 1.

Status variable

mysql> show status like '%Rpl_semi%';+--------------------------------------------+-------+| Variable_name                              | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients               | 1     || Rpl_semi_sync_master_net_avg_wait_time     | 0     || Rpl_semi_sync_master_net_wait_time         | 0     || Rpl_semi_sync_master_net_waits             | 6     || Rpl_semi_sync_master_no_times              | 1     || Rpl_semi_sync_master_no_tx                 | 1     || Rpl_semi_sync_master_status                | ON    || Rpl_semi_sync_master_timefunc_failures     | 0     || Rpl_semi_sync_master_tx_avg_wait_time      | 1120  || Rpl_semi_sync_master_tx_wait_time          | 4483  || Rpl_semi_sync_master_tx_waits              | 4     || Rpl_semi_sync_master_wait_pos_backtraverse | 0     || Rpl_semi_sync_master_wait_sessions         | 0     || Rpl_semi_sync_master_yes_tx                | 4     |+--------------------------------------------+-------+14 rows in set (0.00 sec)

Among the preceding state variables, the following are important:

Rpl_semi_sync_master_clients

Number of slave nodes of semi-synchronous Replication

Rpl_semi_sync_master_no_tx

The number of commits that were not acknowledged successfully by a slave.

Specifically, in the above test, it refers to the transaction insert into test. test values (2.

Rpl_semi_sync_master_yes_tx

The number of commits that were acknowledged successfully by a slave.

Specifically, in the test above, it refers to the following four transactions

Create database test;

Create table test. test (id int );

Insert into test. test values (1 );

Insert into test. test values (3 );

Summary

1. In a one-master-multiple-slave architecture, If You Want To enable semi-synchronous replication, it is not required that all slaves are semi-synchronous replication.

2. MySQL 5.7 greatly improves the performance of semi-synchronous replication.

In semi-synchronous replication of version 5.6, dump thread undertakes two different and very frequent tasks: Send binlog to slave and wait for slave feedback, the two tasks are serialized, And the 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-sync. In highly concurrent business scenarios, such a mechanism will affect the overall TPS of the database.

In semi-synchronous replication of version 5.7, an ack collector thread is generated independently, which is used to receive slave feedback. In this way, two threads on the master can work independently, send binlog to slave at the same time, and receive slave feedback.

This article permanently updates the link address: