Read-write split routing in MaxScale

In this blog post, we’ll discuss read-write split routing in MaxScale.

The two previous posts have shown how to setup high availability (HA) with Maxscale using asynchronous replication and how we monitor replication.

Now let’s focus on the routing module performing read-write splits.

This is our current configuration:

This router module is designed to spread the read queries across multiple servers (slaves by default), and send the write queries to a single server: the master.

This module is intended to work with Asynchronous Master-Slave replication but also with Galera replication if you plan to write to a single node.

So what is routed to the Master?

• Write statements
• All statements within an open transaction, even if this transaction is read only
• Store procedure and user-defined function call.
• DDL statements
• Execution of prepared statements (EXECUTE)
• All statements using temporary tables

Example:

• • percona1: master
  • percona2 and percona3: slaves

Let’s connect to MaxScale with the MySQL’s interactive client:

Now let’s try with a READ ONLY transaction:

As we can see, MaxScale doesn’t support READ ONLY transactions. It considers them the same as any transaction. This means they are routed to the master as a WRITE.

We’ve already seen the  max_slave_replication_lag optional parameter, but there are some others:

• • • max_slave_connections: defines the maximum number of slaves a router session uses, the default is to use all the ones available
    • use_sql_variables_in: defines where queries’ reading session variables should be routed. Valid values are master and all (the latter being the default)
    • weightby: defines the name of the value used to calculate the weights of the server

Now let’s play with the weightby . So in this configuration, we will target 10% of the reads to percona2, and 90% to percona3:

We restart MaxScale, and verify the settings of the service:

The target % seems correct, let’s test it!

That doesn’t seem good! Let’s check the service again:

Five operations for both . . . this looks like a normal load balancer, 50%-50%.

So that doesn’t work as we expected. Let’s have a look at other router options:

• • • slave_selection_criteria. Controls how the router chooses the slaves and how it load balances the sessions. There are some parameter options:

      • LEAST_GLOBAL_CONNECTIONS. Slave with least connections from MaxScale, not on the server itself
      • LEAST_ROUTER_CONNECTIONS. Slave with least connections from this service
      • LEAST_BEHIND_MASTER. Slave with smallest replication lag
      • LEAST_CURRENT_OPRTATIONS. Slave with least active operations (this is the default)

    • master_accept_reads. Uses the master for reads

The are some others; please check the online manual for:

• • • max_sescmd_history
    • disable_sescmd_history

That explains the behavior we just observed. But what if we want to use the weight setting, and not spread the reads equivalently on the slaves?

I found the answer on IRC in the #maxscale freenode. Markus Makela (markusjm) explained to me that the default configuration in 1.3.0 is to use all the slaves, and load balance the actual statements. So to achieve what we want to do, we need to use these options in the service section:

Let’s test it:

Yes! It worked as expected!

max_slave_connections sets the maximum number of slaves a router session uses at any moment. The default is to use all available slaves. When we set it to 1, we get one master and one slave connection per client, and the connections are balanced according to the server weights. The new mechanism uses statements instead of connections for load balancing (see MXS-588).

Finally, this routing module also support routing hints. I’ll cover them in my next MaxScale post.

More information: https://github.com/mariadb-corporation/MaxScale/blob/develop/Documentation/Routers/ReadWriteSplit.md