MySQL databases are just one of many interconnected system components, which call for high availability and horizontal scalability. For databases in particular, horizontal scaling is significantly more complex than with other services, often because most MySQL clients are not smart enough for clustered MySQL installations. If you do not relish the task of programming the missing client functions for database clusters yourself, you are in good hands with MaxScale, which speaks the MySQL protocol, analyzes requests from clients, and routes them to specific servers.

When scaling vertically, admins expand a single large hardware instance on which specific services run, whereas when scaling horizontally, they increase the number of instances, thus distributing the load across more shoulders.

Horizontal scaling has supplanted classic high availability (HA) approaches, because if you have a fair number of instances of a service, the failure of a single instance is only a problem for its currently open connections – and only if the client is not smart enough to reconnect automatically.

Although these two approaches are ready for production use in MySQL – MySQL in master-slave mode and a Galera cluster with multimaster functionality – these solutions are unlikely to do the job if left to their own devices.

MaxScale for Databases

MaxScale comes from MariaDB and made its debut in 2014. The MaxScale team is led by MySQL creator Monty Widenius, who promised that MySQL databases would support high availability, while putting an end to scalability issues. In terms of functionality, MaxScale is a proxy server: At one end, the service opens connections to back ends of the same database in the background; at the other end, it waits for incoming connections from clients. In this article, a client is any application (CRM, web shop, document management, etc.) that uses MySQL as a back end and therefore assumes a client role in its dealings with the database server.

Admins might be reminded of a load balancer, but MaxScale differs in one important aspect: To the client, MaxScale looks like a database, because it speaks the MySQL protocol. It is thus not only transparent to the client, as a local load balancer would be, but it can also analyze requests from clients and route them to specific servers accordingly. In doing so, it can, for example, separate write requests from read requests.

Great Demand, Low Supply

Separating traffic is a common requirement in MySQL installations with more than one node for many reasons:

• Master-slave installations have only one master designed to provide fast writes. The typically far more frequent reads are then usually handled by the many slaves.
• In Galera clusters, although it is possible to write to all back ends at the same time, it turns out not to be such a good idea in practice. Many admins prefer a split configuration, often because the specific application is not designed to write to several MySQL databases in the background.
• No matter where you look, MySQL clients typically do little more than open a database connection and send a query, which can lead to inefficient operation and failure of a MySQL cluster.
• Virtually all common implementations are restricted to specifying a single hostname for the MySQL server – in some cases because the database abstraction layer of the underlying scripting language cannot handle distributed databases. Some of these APIs originate from a time when clustered MySQL did not exist.

Most admins work around these issues by installing a load balancer, such as HAProxy [1], or an F5 Appliance [2] between the client and database. However, all of these solutions suffer from the same problem: Because they do not speak MySQL, they do not have an image of the MySQL cluster in their back ends, and a rigid pattern limits how incoming connections are directed to them.

The idea of working with the shortcomings of the MySQL client directly in your application does not promise much more success: The attempt typically degenerates into a code war that mainly leads to confusion. Although several approaches besides MySQL clusters have arisen to solve the problem (e.g., ScaleArc), all of these products suffer from more or less severe technical limitations that makes them unsuitable for production use.

Modular Architecture

In simple terms, MaxScale sees itself as particularly smart client that assumes a kind of bridge function for less smart clients. Common MySQL APIs – such as those in PHP or C – connect with MaxScale, which establishes the connection to Galera or to a master-slave MySQL in the background. However, this function is not the only outstanding MaxScale feature.

The people at MariaDB put a lot of thought into a suitable design for MaxScale, because the results are impressive: Inside, MaxScale is based on a fully modular architecture. The part that opens ports to the outside world and becomes available for clients is little more than a listener on a TCP port. The listener first passes all the incoming traffic to the MySQL client engine, which evaluates the pending query.

In the case of normal MySQL, this would be the end of the road, and the client would receive a response from the server. Not so with MaxScale: From the client module for MySQL, the evaluated information is now forwarded to the routers after applying any filters. At router level, a decision is made as to what to do with the query. A single router is responsible for a special function: The earlier example, in which incoming requests for write and read access are separated, is handled by the ReadWrite Split router. The routers are therefore the most important modules within MaxScale; in addition to ReadWrite Split, you have a filter for regular expressions and a logging router.

Once the packets leave the router, they migrate to the back-end module that works closely with the monitor plugin, which keeps track of the configured back ends and their availability; it also knows whether it is a master or slave. On the basis of this information, the MySQL back-end module finally sends the respective SQL query to one of its back ends. A cache for login credentials is also involved.