For specific information please visit - https://www.percona.com/services/policies/percona-software-platform-lifecycle
Index of wsrep system variables¶
-
variable
wsrep_OSU_method
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: TOI
- This variable can be used to select schema upgrade method. Available values are:
- TOI - Total Order Isolation - When this method is selected
DDL
is processed in the same order with regards to other transactions in each cluster node. This guarantees data consistency. In case ofDDL
statements cluster will have parts of database locked and it will behave like a single server. In some cases (like bigALTER TABLE
) this could have impact on cluster’s performance and high availability, but it could be fine for quick changes that happen almost instantly (like fast index changes). WhenDDL
is processed under total order isolation (TOI) theDDL
statement will be replicated up front to the cluster. i.e. cluster will assign global transaction ID for theDDL
statement before theDDL
processing begins. Then every node in the cluster has the responsibility to execute theDDL
in the given slot in the sequence of incoming transactions, and thisDDL
execution has to happen with high priority. - RSU - Rolling Schema Upgrade - When this 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 theDDL
statement is processed 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 the replication breaks the SST will be triggered when the node tries to join again but the change will be undone.
- TOI - Total Order Isolation - When this method is selected
-
variable
wsrep_auto_increment_control
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: ON
This variable manages the auto_increment_increment
and auto_increment_offset
variables automatically depending on the size of the cluster. This helps prevent auto_increment
replication conflicts across the cluster by giving each node it’s own range of auto_increment
values.
This may not be desirable depending on application’s use and assumptions of auto-increments. It can be turned off in Master/Slave clusters.
-
variable
wsrep_causal_reads
¶ Version Info: - 5.5.39-25.11 – Variable deprecated by
wsrep_sync_wait
Command Line: Yes
Config File: Yes
Scope: Global, Local
Dynamic: Yes
Default Value: OFF
- 5.5.39-25.11 – Variable deprecated by
In some cases master may apply event faster than a slave, which can cause master and slave being out-of-sync for a brief moment. When this variable is set to ON
slave will wait till that event is applied before doing any other queries. Enabling this variable will also result in larger latencies.
-
variable
wsrep_certify_nonPK
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: ON
When this variable is enabled, primary keys will be generated automatically for the rows that the rows don’t have them. Using tables without primary keys is not recommended.
-
variable
wsrep_cluster_address
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This minimally needs to be any single other cluster node’s address that 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. This takes the form of:
gcomm://<node:ip>,<node:ip>,<node:ip>
If an empty gcomm://
is provided, this tells the node to bootstrap it self (i.e., form a new cluster). This is not recommended for production after the cluster has been bootstrapped initially.
-
variable
wsrep_cluster_name
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: my_wsrep_cluster
This is the name of the cluster and should be identical on all nodes belonging to the same cluster.
-
variable
wsrep_convert_LOCK_to_trx
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
This variable is used to convert LOCK/UNLOCK TABLES
statements to BEGIN/COMMIT
. Although this can help some older applications to work with multi-master setup it can also result in having huge writesets.
-
variable
wsrep_data_home_dir
¶ Command Line: No Config File: Yes Scope: Global Dynamic: No Default Value: mysql datadir
This variable can be used to set up the directory where wsrep provider will store its files (like grastate.dat
).
-
variable
wsrep_dbug_option
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This variable is used to send the DBUG
option to the wsrep provider.
-
variable
wsrep_debug
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
When this variable is set to ON
, debug messages will also be logged to the error_log. This can be used when trying to diagnose the problem or when submitting a bug.
-
variable
wsrep_desync
¶ Command Line: No Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
This variable controls whether the node participates in Flow Control. Setting the wsrep_desync
to ON
does not automatically mean that a node will be out of sync with the cluster. It will continue to replicate in and out the writesets as usual. The only difference is that flow control will no longer take care of the desynced
node. The result is that if wsrep_local_recv_queue
gets higher than maximum allowed, all the other nodes will continue working ignoring the replication lag on the node being in desync
mode. Toggling this back will require a IST or a SST depending on how long it was desynchronized. This is similar to cluster de-synchronization which occurs during RSU TOI. Because of this, it’s not a good idea to keep desync set for a long period of time, nor should you desync several nodes at once. Also, you’ll need to desync a node before it starts causing flow control for it to have any effect. Node can also be desynchronized with /*! WSREP_DESYNC */
query comment.
-
variable
wsrep_drupal_282555_workaround
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
This variable was introduced as workaround for Drupal/MySQL bug #282555. In some cases duplicate key error would occur when inserting the default
value in into the auto_increment
field.
-
variable
wsrep_forced_binlog_format
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: NONE
- This variable defines a binlog format that will be always be effective regardless of session binlog format setting. Supported options for this variable are:
- ROW
- STATEMENT
- MIXED
- NONE - This option resets the forced state of the binlog format
-
variable
wsrep_load_data_splitting
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: ON
This variable controls whether LOAD DATA
transaction splitting is wanted or not.
-
variable
wsrep_log_conflicts
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
This variable is used to control whether sole cluster conflicts should be logged. When enabled details of conflicting InnoDB lock will be logged.
-
variable
wsrep_max_ws_rows
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: 131072 (128K)
This variable is used to control maximum number of rows each writeset can contain. Anything bigger than this will be rejected.
-
variable
wsrep_max_ws_size
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: 1073741824 (1G)
This variable is used to control maximum writeset size (in bytes). Anything bigger than this will be rejected.
-
variable
wsrep_mysql_replication_bundle
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No Default Value: 0 (no grouping) Range: 0-1000
This variable controls how many replication events will be grouped together. Replication events are grouped in SQL slave thread by skipping events which may cause commit. This way the wsrep node acting in MySQL slave role and all other wsrep nodes in provider replication group, will see same (huge) transactions. This implementation is still experimental. This may help with the bottleneck of having only one MySQL slave facing commit time delay of synchronous provider.
-
variable
wsrep_node_address
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No Format: <ip address>[:port] Default Value: Usually set up as primary network interface (eth0)
This variable is used to specify the network address of the node. In some cases when there are multiple NICs available, state transfer might not work if the default NIC is on different network. Setting this variable explicitly to the correct value will makes SST and IST work correctly out of the box. Even in the multi-network setups, IST/SST can be configured to use other interfaces/addresses.
-
variable
wsrep_node_incoming_address
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No Default Value: < wsrep_node_address
>:3306
This is the address at which the node accepts client connections. This is information is used for status variable wsrep_incoming_addresses
which shows all the active cluster nodes.
-
variable
wsrep_node_name
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This variable is used to set up the unique node name.
-
variable
wsrep_notify_cmd
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This variable is used to set the notification command that server will execute every time cluster membership or local node status changes.
-
variable
wsrep_on
¶ Command Line: No Config File: No Scope: Local, Global Dynamic: Yes Default Value: ON
This variable is used to enable/disable wsrep replication. When set to OFF
server will stop replication and behave like standalone MySQL server.
-
variable
wsrep_provider
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: None
This variable should contain the path to the Galera library (like /usr/lib64/libgalera_smm.so
on CentOS/RHEL and /usr/lib/libgalera_smm.so
on Debian/Ubuntu).
-
variable
wsrep_provider_options
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No
This variable contains settings currently used by Galera library.
-
variable
wsrep_recover
¶ Command Line: Yes Config File: No Scope: Global Dynamic: No Default Value: OFF
When server is started with this variable (as --wsrep-recover
) it will parse Global Transaction ID from log, and if the GTID is found, output to stderr (which usually goes into the log). This option is used to recover GTID, mysqld is called with this automatically in mysqld_safe, hence running this manually is not required, also no need to set it in my.cnf.
-
variable
wsrep_reject_queries
¶ Command Line: No Config File: Yes Scope: Global Dynamic: Yes Default Value: NONE
This variable can be used to reject queries for that node. This can be useful during upgrades for keeping node up (with provider enabled) without accepting queries. Using read-only is recommended here unless you want to kill existing queries. Following values are supported:
NONE
- default - nothing is rejected.ALL
- all queries are rejected with ‘Error 1047: Unknown command’.ALL_KILL
- all queries are rejected and existing client connections are also killed without waiting.
Note, that this doesn’t affect galera replication in any way, only the applications which connect to database are affected. If you are looking for desyncing a node then wsrep_desync
is the right option for that.
-
variable
wsrep_replicate_myisam
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No Default Value: Off
This variable controls if MyISAM will be replicated or not. MyISAM replication is still experimental and that is one of the reasons why this variable is set to OFF
by default. From version 5.5.41-25.11
MyISAM DDL (CREATE TABLE
only) isn’t replicated when wsrep_replicate_myisam
is OFF
. Note, 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 with wsrep_replicate_myisam
OFF
since if any table in list is MyISAM, the whole DDL statement is not put under TOI (total order isolation). This also doesn’t work if default_storage_engine
is set to MyISAM
(which is not recommended for Percona XtraDB Cluster) and a table is created without the ENGINE
option.
-
variable
wsrep_retry_autocommit
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: No Default Value: 1
This variable sets the number of times autocommitted transactions will be tried 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 the client’s knowledge with the hopes that it will pass the next time. This can be useful to help an application using autocommit to avoid the deadlock errors that can be triggered by replication conflicts. If this variable is set to 0
transaction won’t be retried and if it is set to 1
it will be retried once.
-
variable
wsrep_slave_FK_checks
¶ Version Info: - 5.5.39-25.11 – Variable introduced
Command Line: Yes
Config File: Yes
Scope: Global
Dynamic: Yes
Default Value: ON
This variable is used to control if Foreign Key checking is done for applier threads.
-
variable
wsrep_slave_UK_checks
¶ Version Info: - 5.5.39-25.11 – Variable introduced
Command Line: Yes
Config File: Yes
Scope: Global
Dynamic: Yes
Default Value: OFF
This variable is used to control if Unique Key checking is done for applier threads.
-
variable
wsrep_slave_threads
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: 1
This variable controls the number of threads that can apply replication transactions in parallel. Galera supports true parallel replication, replication that applies transactions in parallel only when it is safe to do so. The variable is dynamic, you can increase/decrease it anytime, note that, when you decrease it, it won’t kill the threads immediately but stop them after they are done applying current transaction (the effect with increase is immediate though). 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 many a time as suggested in Codership documentation, in JOINED
state for instance to speed up the catchup process to SYNCED
. You can also estimate the optimal value for this from wsrep_cert_deps_distance
as suggested on this page. You can also refer to this for more configuration tips.
-
variable
wsrep_sst_auth
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Format: <username>:<password>
This variable should contain the authentication information needed for State Snapshot Transfer. Required information depends on the method selected in the wsrep_sst_method
. More information about required authentication can be found in the State Snapshot Transfer documentation. This variable will appear masked in the logs and in the SHOW VARIABLES
query.
-
variable
wsrep_sst_donor
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This variable contains the name (wsrep_node_name
) of the preferred donor for the SST. If no node is selected as a preferred donor it will be chosen from one of the available nodes automatically.
-
variable
wsrep_sst_donor_rejects_queries
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: OFF
When this variable is enabled SST donor node will not accept incoming queries, instead it will reject queries with UNKNOWN COMMAND
error code. This can be used to signal load-balancer that the node isn’t available.
-
variable
wsrep_sst_method
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: rsync Recommended: xtrabackup-v2
- This variable sets up the method for taking the State Snapshot Transfer (SST). Available options are:
- xtrabackup - uses Percona XtraBackup to perform the SST, this method requires
wsrep_sst_auth
to be set up with <user>:<password> which XtraBackup will use on donor. Privileges and permissions needed for running XtraBackup can be found here. - xtrabackup-v2 - This is same as xtrabackup SST except that it uses newer protocol, hence is not compatible. This is the recommended option for PXC 5.5.34 and above. For more details, please check Xtrabackup SST Configuration and Percona XtraDB Cluster Errata (as of 5.5.34).
- rsync - uses
rsync
to perform the SST, this method doesn’t use thewsrep_sst_auth
- mysqldump - uses
mysqldump
to perform the SST, this method requireswsrep_sst_auth
to be set up with <user>:<password>, where user has root privileges on the server. - custom_script_name - Galera supports Scriptable State Snapshot Transfer. This enables users to create their own custom script for performing an SST.
- skip - this option can be used to skip the SST, it can be used when initially starting the cluster and manually restore the same data to all nodes. It shouldn’t be used as permanent setting because it could lead to data inconsistency across the nodes.
- xtrabackup - uses Percona XtraBackup to perform the SST, this method requires
Note
- Note the following:
- mysqldump SST is not recommended unless it is required for specific reasons. Also, it is not compatible with
bind_address = 127.0.0.1 or localhost
and will cause startup to fail if set so. - Xtrabackup-v2 SST is currently recommended if you have innodb-log-group_home-dir/innodb-data-home-dir in your cnf. Refer to
sst-special-dirs
for more.
- mysqldump SST is not recommended unless it is required for specific reasons. Also, it is not compatible with
-
variable
wsrep_sst_receive_address
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes Default Value: AUTO
This variable is used to configure address on which the node expects the SST.
-
variable
wsrep_start_position
¶ Command Line: Yes Config File: Yes Scope: Global Dynamic: Yes
This variable contains the UUID:seqno
value. By setting all the nodes to have the same value for this option cluster can be set up without the state transfer.
-
variable
wsrep_sync_wait
¶ Version Info: - 5.5.39-25.11 – Variable introduced
Command Line: Yes
Config File: Yes
Scope: Global, Session
Dynamic: Yes
This variable is used to control causality checks on some SQL statements, such as SELECT
, BEGIN
/END
, SHOW STATUS
, but not on some autocommit SQL statements UPDATE
and INSERT
. Causality check is determined by bitmask:
1
Indicates check onREAD
statements, includingSELECT
,SHOW
,BEGIN
/START TRANSACTION
.2
Indicates check onUPDATE
andDELETE
statements.4
Indicates check onINSERT
andREPLACE
statements
This variable deprecates the wsrep_causal_reads
variable. Setting wsrep_sync_wait
to 1
is the equivalent of setting wsrep_causal_reads
to ON
.
Contact Us
For free technical help, visit the Percona Community Forum.To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or professional services, contact Percona Sales.