Configure Parallel Replication with MariaDB Xpand
MariaDB Xpand 6 provides parallel replication for applications that require extremely scalable replication between Xpand clusters:
Parallel replication minimizes latency and slave lag
Multiple Xpand nodes stream and apply events independently in parallel
Xpand ensures transactional consistency and commits transactions in the same order as the replication primary
Parallel replication can only be used when replicating from another MariaDB Xpand cluster
Parallel replication is supported with MariaDB Xpand 6
Xpand requires row-based replication (RBR) for parallel replication
For parallel replication, all replicated tables should have primary keys
This page explains how to configure parallel replication with MariaDB Xpand. Before configuring parallel replication, ensure that the special cases for replication and special cases for parallel replication do not apply to your environment.
For information on the architecture related to parallel replication with MariaDB Xpand, see "MariaDB Xpand Parallel Replication Architecture".
MariaDB Xpand 6.0
MariaDB Xpand 6.1
Configure Binlogs on Master for Parallel Replication
Configure binlogs on the master by executing
CREATE BINLOG .. FORMAT='ROW':
CREATE BINLOG 'binlog_name' [LOG (target1, target2, ...),] [IGNORE (target3, target4, ...),] FORMAT='ROW';
For additional information, see "Configure MariaDB Xpand as a Replication Master".
Configure Parallel Replication on Slave
Configure parallel replication by executing
CREATE SLAVE slave_name PARALLEL_LOG = master_log_name, PARALLEL_POS = position, SLICES = num_slices, BATCH_SIZE_MS = batch_size [, MASTER_HOST = master_host] [, MASTER_USER = master_user] [, MASTER_PASSWORD = master_password] [, MASTER_PORT = master_port];
Parallel Replication Specific Options
This is used in place of MASTER_
For example: if your binlog file is
This replaces the file based replication argument MASTER_
For example: if the value from your backup file is
Determines the amount of parallel threads the slave will connect to the master with. This value should be N or N*2, where N is the number of nodes in the master cluster. If your cluster contains 6 nodes then the value for SLICES would be 6, or 12.
The amount of time in milliseconds used to determine transactions batch size on the master. Default of 3000 ms.
Since the master uses time to batch transactions, the data on the slave may show 0 seconds behind master, but the actual data will be between 0 and BATCH_
For optimal performance MASTER_
Using the Values in Backup to Configure Parallel Replication
When restoring from a backup for the purposes of replication you can find the values to set
PARALLEL_POS in the binlogs and xid files. The two files are located inside the metadata directory of the Xpand backup directory.
Please note that the value in the xid file appears in hexadecimal but without an 0x prefix. When setting
PARALLEL_POS with a hexadecimal value, it must start with 0x .
[root@server01]# cat backup_all-2020-01-10/metadata/binlogs binlog01.001662:18570355
[root@server01]# cat backup-2020-01-10/metadata/xid 5e23e65e9342f802
Using the above output, the values to use for
PARALLEL_POS would be
The command using the values from the output above as an example would be:
CHANGE SLAVE slave_name TO PARALLEL_LOG='binlog01', PARALLEL_POS='0x5e23e65e9342f802';
Viewing Parallel Replication Status:
To display the status of a parallel master:
SHOW MASTER STATUS PARALLEL;
SHOW ALL MASTER STATUS PARALLEL;
Displaying the status of a parallel slave is the same as with a serial slave:
SHOW SLAVE STATUS slave_name;
SHOW SLAVE STATUS;
To view additional information about the Parallel slave, such as the value of SLICES or BATCH_
SELECT * FROM system.mysql_repconfig WHERE slave_name = 'slave_name';
SELECT slave_name, protocol, slices, batch_size / POW(2, 32) * 1000 AS batch_size_ms FROM system.mysql_repconfig WHERE slave_name = 'slave_name';
How it Works
The Xpand Parallel slave is able to parallelize processing of the replication stream and applies events in batches. Row events from within the same transaction are applied in the same transaction on the slave. Both serial and parallel replication use the same Replication Master for generating binlog(s).
Slave Xpand Cluster
MariaDB Xpand's parallel replication can only be used with a slave Xpand cluster.
MariaDB Xpand requires row-based replication (RBR) for parallel replication, so the binary log should not be created in the statement-based replication (SBR) format. By default, the
CREATE BINLOG command creates a binary log with a
On the primary Xpand cluster, the binary log can be created in the row-based replication (RBR) format using
CREATE BINLOG .. FORMAT='ROW'.
Since MariaDB Xpand's parallel replication requires row-based replication (RBR), all replicated tables should have a Primary Key.
For row-based replication, Xpand uses the table's Primary Key to find the specific row referenced in each binlog event. If any tables do not have a Primary Key, Xpand must scan the table for the row, which can have a negative impact on performance.
MariaDB Xpand does not support foreign keys with parallel replication.
Primary Keys and Unique Indexes
In many cases when a table has both a Primary Key and a Unique index, Xpand can force the table to use serial replication instead of parallel replication.
When using Xpand's parallel replication, MariaDB does not recommend creating tables that have both a Primary Key and a Unique index.