I’m seeking the recommended advice on how to best handle the following scenario.
The setup is a cluster of 6 MySQL nodes (XtraDB Cluster 5.7.29-32). If one or more of the nodes drop from the cluster and go into a not ready status (wsrep_ready) any persistent connections remain connected, returning out-of-sync data on reads for a period until the node or nodes rejoin.
Does Percona XtraDB Cluster 5.7 offer a way to either drop persistent connections on not ready (wsrep_ready) nodes? or an option to return an error on reading out-of-sync data? or use the wsrep_sync_wait (7) variable?
Any help or advice would be greatly appreciated.
Running 6 nodes is not a good idea. You can easily get into 50/50 split brain. Run either 5 nodes, or 7 nodes.
Also, 5.7 is dead, dead, dead. Please upgrade to 8.0 as soon as you can.
How does your application connect to PXC? Using a proxy, or direct connections?
wsrep_dirty_reads=OFF
When a node loses its connection to the Primary Component, it enters a non-operational state. Given that it can’t keep its data current while in this state, it rejects all queries with an ERROR: Unknown command message.
The behavior you desire is already enabled by default. Do you have this parameter enabled by accident?
I agree on the split brain and the old 5.7 version; the cluster is soon to be upgraded to 8.0 using odd hosts. I hope this will resolve this issue.
Checking the wsrep_dirty_reads variable is set to OFF for all hosts. Connectivity is direct to the hosts through a VIP with load balancing (round-robin, monitoring port (ready)).
If wsrep_dirty_reads is OFF, then you shouldn’t be able to execute any DML/DDL, per what the manual says.
In any case, it is the application’s responsibility to observe, and catch error messages, and disconnect from a host if desired.
I recommend 2 options: 1) reconfigure your load-balancer check script to monitor wsrep_primary_component and return false if not in PRIMARY state. This should cause your load balancer to stop sending connections to that node. OR 2) switching away from VIP/load-balancer to ProxySQL which has native support for PXC, and monitors every node state. If a node goes offline, ProxySQL will move frontend connections to active nodes.
1 Like