This blog article will explain what the SST method choices are and what you need to consider when choosing one over another.
To start with, the wsrep_sst_method is a configuration parameter used with Galera Clustering. This parameter tells the database server which method will be used during the initial load of a node joining an existing cluster.
To sync up newly joining nodes, at initial startup, the new node will need to make a state snapshot transfer request by calling an external shell script to establish a connection with the donor node and transfer the database state onto the local database node server of the joiner. This parameter will allow you to define which method and thus which script the node uses in requesting state snapshot transfers.
SST scripts are located in the /usr/bin directory on Linux and owned by root. Below is an example of the scripts found on an MariaDB 10.3 ubuntu install:
wsrep_sst_rsync_wan -> wsrep_sst_rsync
The choices of State Snapshot Transfer (SST) method are as follows:
|SST Method||Performance||Blocks Donor||Live Node Available||Notes|
|mysqldump||slow||Blocks||Available||Donor node cannot execute queries during the sync|
|rsync||Faster||Blocks||Unavailable||Donor node cannot execute queries during the sync|
|clone||Fastest||On DDLS||Unavailable||Available in MySQL 8.022, mayNot be available for MariaDB|
|xtrabackup||Fast||Briefly||Unavailable||In MariaDB 10.3, Percona XtraBackup is not supported.|
|xtrabackup-v2||Briefly||In MariaDB 10.3, Percona XtraBackup is not supported.|
The choice of method should not be taken lightly, as there are implications as to whether the donor will block queries during the transfer to sync up the joiner and a few other considerations and additional configurations needed.
Below is a short list of a few important considerations for some of the SST methods:
If you choose to use one the following meetings for the state transfers: xtrabackup, xtrabackup-v2, mariabackup. Then you will need to additionally do a few prerequisites, shown below.
#create a new user for the backups, unless you already have a specific defined backup user
CREATE USER backup_user@localhost IDENTIFIED BY ‘backuppassw0rd’;
#ensure this backup user has the following needed grants
GRANT PROCESS, LOCK TABLES, RELOAD, REPLICATION CLIENT ON . TO ‘backup_user’@’localhost’;
#edit the MariaDB config file, add the below in the [mysqld] section
#in this example we will choose to use mariabckup
#NOTE: All SST methods except rsync require authentication via username and password.
If you choose to use a physical state snapshot transfer method such as rysync or xtrabackup then there are a few important considerations between the nodes.
The version of rsync must be the same between donor and joiner, if you choose to use this method.
The data directory layout must be similar.
The storage engine configuration must be similar as the donor node, listed below:
log file size
similar settings for InnoDB between donor and joiner
Another thing to note is rsync does not require database configuration or root access. The joiner node will start ‘rsync’ in server mode, then the donor node will start ‘rsync’ in client-mode. Then the donor node ‘rsync’ service will send over the files to the joiner node and place them in the data directory in the same exact locations as laid out on the donor.
If your requirement needs a virtually non-blocking solution, then using the method mariabackup may be the option for you as using the backup solution requires the least amount of donor blocking. If your donor is a live production database that requires near zero interruption then this is an important consideration.
If you must use a SST method that blocks the donor, and you already have 2 nodes in the cluster and you are joining the 3rd node. Then the third node will be acting as an SST joiner and one other node is acting as an SST donor, then there is still one more node to continue executing queries. Likewise, if your cluster already has three nodes and you are joining a fourth node to the galera cluster. Then you have two other nodes that can continue executing queries. This implies that only the period of time to initialize and join the second node in the cluster will require the most amount of interruption to the production database, and joining nodes 3 and more will have less interruptions.
This blog described the multiple choices of state snapshot transfer (SST) available in a MariaDB Galera Cluster configuration. There are differences in set up and impact on the donor node for each. You need to understand the differences to ensure the SST method chosen fits your needs and is configured appropriately.