On Thu, Apr 26, 2018 at 09:09:55AM -0400, Mike Bayer wrote: > 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, <jens.troe...@gmail.com> 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?
I don't know what's your underlying database, but at $work with PostgreSQL we use Pgbouncer + SQLAlchemy NullPool. I think this is the recommended approach with a forking-like (gunicorn, ...) WSGI server For that, we slightly modify the Pyramid templates to be able to use sqlalchemy.poolclass = NullPool in a .ini file: https://gist.github.com/silenius/59b3760fb9cbad71aedebc67fd74ca6c#file-sa-py-L22-L25 > > > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.be) PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: PGP signature
