Index of wsrep system variables¶

Percona XtraDB Cluster introduces a number of MySQL system variables related to write-set replication.

variable wsrep_auto_increment_control¶

Command Line:	--wsrep-auto-increment-control
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	ON

Enables automatic adjustment of auto-increment system variables depending on the size of the cluster:

• auto_increment_increment controls the interval between successive AUTO_INCREMENT column values
• auto_increment_offset determines the starting point for the AUTO_INCREMENT column value

This helps prevent auto-increment replication conflicts across the cluster by giving each node its own range of auto-increment values. It is enabled by default.

Automatic adjustment may not be desirable depending on application’s use and assumptions of auto-increments. It can be disabled in master-slave clusters.

variable wsrep_causal_reads¶

Version Info:	5.6.20-25.7 – Variable deprecated
Command Line:	--wsrep-causal-reads
Config File:	Yes
Scope:	Global, Session
Dynamic:	Yes
Default Value:	OFF

In some cases, master may apply events faster than a slave, which can cause master and slave to become out of sync for a brief moment. When this variable is set to ON, the slave will wait until that event is applied before doing any other queries. Enabling this variable will also result in larger latencies.

variable wsrep_certify_nonPK¶

Command Line:	--wsrep-certify-nonpk
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	ON

Enables automatic generation of primary keys for rows that don’t have them. Write set replication requires primary keys on all tables to allow for parallel applying of transactions. This variable is enabled by default. As a rule, make sure that all tables have primary keys.

variable wsrep_cluster_address¶

Command Line:	--wsrep-cluster-address
Config File:	Yes
Scope:	Global
Dynamic:	Yes

Defines the back-end schema, IP addresses, ports, and options that the node uses when connecting to the cluster. This variable needs to specify at least one other node’s address, which is alive and a member of the cluster. In practice, it is best (but not necessary) to provide a complete list of all possible cluster nodes. The value should be of the following format:

<schema>://<address>[?<option1>=<value1>[&<option2>=<value2>]],...

The only back-end schema currently supported is gcomm. The IP address can contain a port number after a colon. Options are specified after ? and separated by &. You can specify multiple addresses separated by commas.

For example:

wsrep_cluster_address="gcomm://192.168.0.1:4567?gmcast.listen_addr=0.0.0.0:5678"

If an empty gcomm:// is provided, the node will bootstrap itself (that is, form a new cluster). It is not recommended to have empty cluster address in production config after the cluster has been bootstrapped initially. If you want to bootstrap a new cluster with a node, you should pass the --wsrep-new-cluster option when starting.

variable wsrep_cluster_name¶

Command Line:	--wsrep-cluster-name
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	my_wsrep_cluster

Specifies the name of the cluster and should be identical on all nodes.

variable wsrep_convert_lock_to_trx¶

Command Line:	--wsrep-convert-lock-to-trx
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether locking sessions should be converted into transactions. By default, this is disabled.

Enabling this variable can help older applications to work in a multi-master setup by converting LOCK/UNLOCK TABLES statements into BEGIN/COMMIT statements. It is not the same as support for locking sessions, but it does prevent the database from ending up in a logically inconsistent state. Enabling this variable can also result in having huge write-sets.

variable wsrep_data_home_dir¶

Command Line:	No
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	/var/lib/mysql (or whatever path is specified by datadir)

Specifies the path to the directory where the wsrep provider stores its files (such as grastate.dat).

variable wsrep_dbug_option¶

Command Line:	--wsrep-dbug-option
Config File:	Yes
Scope:	Global
Dynamic:	Yes

Defines DBUG options to pass to the wsrep provider.

variable wsrep_debug¶

Command Line:	--wsrep-debug
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Enables additional debugging output for the database server error log. By default, it is disabled. This variable can be used when trying to diagnose problems or when submitting a bug.

variable wsrep_desync¶

Command Line:	No
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether the node should participate in Flow Control. By default, this variable is disabled, meaning that if the receive queue becomes too big, the node engages in Flow Control: it works through the receive queue until it reaches a more manageable size. For more information, see wsrep_local_recv_queue and wsrep_flow_control_interval.

Enabling this variable will disable Flow Control for the node. It will continue to receive write-sets that it is not able to apply, the receive queue will keep growing, and the node will keep falling behind the cluster indefinitely.

Toggling this back to OFF will require an IST or an SST, depending on how long it was desynchronized. This is similar to cluster desynchronization, which occurs during RSU TOI. Because of this, it’s not a good idea to enable wsrep_desync for a long period of time or for several nodes at once.

variable wsrep_dirty_reads¶

Version Info:	5.6.24-25.11 – Variable introduced 5.6.26-25.12 – Variable available both on session and global scope
Command Line:	--wsrep-dirty-reads
Config File:	Yes
Scope:	Session, Global
Dynamic:	Yes
Default Value:	OFF

Defines whether the node accepts read queries when in a non-operational state, that is, when it loses connection to the Primary Component. By default, this variable is disabled and the node rejects all queries, because there is no way to tell if the data is correct.

If you enable this variable, the node will permit read queries (USE, SELECT, LOCK TABLE, and UNLOCK TABLES), but any command that modifies or updates the database on a non-operational node will still be rejected (including DDL and DML statements, such as INSERT, DELETE, and UPDATE).

To avoid deadlock errors, set the wsrep_sync_wait variable to 0 if you enable wsrep_dirty_reads.

variable wsrep_drupal_282555_workaround¶

Command Line:	--wsrep-drupal-282555-workaround
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Enables a workaround for MySQL InnoDB bug that affects Drupal (Drupal bug #282555 and MySQL bug #41984). In some cases, duplicate key errors would occur when inserting the DEFAULT value into an AUTO_INCREMENT column.

variable wsrep_forced_binlog_format¶

Command Line:	--wsrep-forced-binlog-format
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	NONE

Defines a binary log format that will always be effective, regardless of the client session binlog_format variable value.

Possible values for this variable are:

• ROW: Force row-based logging format
• STATEMENT: Force statement-based logging format
• MIXED: Force mixed logging format
• NONE: Do not force the binary log format and use whatever is set by the binlog_format variable (default)

variable wsrep_load_data_splitting¶

Command Line:	--wsrep-load-data-splitting
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	ON

Defines whether the node should split large LOAD DATA transactions. This variable is enabled by default, meaning that LOAD DATA commands are split into transactions of 10 000 rows or less.

If you disable this variable, then huge data loads may prevent the node from completely rolling the operation back in the event of a conflict, and whatever gets committed stays committed.

variable wsrep_log_conflicts¶

Command Line:	--wsrep-log-conflicts
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	OFF

Defines whether the node should log additional information about conflicts. By default, this variable is disabled and Percona XtraDB Cluster uses standard logging features in MySQL.

If you enable this variable, it will also log table and schema where the conflict occurred, as well as the actual values for keys that produced the conflict.

variable wsrep_max_ws_rows¶

Command Line:	--wsrep-max-ws-rows
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	0 (no limit)

Defines the maximum number of rows each write-set can contain.

By default, there is no limit for the maximum number of rows in a write-set. The maximum allowed value is 1048576.

variable wsrep_max_ws_size¶

Command Line:	--wsrep_max_ws_size
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	2147483647 (2 GB)

Defines the maximum write-set size (in bytes). Anything bigger than the specified value will be rejected.

You can set it to any value between 1024 and the default 2147483647.

variable wsrep_mysql_replication_bundle¶

Command Line:	--wsrep-mysql-replication-bundle
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	0 (no grouping)
Range:	From 0 to 1000

Specifies the number of replication events to group together. By default, it is set to 0, which means there is no grouping.

Replication events are grouped in SQL slave thread by skipping events, which may cause commit. In this case, the wsrep node acting as a slave node and all other wsrep nodes in provider replication group, will see the same (huge) transactions. The implementation of grouping is experimental. It may help with the bottleneck of having only one slave facing commit time delay of synchronous provider. You can set it up to group up to 1000 events.

variable wsrep_node_address¶

Command Line:	--wsrep-node-address
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	IP of the first network interface (eth0) and default port (4567)

Specifies the network address of the node. By default, this variable is set to the IP address of the first network interface (usually eth0 or enp2s0) and the default port (4567).

While default value should be correct in most cases, there are situations when you need to specify it manually. For example:

• Servers with multiple network interfaces
• Servers that run multiple nodes
• Network Address Translation (NAT)
• Clusters with nodes in more than one region
• Container deployments, such as Docker
• Cloud deployments, such as Amazon EC2 (use the global DNS name instead of the local IP address)

The value should be specified in the following format:

<ip_address>[:port]

For example:

192.168.0.1:4567

variable wsrep_node_incoming_address¶

Command Line:	--wsrep-node-incoming-address
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	AUTO

Specifies the network address from which the node expects client connections. By default, it uses the IP address from wsrep_node_address and port number 3306.

This information is used for the wsrep_incoming_addresses variable, which shows all active cluster nodes.

variable wsrep_node_name¶

Command Line:	--wsrep-node-name
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	The node’s host name

Defines a unique name for the node. Defaults to the host name.

The name is used for convenience, to help you identify nodes in the cluster by means other than the node address.

variable wsrep_notify_cmd¶

Command Line:	--wsrep-notify-cmd
Config File:	Yes
Scope:	Global
Dynamic:	Yes

Specifies the notification command that the node should execute whenever cluster membership or local node status changes. This can be used for alerting or to reconfigure load balancers.

variable wsrep_on¶

Version Info:	5.6.27-25.13 – Variable available only in session scope
Command Line:	No
Config File:	No
Scope:	Session
Dynamic:	Yes
Default Value:	ON

Defines whether updates from the current session should be replicated. If disabled, it does not cause the node to leave the cluster and the node continues to communicate with other nodes.

variable wsrep_OSU_method¶

Version Info:	5.6.24-25.11 – Variable available both in global and session scope
Command Line:	--wsrep-OSU-method
Config File:	Yes
Scope:	Global and Session
Dynamic:	Yes
Default Value:	TOI

Defines the method for Online Schema Upgrade that the node uses to replicate DDL statements. The following methods are available:

• TOI: When the Total Order Isolation method is selected, data definition language (DDL) statements are processed in the same order with regards to other transactions in each node. This guarantees data consistency.

  In the case of DDL statements, the cluster will have parts of the database locked and it will behave like a single server. In some cases (like big ALTER TABLE) this could have impact on cluster’s performance and availability, but it could be fine for quick changes that happen almost instantly (like fast index changes).

  When DDL statements are processed under TOI, the DDL statement will be replicated up front to the cluster. That is, the cluster will assign global transaction ID for the DDL statement before DDL processing begins. Then every node in the cluster has the responsibility to execute the DDL statement in the given slot in the sequence of incoming transactions, and this DDL execution has to happen with high priority.

• RSU: When the Rolling Schema Upgrade method is selected, DDL statements won’t be replicated across the cluster. Instead, it’s up to the user to run them on each node separately.

  The node applying the changes will desynchronize from the cluster briefly, while normal work happens on all the other nodes. When a DDL statement is processed, the node will apply delayed replication events.

  The schema changes must be backwards compatible for this method to work, otherwise, the node that receives the change will likely break Galera replication. If replication breaks, SST will be triggered when the node tries to join again but the change will be undone.

variable wsrep_preordered¶

Command Line:	--wsrep-preordered
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether the node should use transparent handling of preordered replication events (like replication from traditional master). By default, this is disabled.

If you enable this variable, such events will be applied locally first before being replicated to other nodes in the cluster. This could increase the rate at which they can be processed, which would be otherwise limited by the latency between the nodes in the cluster.

Preordered events should not interfere with events that originate on the local node. Therefore, you should not run local update queries on a table that is also being updated through asynchronous replication.

variable wsrep_provider¶

Command Line:	--wsrep-provider
Config File:	Yes
Scope:	Global
Dynamic:	Yes

Specifies the path to the Galera library. This is usually /usr/lib64/libgalera_smm.so on CentOS/RHEL and /usr/lib/libgalera_smm.so on Debian/Ubuntu.

If you do not specify a path or the value is not valid, the node will behave as standalone instance of MySQL.

variable wsrep_provider_options¶

Command Line:	--wsrep-provider-options
Config File:	Yes
Scope:	Global
Dynamic:	No

Specifies optional settings for the replication provider documented in Index of wsrep_provider options. These options affect how various situations are handled during replication.

variable wsrep_recover¶

Command Line:	--wsrep-recover
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	OFF
Location:	mysqld_safe

Recovers database state after crash by parsing GTID from the log. If the GTID is found, it will be assigned as the initial position for server.

variable wsrep_reject_queries¶

Command Line:	No
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	NONE

Defines whether the node should reject queries from clients. Rejecting queries can be useful during upgrades, when you want to keep the node up and apply write-sets without accepting queries.

When a query is rejected, the following error is returned:

Error 1047: Unknown command

The following values are available:

• NONE: Accept all queries from clients (default)
• ALL: Reject all new queries from clients, but maintain existing client connections
• ALL_KILL: Reject all new queries from clients and kill existing client connections

variable wsrep_replicate_myisam¶

Version Info:	5.6.24-25.11 – Variable available both in global and session scope
Command Line:	--wsrep-replicate-myisam
Config File:	Yes
Scope:	Session, Global
Dynamic:	No
Default Value:	OFF

Defines whether DML statements for MyISAM tables should be replicated. It is disabled by default, because MyISAM replication is still experimental.

On the global level, wsrep_replicate_myisam can be set only during startup. On session level, you can change it during runtime as well.

For older nodes in the cluster, wsrep_replicate_myisam should work since the TOI decision (for MyISAM DDL) is done on origin node. Mixing of non-MyISAM and MyISAM tables in the same DDL statement is not recommended when wsrep_replicate_myisam is disabled, since if any table in the list is MyISAM, the whole DDL statement is not put under TOI.

variable wsrep_restart_slave¶

Command Line:	--wsrep-restart-slave
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether replication slave should be restarted when the node joins back to the cluster. Enabling this can be useful because asynchronous replication slave thread is stopped when the node tries to apply the next replication event while the node is in non-primary state.

variable wsrep_retry_autocommit¶

Command Line:	--wsrep-retry-autocommit
Config File:	Yes
Scope:	Global
Dynamic:	No
Default Value:	1

Specifies the number of times autocommit transactions will be retried in the cluster if it encounters certification errors. In case there is a conflict, it should be safe for the cluster node to simply retry the statement without returning an error to the client, hoping that it will pass next time.

This can be useful to help an application using autocommit to avoid deadlock errors that can be triggered by replication conflicts.

If this variable is set to 0, autocommit transactions won’t be retried.

variable wsrep_slave_FK_checks¶

Command Line:	--wsrep-slave-FK-checks
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	ON

Defines whether foreign key checking is done for applier threads. This is enabled by default.

variable wsrep_slave_threads¶

Command Line:	--wsrep-slave-threads
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	1

Specifies the number of threads that can apply replication transactions in parallel. Galera supports true parallel replication that applies transactions in parallel only when it is safe to do so. This variable is dynamic. You can increase/decrease it at any time.

If any replication consistency problems are encountered, it’s recommended to set this back to 1 to see if that resolves the issue. The default value can be increased for better throughput.

You may want to increase it as suggested in Codership documentation for flow control: when the node is in JOINED state, increasing the number of slave threads can speed up the catchup to SYNCED.

You can also estimate the optimal value for this from wsrep_cert_deps_distance as suggested on this page.

For more configuration tips, see this document.

variable wsrep_slave_UK_checks¶

Command Line:	--wsrep-slave-UK-checks
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether unique key checking is done for applier threads. This is disabled by default.

variable wsrep_sst_auth¶

Command Line:	--wsrep-sst-auth
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Format:	<username>:<password>

Specifies authentication information for State Snapshot Transfer (SST). Required information depends on the method specified in the wsrep_sst_method variable.

For more information about SST authentication, see State Snapshot Transfer.

variable wsrep_sst_donor¶

Command Line:	--wsrep-sst-donor
Config File:	Yes
Scope:	Global
Dynamic:	Yes

Specifies a list of nodes (using their wsrep_node_name values) that the current node should prefer as donors for SST and IST. If the value is empty, the first node in SYNCED state in the index becomes the donor and will not be able to serve requests during state transfer.

If you want to consider other nodes when listed ones are not available, add a comma at the end of the list, for example:

wsrep_sst_donor=node1,node2,

If you remove the trailing comma from the previous example, then the joining node will consider only node1 and node2.

variable wsrep_sst_donor_rejects_queries¶

Command Line:	--wsrep-sst-donor-rejects-queries
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	OFF

Defines whether the node should reject blocking client sessions when it is serving as a donor during a blocking state transfer method (when wsrep_sst_method is set to mysqldump or rsync). This is disabled by default, meaning that the node accepts such queries.

If you enable this variable, queries will return the Unknown command error. This can be used to signal load-balancer that the node isn’t available.

variable wsrep_sst_method¶

Command Line:	--wsrep-sst-method
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	xtrabackup-v2

Defines the method or script for State Snapshot Transfer (SST).

Available values are:

• xtrabackup-v2: Uses Percona XtraBackup to perform SST. This method requires wsrep_sst_auth to be set up with credentials (<user>:<password>) on the donor node. Privileges and perimssions for running Percona XtraBackup can be found in Percona XtraBackup documentation.

  Note

  This is the recommended and default method for Percona XtraDB Cluster. For more information, see Percona XtraBackup SST Configuration.

• rsync: Uses rsync to perform SST. This method doesn’t use the wsrep_sst_auth variable.

• mysqldump: Uses mysqldump to perform SST This method requires superuser credentials for the donor node to be specified in the wsrep_sst_auth variable.

  Note

  This method is not recommended unless it is required for specific reasons. Also, it is not compatible with bind_address set to 127.0.0.1 or localhost, and will cause startup to fail in this case.

• <custom_script_name>: Galera supports Scriptable State Snapshot Transfer. This enables users to create their own custom scripts for performing SST. For example, you can create a script /usr/bin/wsrep_MySST.sh and specify MySST for this variable to run your custom SST script.

• skip: Use this to skip SST. This can be used when initially starting the cluster and manually restoring the same data to all nodes. It shouldn’t be used permanently because it could lead to data inconsistency across the nodes.

variable wsrep_sst_receive_address¶

Command Line:	--wsrep-sst-receive-address
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	AUTO

Specifies the network address where donor node should send state transfers. By default, this variable is set to AUTO, meaning that the IP address from wsrep_node_address is used.

variable wsrep_start_position¶

Command Line:	--wsrep-start-position
Config File:	Yes
Scope:	Global
Dynamic:	Yes
Default Value:	00000000-0000-0000-0000-00000000000000:-1

Specifies the node’s start position as UUID:seqno. By setting all the nodes to have the same value for this variable, the cluster can be set up without the state transfer.

variable wsrep_sync_wait¶

Version Info:	5.6.20-25.7 – Variable introduced
Command Line:	--wsrep-sync-wait
Config File:	Yes
Scope:	Session
Dynamic:	Yes
Default Value:	0

Controls cluster-wide causality checks on certain statements. Checks ensure that the statement is executed on a node that is fully synced with the cluster.

The type of statements to undergo checks is determined by bitmask:

• 0: Do not run causality checks for any statements. This is the default.
• 1: Perform checks for READ statements (including SELECT, SHOW, and BEGIN or START TRANSACTION).
• 2: Perform checks for UPDATE and DELETE statements.
• 3: Perform checks for READ, UPDATE, and DELETE statements.
• 4: Perform checks for INSERT and REPLACE statements.
• 5: Perform checks for READ, INSERT, and REPLACE statements.
• 6: Perform checks for UPDATE, DELETE, INSERT, and REPLACE statements.
• 7: Perform checks for READ, UPDATE, DELETE, INSERT, and REPLACE statements.