MariaDB MaxScale Persistent Connections

The MaxScale persistent connection feature has been available since version 1.3.0. It aims to reduce the load on the database servers in specific scenarios. Testing indicates benefits and feedback from live use has been positive.

A typical scenario where benefits will be obtained is a PHP web server application that uses a group of MariaDB databases to store information. HTTP is well known to be a connectionless protocol, and so each request from a browser is usually handled as a single invocation of a PHP program, which makes a fresh database connection and terminates it on completion. This is likely to happen in well under a second, many times per second. There are thus many instances of the relatively expensive operation of making a new database connection.

You might think that the obvious way to reduce this load is to use PHP persistent connections. But for a variety of technical reasons to do with PHP itself and web servers such as Apache, many experienced web developers strongly prefer to avoid this. It can lead to a build up of lost connections and wasted resources. Many people feel that PHP applications work best when invoked for a brief period with minimal linkage between successive operations.

So this is where the MaxScale persistent connection feature steps in. The feature is configured for each backend database server. The number of connections that can be allowed to persist is specified in the MaxScale configuration section for the server. Typically, where servers are in a group such as a master and some slaves, it will make sense to use the same number for each server, but MaxScale does not require this. An example of a server configuration section with persistent connections enabled is:

[server1]
type=server
address=10.131.24.62
port=3306
protocol=MySQLBackend
persistpoolmax=100
persistmaxtime=3600

Without persistent connections, whenever a client disconnects from MaxScale, all the backend database server connections that were made for that client are dropped. Now, for each server that has had the persistpoolmax configuration set to a non-zero value, when a client connects, a related connection to that database server will be considered for retention in the persistent connection pool. If there are already persistpoolmax connections in the pool, then the connection will be closed, but otherwise it will be added to the pool.

Conversely, when a new backend database connection is needed for a new client connection, the persistent pool for that database will be examined to see if there is a suitable connection already available. It is suitable provided it is for the same user and has not become stale by sitting in the pool for longer than the persistmaxtime specified for the server.

The effect is that a pool of connections can be constantly recycled, being used for a client connection, then placed in the pool, then reused for another client connection request. If the pool is large enough to cater for the maximum simultaneous number of requests, then new connections will rarely be needed.

There is one further check that can affect the handling of connections in the persistent pools. MaxScale continues to monitor the connection for any network messages. Although messages can be received correctly, in this situation MaxScale does not know what to do with them, not least because there is no related client. In fact, by far the most common reason for a message is the backend database disconnecting because of timeout. If any message arrives for a connection in the persistent pool, it is assumed that the connection is no longer valid, and it is removed from the pool.

So far at least, MaxScale persistent connections have been kept very simple, for maximum processing efficiency. You may have noticed that this means that there is a limitation. MaxScale assumes that the client can use an existing connection, irrespective of the state it is in. For example, the previous user may or may not have selected a default database. Or the previous user may have set a value. This limitation is not a problem for the target group of applications. For example, PHP will set any features of the database environment whenever a new connection is made.

Further functionality will be considered in future, although a balance between benefit and processing overhead will be a factor. Given the limitation, it is important to avoid using MaxScale persistent connections for applications that are not suited to it. In appropriate cases, though, valuable gains will be achieved.