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

This article is mainly about the operation of MySQL parallel replication configuration and tuning. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the operation of MySQL parallel replication configuration and tuning.

The background of parallel replication:

Because I thread O thread and SQL thread work in a single thread, while Master can be written in multiple threads, master and slave will inevitably cause delays.

Based on this, in version 5.6, 5.7 and 8.0, multithreading is implemented on SQL threads to improve the concurrency of slave.

Why is it not multi-threaded with IWeiO?

There is no need for multithreading, because the Ithumb O thread is not a bottleneck.

The purpose of parallel replication:

Let Slave SQL threads work as multithreaded as possible to solve the problem of replication delay

Prerequisites for parallel replication implementation:

The key to parallel replication is whether there are lock conflicts between multiple transactions.

MySQL5.6 schema-based parallel replication:

Application scenarios:

It is suitable for scenarios with multiple schema in one library.

The principle of parallel replication:

When MySQL5.6 enables parallel replication, the SQL thread becomes a coordinator thread, which determines whether it can execute concurrently, which means that a worker thread can handle continuous transactions of a database without waiting for other databases to complete.

If it can be executed in parallel, select the worker thread to execute the binary log

If parallel execution is not possible, such as DDL or cross-schema operations, wait for all worker threads to complete before executing the current log

A picture taken from the Internet for reference and understanding:

Problems with schema-based parallel replication:

Because MySQL5.6 parallel replication is only based on schema, but the replication scenario of single-database and multi-table cannot be realized, and even the performance may not be as good as the original single-threaded replication. In practice, the scenario of single-database multi-table is more common than that of multi-database and multi-table.

MySQL5.6 concurrent replication based on schema level can solve the problem of synchronization delay of business tables under different DATABASE, but in actual production, most tables are still placed in the same library. In this case, even if slave_parallel_workers is greater than 0, concurrency cannot be carried out. In the case of high concurrency, it will still cause master-slave replication delay.

MySQL5.6 parallel replication is enabled: (only if the master-slave replication environment is configured)

Mysql > stop slave

Query OK, 0 rows affected (0.03 sec)

Mysql > set global slave_parallel_workers=8

Query OK, 0 rows affected (0.05 sec)

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.07 sec)

MySQL5.6 parallel copy schematic:

Description of parallel replication parameters:

Slave_parallel_workers:

Number of applier threads for executing replication transactions in parallel. A value of 0 disables slave multithreading. Not supported by MySQL Cluster

MySQL5.7 parallel replication principle:

Implementation based on group replication (group commit)

How do I know if the transaction is in the same group?

In MySQL version 5. 7, it is designed to store group submission information in GTID. What if the user does not enable the GTID function, that is, set the parameter gtid_mode to OFF?

MySQL 5.7introduces a binary log event type called Anonymous_Gtid (ANONYMOUS_GTID_LOG_EVENT), where group commit information is stored in Anonymous_Gtid.

When GTID is enabled, a GTID event is added before each operation statement (DML/DDL) is executed. Record the current global transaction ID; in MySQL version 5.7, and the group submission information is also stored in the GTID event. There are two key fields last_committed,sequence_number that are used to identify the group submission information. There is a global counter (global counter) in InnoDB, and the counter value is incremented before each storage engine commit. Before the transaction enters the prepare phase, the current value of the global counter is stored in the transaction, which is called the transaction's commit-parent (that is, last_committed). Last_committed indicates the number of the last transaction commit when the transaction commits. If the transaction has the same last_committed, it means that these transactions are all in a group and can be played back in parallel. On the other hand, sequence_number grows sequentially, with each transaction corresponding to a sequence number.

This means that even if GTID is not enabled in MySQL version 5.7, there will be an Anonymous_Gtid before the start of each transaction, and there is group commit information in this Anonymous_Gtid event. Conversely, if GTID is enabled, the Anonymous_Gtid will not exist, and the group submission information will be recorded in the non-anonymous GTID event.

How does MySQL group these transactions?

Transactions committed by a group can be played back in parallel, because these transactions have entered the prepare phase of the transaction, which means that there is no conflict between transactions (otherwise it is impossible to commit)

Introduction to MySQL5.7 parallel replication:

1) MySQL 5.7 can be called real parallel replication. The main reason is that the playback of slave CVM is consistent with that of master, that is, how parallel playback is performed on master CVM, then parallel playback is carried out on slave. There are no more restrictions on parallel replication of libraries, and there are no special requirements for binary log formats (based on parallel replication of libraries).

2) MySQL5.7 parallel replication supports table level

3) Enhanced Multi-threaded Slaves (MTS)

MySQL5.7 parallel replication parameters:

SHOW VARIABLES LIKE 'slave_parallel_%'

Variable_name Value

Slave_parallel_type DATABASE (variable slave-parallel-type can have two values: DATABASE default, based on parallel copy of the library, and LOGICAL_CLOCK: parallel replication based on group commit (based on table))

Slave_parallel_workers 0

MySQL 5.7parallel replication configuration and tuning:

# slave

Slave-parallel-type=LOGICAL_CLOCK

Slave-parallel-workers=16

Slave_preserve_commit_order=1

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

Relay_log_recovery=ON

MySQL5.7 enables parallel replication online (multithreaded replication):

Reference video: https://www.imooc.com/video/10921

Stop replication of all links on the Slave CVM

Stop slave

Set global slave-parallel-type=LOGICAL_CLOCK

Set global slave-parallel-workers=16

Start slave

Show processlist (see 16 SQL threads)

The MySQL5.7 application transaction order is different from the realy log record transaction order:

MTS after MySQL 5.7 can achieve smaller-grained parallel replication, but you need to set slave_parallel_type to LOGICAL_CLOCK, but only setting it to LOGICAL_CLOCK will have problems, because the order of transactions applied on slave is out of order, which is different from that recorded in relay log, so data consistency cannot be guaranteed, in order to ensure that transactions are played back and forth in the order recorded in relay log. You need to enable the parameter slave_preserve_commit_order.

Is the above operation details about MySQL parallel replication configuration and tuning helpful? If you want to know more about it, 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.