You can't load-balance a write.
Identical database nodes look like multiplied write capacity. Spread your writes across them and the cluster manufactures deadlocks. The design that works.
In this article 2 sections
- The intuitive cluster setup: identical nodes, one load balancer, everything round-robined. It looks like multiplied capacity.
- Under completely normal load it delivers deadlock errors to editors mid-publish, with no locking bug anywhere in the code. Two editors, two nodes, one hot row, and the cluster's conflict check throws one of them back.
- The counterintuitive fix: every write goes to one elected node, and only reads fan out.
- The twist that answers "but isn't distributing faster?": write volume is rarely the constraint. Read capacity is what scales, and the errors go away.
You bought a database cluster. Nobody buys several of a thing to use one of it, so the load balancer config practically writes itself: round-robin everything across every node. The belief underneath it is simple: a single query won't be faster, but many will, divided across the machines. It sounds obviously correct. It is also the most common way we see this class of cluster misused.
The bug that isn't in the code
Under ordinary load, editors intermittently hit save and get a raw database error handed back to them through the CMS: deadlock found. It is intermittent and hard to reproduce, and no amount of reading the application code will find it, because the bug is not in the code.
The workload that becomes the weapon is an ordinary one. A CMS rewrites a handful of globally-named configuration rows on nearly every action. An SEO plugin rebuilds its sitemap on every save. Login timestamps and view counters all touch the same hot rows. Round-robin scatters those competing writes across every node. Each node accepts its own write locally, and then the cluster's certification step catches the collision at commit time and throws it back at a client that did nothing wrong. Two editors, two nodes, the same row: error 1213.
A deadlock under normal load, on a cluster, with no locking pattern anywhere in the application, is a routing symptom. The workload is not wrong. The topology is.
Single-writer for safety, fan out the reads
Does funneling all the writes to one node waste the others? No. Reads balance evenly across the cluster and every write goes to the single elected writer. The round-robin instinct is right about the reads and wrong about the writes; single-writer routing keeps the half that is right and deletes the errors the other half generates. If your first instinct on a busy cluster is to scale up, the sibling lesson is that the right move is often the opposite (size down, not up).
1213
1
“A cluster is multi-primary-capable and single-writer-safe. Those are different sentences.”
If you run a read-balanced cluster, go find out where your writes actually land. If the answer is "wherever the balancer happens to send them," you are one busy afternoon away from manufacturing deadlocks out of your own success. The check costs a config read. The fix is a routing rule, not a rewrite.
Straight answers
Why am I getting deadlock errors (1213) on my Galera or PXC cluster under normal load?+
If there is no locking bug in your code, check write routing. Writes scattered across nodes collide on hot rows at certification time, and the cluster reports the collision as a deadlock to an innocent client. Route every write to one node and the whole class of error disappears.
Doesn't sending all writes to one node waste my cluster?+
Writes are almost never the bottleneck; reads are the volume. With single-writer routing, reads still balance across every node and write throughput does not change, because in effect one node was always doing all the write work.
Can a Galera cluster do multi-master writes?+
It is capable of accepting writes on every node, and it is safe when it does not have to certify competing hot-row writes from several of them at once. Multi-primary-capable, single-writer-safe. Use the capability for failover, not for load distribution.
How do I scale database writes, then?+
First prove you need to: measure write volume against the single writer's headroom, which is usually a rounding error next to reads. Genuine write scaling is sharding or workload-redesign territory, not a load-balancer setting.
What routes reads and writes differently?+
A proxy layer that speaks the database protocol can split them: reads fan out, writes pin to the elected writer, and failover re-elects a writer automatically. We run this pattern in production: ProxySQL in front of Percona XtraDB Cluster.
Seeing deadlocks you can't find in your code?
Send us nothing but the error and how your cluster is balanced. If it's the topology, we'll show you, and fix it in routing, not in your application.
Talk to an engineerYou’ve reached the end.
Put the reading to work
What are
you running?
Tell us about the workload, traffic and recovery requirements. Start with the server builder, or talk through the details with an engineer.
Written by the engineers who build and run the infrastructure it describes.Talk to an engineer