Seeking experience/suggestions regarding connection pools

[jens.t...@gmail.com]

Hello,

The SQLA Pooling section says that the default connection pool (QueuePool is the default pool, isn’t it?) has 5 connections with an overflow of 10 (docs).

However, in our configuration we use

sqlalchemy.pool_recycle = 3600
sqlalchemy.pool_pre_ping = True
sqlalchemy.pool_size = 20
sqlalchemy.max_overflow = 30

Independently of how many connections the PG server accepts (looks like 100 by default, docs) a single gRPC server process has a global Engine instance with a connection pool of that configured size.

Without too many requests flowing through our gRPC server process, we already see these errors:

QueuePool limit of size 20 overflow 30 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)

Assuming that we have the implementation correct (a global Engine and its connection pool, sessions fetching and releasing connections correctly) the error can mean one of two things:

1. too many simultaneous requests hold a connection too long; or
2. these settings aren’t realistic for a production server environment.

Is there another angle I might be missing? What configuration settings would you recommend? Other than turning on debugging (docs) what tips do you have to debug the issue?

Much thanks!

Jens

[Mike Bayer]

On Thu, Jul 13, 2023, at 8:07 PM, jens.t...@gmail.com wrote:

Hello,

The SQLA Pooling section says that the default connection pool (QueuePool is the default pool, isn’t it?) has 5 connections with an overflow of 10 (docs).

However, in our configuration we use

sqlalchemy.pool_recycle = 3600
sqlalchemy.pool_pre_ping = True
sqlalchemy.pool_size = 20
sqlalchemy.max_overflow = 30

Independently of how many connections the PG server accepts (looks like 100 by default, docs) a single gRPC server process has a global Engine instance with a connection pool of that configured size.

Without too many requests flowing through our gRPC server process, we already see these errors:

QueuePool limit of size 20 overflow 30 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)

Assuming that we have the implementation correct (a global Engine and its connection pool, sessions fetching and releasing connections correctly) the error can mean one of two things:

1. too many simultaneous requests hold a connection too long; or
   
2. these settings aren’t realistic for a production server environment.
   

well 1 and 2 are the same thing.  if you are using a webserver that allows 80 simultaneous requests that are all using the database, that will use up the 50 connections you have allocated.   or, if you have some kind of long running worker threads or something that are using up connections, that will do it too.  finally, if your app is *leaking* connections, that is, opening connections and not closing them while continuing to check out more connections at the same time, that causes this problem as well.  it's kind of hard to do that though unless you have some strange programming patterns in use.

When you get that error, Python will give you a stack trace where the connection is being checked out.     you can also turn on pool logging to watch when connections are checked out and returned.   if you want to test some request-handling code in isolation and ensure it uses only one connection, there's a special pool called the AssertionPool (https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.AssertionPool) which might be helpful.

Is there another angle I might be missing? What configuration settings would you recommend? Other than turning on debugging (docs) what tips do you have to debug the issue?

Much thanks!

Jens

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0cdbdb8a-0cff-47f4-b0ca-fccd70ded2den%40googlegroups.com.