The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a server fails, then the router may need to retry failed queries on a different server. The retry may need to be delayed in some cases, such as when automatic failover is in progress.

Configuring Delayed Retries for Failed Queries

1. Configure delayed retries for failed queries by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

MaxScale's Read/Write Split Router (readwritesplit) performs query-based load balancing. For each client connected to MaxScale, it opens up connections to multiple back-end database servers. When the client sends a write query to MaxScale, it routes the query to the connection opened with the primary server. When the client sends a read query to MaxScale, it routes the query to a connection opened with one of the replicas.

This page contains topics that need to be considered when designing applications that use the Read/Write Split Router.

• How does the Read/Write Split Router route queries?

• How does the

• How does the

• How does the

• How does the

Additional information is available

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a read-only query fails, then the router can retry the query on a different server.

Configuring Retries for Failed Reads

1. Configure retries for failed reads by configuring the retry_failed_reads parameter for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The load balances read-only queries between one or more replica servers. It selects a replica server to execute a query using criteria configured by the slave_selection_criteria parameter.

The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If a server fails, then the router may need to replay in-progress transactions on a different server.

Session Command History

The Read/Write Split Router (readwritesplit) maintains connection state on replica servers by keeping a session command history. If the router has to create a new connection, then it replays these session commands from the previous connection on the new connection.

Minimizing Memory Usage of Session Command History

The session command history can require a lot of memory if connections are long-lived. In these cases, there are two options to limit memory usage:

• Configure a maximum size for the session command history

• Disable the session command history. This option is not recommended, because you would lose out on the benefits of the session command history.

Configuring Transaction Replay

1. Configure transaction replay by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The Read/Write Split Router (readwritesplit) routes write queries to the primary server and load balances read-only queries between one or more replica servers. If the primary server fails, then the router can automatically reconnect existing client connections to the new primary server.

Configuring Automatic Primary Server Re-connection

1. Configure automatic primary server re-connection by configuring several parameters for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

MaxScale's performs query-based load balancing. For each client connected to MaxScale, it opens up connections to multiple back-end database servers. When the client sends a write query to MaxScale, it routes the query to the connection opened with the primary server. When the client sends a read query to MaxScale, it routes the query to a connection opened with one of the replicas.

What Does the Read/Write Split Router Support?

The supports:

The Read/Write Split Router (readwritesplit) load balances read-only queries between one or more replica servers. If the replica servers are using asynchronous MariaDB replication, then the data on the replica servers can sometimes lag behind the primary server. When this occurs, read-only queries that are executed on the replica servers can return stale results if they are not executed in a causally consistent manner. Causal consistency is the act of ensuring that interdependent operations maintain consistency by performing them in the same order on all servers.

To prevent this, the Read/Write Split Router can be configured to enable "causal reads", which ensures causal consistency for read-only queries. When causal reads is enabled, the Read/Write Split Router ensures that load balanced read-only queries are only executed on the replica server after all write statements previously executed on the primary server are fully replicated and applied on that specific replica server.

Multiple MaxScale Nodes

Starting with MaxScale 22.08, the Read/Write Split Router's causal reads functionality can be used with multiple MaxScale nodes.

Example of a Causal Read Let's say that a client does the following:

1. The client executes an statement:

The router will route this statement to the primary server.

1. The client executes a statement that reads the inserted row:

The router will route this statement to a replica server.

In the above example, the replica server may not replicate and apply the statement immediately. If the statement is executed before this happens, then the results of the query will not be causally consistent.

However, if causal reads is enabled, then the Read/Write Split Router will only execute the statement after the statement has been fully replicated and applied on the replica server.

Enabling Causal Reads

Causal reads requires configuration changes on both the back-end MariaDB Servers and on the MaxScale instance.

Enabling Causal Reads on MariaDB Server

Perform the following procedure on all MariaDB Servers used by MaxScale:

1. Choose a configuration file in which to configure your system variables and options. It is not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

• On RHEL, CentOS, Rocky Linux, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

• On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

1. Set the system variable to last_gtid, so that the server will track session-level changes to the value of the system variable.

It needs to be set in the configuration file in a group that will be read by , such as [mariadb] or [server].

For example:

1. Restart the server.

Enabling Causal Reads on MaxScale 2.5

1. Set the causal_reads and causal_reads_timeout parameters for the Read/Write Split Router in maxscale.cnf. The causal_reads parameter can be set to the following values:

For example:

1. Restart the MaxScale instance.

Enabling Causal Reads on MaxScale 2.4 and Before

1. Set the causal_reads and causal_reads_timeout parameters for the Read/Write Split Router in maxscale.cnf.

For example:

1. Restart the MaxScale instance.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The load balances read-only queries between one or more replica servers. If a replica server fails, then the router may need to create new connections to a different replica server for any existing client connections. The router takes certain steps to ensure that those new replica server connections have the same state as the old replica server connections.

Session Command History

The maintains connection state on replica servers by keeping a session command history. If the router has to create a new connection, then it replays these session commands from the previous connection on the new connection.

The Read/Write Split Router (readwritesplit) uses well-defined rules to determine whether a statement can be routed to a replica server, or whether it needs to be routed to the primary server. Application designers must understand these rules to ensure that the router can properly load balance queries.

Statements Routed to the Primary Server

The following statements are routed to the primary server:

• Queries that write to the database. For example, this includes, but is not limited to, the following statements:
• Queries that modify the database (DDL) For example, this includes, but is not limited to, the following statements:

• Queries within open transactions If the application uses explicit transactions, then all queries within the transaction will be routed to the primary server. Explicit transactions are used in the following cases:

  • When is set to OFF.

  • When is executed.

For example, all queries will be routed to the primary server in this case:

And all queries will also be routed to the primary server in this case:

• Queries using stored procedures

• Queries using stored functions

• Queries using user-defined functions (UDF)

Statements Routed to a Replica Server

The following statements are routed to a replica server:

• Queries that are read-only For example, this includes, but is not limited to, the following statements:

• Queries that read system or user-defined variables For example, this includes, but is not limited to, the following statements:

For example, the following queries would be routed to a replica:

• Queries using built-in functions

Statements Routed to All Servers

The following statements are routed to all servers:

• statements, including those embedded in read-only statements

• statements

• statements that create prepared statements

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}