Galera provides a set of status variables, which can be used to monitor the status of each node. Like Galera server variables, each status variable starts with the 'wsrep_'
prefix. This allows visualizing all the variables easily with the following query:
SHOW STATUS LIKE 'wsrep%';
There are at least two ways to automate the monitoring of MariaDB Galera Cluster:
The
wsrep_notify_cmd
server variable can be set to a shell command or script, which will be automatically called when the node changes its status. Some parameters will be added to the invocation to provide the notification script with all the information it may need. The script can then use this information to perform any desired action, such as logging an event on a file, logging an event in a MariaDB table, or sending a mail to inform the database administrator that a major problem has occurred.
The notification script will be invoked with the following syntax:
<command> --status <new_status> --uuid <state_UUID> --primary [yes|no] --members <members_list> --index <node_index>
Here's an explanation of the parameters:
command
: This is the command specified in the wsrep_notify_cmd
variablenew_status
: This is the current status of the nodestate_UUID
: This is a Universally Unique Identifier (UUID) associated with the last state change--primary
: This argument indicates whether the node is a member of a primary clustermembers_list
: This is a comma-separated list of the nodes connected to the node's cluster partitionThe following example is not very useful but it shows how to write a notification script, which does something useful. The following code is a bash script. It simply sends an e-mail to the DBA if Node1
becomes part of a non-primary node. The code is as follows:
#!/bin/sh -eu while [ $# -gt 0 ] do case $1 in --primary) [ "$2" != "yes" ] echo "Node1 is not in a primary cluster anymore!"> $EMAILMESSAGE /bin/mail -s "Galera Problem" "[email protected]" < $EMAILMESSAGE shift ;; esac shift done
To instruct Galera to call this script, we can use a statement similar to the following:
SET @@global.wsrep_notify_cmd = '/path/to/notify.sh';
There are several checks that can be performed periodically to verify the integrity of a cluster. These checks fall into four categories:
A status variable that we may want to periodically check is wsrep_cluster_size
. If this value is lower than the expected one, at least one node has crashed or the cluster has been split into multiple partitions. Checking this variable on one node is sufficient. However, if we detect that the size of the cluster is too low, we will want to check other nodes to verify what happened.
In this case, we can check the wsrep_cluster_state_uuid
and wsrep_cluster_conf_id
variables in each node. These values are UUIDs, which in normal conditions are identical for all nodes. If two nodes have different values for wsrep_cluster_state_uuid
, they are not connected to the same cluster. If those values are identical, but the value of wsrep_cluster_conf
differs, the cluster has been partitioned.
If the cluster is partitioned, we may want to connect to a node for each partition to check which of them is the primary node. To do this, we can query the wsrep_cluster_status
status variable. Checking this variable for each node in the cluster is another way to find out whether the cluster is partitioned; if at least one node does not belong to the primary cluster, more than one partition exists.
If the cluster's size is small, we may want to check each node to find out whether it is running or not.
The best way to check whether a node is properly running is to query the wsrep_ready
status variable. If the node is in good health, its value must be true. If not, we must try to find out what is going wrong.
The wsrep_connected
status variable indicates whether the wsrep
library is running and connected to MariaDB. This probably means that wsrep
could not be loaded because of a configuration error. In this case, we will check the correctness of variables like wsrep_cluster_address
.
If the value of wsrep_connected
is true
, we can check the value of wsrep_local_state_comment
. If it is 'Joining'
, 'Waiting for SST'
, or 'Joined'
, the node is still connected to the cluster. With big databases and slow connections, the 'Waiting for SST'
phase can take a lot of time.
In synchronous replication, a cluster is not faster than the slower node. This happens because all nodes, after performing a transaction, have to wait until all the other nodes have performed the same transaction. For this reason, it is important to periodically check the wsrep_flow_control_paused
status variable in Galera. This value is in the range between 0
and 1
and represents the fraction of time that the node spent waiting until the other nodes completed a transaction. If the value is not very close to 0
, we have a latency problem.
In this case, we will need to identify the slow nodes. To do this, we will check two status variables:
wsrep_flow_control_sent
wsrep_local_recv_queue_avg
Slower nodes have higher values.
If a node is slow, we should try to increase the number of parallel threads used for replication. Do not forget to check common MariaDB performance problems such as bad usage of the buffer pool.
If none of the nodes is sensibly slower than the others and we are still not happy with the performance, the bottleneck is probably the network speed. To verify this, we can check the
wsrep_local_send_queue_avg
status variable. A slow network leads to a high number of queued messages. The trivial but vital ping tool can confirm poor network performance. Or iftop
can show us data about the network traffic, confirming whether the bandwidth is saturated.
There are many possible reasons for a slow network and this topic cannot be covered by a book about MariaDB. Some general tips are as follows: