Edit: The causal_reads
feature suggested in the original answer only works with asynchronous MariaDB replication.
Galera Replication
For Galera replication, the best way to achieve read causality is to turn on wsrep_sync_wait
. You can turn this on without modifying your application by writing SET SESSION wsrep_sync_wait=1
into a file and then using that file and connection_init_sql_file
in the listener that your application connects to.
An alternative to this would be to use the Consistent Critical Read filter to redirect reads to the node where the writes were done for a short while.
Asynchronous Replication
There's a feature in the readwritesplit router specifically for this sort of a workload. The causal_reads=local mode is what you're looking for. This mode will synchronize your reads done with your previous writes which gives you something like Read Your Writes consistency and makes MaxScale behave as if you were dealing with a single database node instead of a cluster of servers.
The local
mode prefers read throughput at the cost of latency but you can change that to prefer latency at the cost of read throughput by configuring it with causal_reads=fast
. In this mode, instead of synchronizing the read with the write, the read is directed to an up-to-date server which has replicated those writes. Although usually in this mode reads done shortly after a write are simply routed to the original server where the write was done.
The causal_reads=global
(favors read throughput) and causal_reads=fast_global
(favors latency) modes extend this idea from one session to one MaxScale instance. In these modes, instead of looking at the writes of one session, all writes done through a single readwritesplit are are visible to reads from other sessions done through the same readwritesplit instance. This mode gives the same guarantees that the local
mode gives but at the cost of more synchronization for potentially unrelated reads and writes.
There's also the causal_reads=universal
and causal_reads=fast_universal
modes that extend the idea from one MaxScale instance to any number of MaxScale instances. These modes also have the feature of guaranteeing visibility of writes done outside of any MaxScale instance (e.g. data load that is done locally is visible to reads done through MaxScale). As usual, this sort of "universal causality" comes at a high latency and throughput cost and should usually be a last resort.