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

[jens . troeger]

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



On Wednesday, May 2, 2018 at 3:38:10 AM UTC+10, Jonathan Vanasco wrote:
>
> 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. 
>
>

-- 
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/7ab21cf5-f387-4b2d-b644-0dc263c2397a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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

-- 
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/5d4743af-52c2-489d-8344-1f688a085cb2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[jens . troeger]

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 

-- 
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/c8f29336-c224-453c-923f-15e0df5ead5c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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

-- 
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/742c3ea1-0637-4e5c-a6e7-3d10a39b3bdb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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

-- 
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/fa5d06e5-b16d-4ce5-a2f8-bc7b6a79d941%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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


On Thu, Apr 26, 2018 at 8:01 AM, Michael Merickel  wrote:
> 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.



-- 
Mike Orr 

-- 
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/CAH9f%3Duq%3DwaQ91RhT0

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

[jens . troeger]

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

-- 
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/d476c9da-d45b-4530-b342-ff408fb0ef2a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[jens . troeger]

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 

-- 
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/570274fd-0ea3-4258-a9e0-719684b2b4cc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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.