Using MaxScale to segregate load in a MySQL server cluster

The server weighting feature of MaxScale may initially be seen as a way to influence load balancing for unequal servers, but it can also be useful for other reasons. Here we look at how we can use it to segregate different workloads within a cluster of MariaDB or MySQL servers.

Concept

The concept is based around a scenario in which you might have a number of applications that share the same database, but that have different workload profiles on that data. For example an online order processing system, with a fulfilment application and a reporting application. Each of these has different demands it places on the database and each has different priorities within the business. It is probably more important for a business to accept orders and fulfil them than to have up-to-the-minute reporting if failures start to occur within the infrastructure.

The Implementation

There are a few things to understand about MaxScale which make the implementation here possible before delving into the configuration.

Services

An application accesses databases via MaxScale using services that are defined within the MaxScale configuration file. These services define a combination of the protocols used, the databases that support the service and the servers to which the service will route. A single instance of MaxScale can support multiple services and the same server can appear in multiple services within MaxScale.

Server Weighting

The server weighting is not performed on a fixed weight parameter for every server, instead every service can define the parameter used by the service to weight the connections. This means that a server can have many weights associated with it, and each service the includes the server can weight the distribution on different server parameters.

Example

Returning to the example in the introduction, assume we define three parameters for each server,

• processing_weight – the order processing weight
• fulfilment_weight – the fulfilment weight
• reporting_weight – the reporting weight

We also define three services within MaxScale

• processing – used by the online order processing application
• fulfilment – used by the fulfilment application
• reporting – used by the reporting application

Each of these services uses the weight associated with it from the above list.

[processing]
type=service
weightby=processing_weight
…

[fulfilment]
type=service
weightby=fulfilment_weight
…

[reporting]
type=service
weightby=reporting_weight
…

If we assume we have six servers, called db1to db6, then we can define the set of servers for each service to be all six servers. Each server can be assigned a weight to be used by each service.

[db1]
type=server
processing_weight=1000
fulfilment_weight=1
reporting_weight=1
…

[db2]
type=server
processing_weight=1000
fulfilment_weight=1
reporting_weight=1
…

[db3]
type=server
processing_weight=1000
fulfilment_weight=1
reporting_weight=1
…

[db4]
type=server
processing_weight=1
fulfilment_weight=1000
reporting_weight=1
…

[db5]
type=server
processing_weight=1
fulfilment_weight=1000
reporting_weight=1
…

[db6]
type=server
processing_weight=1
fulfilment_weight=1
reporting_weight=1000
…

Using the values above, the order processing application, connecting to the processing service would have 1000 connections to db1, db2 and db2 before any connections would be routed to db4, db5 or db6. The fulfilment application would have 1000 connections routed to db4 and db5 before any other servers are used. The reporting application would have 1000 connections to db6 before any other servers are used. So what do you gain by doing this above simply having services that just include the subset of hosts that you wish to have the particular application connect with? The answer is you have high availability.

If in our example the server db6 fails, connections from the reporting application would be shared amongst the remaining servers. In this case we have made it equally likely that these go to the ordering system servers and the fulfilment system servers, but the weight can be adjusted to make reporting favour the fulfilment database servers if that is a more desirable failure mode.

In this example I have used the term connections, but the same mechanism is available for both connection routing and routing using the Read/Write Splitter. The segregation method can also be applied to both replication clusters and Galera Clusters. In the former only read operations (or connections) can be segregated; however in a Galera Cluster both read and write operations can be segregated.