Re: [pylons-discuss] DB QueuePool limit overflow and Pyramid sub-requests

[Jonathan Vanasco]

I'm confused by wording on this, or perhaps the functionality in Pyramid.

> 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:

Assuming you have 1 request and 5 subrequests, shouldn't there only be 2 
connections needed in the pool (i.e. the main request establishes a first 
connection, then subrequest 1 establishes a second connection which is 
re-used by 2-5)?  You wouldn't be able to save a connection like this if 
you had recursive subrequests - but that would be a design flaw in the 
application logic.

If you're connecting to sqlalchemy during your setup, you can screw up the 
connection pool unless you call `engine.dispose()` (see a thread from a few 
weeks ago), because SqlAlchemy's connections and pool aren't forksafe or 
threadsafe.

-- 
You received this message because you are subscribed to the Google Groups 
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to pylons-discuss+unsubscr...@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/ca945b9a-b9ce-4a2a-aede-f4a07226cb34%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [pylons-discuss] DB QueuePool limit overflow and Pyramid sub-requests

[Michael Merickel]

In general I strongly urge you to reconsider using subrequests... they are
there for people to use but they have lots of drawbacks versus just calling
a bunch of reusable functions.

Anyway, this is how subrequests work - they are isolated. They do have an
odd feature that sort of lets you merge them with the current request by
setting use_tweens=False, from there you could copy request.tm and
request.dbsession to the new request and then it'd be using the same
pyramid_tm and dbsession which is one step closer to thinking of your
sub-views as part of the same top-level request context.

- Michael

On Thu, Apr 26, 2018 at 8:55 AM,  wrote:

> Hello,
>
> Perhaps this forum is more appropriate to ask than the SQLAlchemy group
> (see this same question over there
> ).
>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "pylons-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to pylons-discuss+unsubscr...@googlegroups.com.
> To post to this group, send email to pylons-discuss@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/
> msgid/pylons-discuss/6c2f13c2-ea4d-4f9d-9d47-50dc98c6e583%
> 40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to pylons-discuss+unsubscr...@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/CAKdhhwF0q%3DOjphnVsEX8xpN2wwSxgK_zSPp3w3N8Ty8%2BUwNy6Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[pylons-discuss] DB QueuePool limit overflow and Pyramid sub-requests

[jens . troeger]

Hello,

Perhaps this forum is more appropriate to ask than the SQLAlchemy group 
(see this same question over there 
).

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

-- 
You received this message because you are subscribed to the Google Groups 
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to pylons-discuss+unsubscr...@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/6c2f13c2-ea4d-4f9d-9d47-50dc98c6e583%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.