Overview of Database Connection Pooling
- A database connection to a remote database server like a MySQL Server is a TCP/IP connection established by contacting the database server on a specific port.
- For each database connection established from a client, a database server such as MySQL Server allocates a set of buffers to cater to the client needs. Based on design - the allocation, management and de-allocation of these buffers could get as complicated as it could on the server side.
- When a database connection is closed, all of the allocated buffers are cleared in a graceful manner and the TCP sockets are closed as well.
- The overhead involved equals to the number of database connections made to the server multiplied by the time and resources allocated per connection.
- For database applications running in various layers of the deployment of distributed software architecture, it is often necessary to deal with several database connections.
- However, opening these database connections every time afresh on need basis will result in poor performance and will affect the end use cases of applications.
- To address the above issue(s), database connections are often created in a batch, cached, used, marked for reuse and reused, which is together known as pooling of database connections.
Database Connection pooling with PyMySQL and DBUtils
- The PyMySQL is a client library for MySQL Server, which is compliant to Python Database API specification version 2.0.
- PyMySQL is developed fully using the Python Programming Language.
- The DBUtils is a Python library, which provides a database-connection-pooling framework for multithreaded Python environments.
- DBUtils provides the PooledDB class, which represents a pool of database connections to a database server like MySQL.
- The constructor of DBUtils.PooledDB takes a Python method as parameter, which returns a Python DB-API specification 2.0 compliant connection object or a python database module, compliant to the Python DB-API specification 2.0.
- Calling connection() method on a PooledDB instance will return a connection(a PooledSharedDBConnection instance)from the connection pool.
- Calling close() on the PooledSharedDBConnection instance will return the database connection for reuse.
- The parameter maxconnections of the PooledDB constructor specifies the maximum number of connections that can be created for a database connection pool. If the blocking parameter is True, the requests for a connection to the pool will block till a connection is released.
- If False, no blocking will happen upon exceeding the maximum number of connections available in the connection pool which could lead to the exception DBUtils.PooledDB.TooManyConnections.
Example
from threading import Thread
from datetime import datetime
import time
import pymysql
from DBUtils.PooledDB import PooledDB
# Definition of CrawlInfo
class CrawlInfo:
def __init__(self, url_in, header_in, body_in, lastVisit_in):
self.url = url_in
self.header = header_in
self.body = body_in
self.lastVisit = lastVisit_in
# The thread function for MySQL Insert
# This thread will insert records into MySQL table using the connections
# from connection pool
def MySQLInsertThread(threadNum_in, dbConnection_in, crawlInfo_in):
try:
print("Thread{} started".format(threadNum_in))
url = crawlInfo_in.url
header = crawlInfo_in.header
body = crawlInfo_in.body
lastVisit = crawlInfo_in.lastVisit
# SQL statement to insert into a MySQL database table
sqlInsert = "INSERT INTO crawled values ('{}','{}','{}','{}')".format(url, header, body, lastVisit)
# Obtain a cursor object
mySQLCursor = dbConnection_in.cursor()
# Execute the SQL stament
mySQLCursor.execute(sqlInsert)
# Close the cursor and connection objects
mySQLCursor.close()
dbConnection_in.close()
print("Thread{} exiting".format(threadNum_in))
except Exception as e:
print("Exception: %s" % e)
return
# Information required to create a connection object
dbServerIP = "127.0.0.1" # IP address of the MySQL database server
dbUserName = "root" # User name of the MySQL database server
dbUserPassword = "" # Password for the MySQL database user
databaseToUse = "TestDatabase" # Name of the MySQL database to be used
charSet = "utf8mb4" # Character set
cusrorType = pymysql.cursors.DictCursor
Crawl_Info_Count = 10
mySQLConnectionPool = PooledDB(creator = pymysql, # Python function returning a connection or a Python module, both based on DB-API 2
host = dbServerIP,
user = dbUserName,
password = dbUserPassword,
database = databaseToUse,
autocommit = True,
charset = charSet,
cursorclass = cusrorType,
blocking = False,
maxconnections = Crawl_Info_Count)
threadCollection = []
crawlInfoCollection = []
# Create data to insert data into MySQL table
for i in range(Crawl_Info_Count):
url = "url"+str(i)
header = "header"+str(i)
body = "body"+str(i)
lastVisit = time.strftime('%Y-%m-%d %H:%M:%S')
crawlInfoCollection.append(CrawlInfo(url, header, body, lastVisit))
# Create threads to insert data into MySQL table
for i in range(Crawl_Info_Count):
mySQLConnection = mySQLConnectionPool.connection()
t = Thread(target= MySQLInsertThread, args=(i, mySQLConnection,crawlInfoCollection[i]))
threadCollection.append(t)
# Start the db insert thread
t.start()
print("Number of database connections in use:{}".format(mySQLConnectionPool._connections))
# Proceed once the other threads are complete
for thread in threadCollection:
thread.join()Output
Thread0 started
Number of database connections in use:1
Thread1 started
Number of database connections in use:2
Thread2 started
Number of database connections in use:3
Thread3 started
Number of database connections in use:4
Thread4 started
Number of database connections in use:5
Thread5 started
Number of database connections in use:6
Thread6 started
Number of database connections in use:7
Thread7 started
Number of database connections in use:8
Thread8 started
Number of database connections in use:9
Thread9 started
Number of database connections in use:10
Thread0 exiting
Thread1 exiting
Thread8 exiting
Thread5 exiting
Thread9 exiting
Thread4 exiting
Thread3 exiting
Thread6 exiting
Thread7 exiting
Thread2 exiting