Chapter 8. Managing database replication with Galera

Procedure

• Run the following command and replace <variable> with the wsrep database variable that you want to check:

  $ sudo podman exec galera-bundle-podman-0 sudo mysql -B --password="[MYSQL-HIERA-PASSWORD]" -e "SHOW GLOBAL STATUS LIKE <variable;"

  Copy to Clipboard Toggle word wrap

  The following example shows how to view the cluster state UUID of the node:

  $ sudo podman exec galera-bundle-podman-0 sudo mysql -B --password="[MYSQL-HIERA-PASSWORD]" -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid';"
  
      +--------------------------+--------------------------------------+
      | Variable_name            | Value                                |
      +--------------------------+--------------------------------------+
      | wsrep_cluster_state_uuid | e2c9a15e-5485-11e0-0800-6bbb637e7211 |
      +--------------------------+--------------------------------------+

  Copy to Clipboard Toggle word wrap

  The following table lists the wsrep database variables that you can use to check cluster integrity.

  Expand

  Table 8.1. Database variables to check for cluster integrity

  Variable	Summary	Description
  wsrep_cluster_state_uuid	Cluster state UUID	ID of the cluster to which the node belongs. All nodes must have an identical cluster ID. A node with a different ID is not connected to the cluster.
  wsrep_cluster_size	Number of nodes in the cluster	You can check this on any node. If the value is less than the actual number of nodes, then some nodes either failed or lost connectivity.
  wsrep_cluster_conf_id	Total number of cluster changes	Determines whether the cluster was split to several components, or partitions. Partitioning is usually caused by a network failure. All nodes must have an identical value. In case some nodes report a different wsrep_cluster_conf_id, check the wsrep_cluster_status value to see if the nodes can still write to the cluster (Primary).
  wsrep_cluster_status	Primary component status	Determines whether the node can write to the cluster. If the node can write to the cluster, the wsrep_cluster_status value is Primary. Any other value indicates that the node is part of a non-operational partition.

  Show more

Procedure

• Run the following command and replace <variable> with the wsrep database variable that you want to check:

  $ sudo podman exec galera-bundle-podman-0 sudo mysql -B --password="[MYSQL-HIERA-PASSWORD]" -e "SHOW GLOBAL STATUS LIKE <variable>;"

  Copy to Clipboard Toggle word wrap

  The following table lists the wsrep database variables that you can use to check node integrity.

  Expand

  Table 8.2. Database variables to check for node integrity

  Variable	Summary	Description
  wsrep_ready	Node ability to accept queries	States whether the node can accept write-sets from the cluster. If so, then wsrep_ready is ON.
  wsrep_connected	Node network connectivity	States whether the node can connect to other nodes on the network. If so, then wsrep_connected is ON.
  wsrep_local_state_comment	Node state	Summarizes the node state. If the node can write to the cluster, then typical values for wsrep_local_state_comment can be Joining, Waiting on SST, Joined, Synced, or Donor. If the node is part of a non-operational component, then the value of wsrep_local_state_comment is Initialized.

  Show more
  Note
  • The wsrep_connected value can be ON even if the node is connected only to a subset of nodes in the cluster. For example, in case of a cluster partition, the node might be part of a component that cannot write to the cluster. For more information about checking cluster integrity, see Section 8.2, “Checking MariaDB cluster integrity”.
  • If the wsrep_connected value is OFF, then the node is not connected to any cluster components.

Procedure

• Run the following command and replace <variable> with the wsrep database variable that you want to check:

  $ sudo podman exec galera-bundle-podman-0 sudo mysql -B --password="[MYSQL-HIERA-PASSWORD]" -e "SHOW STATUS LIKE <variable>;"

  Copy to Clipboard Toggle word wrap

  The following table lists the wsrep database variables that you can use to test database replication performance.

  Expand

  Table 8.3. Database variables to check for database replication performance

  Variable	Summary	Usage
  wsrep_local_recv_queue_avg	Average size of the local received write-set queue after the last query.	A value higher than 0.0 indicates that the node cannot apply write-sets as quickly as it receives write-sets, which triggers replication throttling. Check wsrep_local_recv_queue_min and wsrep_local_recv_queue_max for a detailed look at this benchmark.
  wsrep_local_send_queue_avg	Average send queue length after the last query.	A value higher than 0.0 indicates a higher likelihood of replication throttling and network throughput problems.
  wsrep_local_recv_queue_min and wsrep_local_recv_queue_max	Minimum and maximum size of the local receive queue after the last query.	If the value of wsrep_local_recv_queue_avg is higher than 0.0, you can check these variables to determine the scope of the queue size.
  wsrep_flow_control_paused	Fraction of the time that Flow Control paused the node after the last query.	A value higher than 0.0 indicates that Flow Control paused the node. To determine the duration of the pause, multiply the wsrep_flow_control_paused value with the number of seconds between the queries. The optimal value is as close to 0.0 as possible. For example: If the value of wsrep_flow_control_paused is 0.50 one minute after the last query, then Flow Control paused the node for 30 seconds. If the value of wsrep_flow_control_paused is 1.0 one minute after the last query, then Flow Control paused the node for the entire minute.
  wsrep_cert_deps_distance	Average difference between the lowest and highest sequence number (seqno) value that can be applied in parallel	In case of throttling and pausing, this variable indicates how many write-sets on average can be applied in parallel. Compare the value with the wsrep_slave_threads variable to see how many write-sets can actually be applied simultaneously.
  wsrep_slave_threads	Number of threads that can be applied simultaneously	You can increase the value of this variable to apply more threads simultaneously, which also increases the value of wsrep_cert_deps_distance. The value of wsrep_slave_threads must not be higher than the number of CPU cores in the node. For example, if the wsrep_cert_deps_distance value is 20, you can increase the value of wsrep_slave_threads from 2 to 4 to increase the amount of write-sets that the node can apply. If a problematic node already has an optimal wsrep_slave_threads value, you can exclude the node from the cluster while you investigate possible connectivity issues.

  Show more