MySQL Semi-synchronous replication

Starting with MySQL5.5, MySQL supports semi-synchronous replication in the form of plugins. How to understand the semi-synchronous? First, let's look at the asynchronous, fully synchronous concept.

Asynchronous replication (asynchronous replication)

MySQL default replication is asynchronous, the main library after executing the client committed transactions will immediately return the results to the client, do not care whether the library has been received and processed, so there is a problem, if the Lord crash off, at this time the master has committed the transaction may not be uploaded from the top, if at this time, Force will be promoted from the main, may lead to the new master's data is incomplete.

Full synchronous replication (Fully synchronous replication)

When the main library executes a transaction, all of the transactions executed from the library are returned to the client. Because it is necessary to wait for all the transactions from the library to be returned, the performance of full synchronous replication must be severely impacted.

Semi-synchronous replication (semisynchronous replication)

Between asynchronous replication and full synchronous replication, the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one receipt from the library and written to the relay log to return to the client. Semi-synchronous replication improves data security relative to asynchronous replication, and it also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

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

Potential problems with semi-synchronous replication

After the client transaction is committed at the storage engine level, the main library is down during the process of getting confirmation from the library, and there are two possible scenarios

The transaction has not yet been sent to the library.

At this point, the client will receive a transaction submission failure information, the client will resubmit the transaction to the new master, when the main library of the outage to rejoin the master-slave structure from the identity of the library, it will be found that the transaction was submitted from the Library two times, one time before the primary, once was synchronized by the new host.

The transaction has been sent to the slave library

At this point, the transaction has been received and applied from the library, but the client will still receive information about the transaction submission failure and resubmit the transaction to the new Lord.

Countless data-loss semi-synchronous replication

For these potential problems, MySQL 5.7 introduces a new semi-synchronous scheme: Loss-less semi-synchronous replication.

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

Of course, the previous semi-synchronous scheme is also supported, and MySQL 5.7.2 introduces a new parameter for control-rpl_semi_sync_master_wait_point

Rpl_semi_sync_master_wait_point There are two kinds of values

After_sync

This is the new semi-synchronous scheme, waiting Slave dump before storage commit.

After_commit

The old half-synchronous scheme.

Installation deployment for semi-synchronous replication

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

1. MySQL version 5.5 and above

2. Variable have_dynamic_loading is yes

3. Asynchronous replication already exists

Load plug-ins first

Because the user needs to execute install PLUGIN, SET GLOBAL, STOP slave and start slave operation, the user should have super privilege.

Main:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

From:

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

To see if the plugin was loaded successfully

There are two ways of

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 in Set (0.00 sec)

Start a semi-synchronous replication

When the plug-in is installed, the semi-synchronous copy is turned off by default, and the parameter is set to turn on the semi-synchronous

Main:

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

From:

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Restarting the IO thread from the top

mysql> STOP SLAVE Io_thread;

mysql> START SLAVE Io_thread;

If there is no restart, the default is asynchronous replication, and after rebooting, slave is registered as the slave role for semi-synchronous replication on master.

At this time, the following message will be printed in the master's Error.log:

 .- ,-05t10:Geneva: +.104327Z5[Note] While initializing dump thread forSlave with UUID <ce9aaf22-5af6-11e6-850b-000c2988bad2>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread (4). .- ,-05t10:Geneva: +.111175Z4[Note] Stop asynchronous Binlog_dump to Slave (server_id:2) .- ,-05t10:Geneva: +.119037Z5[Note] Start Binlog_dump to MASTER_THREAD_ID (5) Slave_server (2), POS (mysql-bin.000003,621) .- ,-05t10:Geneva: +.119099Z5[Note] Start semi-SyncBinlog_dump to Slave (server_id:2), POS (mysql-bin.000003,621)

See if the half-sync is running

Main:

Mysql> Show status like ' Rpl_semi_sync_master_status ';

+-----------------------------+-------+|Variable_name|Value|+-----------------------------+-------+|Rpl_semi_sync_master_status|  on    |+-----------------------------+-------+1Rowinch Set(0.00Sec

From:

Mysql> Show status like ' Rpl_semi_sync_slave_status ';

+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Rpl_semi_sync_slave_status|  on    |+----------------------------+-------+1Rowinch Set(0.20Sec

These two variables are commonly used to monitor whether Master and slave are running in semi-synchronous replication mode.

At this point, MySQL semi-synchronous replication is completed ~

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

When a half-synchronous replication time-out (controlled by the rpl_semi_sync_master_timeout parameter, in milliseconds, by default 10000, or 10s), semi-synchronous replication is temporarily turned off and asynchronous replication is used instead. When the master dump line Cheng all events for a transaction, if the response from the library is received within Rpl_semi_sync_master_timeout, the master and slave are reverted back to semi-synchronous replication.

Let's test it here.

This verification is divided into three stages

1. Before slave executes stop slave, the main insert operation can be returned very 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.

At this point, the values of the two states are viewed as "off".

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

 2016 -08  -05t11:51 : 49 . 855452Z 6  [Warning] Timeout waiting for  reply of Binlog (file : Mysql-bin. 000003 , Pos: sync  up to file  mysql-bin. 000003 , Position . 2016 -51 : 49 . 855742Z 6  [Note] semi-

3. After slave executes the start slave, the main insert operation is quickly returned, and the values of the two states become "on".

At the same time, the following information is printed in the main error.log:

 .- ,-05T11: the: +.477098Z7[Note] Start Binlog_dump to MASTER_THREAD_ID (7) Slave_server (2), POS (mysql-bin.000003,1196) .- ,-05T11: the: +.477168Z7[Note] Start semi-SyncBinlog_dump to Slave (server_id:2), POS (mysql-bin.000003,1196) .- ,-05T11: the: +.523475Z0[Note] semi-SyncReplication switched on at (Mysql-bin.000003,1447)

Other variables

Environment variables

MySQL 5.7.3 introduced the Rpl_semi_sync_master_wait_for_slave_count, which sets the master to wait for n slave to be answered before returning to the client, which defaults to 1.

State variables

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     |+--------------------------------------------+-------+ -Rowsinch Set(0.00Sec

Of the above state variables, the more important ones are the following

Rpl_semi_sync_master_clients

Number of semi-synchronous copies from

Rpl_semi_sync_master_no_tx

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

Specifically to the above test, it refers to the insert into test.test values (2) of this transaction.

Rpl_semi_sync_master_yes_tx

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

Specifically, the following four transactions are referred to in the above test

Create DATABASE test;

CREATE TABLE test.test (id int);

INSERT into test.test values (1);

INSERT into Test.test values (3);

Summarize

1. In a master multi-slave architecture, if you want to turn on semi-synchronous replication, it is not required that all the slave copies are semi-synchronous.

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

5.6 version of the semi-synchronous replication, dump thread undertook two different and very frequent tasks: send Binlog to Slave, but also need to wait for slave feedback information, and these two tasks are serial, dump thread must wait for slave The next events transaction is not delivered until it is returned. Dump thread has become a bottleneck in the overall semi-synchronous performance improvement. In high concurrency scenarios, such a mechanism affects the overall TPS of the database.

In the 5.7 version of the semi-synchronous replication, an ACK collector thread is isolated, specifically for receiving feedback from slave. This allows two threads on master to work independently, sending Binlog to slave at the same time, and receiving feedback from slave.

Reference

1. MARIADB Principle and realization

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

3. http://sanwen8.cn/p/105GRDe.html

4. "MySQL 5.7 Replication new features" shared

MySQL Semi-synchronous replication