Every MariaDB primary-replica setup eventually produces the same complaint: the primary is sweating while two replicas sit nearly idle, and someone asks why the application cannot "just send reads to the replicas." The honest answer is that applications are terrible at topology. They should see one endpoint and let something smarter decide where each statement goes. For MariaDB, that something is usually MaxScale, the proxy layer from MariaDB plc, and specifically its readwritesplit router.
I have run MaxScale in front of MariaDB clusters for years. It solves real problems — read scaling, failover transparency, topology hiding — and it adds a moving part you now have to understand. This is the field guide: how readwritesplit decides where a query goes, where session state breaks your assumptions, how to survive replica lag with causal reads, and how failover coordination with the MariaDB monitor actually works.
How readwritesplit decides where a query goes
MaxScale terminates client connections and opens its own connections to the backend servers, whose roles it learns from a monitor module — mariadbmon for MariaDB replication. For each statement, readwritesplit parses enough SQL to classify it. Writes, DDL, and locking reads go to the primary. Plain autocommit SELECTs are candidates for the replicas, balanced according to the router's selection criteria. LEAST_CURRENT_OPERATIONS, which sends the query to the replica handling the fewest in-flight operations, is the sane default for most workloads.
The distinction that matters is statement routing versus connection routing. The older readconnroute router pins an entire client session to one server; readwritesplit decides per statement. With an ORM connection pool this is mostly invisible and mostly wonderful, right up until a session carries state. There is one hard rule you can rely on: once a read-write transaction begins, everything in that session pins to the primary until COMMIT or ROLLBACK. Mixing reads and writes of one transaction across servers would break read-your-writes, and readwritesplit refuses to do it to you. The deliberate exception is the explicit read-only transaction: START TRANSACTION READ ONLY tells the router no write can follow, so the whole transaction can be served by a replica.
Session state: the part that bites
SQL sessions are stateful. User variables, temporary tables, session system variables, the current database, and prepared statements all make later statements depend on earlier ones. MaxScale tracks these session commands and either replays them on replicas before routing a dependent statement there, or pins the session to the primary when safe splitting is not possible. The failure mode is quiet and session-scoped: MaxScale broadcasts replayable session commands — SET statements, USE, prepared statements — to a session's backends, so a user variable does not pin anything by itself. Temporary tables are the exception that bites: MaxScale routes a CREATE TEMPORARY TABLE and every later statement that reads it to the primary, so one temp table effectively pins that session's reads to the primary for its lifetime. And any session command that cannot be replayed, or that fails against a freshly connected replica, drops that replica connection and keeps the session on the primary. Multiply one pinned session by a pool where every checkout creates a temp table and your read scaling evaporates without a single error.
-- statements that change how a session can be routed:
SET @tenant_id := 42;
CREATE TEMPORARY TABLE tmp_recent_orders AS
SELECT id FROM orders ORDER BY id DESC LIMIT 100;
PREPARE stmt FROM 'SELECT * FROM orders WHERE tenant_id = ?';When you suspect pinning, look at the proxy, not the database. maxctrl show session <id> reveals which servers a given session is using, while maxctrl list servers gives per-server connection and load counts that tell the story quickly: if the replicas are idle while the primary carries everything, find the session command that is sticking people. In my experience it is usually a reporting tool creating temp tables, or a framework that SETs session variables on every connection checkout.
Reads after writes: lag and causal reads
Read/write splitting inherits replication's oldest lie: a write that just succeeded might not be visible on a replica yet. MariaDB replication is asynchronous by default; lag is usually sub-second, but it spikes during DDL, backups, and batch jobs. Without protection, the classic bug is the user who edits their profile, refreshes, gets routed to a lagging replica, and sees stale data come back.
readwritesplit's answer is causal reads. With causal_reads enabled, MaxScale captures the GTID of each write and, before serving the next read on a replica, waits for that replica to apply up to that position. Under the hood it is the same primitive MariaDB exposes directly:
-- what causal reads effectively does on the replica before your SELECT:
SELECT MASTER_GTID_WAIT('0-1-548921', 5);
-- checking positions by hand:
SELECT @@global.gtid_binlog_pos AS binlog_pos,
@@global.gtid_slave_pos AS applied_pos;The wait is bounded by causal_reads_timeout. When a replica cannot catch up in time, MaxScale does not serve you the stale read — it falls back and runs the query on the primary rather than hanging forever, and inside an explicit read-only transaction, where that fallback is impossible, the statement returns an error instead. Pick that number deliberately, because it is your correctness-versus-latency dial. Pair causal reads with a lag budget: the max_replication_lag setting takes replicas that fall too far behind out of rotation entirely, so a replica in trouble gets quarantined instead of quietly serving hour-old data.
Failover coordination with mariadbmon
Splitting reads is only half the job; the other half is not routing into a dead primary. The mariadbmon monitor watches the topology, detects primary failure, promotes the most current replica by GTID position when auto_failover is on, and re-points the surviving replicas. When the old primary returns, auto_rejoin can fold it back in as a replica. If you run more than one MaxScale instance — and you should, because the proxy is critical infrastructure — cooperative monitoring locks keep two proxies from promoting different nodes at the same time. That lock is your split-brain defense at the proxy layer; do not disable it.
From the application's side, the moment of failover is the dangerous one: a transaction in flight when the primary dies would normally mean an error and a retry. readwritesplit's transaction_replay feature re-executes the interrupted transaction against the new primary and returns the result as if nothing had happened, bounded by transaction_replay_max_size. It covers the common cases — small, deterministic transactions — and honestly cannot cover the rest: huge transactions, non-deterministic functions, or sessions deep in temporary-table state will still see an error and must retry. One boundary deserves emphasis: since MaxScale 23.08, transaction_replay_safe_commit is on by default, so a failure that strikes during the final COMMIT is not replayed at all — nobody can know whether that commit landed, so the client gets the error and must verify. Test this in staging with maxctrl's switchover command before production tests it for you.
An operational checklist
- Run at least two MaxScale instances behind a VIP or load balancer, with cooperative locks enabled, and monitor the proxies themselves.
- Enable causal_reads on user-facing paths and leave it off for reporting workloads that tolerate staleness.
- Alert on lag-excluded replicas. A replica dropping out of rotation at peak is a capacity event, not a footnote.
- Rehearse switchover monthly. Measure how long the application actually sees errors, not how long the monitor says failover took.
- Watch routing ratios per server. A slow drift toward the primary usually means creeping session pinning, and catching it early beats discovering it during a traffic spike.
Where MonPG fits (coming soon)
Everything in this guide is observable today with maxctrl and the status counters; what is usually missing is a place where those signals live long-term. That gap is exactly what MonPG's planned MariaDB support is being designed around — proxy-layer signals like per-server routing ratios, causal-read wait time, and lag-exclusion events, trended next to the database's own counters. To keep the record straight: MonPG ships PostgreSQL monitoring today, and the MariaDB side is coming soon, not available yet. Progress is tracked on the MariaDB monitoring (coming soon) page, and more field notes like this one live on the MonPG blog. If PostgreSQL is also in your stack, the evidence-first workflow described here exists for it already — see the PostgreSQL overview.