mariadbcommitgaleramaxscale

How to wait for replication at mariadb transaction commit?

I have a MariaDB cluster made with Galera and my clients use MaxScale with RW router.
I'm having an issue because the application has something this sequential steps:

Due to the MaxScale router these commands are executed in 2 different servers:

My issue is that the select often doesn't get any row because the replication is not yet completed between servers.

I'm wondering if is there any way to let the commit command waits for the successful replication before returning the operation. Unfortunately the sw refactoring is not an option, this application well worked with a single server environment. Moreover I would like to avoid to remove or change the maxscale router (it was introduced to minimize the deadlocks due to optimistic lock for galera).

Solution

  • You have a few options when it comes to managing this kind of an application.

    Option 1: Add wsrep_sync_wait=1 as one of the initialization queries

    The wsrep_sync_wait variable can be set automatically by MaxScale on session startup if you use the connection_init_sql_file mechanism. This is probably the best option as it relies on Galera's own mechanisms for synchronization.

    To enable it, first save the SQL statement into a file that's readable by MaxScale.

    echo "SET SESSION wsrep_sync_wait=1" |sudo tee /var/lib/maxscale/init_queries.sql
sudo chown maxscale:maxscale /var/lib/maxscale/init_queries.sql

    Then add connection_init_sql_file=/var/lib/maxscale/init_queries.sql to the listener sections in MaxScale.

    Option 2: Use causal_reads with readwritesplit

    The causal reads feature uses the GTID position to synchrnonize a replicating server with a transaction committed elsewhere. However, only the causal_reads=fast would work with Galera as it doesn't support the MASTER_GTID_WAIT() function that's used for synchronization. Also note that this only works on versions of recent MaxScale versions due to MXS-5438.

    Option 3: Use namedserverfilter to redirect the read queries

    The namedserverfilter module can be used to redirect reads to be treated as writes. The downside of this approach is that you have to know which queries exhibit the problem and write a regular expression to match those. Here's an example configuration that would treat SELECT statements on the my_table table as writes.

    [NamedServerFilter]
type=filter
module=namedserverfilter
match01=SELECT.*FROM.*my_table
target01=->master

    You'd take it into use by adding filters=NamedServerFilter in the service section where you define the readwritesplit router.

    Option 3.5: Use regexfilter to inject wsrep_sync_wait

    Similarly to how namedserverfilter can redirect the reads, you could also modify the SQL itself with the regexfilter to inject a SET STATEMENT wsrep_sync_wait=1 FOR prefix to all SELECT queries that you'd want to be done in a synchronous fashion. However this has the same downsides as the namedserverfilter approach in that you must know the SQL you want to make synchronous and that can be a big maintenance burden if the number of SQL statements is large.

    Option 4: Use the ccrfilter

    The CCRFilter module will mark all queries as writes for a fixed duration after a write has happened.

    Here's an example configuration:

    [CCRFilter]
type=filter
module=ccrfilter
time=5s

    This configuration would route all reads as writes for 5 seconds after a write. In the case of Galera, using time=1s is most likely enough unless there's significant lag between the nodes.

    Option 5: Use the readconnroute router

    This is usually the last option as you lose out on the failure tolerance and load balancing benefits of using readwritesplit. Configuring readconnroute with router_options=master will make it so that all SQL statements are sent to the node labeled as Master in MaxScale. This will still avoid deadlocks in Galera but the throughput of the cluster will suffer due to forcing all traffic onto one node.