Mysql5.7 MTS(multi-threaded slave)

Server environment

1
2
3
4
5
$ cat /etc/issue
Ubuntu 14.04.5 LTS

$ mysqld -V
mysqld Ver 5.7.20 for Linux on x86_64 (MySQL Community Server (GPL))

Before version 5.6, There are two threads on Mysql Slave, I/O and SQL.
I/O thread is responsible for receiving binary log.
SQL thread is responsible for play back binary log.

At version 5.6, SQL thread became the Coordinator thread.

Coordinator thread is mainly responsible for two part.

  • Worker thread will be chosen to execute binary log if Coordinator thread thinks that it can be parallel executed.
  • If not, such as DDL operation or cross-schema transaction, current log will be executed after all Workder threads are finished.

It means that Coordinator thread does not only send log to Worker thread, also plays back the log itself, but all parallel operations are handle by Worker thread.

Coordinator thread and Worker thread are typical producer and customer models.

Only from the MySQL 5.7 release can be called truly parallel replication.
The main reason is that the play back on Slave is consistent with the Master.There is no longer parallel replication limit for Slave and special requirement to binary log.

MySQL 5.7 introduces a event type binary log called Anonymous_Gtid
There will be an Anonymous_Gtid before each transaction begins and there is group submission information in this GTID.

There are two more things in new MySQL version ‘s binlog, last_committed and sequence_number
last_committed is always equal to sequence_number of previous transaction, because transactions are submitted sequentially.
last_committed and sequence_number represent the LOGICAL_CLOCK

About Master-Slave Setup

You can see my earlier blog

Check MySQL 5.7 Group Submission Attributes

1
2
3
4
5
6
7
8
mysql> show global variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)

binlog_group_commit_sync_delay: global dynamic variable, microsecond unit, default is 0, from 0~1000000 second.
How long it will delay to sync to disk after binlog commit. If the value is more than 0, multi transaction ‘s log can be allowed to commit by group.

binlog_group_commit_sync_no_delay_count: global dynamic variable, number unit, default is 0, from 0~1000000.
Max transaction number to delay commit. If the time of binlog_group_commit_sync_delay does not arrive,data will sync to disk directly. If binlog_group_commit_sync_delay has not be set, this one will not be available.

Check Sync process num

1
2
3
4
5
6
7
8
9
10
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 119 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 117 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost | NULL | Sleep | 2 | | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

It shows there is only one master process waiting for synchronization.

Check Parallel Worker Num

1
2
3
4
5
6
7
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Current parallel process num is 0.

Check Replication Type

1
2
3
4
5
6
7
mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.01 sec)

Current replication type is DATABASE, it means that only one thread is replicating in one database and cannot replicate parallel.

DATABASE:Default, replicate parallel method base on Slave.
LOGICAL_CLOCK:replicate parallel method base on group submission

Configure multi-thread

  1. Set binlog_group_commit_sync_delay bigger than 0 in Master

    1
    mysql> set global binlog_group_commit_sync_delay=10;
  2. Stop Replication In Slave

    1
    2
    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
  3. Set Replication Type To LOGICAL_CLOCK In Slave

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> set global slave_parallel_type='logical_clock';
    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like 'slave_parallel_type';
    +---------------------+---------------+
    | Variable_name | Value |
    +---------------------+---------------+
    | slave_parallel_type | LOGICAL_CLOCK |
    +---------------------+---------------+
    1 row in set (0.00 sec)
  4. Set The Parallel Number To 4 In Slave Machine

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> set global slave_parallel_workers=4;
    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like 'slave_parallel_workers';
    +------------------------+-------+
    | Variable_name | Value |
    +------------------------+-------+
    | slave_parallel_workers | 4 |
    +------------------------+-------+
    1 row in set (0.00 sec)
  5. Restart Slave

    1
    2
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
  6. Check Current Running Process Num In Slave

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> show processlist;
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
    | 5 | root | localhost | NULL | Sleep | 230 | | NULL |
    | 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
    | 8 | system user | | NULL | Connect | 4 | Waiting for master to send event | NULL |
    | 9 | system user | | NULL | Connect | 4 | Slave has read all relay log; waiting for more updates | NULL |
    | 10 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
    | 11 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
    | 12 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
    | 13 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
    8 rows in set (0.00 sec)

Because there is sync replication delay between master and slave, the purpose of multi-thread is to minimize the delay time.

Share