MySQL multi thread master slave replication:
In Multi-thread slave replication transactions are split per MTS thread (multiple sql threads processing events from the relay log) based on the database/schema. If you have one schema there will be no benefit as there is nothing or little to split between worker threads. Multi-thread replication helps environments with lots of databases, each database is writing heavily and the writes can be split between worker threads. Order of updates on a database are the same as they are on the master.
For cross-database transactions, the slave waits until all preceding transactions that are working on the same database set are over.
Enable Multi-thread Master slave replication:
01. Set following parameters on Slave in /etc/my.cnf file:
master-info-repository = TABLE
slave-parallel-workers = 2
relay-log-info-repository = TABLE
02. Check exisiting parameter value
mysql> show global variables like "%repository%";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
03. Set slave paraller workers.
mysql> set global slave_parallel_workers=4;
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
Preserver Master commit order on slave require following parameters:
01. Enable slave_perserver_commit_order on slave ensures the order which transactions were committed on the master is preserved on the slave.
mysql> set global slave_perserver_commit_order=ON;
02. Make sure binlog_order_commits is ON.
mysql> show variables like '%binlog_order%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| binlog_order_commits | ON |
+----------------------+-------+
03. Enable slave paraller workers
mysql> set global slave_parallel_workers=4;
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
Ref. https://mysqlhighavailability.com/preserve-masters-commit-order-on-slave/
In Multi-thread slave replication transactions are split per MTS thread (multiple sql threads processing events from the relay log) based on the database/schema. If you have one schema there will be no benefit as there is nothing or little to split between worker threads. Multi-thread replication helps environments with lots of databases, each database is writing heavily and the writes can be split between worker threads. Order of updates on a database are the same as they are on the master.
For cross-database transactions, the slave waits until all preceding transactions that are working on the same database set are over.
Enable Multi-thread Master slave replication:
01. Set following parameters on Slave in /etc/my.cnf file:
master-info-repository = TABLE
slave-parallel-workers = 2
relay-log-info-repository = TABLE
02. Check exisiting parameter value
mysql> show global variables like "%repository%";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
03. Set slave paraller workers.
mysql> set global slave_parallel_workers=4;
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
Preserver Master commit order on slave require following parameters:
01. Enable slave_perserver_commit_order on slave ensures the order which transactions were committed on the master is preserved on the slave.
mysql> set global slave_perserver_commit_order=ON;
02. Make sure binlog_order_commits is ON.
mysql> show variables like '%binlog_order%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| binlog_order_commits | ON |
+----------------------+-------+
03. Enable slave paraller workers
mysql> set global slave_parallel_workers=4;
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
Ref. https://mysqlhighavailability.com/preserve-masters-commit-order-on-slave/
MySQL multi-threaded master-slave replication significantly improves performance by allowing concurrent processing of multiple replication threads. Explore the benefits and implementation strategies to optimize your database setup with hostingmella!
ReplyDelete