MariaDB Galera Cluster nodes can be configured by setting generic MariaDB system variables and Galera-specific variables. The most important variable is wsrep_provider_options; it can be used to set many wsrep parameters, separated by a semicolon. This variable is dynamic.
The most important variables and parameters are explained in this section. A complete list of options and parameters can be found in the MariaDB Knowledge Base.
All Galera-specific variables have a 'wsrep_' suffix. To list them, we can use the following query:
SHOW VARIABLES LIKE 'wsrep%';
A discussion follows about the most important Galera system variables. They have been split into groups to make the discussion clearer.
The following list shows us what the generic cluster settings stand for:
wsrep_provider: This is the path of thewsreplibrary.wsrep_cluster_address: This is the address of one or more cluster nodes. As explained earlier, setting this value is necessary to add a node to a cluster. This variable is not dynamic.wsrep_cluster_name: This is the name of the cluster. Nodes refuse to connect to other nodes if they do not belong to the same cluster. This variable is dynamic and can be used to split a cluster.wsrep_node_address: This can be used to explicitly set the address of the current node. This variable is not dynamic.wsrep_node_name: This sets the node name. By default, the hostname is used.wsrep_on: This determines whether the node replicates data. It can be used to temporarily pause a node.
The following list shows the settings that can be used to tune performance and reliability:
wsrep_data_home_dir: This sets thedatadirectory for the current node.wsrep_slave_threads: This determines the number of concurrent threads used for replication. This may or may not speed up most operations; it is especially useful to speed up the synchronization of nodes. The minimum recommended value is4multiplied by the number of CPU cores, but the optimal value can sometimes be much higher. This variable is not dynamic, so it could be necessary to restart a node several times to test its performance with several values. Note that if this value is higher than1,innodb_locks_unsafe_for_binlogmust be set to1.wsrep_causal_reads: If this variable is set toOFF, it causes faster nodes to apply a write set and start the execution of new statements without waiting for slower nodes. This behavior is the default value, but it causes small inconsistencies for a short period of time. Setting this variable toONprevents these inconsistencies, but increases the latency. Its default value isOFF.wsrep_max_ws_size setd: This determines the maximum size of write sets, expressed in bytes. This variable is not dynamic.wsrep_max_ws_rows: This sets the maximum number of rows in a single write set. This variable is not dynamic.wsrep_retry_autocommit: This helps dealing with frequent conflicts within the cluster. It sets the maximum number of retries when a transaction fails because of such conflicts. This variable is not dynamic.wsrep_load_data_splitting: This splits long runningLOAD DATA INFILEstatements into multiple transactions, making them faster but less reliable.
The following settings affect the behavior of the SST:
wsrep_sst_donor: This is a comma-separated list of the nodes that can be used as donors. The nodes' names must be used here instead of their addresses.wsrep_sst_donor_rejects_queries: This determines whether the current node refuses to act as a donor for other nodes. By default, this value isOFF, which means that it can act as a donor.wsrep_sst_method: This determines the SST to be used, as explained in the State Snapshot Transfer section.wsrep_sst_auth: This is only relevant for SST methods that imply a connection to a running server, such asxtrabackupormysqldump. This variable contains the username and password to be used for authentication, separated by a colon, for example,root:my_password.
The following settings are useful when dealing with Galera Cluster limitations:
wsrep_replicate_myisam: This determines whether experimental MyISAM replication will be used. Its default value isOFF, which means that the MyISAM tables will simply be ignored.wsrep_convert_LOCK_to_trx: This can sometimes help the transition of applications from MyISAM to InnoDB. If it is set toON, theLOCK TABLESandUNLOCK TABLESstatements are silently converted intoSTART TRANSACTIONandCOMMIT. Its default value isOFF. If we enable this feature, we should be sure to check whether our applications work correctly.wsrep_certify_nonPK: This instructs Galera to automatically add primary keys for tables that do not have one. This solves several replication problems. It isONby default.
Many wsrep settings can be specified using the wsrep_provider_options variable. The settings must be separated with a semicolon, as seen in the following syntax:
SET wsrep_provider_options = 'option=value;option=value';
Similar to server variables, the wsrep options can be dynamic or not. If an option is not dynamic, the node must be restarted in order to change its value.
Almost all the option names have a prefix, which approximately indicates the component the option refers to. The pattern is as follows:
group_name.option_name
Most of these parameters require a deep knowledge of wsrep. Only the most important ones are explained here. The complete list can be found in the MariaDB Knowledge Base.
base_host: This is the node's IP address or hostname.base_port: This is the port used for replication.evs.inactive_timeout: This determines the amount of time a node can be unreachable before the weighted quorum algorithm is used to handle the problem.evs.user_send_window: This determines the number of packets that can be replicated together. If the network is slow, this value should be increased. This option is dynamic.gcache.dir: This sets a path for the GCache files. This allows writing the GCache files on a different storage device to optimize the performance. This is not dynamic.gcache.mem_size: This is the maximum size of the GCache. This is not dynamic.gcache.page_size: This is the size of the GCache pages. This is not dynamic.gcs.fc_master_slave: This can be set toONif only one node in the cluster can be used as a master, which means that all the clients only connect to that node. This allows somewsrepoptimizations. This is not dynamic.gcs.max_throttle: This determines how much the node provisioning can be accelerated, slowing down normal replication. The lower this value, the more the state transfer will be throttled. With a value of0, the replication will completely stop until the state transfer is completed. Remember that in Galera, slowing down the replication for one node means to slow down the replication for all the nodes.pc.ignore_quorum: This disables the weighted quorum algorithm. Setting this value to1implies that we know how to handle conflicts if a split brain problem occurs.