Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Master the Read/Write Split Router. This collection of guides details how to configure query load balancing, automatic failover, transaction replay, and causal consistency settings.
Handle transient failures gracefully. Learn to configure the delayed_retry parameter to pause and retry queries when backend servers are temporarily unavailable.
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.
Configure delayed retries for failed queries by configuring several parameters for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
delayed_retry
• When this parameter is enabled, failed queries will not immediately return an error to the client. Instead, the router will retry the query if a different server becomes available before the timeout is reached. • This parameter is disabled by default.
delayed_retry_timeout
• The maximum amount of time to wait until returning an error if a query fails. • The value can be followed by any of the following units: h, m, s, and ms, for specifying durations in hours, minutes, seconds, and milliseconds. • The default value is 10 seconds.
[split-router]
type = service
router = readwritesplit
...
delayed_retry = true
delayed_retry_timeout = 30s$ sudo systemctl restart maxscaleOptimize applications for read-write splitting. This guide outlines best practices for transaction management and connection handling to ensure compatibility with MaxScale.
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
Additional information is available
This page is: Copyright © 2025 MariaDB. All rights reserved.
Improve read reliability. Learn to configure retry_failed_reads to automatically attempt failed SELECT queries on alternative replica servers.
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.
Configure retries for failed reads by configuring the retry_failed_reads parameter for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
Get an overview of the readwritesplit router. Learn how it splits traffic for MariaDB replication and Galera clusters to enhance scalability and performance.
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.
The Read/Write Split Router (readwritesplit) supports:
deployments.
deployments.
and deployments.
The allows you to:
Perform query-based load balancing.
Route client connections to multiple servers simultaneously.
Route write queries to primary and read queries to replicas.
Automatically reconnect clients to the new primary after failover or switchover.
This page is: Copyright © 2025 MariaDB. All rights reserved.
Automate recovery after primary failure. This guide explains how to use the master_reconnection parameter to seamlessly move client connections to a new primary server.
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.
Configure automatic primary server re-connection by configuring several parameters for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
Mask failovers from applications. Configure transaction_replay to automatically re-execute interrupted transactions on a new primary server without returning errors.
The 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.
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.
Automatically replay transactions on the new primary after failover or switchover.
Automatically retry failed queries.
Enforce causal reads to avoid reading stale data caused by slave lag.
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.
Configure transaction replay by configuring several parameters for the Read/Write Split Router in maxscale.cnf.
transaction_replay
• When this parameter is enabled, transactions will be replayed if they are interrupted. It also implicitly enables the delayed_retry and master_reconnection parameters. • When this parameter is disabled, interrupted transactions will cause the client connection to be closed. • This parameter is disabled by default.
transaction_replay_max_size
• The maximum size of the transaction cache for each client connection. The unit is bytes, but EIC binary prefixes (Ki, Mi, Gi, and Ti) and SI prefixes (k, M, G, and T) can also be specified. • The default value is 1 MiB.
transaction_replay_attempts
• The maximum number of attempts to make when replaying a transaction. • The default value is 5.
transaction_replay_retry_on_deadlock
• When this parameter is enabled, transactions will be replayed if a deadlock occurs. • When this parameter is disabled, the client will receive an error if a deadlock occurs. • This parameter is disabled by default.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
[split-router]
type = service
router = readwritesplit
...
retry_failed_reads = true$ sudo systemctl restart maxscalemaster_reconnection
• When this parameter is enabled, if the primary server fails and if master_failure_mode is not set to fail_instantly, then existing client connections will be automatically reconnected to the new primary server. • This parameter is disabled by default.
master_failure_mode
• This parameter defines how client connections are handled when the primary server fails. • This parameter must be set to either fail_on_write or error_on_write to allow automatic primary server reconnection. • When this parameter is set to fail_on_write, the client connection is closed if a write query is received when no primary is available. • When this parameter is set to error_on_write, if no primary server is available and a write query is received, an error is returned stating that the connection is in read-only mode.
[split-router]
type = service
router = readwritesplit
...
master_reconnection = true
master_failure_mode = fail_on_write$ sudo systemctl restart maxscaleCustomize load balancing algorithms. Explore options for the slave_selection_criteria parameter, including adaptive routing and least-global-connections strategies.
The Read/Write Split Router (readwritesplit) 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.
ADAPTIVE_ROUTING
Selects using average response times
LEAST_BEHIND_MASTER
Selects based on replication lag
LEAST_CURRENT_OPERATIONS
The uses adaptive routing when the slave_selection_criteria parameter is set to ADAPTIVE_ROUTING.
In this mode, the router measures average server response times. When the router routes queries, it compares the response times of the different replica servers. It favors the faster servers for most queries, while still guaranteeing some traffic on the slowest servers.
The uses the replica server that is least behind the primary server when the slave_selection_criteria parameter is set to LEAST_BEHIND_MASTER. This mode is only compatible with
In this mode, the router measures replica lag using the Seconds_Behind_Master column from The replica server that has the lowest value is considered to be the least behind the primary server.
Set the replica selection criteria by configuring the slave_selection_criteria parameter for the Read/Write Split Router in maxscale.cnf:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
[split-router]
type = service
router = readwritesplit
...
transaction_replay = true
transaction_replay_max_size = 10Mi
transaction_replay_attempts = 10
transaction_replay_retry_on_deadlock = true$ sudo systemctl restart maxscaleSelects based on number of active operations (the default)
LEAST_GLOBAL_CONNECTIONS
Selects based on number of connections from MariaDB MaxScale
LEAST_ROUTER_CONNECTIONS
Selects based on number of connections from the service
[split-router]
type = service
router = readwritesplit
...
slave_selection_criteria = LEAST_GLOBAL_CONNECTIONS$ sudo systemctl restart maxscalePrevent stale reads in replication clusters. Configure the causal_reads setting to ensure clients always see their own updates by waiting for replicas to catch up.
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.
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:
The client executes an statement:
The router will route this statement to the primary server.
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.
Causal reads requires configuration changes on both the back-end MariaDB Servers and on the MaxScale instance.
Perform the following procedure on all MariaDB Servers used by MaxScale:
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
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:
Restart the server.
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:
Restart the MaxScale instance.
Set the causal_reads and causal_reads_timeout parameters for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
Preserve session context across connections. Learn how MaxScale's session command history replays SET statements on new replica connections to maintain state consistency.
The Read/Write Split Router (readwritesplit) 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.
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.
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.
Set the maximum size of the session command history by configuring some parameters for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
Disable the session command history by configuring the disable_sescmd_history parameter for the Read/Write Split Router in maxscale.cnf.
For example:
Restart the MaxScale instance.
This page is: Copyright © 2025 MariaDB. All rights reserved.
none
• Causal reads are disabled. • This is the default value.
local
• Writes are locally visible. • Writes are guaranteed to be visible only to the connection that does it. Unrelated modifications done by other connections are not visible. • This mode improves read scalability at the cost of latency and reduces the overall load placed on the primary server without breaking causality guarantees.
global
• Writes are globally visible. • If one connection writes a value, all connections to the same service will see it. • In general this mode is slower than the local mode due to the extra synchronization it has to do. This guarantees global happens-before ordering of reads when all transactions are inside a single GTID domain. • This mode gives similar benefits as the local mode in that it improves read scalability at the cost of latency.
fast
• This mode is similar to the local mode where it will only affect the connection that does the write. • Whereas the local mode waits for a replica server to catch up, this mode will only use servers that are known to have replicated the write. • This means that if no replica server has replicated the write, the primary server where the write was done will be used. • The value of causal_reads_timeout is ignored in this mode. • Currently the replication state is only updated by the MariaDB Monitor (mariadbmon) whenever the servers are monitored. This means that a smaller monitor_interval provides faster replication state updates and possibly better overall usage of servers. • This mode is the inverse of the local mode in the sense that it improves read latency at the cost of read scalability while still retaining the causality guarantees for reads.
max_sescmd_history
• The maximum number of distinct session commands that will be stored for each connection. • The default value is 50.
prune_sescmd_history
• When this parameter is enabled, the session command history for a connection is pruned when the number of distinct session commands reaches max_sescmd_history. • When this parameter is disabled, the session command history for a connection is disabled when the number of distinct session commands reaches max_sescmd_history. • This parameter is disabled by default.
INSERT INTO hq_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');SELECT * FROM hq_sales.invoices
WHERE customer_id = 1
AND invoice_date = '2020-05-10 12:35:10';[mariadb]
...
session_track_system_variables=last_gtid$ sudo systemctl restart mariadb[split-router]
type = service
router = readwritesplit
...
causal_reads = local
causal_reads_timeout = 15
The unit for the causal_reads_timeout parameter is seconds, and the default value is 10.$ sudo systemctl restart maxscale[split-router]
type = service
router = readwritesplit
...
causal_reads = ON
causal_reads_timeout = 15
The unit for the causal_reads_timeout parameter is seconds, and the default value is 10.$ sudo systemctl restart maxscale[split-router]
type = service
router = readwritesplit
...
max_sescmd_history = 1500
prune_sescmd_history = true$ sudo systemctl restart maxscale[split-router]
type = service
router = readwritesplit
...
disable_sescmd_history = true$ sudo systemctl restart maxscaleUnderstand the routing logic of the readwritesplit router. This guide explains how MaxScale identifies write statements for the primary and distributes reads to replicas.
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.
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.
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)
Queries that use temporary tables
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
The following statements are routed to all servers:
statements, including those embedded in read-only statements
statements
statements that create prepared statements
Internal client commands, such as QUIT, PING, STMT RESET, and CHANGE USER.
This page is: Copyright © 2025 MariaDB. All rights reserved.
SET SESSION autocommit=OFF;
SELECT * FROM hq_sales.invoices WHERE branch_id=1;
INSERT INTO hq_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
COMMIT;BEGIN;
SELECT * FROM hq_sales.invoices WHERE branch_id=1;
INSERT INTO hq_sales.invoices
(customer_id, invoice_date, invoice_total, payment_method)
VALUES
(1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
COMMIT;SELECT @@global.alter_algorithm;
SELECT @@my_user_var;
SHOW statements