Hi all,
I would like to discuss a common situation in a Galera Cluster setup where the intended primary writer (Node 1) stops accepting new requests, causing the loadbalancer to forward write traffic to Node 2. This results in an accidental multi-writer situation, which is undesirable for a specific application logic.
The error log on Node 1 shows a “Too many connections” alert. When this happens, the load balancer identifies Node 1 as saturated and fails over to the Node 2.
What is the best practice to ensure that Node 1 remains the sole writer even during connection spikes?
Just off the top of my head you could:
On Node 2 (and Node 3…) enable:
read_only=ONsuper_read_only=ON (stronger; blocks even users with SUPER-like powers except internal threads)
Reason this will help: even if the Load Balancer sends writes to Node 2, they’ll fail immediately instead of silently becoming a second writer. Galera replication applier threads can still apply replicated transactions even when the node is read_only/super_read_only.
1 Like
Hi @Wayne_Leutwyler - thank you so much for your input.
Indeed, in our case, we were advised to increase the max_connections variable. This should provide the first node with enough resources to handle client spikes without triggering a (writing) failover to Node 2.
If there are no other suggestions, I will close this post with this conclusion. Thanks for the help!