I am running a python (v3.9.16) application from a main thread while a separate worker thread runs an asyncio loop that makes SQL queries to a database (using aioodbc v0.5.0). Currently there are 4 asyncio tasks running in the worker thread. With the create_async_engine command, I have configured the connection pool size to 8 and the max overflow to 10.
I added support to monitor the connection pool status with the following code:
pool_status = session.get_bind().pool.status()
Attached below is a snippet from the log of the pool_status, which is displayed during each query made to the database. The Pool size is 8, which makes sense given my create_async_engine configuration. Can someone please provide clarification on the meaning / behavior of other three components of the pool_status: Connections in pool, Current Overflow, Checked out connections? For example, the Current Overflow is confusing since it shows a negative value.
Pool size: 8 Connections in pool: 1 Current Overflow: -7 Current Checked out connections: 0
Pool size: 8 Connections in pool: 1 Current Overflow: -7 Current Checked out connections: 0
Pool size: 8 Connections in pool: 0 Current Overflow: -7 Current Checked out connections: 1
Pool size: 8 Connections in pool: 2 Current Overflow: -6 Current Checked out connections: 0
Pool size: 8 Connections in pool: 2 Current Overflow: -6 Current Checked out connections: 0
Pool size: 8 Connections in pool: 2 Current Overflow: -6 Current Checked out connections: 0
Pool size: 8 Connections in pool: 2 Current Overflow: -6 Current Checked out connections: 0
Pool size: 8 Connections in pool: 1 Current Overflow: -6 Current Checked out connections: 1
Pool size: 8 Connections in pool: 2 Current Overflow: -6 Current Checked out connections: 0
Pool size: 8 Connections in pool: 1 Current Overflow: -6 Current Checked out connections: 1
Pool size: 8 Connections in pool: 1 Current Overflow: -6 Current Checked out connections: 1
Pool size: 8 Connections in pool: 1 Current Overflow: -6 Current Checked out connections: 1
Pool size: 8 Connections in pool: 2 Current Overflow: -5 Current Checked out connections: 1
Pool size: 8 Connections in pool: 2 Current Overflow: -5 Current Checked out connections: 1
Pool size: 8 Connections in pool: 3 Current Overflow: -5 Current Checked out connections: 0
Pool size: 8 Connections in pool: 3 Current Overflow: -5 Current Checked out connections: 0
Pool size: 8 Connections in pool: 3 Current Overflow: -5 Current Checked out connections: 0
Pool size: 8 Connections in pool: 1 Current Overflow: -5 Current Checked out connections: 2
First of the easy ones:
Pool size : it indicates the maximum connections available that can be made in the pool without going overflow.
Connections in pool : it indicates the number of connections idle (available for new tasks) in the pool. A connection returns back into pool once the task using it is completed.
Current Checked out connections : its indicates the number of connections that are currently being used by tasks in pool. or the connections that are unavailable for new tasks
Current Overflow : its indicates the number of connections that are made additionally from configuration due to the demand (if the number is +ive).
If its negative there may be several reasons of it:
(a) negative values can sometimes appear in log output, especially if the connections are not being returned correctly, or there's a misconfiguration between pool size and max overflow.
To fix it - >
=> Make sure that all connections are properly released back to the pool after each use. Connections should be closed or returned to the pool even if exceptions occur in the code.
=> Ensure aioodbc compatibility with the pool settings, as some async drivers may have internal pool management conflicts when used alongside certain pool configurations in SQLAlchemy.