During the design period of a new cluster, it is always advised to have at least 3 nodes (this is the case with PXC but it’s also the same with PRM). But why and what are the risks ?
The goal of having more than 2 nodes, in fact an odd number is recommended in that kind of clusters, is to avoid split-brain situation. This can occur when quorum (that can be simplified as “majority vote”) is not honoured. A split-brain is a state in which the nodes lose contact with one another and then both try to take control of shared resources or provide simultaneously the cluster service.
On PRM the problem is obvious, both nodes will try to run the master and slave IPS and will accept writes. But what could happen with Galera replication on PXC ?
Ok first let’s have a look with a standard PXC setup (no special galera options), 2 nodes:
two running nodes (percona1 and percona2), communication between nodes is ok
|
1 2 3 4 5 6 7 8 |
[root@percona1 ~]# clustercheck HTTP/1.1 200 OK Content-Type: Content-Type: text/plain Node is running. |
Same output on percona2
Now let’s check the status variables:
|
1 2 3 4 5 6 7 8 9 10 |
| wsrep_local_state_comment | Synced (6) | | wsrep_cert_index_size | 2 | | wsrep_cluster_conf_id | 4 | | wsrep_cluster_size | 2 | | wsrep_cluster_state_uuid | 8dccca9f-d4b8-11e1-0800-344f6b618448 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_index | 0 | | wsrep_ready | ON | |
on percona2:
|
1 2 3 4 5 6 7 8 9 10 |
| wsrep_local_state_comment | Synced (6) | | wsrep_cert_index_size | 2 | | wsrep_cluster_conf_id | 4 | | wsrep_cluster_size | 2 | | wsrep_cluster_state_uuid | 8dccca9f-d4b8-11e1-0800-344f6b618448 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_index | 1 | | wsrep_ready | ON | |
only wsrep_local_index defers as expected.
Now let’s stop the communication between both nodes (using firewall rules):
This rule simulates a network outage that makes the connections between the two nodes impossible (switch/router failure)
|
1 2 3 4 5 6 7 8 9 10 |
[root@percona1 ~]# clustercheck HTTP/1.1 503 Service Unavailable Content-Type: Content-Type: text/plain Node is *down*. |
We can see that the node appears down, but we can still run some statements on it:
on node1:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
| wsrep_local_state_comment | Initialized (0) | | wsrep_cert_index_size | 2 | | wsrep_cluster_conf_id | 18446744073709551615 | | <strong>wsrep_cluster_size</strong> | <strong>1</strong> | | wsrep_cluster_state_uuid | 8dccca9f-d4b8-11e1-0800-344f6b618448 | | wsrep_cluster_status | non-Primary | | wsrep_connected | ON | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy info@codership.com | | wsrep_provider_version | 2.1(r113) | | <strong>wsrep_ready</strong> | <strong>OFF</strong> | |
on node2:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
| wsrep_local_state_comment | Initialized (0) | | wsrep_cert_index_size | 2 | | wsrep_cluster_conf_id | 18446744073709551615 | | wsrep_cluster_size | <strong>1</strong> | | wsrep_cluster_state_uuid | 8dccca9f-d4b8-11e1-0800-344f6b618448 | | wsrep_cluster_status | non-Primary | | wsrep_connected | ON | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy info@codership.com | | wsrep_provider_version | 2.1(r113) | | <strong>wsrep_ready</strong> | <strong>OFF</strong> | |
And if you test to use the mysql server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 868 Server version: 5.5.24 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. percona1 mysql> use test ERROR 1047 (08S01): Unknown command |
If you try to insert data just while the communication problem occurs, here is what you will have: