On Thu, Jul 13, 2023, at 8:07 PM, jens.t...@gmail.com wrote: > Hello, > > The SQLA Pooling section > <https://docs.sqlalchemy.org/en/20/core/pooling.html> says that the default > connection pool (QueuePool is the default pool, isn’t it?) has 5 connections > with an overflow of 10 (docs > <https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.QueuePool.__init__>). > > 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 > <https://www.postgresql.org/docs/current/runtime-config-connection.html>) 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 > <https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.Pool.params.echo>) > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/0cdbdb8a-0cff-47f4-b0ca-fccd70ded2den%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/0cdbdb8a-0cff-47f4-b0ca-fccd70ded2den%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b465c695-bc35-4386-9d1a-82da190186f6%40app.fastmail.com.
