DB QueuePool limit overflow and Pyramid sub-requests

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

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

[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

--
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+unsubscribe@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.

[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.

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

Thank you Michael,

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.

What would you recommend then to issue a request from within a view function? Using something like the requests package?

Cheers,

Jens 

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

Hi Jonathan,

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.

From within the view function (i.e. handling the incoming request) I issue 5 subrequests one after the other. Doing so I noticed that the number of subrequests was bound by the pool_size + max_overflow, hence my question here and in the SQLA group. 

 

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.

I'm not sure what you mean here: "during setup" meaning when the app starts, or when the request is being handled?

Cheers,

Jens

[Mike Orr]

I'd second the recommendation to minimize the use of subrequests. A
subrequest goes through the entire router overhead and just adds
unnecessary complications and indirection and possible bugs. If you
have two views and you want to call one from another, or one is a thin
wrapper over the other, that's usually a sign that you should put the
shared code in a utility function, or have one view call the other
directly. Usually a shared utility function is best because you can
design the arguments at the exact right level, rather than having to
go all the way up to creating a Request for it. (Or you can just pass
the current request, which you already have.)

>> email to pylons-discus...@googlegroups.com.
>> To post to this group, send email to pylons-...@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-discus...@googlegroups.com.
> To post to this group, send email to pylons-...@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.

--
Mike Orr <slugg...@gmail.com>

[Jonathan Vanasco]

On Saturday, April 28, 2018 at 5:45:23 AM UTC-4, jens.t...@gmail.com wrote:

 I'm not sure what you mean here: "during setup" meaning when the app starts, or when the request is being handled?

When the app starts, anything called in (or by something called in) your pyramid's app's `main()` (or the equivalent in middleware). Connecting to the database before a request is handled in a fork o threads will create and populate the connection pool with database connections -- which are not safe across forks or threads.  It's the most common cause of issues I've seen in SqlAlchemy webapps.

[Jonathan Vanasco]

On Saturday, April 28, 2018 at 5:45:23 AM UTC-4, jens.t...@gmail.com wrote:

From within the view function (i.e. handling the incoming request) I issue 5 subrequests one after the other. Doing so I noticed that the number of subrequests was bound by the pool_size + max_overflow, hence my question here and in the SQLA group. 

My confusion in your wording was that "It shouldn't work like that".  Pyramid handles subrequests independently, so it will create a new session with a connection checked out from the pool, and then should close and return it at the end.  I thought there may be something wrong with the connection pool, perhaps during startup or perhaps by the subrequest functionality, but looking at the subrequest source it doesn't look to spawn a new thread.

How are you handling your session connection and cleanup?  Are you using pyramid_tm? If so, are you using the `use_tween` on the invoke_subrequest to properly close each connection in the pool?  If not, how are you cleaning up your connections?

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

Jonathan,

On Monday, April 30, 2018 at 4:17:50 AM UTC+10, Jonathan Vanasco wrote:

How are you handling your session connection and cleanup?  Are you using pyramid_tm? If so, are you using the `use_tween` on the invoke_subrequest to properly close each connection in the pool?  If not, how are you cleaning up your connections?

I’ve followed the suggested cookie cutter code exactly: https://github.com/Pylons/pyramid-cookiecutter-alchemy, which uses pyramid_tm and adds the SQLA session to that transaction manager.

Thanks!

Jens 

[Jonathan Vanasco]

On Tuesday, May 1, 2018 at 1:17:31 AM UTC-4, jens.t...@gmail.com wrote:

 

I’ve followed the suggested cookie cutter code exactly: https://github.com/Pylons/pyramid-cookiecutter-alchemy, which uses pyramid_tm and adds the SQLA session to that transaction manager.

 

Are you passing in `use_tweens=True` ?

https://docs.pylonsproject.org/projects/pyramid/en/latest/api/request.html#pyramid.request.Request.invoke_subrequest
https://docs.pylonsproject.org/projects/pyramid/en/latest/narr/subrequest.html?highlight=use_tweens#subrequests-with-tweens

If not, the behavior you show is expected.  `pyramid_tm` handles the transaction begin/commit via tweens.  If the subrequests don't use the tweens, they're part of your main transaction and don't close the dbconnection or return it to the pool.  So they will require 1 db connection for main + [1 db connections for each subrequest].

If you are not enabling tweens in the subrequest, then Pyramid doesn't work how I'd imagine it to.  But this behavior makes sense for pyramid_tm without tweens enabled.

Sidenote: I don't know if transaction/pyramid_tm can handle subrequests like this.  You might want the functionality that Michael Merickel suggested above, by copying over the main db session.

Like most others though, I avoid subrequests at all costs. 

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

Thank you for the explanation, Jonathan! No, I didn't use use_tweens and perhaps will give that a try.

However, considering our other conversation regarding the requests package and how to issue API calls within a view callable, I’ll switch to using that anyway.

Jens