Connection Pooling using Connector/Python

What is Connection Pool?

So far every time we want to talk to the database we have created a new connection. This is fine if your application’s user base is small. However, if you already know that your application is going to be used by millions of people around the globe then this approach is not very feasible.

The problem is that opening and maintaining a connection is an expensive operation and wastes a lot of resources. Issues like this can make your application slow and unresponsive. Eventually, driving the users away from the application.

Enter the Connection pooling to the rescue.

The connection pool a standard way of caching connections in the memory so that they can be reused when needed instead of creating a connection from scratch. The connection pool if implemented correctly offers a significant performance boost.

In connection pooling whenever an application needs to talk to the database it fetches the connection from the pool. After performing the desired queries application releases the connection and pushes it back to the pool. In case all the connections are being used, a new connection is created and added to the pool.

Creating Connection Pool in MySQL Connector/Python

There are two ways of creating a connection pool.

  1. mysql.connector.connect()
  2. mysql.connector.pooling.MySQLConnectionPool

Creating Connection Pool via connect() function

To create a connection pool with the connect() function, we pass any one of the following pool related argument to it.

Argument Description
pool_name (optional) Name of the connection pool. This parameter is optional and if not specified the pool name will be generated automatically by joining the values from host, port, user, and database connection arguments, in that order.
pool_size (optional) Size of the pool i.e. the number of connection to hold in the pool. This argument is optional and if not specified, it defaults to 5.

Here is an example:

The above call to connect() function does two things:

  1. Creates a connection pool named my_connection_pool of size 10.
  2. Returns the first connection from the pool and assign it to the db variable.

From now on, subsequent calls to connect() with the same pool name, will return the connection from an existing pool. If the connect() function is called and there is no available connection in the pool, a PoolError exception is raised.

Furthermore, the connection object returned by connect() is of type PooledMySQLConnection instead of MySQLConnection or CMySQLConnection.

The pooled connection object (i.e PooledMySQLConnection) object behaves exactly like MySQLConnection or CMySQLConnection, the only differences are:

  1. The close() returns the connection to the connection pool instead of actually closing the connection.
  2. The pool connection object has a property named pool_name which returns the name of the connection pool to which the connection belongs.

Note that connection are created lazily by the pool. This means if you create a connection pool of size 10, but only 5 users are accessing the application simultaneously then only 5 connections will be made. The 6th connection or so on will be created on demand.

Let’s now take some examples:

Example 1: Fetching connections from the connection pool

Expected Output:

Note: Connection ids may differ every time you run the program.

Here is how it works:

  1. In line 78, we create a connection of size 3 and fetch the first connection from it.
  2. In line 13, we fetch the second connection from the pool.
  3. In line 17, we fetch the third connection from the pool.
  4. The connection pool is now exhausted, subsequent calls to connect() with the same pool name will raise PoolError exception. This is exactly what’s happening in line 25.
  5. In line 31, we close db3 connection. So, now there is one available connection in the pool. The subsequent call to connect() assigns this connection to the db4 variable (in line 34).
  6. Finally, we print the connection ids and then close the connections. Note that the connection id of db3 and db4 is same this is because db4 is reusing the same connection which was used by db3.

Example 2: Executing queries on a connection fetched from the connection pool

Expected Output:

Creating Connection Pool via MySQLConnectionPool class

Another way to create connection pool is to instantiate it directly using the MySQLConnectionPool class. The MySQLConnectionPool is the main class which creates and manages the connection pool. The syntax of MySQLConnectionPool constructor is as follows:

Argument Description
pool_name (required) Name of the connection pool. This parameter is optional and if not specified the pool name will be generated automatically by joining the values from host, port, user, and database connection arguments, in that order.
pool_size (optional) Size of the connection pool. It defaults to 5.
kwargs (optional) It refers to the connection arguments that we have been passing to the connect() function.

Here is an example:

Instantiating MySQLConnectionPool only creates the connection pool it doesn’t automatically fetch the first connection from the pool.

To obtain a connection, we use the get_connection() method of the MySQLConnectionPool instance.

As usual, when the connection pool is exhausted, a PoolError exception will be raised.

Let’s take some examples now:

Example 1: Fetching connections from the connection pool

Expected Output:

This example works exactly like the example we went through in the preceding section. The only difference is that here we are creating a connection pool explicitly via MySQLConnectionPool and then obtaining connections via the get_connection() method.

Example 2: Executing queries on a connection fetched from the connection pool

Expected Output:

Leave a Comment