Menu
This is a follow-up to a question I posted earlier about DB Connection Pooling errors in SQLAlchemy.
According to the SQLAlchemy docs the sqlalchemy.pool.QueuePool.__init__() method takes the following argument:
pool_size – The size of the pool to be maintained, defaults to 5. This is the largest number of connections that will be kept persistently in the pool. Note that the pool begins with no connections; once this number of connections is requested, that number of connections will remain. pool_size can be set to 0 to indicate no size limit; to disable pooling, use a NullPool instead.
What are the drawbacks to setting pool_size=0? What is the benefit of limiting the connection pool size? Is it just to save memory? The database shouldn't really care if a large number of unused connections are open, right?
386
A connection pool is a standard technique used to maintain long running connections in memory for efficient re-use, as well as to provide management for the total number of connections an application might use simultaneously.
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default).
You reuse a prior database connection, in a new context to avoid the cost of setting up a new database connection for each request. The primary reason to avoid using database connections is that you're application's approach to solving problems isn't structured to accommodate a database connection pool.
When those additional connections are returned to the pool, they are disconnected and discarded. It follows then that the total number of simultaneous connections the pool will allow is pool_size + max_overflow , and the total number of “sleeping” connections the pool will allow is pool_size .
The main drawback to not limiting the pool size would be the potential for a runaway program to create too many connections.
There are real limits, both at the database level, and O/S level to how many connections the database will support. Each connection will also use additional memory. Limiting the pool size helps protect your database against both bugs in your program, or a malicious attack on your server. Either of those could bring your database server to a standstill by using too many connections or too much memory.
Under normal circumstances, the additional memory each connection uses shouldn't be too much of an issue, but it's best to limit it to the maximum number you think you'll use concurrently (maybe plus a few for good measure).
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
