I occasionally see customers who are taking backups from their PXC clusters that complain that the cluster “stalls” during the backup. As I wrote about in a previous blog post, often these stalls are really just Flow Control. But why would a backup cause Flow control?
Most backups I know of (even Percona XtraBackup) take a FLUSH TABLES WITH READ LOCK (FTWRL) at some point in the backup process. This can be disabled in XtraBackup (in certain circumstances), but it is enabled by default.
If you go to your active cluster right now an execute a FTWRL (don’t actually do this in production!), you’ll see this message in your error log on that node:
|
1 |
131004 13:18:00 [Note] WSREP: Provider paused at 49548c16-2abd-11e3-a720-b26997b9b8c6:35442 |
This indicates that Galera is unable to apply writes on the local node. This by itself is does not indicate Flow control, but flow control is likely if it lasts too long. Once the lock is released, we get a message that Galera is at work again:
|
1 |
131004 13:18:09 [Note] WSREP: Provider resumed. |
During this interval (9 seconds in this case), the wsrep_local_recv_queue was backing up on this node and could cause Flow control, depending on how the fc_limit and other settings are configured. I talk about how to tune Flow control in my other post, but what we really want is for flow control to not be in effect for the duration of our backup for this one specific node.
Astute Galera users know that a Donor during SST does not trigger flow control, even though it may get far behind the rest of the cluster. What if we could manually make a node act like a donor for the purposes of a backup? Turns out we now can.
Starting with PXC 5.5.33, a new variable has been added called ‘wsrep_desync’. This allows us to manually toggle a node into and out of the ‘Donor/Desynced’ state. The Donor/Desynced state is nothing magical. It really just turns off flow control, and allows the node to fall arbitrarily far behind the rest of the cluster, but only when it is forced to. This could be caused by a FTWRL, but also anything that may cause the node to lag like heavy disk utilization.
So, I can set Desync like this:
|
1 |
node3 mysql> set global wsrep_desync=ON; |
When I do that, I can see the node drop into the ‘Donor/Desynced’ state:
|
1 |
node3 mysql> show global status like 'wsrep%';<br>+----------------------------+-------------------------------------------------------+<br>| Variable_name | Value |<br>+----------------------------+-------------------------------------------------------+<br>...<br>| wsrep_local_recv_queue | 0 |<br>| wsrep_local_recv_queue_avg | 0.000000 |<br>| wsrep_flow_control_paused | 0.000000 |<br>| wsrep_flow_control_sent | 0 |<br>| wsrep_flow_control_recv | 0 |<br>...<br>| wsrep_local_state | 2 |<br>| wsrep_local_state_comment | Donor/Desynced |<br>...<br>| wsrep_cluster_size | 3 |<br>| wsrep_cluster_state_uuid | 49548c16-2abd-11e3-a720-b26997b9b8c6 |<br>| wsrep_cluster_status | Primary |<br>| wsrep_connected | ON |<br>| wsrep_local_index | 2 |<br>| wsrep_provider_name | Galera |<br>| wsrep_provider_vendor | Codership Oy <info@codership.com> |<br>| wsrep_provider_version | 2.7(r157) |<br>| wsrep_ready | ON |<br>+----------------------------+-------------------------------------------------------+<br>40 rows in set (0.01 sec) |
However, notice that my wsrep_local_recv_queue is still empty, and flow control is not apparently in effect. myq_status agrees with this:
|
1 |
[root@node3 ~]# myq_status wsrep<br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:36:07 P 60 3 Dono T/T 0 0 0 15k 0 69M 0.0 0 0 0 61 0 0 1<br>13:36:08 P 60 3 Dono T/T 0 0 0 14 0 20K 0.0 0 0 0 65 0 0 1<br>13:36:09 P 60 3 Dono T/T 0 0 0 15 0 21K 0.0 0 0 0 71 0 0 1<br>13:36:10 P 60 3 Dono T/T 0 0 0 12 0 16K 0.0 0 0 0 75 0 0 0<br>13:36:11 P 60 3 Dono T/T 0 0 0 18 0 24K 0.0 0 0 0 81 0 0 0<br>13:36:12 P 60 3 Dono T/T 0 0 0 12 0 16K 0.0 0 0 0 84 0 0 1<br>13:36:13 P 60 3 Dono T/T 0 0 0 9 0 11K 0.0 0 0 0 88 0 0 1<br>13:36:14 P 60 3 Dono T/T 0 0 0 9 0 12K 0.0 0 0 0 91 0 0 0<br>13:36:16 P 60 3 Dono T/T 0 0 0 12 0 17K 0.0 0 0 0 95 0 0 1<br>13:36:17 P 60 3 Dono T/T 0 0 0 9 0 12K 0.0 0 0 0 100 0 0 1<br>13:36:18 P 60 3 Dono T/T 0 0 0 9 0 12K 0.0 0 0 0 101 0 0 1<br>13:36:19 P 60 3 Dono T/T 0 0 0 10 0 14K 0.0 0 0 0 106 0 0 1 |
Moving to Donor/Desynced state does not force the node to fall behind, it just allows it without triggering flow control. Now, let’s take a FTWRL on node3 and observe:
|
1 |
node3 mysql> flush tables with read lock;<br>Query OK, 0 rows affected (0.02 sec)<br><br>[root@node3 ~]# myq_status wsrep<br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:37:32 P 60 3 Dono T/T 0 0 0 15k 0 70M 0.0 0 0 0 111 0 0 1<br>13:37:33 P 60 3 Dono T/T 0 0 0 16 0 22K 0.0 0 0 0 119 0 0 0<br>13:37:34 P 60 3 Dono T/T 0 0 0 12 0 16K 0.0 0 0 0 124 0 0 0<br>13:37:35 P 60 3 Dono T/T 0 0 0 11 0 13K 0.0 0 0 0 127 0 0 1<br>13:37:36 P 60 3 Dono T/T 0 0 0 9 0 12K 0.0 0 0 0 131 0 0 1<br>13:37:37 P 60 3 Dono T/T 0 0 0 15 0 20K 0.0 0 0 0 134 0 0 1<br>13:37:38 P 60 3 Dono T/T 0 0 0 5 0 7.3K 0.0 0 0 0 135 0 0 0<br>13:37:39 P 60 3 Dono T/T 0 0 0 9 0 13K 0.0 0 0 0 136 0 0 1<br>13:37:40 P 60 3 Dono T/T 0 0 0 12 0 16K 0.0 0 0 0 141 0 0 0<br>13:37:41 P 60 3 Dono T/T 0 0 0 8 0 10K 0.0 0 0 0 144 0 0 1<br>13:37:42 P 60 3 Dono T/T 0 9 0 4 0 4.8K 0.0 0 0 0 144 0 0 0<br>13:37:44 P 60 3 Dono T/T 0 18 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:45 P 60 3 Dono T/T 0 28 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:46 P 60 3 Dono T/T 0 45 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:47 P 60 3 Dono T/T 0 52 0 0 0 0 0.0 0 0 0 144 0 0 0<br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:37:48 P 60 3 Dono T/T 0 59 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:49 P 60 3 Dono T/T 0 66 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:50 P 60 3 Dono T/T 0 78 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:51 P 60 3 Dono T/T 0 96 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:52 P 60 3 Dono T/T 0 105 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:53 P 60 3 Dono T/T 0 115 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:54 P 60 3 Dono T/T 0 122 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:55 P 60 3 Dono T/T 0 133 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:56 P 60 3 Dono T/T 0 154 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:57 P 60 3 Dono T/T 0 165 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:58 P 60 3 Dono T/T 0 177 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:37:59 P 60 3 Dono T/T 0 183 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:38:00 P 60 3 Dono T/T 0 197 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:38:01 P 60 3 Dono T/T 0 206 0 0 0 0 0.0 0 0 0 144 0 0 0<br>13:38:03 P 60 3 Dono T/T 0 222 0 0 0 0 0.0 0 0 0 144 0 0 0 |
My FC settings on this node are the defaults:
|
1 |
node3 mysql> show global variables like 'wsrep_provider_options'G<br>*************************** 1. row ***************************<br>Variable_name: wsrep_provider_options<br> Value: ... gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; ... |
and yet flow control has not kicked in in the cluster.
So if I am taking my backup here, I know flow control should not kick in because of this node. FTWRL may not be the only reason replication may lag on this node, maybe just resource utilization taking the backup could also allow the queue to get high enough to cause FC.
Either way, once I’m done, I release the lock and I can immediately see the queue start to drop:
|
1 |
13:49:51 P 60 3 Dono T/T 0 30k 0 0 0 0 0.0 0 0 0 50 0 0 0<br>13:49:52 P 60 3 Dono T/T 0 30k 0 0 0 0 0.0 0 0 0 50 0 0 0<br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:49:53 P 60 3 Dono T/T 0 30k 0 0 0 0 0.0 0 0 0 50 0 0 0<br>13:49:54 P 60 3 Dono T/T 0 30k 0 0 0 0 0.0 0 0 0 50 0 0 0<br>13:49:55 P 60 3 Dono T/T 0 28k 0 2k 0 2.7M 0.0 0 0 0 394 84 2 3<br>13:49:57 P 60 3 Dono T/T 0 25k 0 3k 0 3.8M 0.0 0 0 0 493 94 2 4<br>13:49:58 P 60 3 Dono T/T 0 23k 0 3k 0 3.5M 0.0 0 0 0 529 94 3 3<br>13:49:59 P 60 3 Dono T/T 0 21k 0 2k 0 3.3M 0.0 0 0 0 544 93 4 3<br>13:50:01 P 60 3 Dono T/T 0 18k 0 3k 0 3.6M 0.0 0 0 0 569 94 2 4<br>13:50:02 P 60 3 Dono T/T 0 16k 0 2k 0 2.9M 0.0 0 0 0 583 90 5 3<br>13:50:03 P 60 3 Dono T/T 0 14k 0 2k 0 3.2M 0.0 0 0 0 621 87 3 3 |
But, what about wsrep_desync? Should I turn it off immediately, or wait for the queue to drop?
|
1 |
node3 mysql> unlock tables; set global wsrep_desync=OFF;<br>Query OK, 0 rows affected (0.02 sec)<br><br>Query OK, 0 rows affected, 1 warning (0.01 sec)<br><br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:50:55 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:50:56 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:50:57 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:50:58 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:50:59 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:51:00 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:51:01 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:51:02 P 60 3 Dono T/T 0 15k 0 0 0 0 0.0 0 0 0 647 0 0 0<br>13:51:03 P 60 3 Dono T/T 0 14k 0 997 0 1.3M 0.0 0 0 0 650 94 6 3<br>13:51:05 P 60 3 Dono T/T 0 12k 0 3k 0 3.7M 0.0 0 0 0 673 96 2 4<br>13:51:06 P 60 3 Dono T/T 0 9k 0 2k 0 3.2M 0.0 0 0 0 695 100 3 4<br>13:51:07 P 60 3 Dono T/T 0 7k 0 3k 0 3.4M 0.0 0 0 0 712 97 3 4<br>13:51:08 P 60 3 Dono T/T 0 4k 0 3k 0 3.5M 0.0 0 0 0 724 93 2 4<br>13:51:10 P 60 3 Dono T/T 0 2k 0 2k 0 3.0M 0.0 0 0 0 720 100 5 4<br>13:51:11 P 60 3 Join T/T 0 56 0 2k 0 2.9M 0.0 0 0 0 715 52 6 2<br>mycluster / node3 / Galera 2.7(r157)<br>Wsrep Cluster Node Queue Ops Bytes Flow Conflct PApply Commit<br> time P cnf # cmt sta Up Dn Up Dn Up Dn pau snt lcf bfa dst oooe oool wind<br>13:51:12 P 60 3 Sync T/T 0 0 0 125 0 169K 0.0 0 0 0 133 0 0 1<br>13:51:13 P 60 3 Sync T/T 0 0 0 61 0 84K 0.0 0 0 0 124 0 0 1<br>13:51:14 P 60 3 Sync T/T 0 0 0 58 0 82K 0.0 0 0 0 132 0 0 1<br>13:51:15 P 60 3 Sync T/T 0 0 0 61 0 82K 0.0 0 0 0 149 0 0 1 |
No! Turning off wsrep_desync keeps the node in Donor/Desynced state until it drops back down below the FC limit. This you means you can turn it off right away and Galera will do the right thing by letting the node catch up first before moving it back to ‘Sync’ and allowing FC to be active again.
EDIT: Actually, turning off wsrep_desync will move the node to the JOINED state, note you can see that happen at time 13:51:11 above, though in reality it happened as soon as we toggled wsrep_desync and the event had to wait in the queue. In this state the node will send flow control messages in a limited fashion to help it catch up faster. If you want to let the node catch up naturally without causing flow control, then leave wsrep_desync ON until the wsrep_local_recv_queue is back down to 0.
This is all well and good, but how can your HA solution deal with a node in this state? Well, it should do the same thing you do when a node is a regular Donor, take it out of rotation! Every SST method currently has some FTWRL, and a node in a Donor/Desync state may (or may not) be far behind, so it’s safest to ensure these nodes are taking out of rotation. Note that the clustercheck script that ships with PXC server gives you an option to report the node as ‘down’ when it is in a Donor state. This should allow you to easily integrate this feature with HAproxy or similar HA solutions for Percona XtraDB Cluster.
In summary, this should make it easy write your backup scripts: just turn on wsrep_desync at the start and turn it off as soon as you are done and Galera will handle the rest. Happy backups!
Resources
RELATED POSTS
