Galera Cluster System Variables
You are viewing an old version of this article. View
the current version here.
Contents
- Viewing MariaDB Galera Cluster Configuration Variables
- MariaDB Galera Cluster Configuration Variables
- wsrep_auto_increment_control
- wsrep_causal_reads
- wsrep_certify_nonPK
- wsrep_cluster_address
- wsrep_cluster_name
- wsrep_convert_LOCK_to_trx
- wsrep_data_home_dir
- wsrep_dbug_option
- wsrep_debug
- wsrep_desync
- wsrep_dirty_reads
- wsrep_drupal_282555_workaround
- wsrep_forced_binlog_format
- wsrep_gtid_domain_id
- wsrep_gtid_mode
- wsrep_load_data_splitting
- wsrep_log_conflicts
- wsrep_max_ws_rows
- wsrep_max_ws_size
- wsrep_mysql_replication_bundle
- wsrep_node_address
- wsrep_node_incoming_address
- wsrep_node_name
- wsrep_notify_cmd
- wsrep_on
- wsrep_OSU_method
- wsrep_provider
- wsrep_provider_options
- wsrep_recover
- wsrep_replicate_myisam
- wsrep_restart_slave
- wsrep_retry_autocommit
- wsrep_slave_fk_checks
- wsrep_slave_threads
- wsrep_slave_uk_checks
- wsrep_sst_auth
- wsrep_sst_donor
- wsrep_sst_donor_rejects_queries
- wsrep_sst_method
- wsrep_sst_receive_address
- wsrep_start_position
- wsrep_sync_wait
Viewing MariaDB Galera Cluster Configuration Variables
Galera Cluster configuration variables can be viewed with the SHOW VARIABLES statement:
SHOW VARIABLES LIKE 'wsrep%';
See also the Full list of MariaDB options, system and status variables.
MariaDB Galera Cluster Configuration Variables
wsrep_auto_increment_control
- Description: If set to
1
(the default), will automatically adjust the auto_increment_increment and auto_increment_offset variables according to the size of the cluster, and when the cluster size changes. This avoids replication conflicts due to auto_increment. In a master-slave environment, can be set toOFF
. - Scope: Global
- Dynamic: Yes
- Default Value:
ON
wsrep_causal_reads
- Description: If set to
ON
(OFF
is default), enforces read-committed characteristics across the cluster. In the case that a master applies an event more quickly than a slave, the two could briefly be out-of-sync. With this variable set toON
, the slave will wait for the event to be applied before processing further queries. Setting toON
also results in larger read latencies. - Scope: Global, Session
- Dynamic: Yes
- Default Value:
OFF
wsrep_certify_nonPK
- Description: When set to
ON
(the default), primary keys will be automatically generated for rows without one. This is required for parallel applying, and strongly recommended for all tables. - Scope: Global
- Dynamic: Yes
- Default Value:
ON
wsrep_cluster_address
- Description: The addresses of cluster nodes to connect to when starting up, for example
gcomm:192.168.0.1:1234?gmcast.listen_addr=0.0.0.0:2345
. Good practice is to specify all possible cluster nodes, in the formgcomm:<node1:ip>,<node2:ip2>,<node3:ip>
. Specifying an empty ip (gcomm:/ /
) will cause the node to start a new cluster (which should not be done in the my.cnf file, as after each restart the server will not rejoin the current cluster). The variable can be changed at runtime in some configurations, and will result in the node closing the connection to any current cluster, and connecting to the new address. If specifying a port, note that this is the Galera port, not the MariaDB port. - Scope: Global
- Dynamic: No
wsrep_cluster_name
- Description: The name of the cluster. Nodes cannot connect to clusters with a different name, so needs to be identical on all nodes in the same cluster.
- Scope: Global
- Dynamic: Yes
- Default Value:
my_wsrep_cluster
wsrep_convert_LOCK_to_trx
- Description: Converts LOCK/UNLOCK TABLES statements to BEGIN and COMMIT. Used mainly for getting older applications to work with a multi-master setup, use carefully, as can result in extremely large writesets.
- Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_data_home_dir
- Description: Directory where wsrep provider will store its internal files.
- Scope: Global
- Dynamic: No
- Default Value: The datadir variable value.
wsrep_dbug_option
- Description: Used to pass the DBUG option to the wsrep provider.
- Scope: Global
- Dynamic: Yes
wsrep_debug
- Description: When set to
ON
(OFF
is default), debug messages will be logged to the error log as well. - Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_desync
- Description: When set to
ON
(OFF
is default), the node will be desynced from the cluster. The same effect can be achieved with a/*! WSREP_DESYNC */
query comment. - Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_dirty_reads
- Description: By default, when not synchronized with the group (wsrep_ready=OFF) a node will reject all queries other than SET and SHOW. If
wsrep_dirty_reads
is set to1
, SELECT queries (dirty reads) will be accepted by the node. - Scope: Session
- Dynamic: Yes
- Default Value:
OFF
- Valid Values:
ON
,OFF
- Introduced:
MariaDB Galera 5.5.42
MariaDB Galera 10.0.16
MariaDB 10.1.3
wsrep_drupal_282555_workaround
- Description: If set to
ON
, a workaround for Drupal/MySQL/InnoDB bug #282555 is enabled. This is a bug where, in some cases, when inserting aDEFAULT
value into an AUTO_INCREMENT column, a duplicate key error may be returned. - Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_forced_binlog_format
- Description: A binary log format that will override any session binlog format settings.
- Scope: Global
- Dynamic: Yes
- Default Value:
NONE
- Valid Values:
STATEMENT
,ROW
,MIXED
orNONE
(which resets the forced binlog format state).
wsrep_gtid_domain_id
- Description: When wsrep_gtid_mode is set, this value is used as gtid_domain_id for galera transactions and also copied to the joiner nodes during state transfer. It is ignored, otherwise.
- Commandline:
--wsrep-gtid-domain-id=#
- Scope: Global
- Dynamic: Yes
- Data Type:
numeric
- Default Value:
0
- Range:
0
to4294967295
- Introduced:
MariaDB 10.1.4
wsrep_gtid_mode
- Description: Automatically update the (joiner) node's wsrep_gtid_domain_id value with that of donor's (received during state transfer) and use it in place of gtid_domain_id for all galera transactions. When OFF (default), wsrep_gtid_domain_id is simply ignored (backward compatibility).
- Commandline:
--wsrep-gtid-mode[={0|1}]
- Scope: Global
- Dynamic: Yes
- Data Type:
boolean
- Default Value:
OFF
- Introduced:
MariaDB 10.1.4
wsrep_load_data_splitting
- Description: If set to
ON
(the default), LOAD DATA INFILE supports big data files by introducing transaction splitting. - Scope: Global
- Dynamic: Yes
- Default Value:
ON
- Introduced:
MariaDB Galera 5.5.32
wsrep_log_conflicts
- Description: If set to
ON
(OFF
is default), details of conflicting InnoDB locks in the cluster will be logged. - Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_max_ws_rows
- Description: Maximum permitted number of rows per writeset.
- Scope: Global
- Dynamic: Yes
- Default Value:
131072
wsrep_max_ws_size
- Description: Maximum permitted size in bytes per writeset.
- Scope: Global
- Dynamic: Yes
- Default Value:
1073741824
(1GB)
wsrep_mysql_replication_bundle
- Description: Determines the number of replication events that are grouped together. Experimental implementation aimed to assist with bottlenecks when a single slave faces a large commit time delay. If set to
0
(the default), there is no grouping. - Scope: Global
- Dynamic: No
- Default Value:
0
- Range:
0
to1000
wsrep_node_address
- Description: Specifies the node's network address, in the format
ip address[:port]
. Used in situations where autoguessing is not reliable. - Scope: Global
- Dynamic: No
- Default Value: Primary network address, usually
eth0
with a default port of 4567
wsrep_node_incoming_address
- Description: Address this node listens on for client connections.
- Scope: Global
- Dynamic: No
- Default Value:
AUTO
wsrep_node_name
- Description: Name of this node.
- Scope: Global
- Dynamic: Yes
- Default Value: The server's hostname.
wsrep_notify_cmd
- Description: Command to be executed each time the node state or the cluster membership changes. Can be used for raising an alarm, configuring load balancers and so on. See the Codership Notification Script page for more details.
- Scope: Global
- Dynamic: Yes
- Default Value: Empty
wsrep_on
- Description: Whether or not wsrep replication is enabled. If set to
OFF
(ON
is default), no changes will be replicated. - Scope: Global, Session
- Dynamic: Yes
- Default Value:
ON
- Valid Values:
ON
,OFF
wsrep_OSU_method
- Description: Online schema upgrade method.
TOI
: Total Order Isolation. In each cluster node, DDL is processed in the same order regarding other transactions, guaranteeing data consistency. However, affected parts of the database will be locked for the whole cluster.RSU
: Rolling Schema Upgrade. DDL processing is only done locally on the node, and the user needs perform the changes manually on each node. The node is desynced from the rest of the cluster while the processing takes place to avoid the blocking other nodes. Schema changes must be backwards compatible to avoid breaking replication when the DDL processing is complete on the single node, and replication recommences.
- Scope: Global, Session
- Dynamic: Yes
- Default Value:
TOI
MariaDB 10.0.19
Starting MariaDB Galera version 10.0.19, it has also been made a session variable.
wsrep_provider
- Description: Location of the wsrep library
- Scope: Global
- Dynamic: Yes
- Default Value:
none
wsrep_provider_options
- Description: Semicolon (;) separated list of wsrep options (see wsrep_provider_options)
- Scope: Global
- Dynamic: No
- Default Value: Empty
wsrep_recover
- Description: Used for recovering the Global Transaction ID. If set to
ON
when the server starts, Galera will search for the Global Transaction ID in the log and output it to stderr, usually the error log. - Commandline:
--wsrep-recover
- Scope: Global
- Dynamic: No
- Default Value:
OFF
wsrep_replicate_myisam
- Description: Whether or not MyISAM tables will be replicated. This functionality is still experimental and should not be relied upon in production systems.
- Scope: Global
- Dynamic: No
- Default Value:
OFF
- Valid Values:
ON
,OFF
wsrep_restart_slave
- Description: If set to ON, the replication slave is restarted automatically, when node joins back to cluster.
- Scope: Global
- Dynamic: Yes
- Default Value: OFF
- Introduced:
MariaDB Galera 5.5.39
MariaDB Galera 10.0.10
wsrep_retry_autocommit
- Description: Number of times autocommited queries will be retried due to cluster-wide conflicts before returning an error to the client. If set to
0
, no retries will be attempted, while a value of1
(the default) or more specifies the number of retries attempted. Can be useful to assist applications using autocommit to avoid deadlocks. - Scope: Global
- Dynamic: No
- Default Value:
1
wsrep_slave_fk_checks
- Description: If set to ON (the default), the applier slave thread performs foreign key constraint checks.
- Scope: Global
- Dynamic: Yes
- Default Value: ON
- Introduced:
MariaDB Galera 5.5.39
MariaDB Galera 10.0.12
wsrep_slave_threads
- Description: Number of threads used for applying slave writesets in parallel. Galera parallel replication is only applied to transactions when safe to do so. If changed from the default (
1
), as a rough guideline consider using twice the number of CPU cores, and a quarter the number of writing clients used by other connections. - Scope: Global
- Dynamic: Yes
- Default Value:
1
- Range:
1
to512
wsrep_slave_uk_checks
- Description: If set to ON, the applier slave thread performs secondary index uniqueness checks.
- Scope: Global
- Dynamic: Yes
- Default Value: OFF
- Introduced:
MariaDB Galera 5.5.39
MariaDB Galera 10.0.12
wsrep_sst_auth
- Description: Username and password of the user to use for replication. Unused if wsrep_sst_method is set to
rsync
, while for other methods it should be in the format<user>:<password>
. The contents are masked in logs and when querying the value with SHOW VARIABLES. - Scope: Global
- Dynamic: Yes
- Default Value:
wsrep_sst_donor
- Description: Comma-separated list (from 5.5.33) or name (as per wsrep_node_name) of the servers as donors, or the source of the state transfer, in order of preference. If empty, or none of the provided ones are available, Galera will select one itself.
- Scope: Global
- Dynamic: Yes
- Default Value:
wsrep_sst_donor_rejects_queries
- Description: If set to
ON
(OFF
is default), the donor node will reject incoming queries, returning anUNKNOWN COMMAND
error code. Can be used for informing load balancers that a node is unavailable. - Scope: Global
- Dynamic: Yes
- Default Value:
OFF
wsrep_sst_method
- Description: Method used for taking the state snapshot transfer (sst). The command
wsrep_sst_<wsrep_sst_method>
will then be called, so a custom script can be written for this purpose. See Codership Scriptable State Snapshop page for details. Existing valid values includersync
(the default),mysqldump
,xtrabackup
andxtrabackup-v2
, which use those respective methods to perform the state snapshop transfer, orskip
.skip
skips the transfer and should only be used temporarily, for example when starting the cluster and manually restoring data. See also the mysqldump and Xtrabackup articles. Note that thersync
andmysqldump
SST methods support GTID. However, thextrabackup-v2
andxtrabackup
SST methods currently do not support GTID. Thextrabackup-v2
sst method was introduced in MariaDB Galera 5.5.37 and 10.0.10. - Scope: Global
- Dynamic: Yes
- Default Value:
rsync
- Valid Values:
rsync
,mysqldump
,xtrabackup
,xtrabackup-v2
wsrep_sst_receive_address
- Description: Address this node receives updates on.
- Scope: Global
- Dynamic: Yes
- Default Value:
AUTO
wsrep_start_position
- Description: UUID sequence number.
- Scope: Global
- Dynamic: Yes
- Default Value:
00000000-0000-0000-0000-000000000000:-1
wsrep_sync_wait
- Description: Setting it ensures synchronous read view before executing an operation of the type specified by
bitmask:
1
- READ (SELECT, SHOW & BEGIN/START TRANSACTION);2
- UPDATE and DELETE;4
- INSERT and REPLACE.
- Scope: Global, Session
- Dynamic: Yes
- Default Value: 0
- Introduced:
MariaDB Galera 5.5.39
MariaDB Galera 10.0.13
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.