I'm not familiar with this concept but it seems to me that your web server can have at most N concurrent requests and that there would need to be some system that also sets a limit on the number of subrequests. If you are planning to have thousands of concurrent subrequests at a time and you'd like them to all use independent database connections then you'd need to configure your pool to allow thousands of overflow connections, however, now you need to look at how many processes you will be running and how many connections your database itself allows.
The point is that there are hard limits on how many connections you can have to your database, which is a good thing. Any system that generates lots of database connections similarly needs to work within these limits, so you'd need to plan for this. On Wed, Apr 25, 2018 at 9:15 PM, <[email protected]> wrote: > Hello, > > I would like to understand the interplay between a SQLA session and a > Pyramid’s subrequest. When a request is handled, a new session is created > for that request as per the Pyramid/SQLA cookiecutter, and it looks to me > like subrequests create a new session too. > > When I set the pool_size of the engine to N and max_overflow to M then I can > issue only a max of N+M subrequests, after which I get an exception: > > Traceback (most recent call last): > File "/…/site-packages/sqlalchemy/pool.py", line 1122, in _do_get > return self._pool.get(wait, self._timeout) > File "/…/site-packages/sqlalchemy/util/queue.py", line 156, in get > raise Empty > sqlalchemy.util.queue.Empty > > During handling of the above exception, another exception occurred: > > […] > File "/…/site-packages/sqlalchemy/engine/base.py", line 2147, in > _wrap_pool_connect > return fn() > File "/…/site-packages/sqlalchemy/pool.py", line 387, in connect > return _ConnectionFairy._checkout(self) > File "/…/site-packages/sqlalchemy/pool.py", line 766, in _checkout > fairy = _ConnectionRecord.checkout(pool) > File "/…/site-packages/sqlalchemy/pool.py", line 516, in checkout > rec = pool._do_get() > File "/…/site-packages/sqlalchemy/pool.py", line 1131, in _do_get > (self.size(), self.overflow(), self._timeout)) > sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 0 reached, > connection timed out, timeout 30 > > for > > sqlalchemy.pool_size = 5 > sqlalchemy.max_overflow = 0 > > When I up the pool size to fit all subrequests, then everything works fine > and the SQLA log shows me a ROLLBACK for each subrequest and one COMMIT at > the end which I think is the main request. > > Now I could set pool size to 0 to indicate no pool size limit, but I’m not > sure if that would be the correct solution here. > > What’s the recommended approach here? > > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
