MySQL X DevAPI Connection Pool with Connector/Python

If you have an application that need to use multiple connections to the MySQL database for short periods of times, it can be a good to use a connection pool to avoid creating a new connection and going through the whole authentication process every time a connection is needed. For the Python Database API (PEP249), MySQL Connector/Python has had support for connection pools for a long time. With the release of MySQL 8.0.13, the new X DevAPI also has support for connection pools.

This blog will first cover the background of the X DevAPI connection pool feature in MySQL Connector/Python. Then provide an example.

Background

You create a connection pool using the mysqlx.get_client() function. You may wonder why you are creating a client and not a pool? As will be shown later, there is a little more to this feature than just a connection pool. So, it makes sense to use a more generic term.

The get_client() function takes two arguments: The connection options and the client options. The connection options are the usual arguments defining which MySQL instance to connect to, authentication related options, how to connect, etc. The client options are the interesting ones in the discussion of a connection pool.

The client options is a dictionary or a JSON document written as a string. Currently, the only supported client options are the ones defining the connection pool. These are specified under the pooling field (and example will be provided shortly). This leaves room for the possibility to expand get_client() later with other features than a connection pool.

There are currently four connection pool options:

• enabled: Whether the connection pool is enabled. The default is True.
• max_size: The maximum number of connections that can be in the pool. The default is 25.
• max_idle_time: How long time in milliseconds a connection can be idle before it is closed. The default is 0 which means “infinite” (in practice 2147483000 milliseconds).
• queue_timeout: The maximum amount of time in milliseconds that an attempt to get a connection from the pool will block. If no connections have become available before the timeout, a mysqlx.errors.PoolError exception is raised. The default is 0 which means “infinite” (in practice 2147483000 milliseconds).

What happens if you disable the connection pool? In that case the client that is returned simply work as a template for connections and you can keep creating connections until MySQL Server runs out of connections. In that case, the session you end up with is a regular connection, so it when you close it, it will disconnect to MySQL.

Back to the case where the connection pool is enabled. Once you have the client object, you can start using the pool. You retrieve a connection from the pool with the get_session() method. No arguments are used. After this you can use the session just as a regular standalone connection. The only difference is that when you close the session, it is returned to the pool rather than disconnected.

Enough background. Let's see an example.

Example

The following example creates a connection pool with at most two connections. Then two sessions are fetched from the pool and their connection IDs are printed. A third session will be requested before one of the original sessions is returned to the pool. Finally, a session is reused and its connection ID is printed.

import mysqlx
from datetime import datetime

cnxid_sql = "SELECT CONNECTION_ID() AS ConnectionID"
fmt_id = "Connection {0} ID ..........................: {1}"

connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
};

client_options = {
    "pooling": {
        "enabled": True,
        "max_size": 2,
        "max_idle_time": 60000,
        "queue_timeout": 3000,
    }
}

# Create the connection pool
pool = mysqlx.get_client(connect_args, client_options)

# Fetch two connections (exhausting the pool)
# and get the connection ID for each
connection1 = pool.get_session()
id1_row = connection1.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(1, id1_row["ConnectionID"]))
connection2 = pool.get_session()
id2_row = connection2.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(2, id2_row["ConnectionID"]))

# Attempt to get a third connection
time = datetime.now().strftime('%H:%M:%S')
print("Starting to request connection 3 .........: {0}".format(time))
try:
    connection3 = pool.get_session()
except mysqlx.errors.PoolError as err:
    print("Unable to fetch connection 3 .............: {0}".format(err))
time = datetime.now().strftime('%H:%M:%S')
print("Request for connection 3 completed .......: {0}".format(time))

# Return connection 1 to the pool
connection1.close()

# Try to get connection 3 again
connection3 = pool.get_session()
id3_row = connection3.sql(cnxid_sql).execute().fetch_one()
print(fmt_id.format(3, id3_row["ConnectionID"]))

# Close all connetions
pool.close()

The first thing to notice is the client options defined in lines 14-21. In this case all four options are set, but you only need to set those where you do not want the default value. The settings allow for at most two connections in the pool, when requesting a session it is allowed to take at most 3 seconds, and idle sessions should be disconnected after 60 seconds.

In line 24 the connection pool (client) is created and subsequent two sessions are fetched from the pool. When a third session is requested, it will trigger a PoolError exception as the pool is exhausted. Lines 38-41 shows how to handle the exception.

Finally the first connection is returned to the pool and it is possible to get the third request to complete.

An example of the output is (the connection IDs and timestamps will differ from execution to execution):

Connection 1 ID ..........................: 239
Connection 2 ID ..........................: 240
Starting to request connection 3 .........: 18:23:14
Unable to fetch connection 3 .............: pool max size has been reached
Request for connection 3 completed .......: 18:23:44
Connection 3 ID ..........................: 241

From the output you can see that the first attempt to fetch connection 3 takes three seconds before it times out and raises the exception – just as specified by the queue_timeout setting.

What may surprise you (at least if you have studied Chapter 5 from MySQL Connector/Python Revealed) from this output is that once connection 1 has been returned to the pool and connection 3 fetches the session again, it has a new connection ID. Does that mean the pool is not working? No, the pool is working alright. However, the X Plugin (the plugin in MySQL Server handling connections using the X Protocol) works differently than the connection handling for the traditional MySQL protocol.

The X Plugin distinguishes between the connection to the application and the thread inside MySQL. So, when the session is returned to the pool and the session is reset (to set the session variables back to the defaults and remove user variables) the thread inside MySQL is removed. As MySQL uses threads, it is cheap to create a new thread as it is needed, so this is not a performance problem. However, the connection to the application is maintained. This means you safe the expensive steps of creating the connection and authenticating, while the threads only actually exists inside MySQL while it is out of the pool.

If you are interested in learning more about MySQL Connector/Python 8 including how to use the X DevAPI, then I am the author of MySQL Connector/Python Revealed (Apress). It is available from Apress, Amazon, and other book stores.