Larger customers may have thousands or tens of thousands of connections to the database. To mitigate risk to the database from many active connections and still allow larger in-memory buffers, we should have maxscale pool connections to the backend databases.
Connection pooling could be implemented by multiplexing N client connections over M connections to a backend MariaDB server. After a client connection request completes successful authentication with a backend MariaDB server, the proxy severs the link between the backend connection and client connection and parks it in a connection pool for the backend server.
The server connection pool size should be configurable or dynamic, and typically a small number. Forks of MaxScale have leveraged the persistent connections feature to implement the server connection pool. When receiving a query on a client connection, MaxScale can pick a backend connection in the pool, link it with the client connection, and forward the query to the backend MariaDB server. MaxScale understands the transaction context of a client session and therefore it knows to keep the linked backend connection until transaction commits. The link must be kept and used for forwarding the query result back to the client.
One challenge in this connection pooling implementation is knowing when to unlink and return the backend connection to the pool. A query response consists of one or more MariaDB packets. Because MaxScale keeps one-to-one link between a client connection and a backend connection, it just forwards response packets as they come. In connection pooling mode, unlinking a backend connection prematurely would cause the client to wait indefinitely for the complete set of MariaDB packets. For correct query response forwarding, consider implementing packets by following the MariaDB client server protocol for COM_QUERY_RESPONSE. That way, MaxScale will not unlink a backend connection until it has seen the complete MariaDB packets of a query response. Aside from forwarding responses, it would allow us to measure query response size for monitoring.