I learn more and more about Galera every day.  As I learn more, I try to keep my myq_gadgets toolkit up to date with what I consider is important to keep any eye on on a PXC node.  In that spirit, I just today pushed some changes to the ‘wsrep’ report, and I thought I’d go over some of the status variables and metrics being tracked there with the aim to show folks what they should be watching (at least from my opinion, this is subject to change!).

First, let’s take a look at the output:

As I’ve mentioned before, myq_status gives an iostat-like output of your server.  This tool takes what are usually global counters in SHOW GLOBAL STATUS and calculates the change each second and reports that.  There’s lot of other reports it can run, but this one is focused on ‘wsrep%’ status variables.

It’s important to note that this reflects the status of a single PXC node in my cluster, node3 to be precise, so some information is cluster-wide, other information is specific to this particular node.  I tend to open a window for each node and run the tool on each so I can see things across the entire cluster at a glance.  Sometime in the future, I’d like to build a tool that polls every cluster node, but that’s not available currently.

Let’s go through the columns.

Cluster

There are 4 columns in the cluster section, and it’s important to understand that this tool only currently connections to a single node (by default, localhost).  The state of the cluster could be divergent across multiple nodes, so be careful to not assume all nodes have these values!

name

The cluster’s name (first 5 characters).  This is wsrep_cluster_name.

P

Either P for Primary or N for Non-primary.  This is the state of this partition of the cluster.  If a cluster gets split brained, then only a quorum (>=51%) of the remaining nodes will remain Primary.  Non-primary clusters are the remaining minority and will not allow database operations.

cnf

This is wserep_cluster_conf_id — the version # of the cluster configuration.  This changes every time a node joins or leaves the cluster.  Seeing high values here may indicate you have nodes frequently dropping out and rejoining the cluster and you may need some retuning of some node timeouts to prevent this.

#

The number of nodes in the cluster.

Node

This is state data about the local node that the tool happens to be connected to.

name

The name of this local node (first 5 characters).  This is handy when you have this tool running in several windows on several nodes.

cmt

This is the wsrep_local_state_comment — basically a plaintext word describing the state of the  node in terms of the rest of the cluster.  ‘Sync’ (Synced) is what you want to see, but ‘Dono’ (Donor), ‘Join’ (Joiner), and others are possible.  This is handy to quickly spot which node was elected to Donate either SST or IST to another node entering the cluster.

sta

Short for state, this is two True/False values (T/F) for wsrep_ready and wsrep_connected.  These are somewhat redundant with the local_state value, so I may remove them in the future.

Queue

This is information about the replication queues in both directions.

The ‘Up’ queue is outbound replication.  This generally increases when some other node is having difficulty receiving replication events.

The ‘Dn’ (down) queue is inbound replication.  Positive values here can be an indicator that this node is slow to apply replication writesets.

Ops

Ops are simply replication transactions or writesets.  Up is outbound, i.e., where this node was the originator of the transaction.  Dn is download, that is, transactions from other nodes in the cluster.

Bytes

Just like Ops, but in Bytes instead of transaction counts.  I have seen production clusters having performance issues where I noticed that the Ops and Bytes went to Zeros on all the nodes for a few seconds, and then a massive 90M+ replication transaction came through.  Using the Up and Dn columns, I could easily see which node was the originator of the transaction.

Flow

Flow gives some information about Flow Control events.  Galera has some sophisticated ways of metering replication so lag does not become a problem.

pau

wsrep_flow_control_paused — This is the amount of time since the last time SHOW GLOBAL STATUS was run that replication was paused due to flow control.  This is a general indicator that flow control is slowing replication (and hence overall cluster writes) down.

snt

wsrep_flow_control_sent — how many flow control events were SENT from this node.  Handy to find the node slowing the others down.

dst

This does not go under the Flow group.  This is wsrep_cert_deps_distance — This is a general indicator of how many parallel replication threads you could use.  In practice I haven’t found this extremely helpful yet and I may remove this in the future.  I think being aware of how Flow control works and watching flow control events and queue sizes is a better way to detect replication lag, and this really just tells you if multi-threaded replication could help improve replication speed at all.

Conflct

Replication conflicts, as described in my last post.  lcf is local certification failures, and bfa is brute force aborts.  This should be helpful to understand that these conflicts are or are not happening.

Interpreting the results

Let’s look at that output again and make some observations about our cluster and this node:

We can see are are connected to the node identified as ‘node3’.  Our cluster is Primary and there are 3 nodes total belonging to it.

There isn’t any replication queue activity, and I find this is common except during cluster stalls.  There are clearly a fair amount of transactions being replicated to and from this node: approximately 100K worth of data outbound, and just a hair more than that coming in.

Our replication is performing well, because the Flow control columns are zeroes, but we do see some replication conflicts.  Mostly these are brute force aborts, but I was able to see the (very occasional) local certification failure.  This makes sense to me because the replication inbound queue always reports as empty, so it seems that replication is being applied nearly immediately. Local certification failures only happen when the inbound queue is > 0.  Instead brute force aborts are applying writesets rolling back locally open transactions.

In fact, this is a sysbench test what is running full speed (these are VMs, so that’s not that particularly fast) on two of my three nodes, and more slowly on the third.  I had to decrease my table size from 250k to 2.5k to start seeing replication conflicts regularly.

Hopefully this is helpful for you to get an idea of how to observe and interpret Galera status variables.

7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Henrik Ingo

So my tip with sysbench helped 🙂

This is a really nice tool, thanks for doing it!

0
Sergey

Very nice tool, tnanks.
I hope such tool will be a part of percona-toolkit in the future.

0
Sergey

> Ops
> Ops are simply replication transactions or writesets. Up is outbound, i.e., where this node was the originator of the transaction.
> In is inbound, that is, transactions from other nodes in the cluster.

1) s/In/Dn ?

2) I have ‘Ops Dn’ > 0 on the Master (node for writing). It’s much smaller than ‘Ops Up’, but it is not zero.
What are these writesets?

0
Jay Janssen

Sergey:

1) Fixed, thanks 🙂

2) Are you SURE there is only one master? 🙂 Check your other nodes to see where they are originating from (i.e., where they are the ‘Up’). I believe I have seem some ops generated during cluster state changes, but that’s not typical AFAIK.

0
Mrten

I’ve been graphing these variables for some time now with Munin and kjellm’s mysql-munin plugin:

https://github.com/kjellm/munin-mysql/

(There’s a Galera.pm in the contrib directory).

The downside is that munin only pokes once every five minutes. However, graphing it continuously gives you a baseline.

0
Jay Janssen

Mrten: thanks, I love kjellm’s munin work!

0
Krisia06

How do you calculate the # of nodes in the cluster?
I’m not finding a wsrep variable with that info?
Thanks,

0