Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to copy Mysql5.7 in parallel. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Enable MySQL parallel replication

The parallel replication of MySQL 5.7is based on group commit, and all statements that can complete Prepared on the main library indicate that there is no data conflict, so it can be replicated in parallel on the Slave node.

For the group submission of MySQL 5.7, we need to take a look at the following parameters:

(test) > show global variables like'% group_commit%'-> +-- +-+ | Variable_name | Value | +-+-+ | Binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | +-+-+

To enable MySQL 5.7parallel replication requires the following two steps: first, set the value of binlog_group_commit_sync_delay greater than 0 in the main library.

> set global binlog_group_commit_sync_no_delay_count=20; > set global binlog_group_commit_sync_delay = 10

Here is a brief description of the role of the binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters.

Binlog_group_commit_sync_delay

Global dynamic variable, unit subtle, default 0, range: 0cm 1000000 (1 second).

Indicates how long it takes to wait for binlog to be submitted before synchronizing to disk. Default is 0, and there is no delay. When set above 0, the logs of multiple transactions are allowed to be committed together at the same time, which is what we call group commit. Group commit is the basis of parallel replication, we set this value greater than 0 means to turn on the function of group submission.

Binlog_group_commit_sync_no_delay_count

Global dynamic variable, number of units, default 0, range: 0mm 1000000.

Indicates the maximum number of transactions waiting for delayed commit. If the time for the above parameter is not up, but the number of transactions has arrived, it will be synchronized directly to disk. If binlog_group_commit_sync_delay is not enabled, this parameter will not be enabled.

Second, set the following parameters on the Slave host:

# too many threads will increase the overhead of synchronization between threads. 4-8 Slave threads are recommended. Slave-parallel-type=LOGICAL_CLOCKslave-parallel-workers=4

Or it is possible to enable it directly online:

Mysql > stop slave;Query OK, 0 rows affected (0.07 sec) mysql > set global slave_parallel_type='LOGICAL_CLOCK';Query OK, 0 rows affected (0.00 sec) mysql > set global slave_parallel_workers=4;Query OK, 0 rows affected (0.00 sec) mysql > start slave;Query OK, 0 rows affected (0.06 sec) mysql > show variables like 'slave_parallel_%' +-+-+ | Variable_name | Value | +-+-+ | slave_parallel_type | LOGICAL_CLOCK | | slave_parallel_workers | 4 | | +-+ 2 rows in set (0.00 sec) checks the status of Worker threads |

The current SQL thread of Slave is Coordinator (coordinator), and the thread that executes the Relay log log is Worker (the current SQL thread not only acts as a coordinator, but also replays transactions committed by the main library in Relay log).

The number of threads we set above is 4, and you can see four Coordinator processes from the library.

Parallel replication configuration and tuning

After enabling the MTS feature, be sure to set the parameter master-info-repository to TABLE, so that the performance can be improved by 50% to 80%. This is because when parallel replication is enabled, the update of the master.info file will be greatly increased, and the competition for resources will also increase.

In MySQL 5. 7, it is recommended that master-info-repository and relay-log-info-repository be set to TABLE to reduce this overhead.

Master-info-repository = tablerelay-log-info-repository = tablerelay-log-recovery = ON parallel replication monitoring

Replication can still be monitored through SHOW SLAVE STATUS\ G, but MySQL 5.7 adds the following metadata tables under the performance_schema architecture, so users can monitor them in more detail:

Mysql > use performance_schema;mysql > show tables like 'replication%' +-+ | Tables_in_performance_schema (replication%) | +-+ | replication_applier_configuration | | replication_applier_ Status | | replication_applier_status_by_coordinator | | replication_applier_status_by_worker | | replication_connection_configuration | | replication_connection_status | | replication_group_member_stats | | replication_group_members | +-- -+ 8 rows in set (0. 00 sec) find a way to figure out the ratio used by each synchronous thread. The statistical methods are as follows: 1. Turn on the statistics of online slaves (it is turned off by default for performance reasons). The opening method can be as follows: SQL:UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'WHERE NAME LIKE' events_transactions%';UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED =' YES'WHERE NAME = 'transaction';2, and create a view to view the usage of each synchronization thread. The code is as follows: USE test CREATE VIEW rep_thread_count AS SELECT a.THREAD_ID AS THREAD_ID,a.COUNT_STAR AS COUNT_STAR FROM performance_schema.events_transactions_summary_by_thread_by_event_name a WHERE a.THREAD_ID in (SELECT b.THREAD_ID FROM performance_schema.replication_applier_status_by_worker b); 3. Count the usage ratio of each synchronization thread after a period of time. The SQL is as follows: SELECT SUM (COUNT_STAR) FROM rep_thread_count INTO @ total SELECT 100* (COUNT_STAR/@total) AS thread_usage FROM rep_thread_count; on "how to copy Mysql5.7 in parallel" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.