Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-21 Thread Arndt Droullier 
I use a standalone connection pooling for Postgresql in some cases. It is
faster and more efficient than
doing full connects to Postgresql directly in each request.

https://www.pgbouncer.org/

Arndt.

Am Di., 21. Nov. 2023 um 16:14 Uhr schrieb Eldav :

> Thank you Jonathan,
>
> after asking my question, I did more googling and found this :
>
> https://docs.sqlalchemy.org/en/20/core/pooling.html#pooling-multiprocessing
>
> It does mention Engine.dispose :) I tried their solution #4, which seemed
> to be the one which fit best in my code. But somehow I feel it isn't quite
> satisfactory, I see a lot of connects / checkout, which seems inelegant and
> maybe bad for performance.
>
> Laurent.
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/pylons-discuss/e22c60a5-978f-4204-81a6-c2c64b26475bn%40googlegroups.com
> 
> .
>


-- 
--
nive.co 
Arndt Droullier
Tel 0221-2725782
Nive GmbH, Händelstr. 26, Köln, Germany

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/CAJYR-0NPoXCDu_oTG%3DLNaugF9uunUwGXdRswSCz_X0XvhkG%3D_w%40mail.gmail.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-21 Thread Eldav 


This should not happen. Do you know which cookiecutter you used, and when?  
This should not happen in the most recent cookiecutter.


As far as I remember, I used the official cookiecutter, but that was a few 
years ago (around the time when Pyramid 2.0 was released, and I felt the 
need to synchronize my code with it.

Based on what you're saying, I should probably do it again now, as a first 
step :) I'll let you know how it went.

Thank you again,

Laurent.

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/5091b343-c00f-4d3d-b3d0-e2cc38eeb5cdn%40googlegroups.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-21 Thread Eldav 
Thank you Jonathan, 

after asking my question, I did more googling and found this :

https://docs.sqlalchemy.org/en/20/core/pooling.html#pooling-multiprocessing

It does mention Engine.dispose :) I tried their solution #4, which seemed 
to be the one which fit best in my code. But somehow I feel it isn't quite 
satisfactory, I see a lot of connects / checkout, which seems inelegant and 
maybe bad for performance.

Laurent.

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/e22c60a5-978f-4204-81a6-c2c64b26475bn%40googlegroups.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-21 Thread Jonathan Vanasco 
> Namely, if you deploy with Gunicorn a Pyramid + PostgreSQL app based on 
the standard cookiecutter, you will run into problems, because the 
connection to the DB can't be shared between the processes, so each process 
needs to have its own connection to the DB.

I forgot to mention...

This should not happen. Do you know which cookiecutter you used, and when?  
This should not happen in the most recent cookiecutter.

The cookiecutter works by adding a transaction/thread safe session to each 
request: 
https://github.com/Pylons/pyramid-cookiecutter-starter/blob/latest/%7B%7Bcookiecutter.repo_name%7D%7D/%7B%7Bcookiecutter.repo_name%7D%7D/sqlalchemy_models/__init__.py#L87-L127

It also requires pyramid_tm, which will cleanup the connections at the end 
of the request lifecycle.  If you decide to not use pyramid_tm, then you'll 
have to use a cleanup routine like this one when you grab the request.  
This was one the pattern in the pyramid cookiecutter, but it was taken out 
because pyramid_tm effectively does that itself.

  
  
https://github.com/jvanasco/pyramid_oauthlib_lowlevel/blob/main/tests/oauth2_app/model/__init__.py#L71-L77

The only way the connection pool sharing should be able to happen is if you 
grabbed a database connection before the fork - like during application 
setup, which would then require a call to Engine.dispose.

If you are not consciously grabbing a database connection before the fork, 
you should do a code audit to figure out where the first connection is 
being made and how it is recycled, as the last few versions of the 
cookiecutter do defend against this behavior.




On Tuesday, November 21, 2023 at 1:06:43 AM UTC-5 Mike Orr wrote:

> On Mon, Nov 20, 2023 at 4:14 PM Jonathan Vanasco  
> wrote:
> >
> > SQLAlchemy supports this via `Engine.dispose()`, which is the documented 
> way of handling a post-fork connection:
> >
> > https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal
>
> Yes, that sounds familiar.
>

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/984f5ea0-5d10-416b-83f0-c6d19d30ad9dn%40googlegroups.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-20 Thread Mike Orr 
On Mon, Nov 20, 2023 at 4:14 PM Jonathan Vanasco  wrote:
>
> SQLAlchemy supports this via `Engine.dispose()`, which is the documented way 
> of handling a post-fork connection:
>
>https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal

Yes, that sounds familiar.

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/CAH9f%3DuoNxgs5ufQT7p8%2BPobzeS0COJPrEP-8o%3D%2BQciCrbyFU%2BQ%40mail.gmail.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-20 Thread Jonathan Vanasco 
SQLAlchemy supports this via `Engine.dispose()`, which is the documented 
way of handling a post-fork connection:

   https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal

Invoking `Pool.recreate()` should be fine, but the documented pattern is to 
call `Engine.dispose`

> How should I modify my app to make it compatible with both Waitress and 
Gunicorn?

I've been trying to standardize this with pyramid_forkfsafe when I was 
deploying projects on uwsgi and gunicorn simultaneously:

https://pypi.org/project/pyramid-forksafe/
https://github.com/jvanasco/pyramid_forksafe

I don't know if the gunicorn is currently working, as I only work with 
uwsgi now and never got around to writing tests for gunicorn.

Ideally, my package works like this:

* it defines a new `ApplicationPostFork` event
* you write your post-fork code in an event subscriber
* the package invokes the event at the right time

This allows you to just define the post-fork routine, and if everything 
goes correctly pyramid_forksafe will detect the correct post-fork event 
and invoke your routine.
On Thursday, November 16, 2023 at 12:18:59 PM UTC-5 Theron Luhn wrote:

> If you aren’t using `—preload` then gunicorn should load the application 
> fresh for each worker and you shouldn’t have any issues.
>
> If you are using preload, you have to recreate any existing connections on 
> fork.  For SQLAlchemy I use:
>
> def after_fork(registry):
> registry['db_engine'].pool.recreate()
>
> def includeme(config):
> os.register_at_fork(
> after_in_child=functools.partial(after_fork, config.registry),
> )
>
>
> — Theron
>
>
>
> On Nov 16, 2023, at 7:41 AM, Laurent Daverio  wrote:
>
> Hello list,
>
> this page seems to describe perfectly a problem I've stumbled on: 
>
>
> https://stackoverflow.com/questions/64995178/decryption-failed-or-bad-record-mac-in-multiprocessing
>
> Namely, if you deploy with Gunicorn a Pyramid + PostgreSQL app based on 
> the standard cookiecutter, you will run into problems, because the 
> connection to the DB can't be shared between the processes, so each process 
> needs to have its own connection to the DB.
>
> Before I start trying to develop a workaround, has anybody encountered the 
> problem? How should I modify my app to make it compatible with both 
> Waitress and Gunicorn?
>
> Thanks in advance,
>
> Laurent.
>
> -- 
> 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 [email protected].
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/pylons-discuss/CAB7cU6z1DqHpEazrrJ1sPHmSPQvYtfkmeKfsJP_jLmsDyPA96w%40mail.gmail.com
>  
> 
> .
>
>
>

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/459deec2-f880-494d-afc4-bfc300f15093n%40googlegroups.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-18 Thread Eldav 
Thank you Theron,

I'm not using "--preload", actually not doing anything special, since I'm 
trying to use Gunicorn as a drop-in replacement for Waitress, like I always 
did, BUT I'm realizing that I was using `psycopg2` in the past, whereas I`m 
using `psycopg` (i.e. version 3) now, and version 3 creates a pool of 
connections. That must be the explanation why it worked then, but not 
anymore. With `psycopg2`, I had a connection created by each worker. Now I 
probably have a shared pool of connections.

Laurent.

Le jeudi 16 novembre 2023 à 18:18:59 UTC+1, Theron Luhn a écrit :

> If you aren’t using `—preload` then gunicorn should load the application 
> fresh for each worker and you shouldn’t have any issues.
>
> If you are using preload, you have to recreate any existing connections on 
> fork.  For SQLAlchemy I use:
>
> def after_fork(registry):
> registry['db_engine'].pool.recreate()
>
> def includeme(config):
> os.register_at_fork(
> after_in_child=functools.partial(after_fork, config.registry),
> )
>
>
> — Theron
>
>
>
> On Nov 16, 2023, at 7:41 AM, Laurent Daverio  wrote:
>
> Hello list,
>
> this page seems to describe perfectly a problem I've stumbled on: 
>
>
> https://stackoverflow.com/questions/64995178/decryption-failed-or-bad-record-mac-in-multiprocessing
>
> Namely, if you deploy with Gunicorn a Pyramid + PostgreSQL app based on 
> the standard cookiecutter, you will run into problems, because the 
> connection to the DB can't be shared between the processes, so each process 
> needs to have its own connection to the DB.
>
> Before I start trying to develop a workaround, has anybody encountered the 
> problem? How should I modify my app to make it compatible with both 
> Waitress and Gunicorn?
>
> Thanks in advance,
>
> Laurent.
>
> -- 
> 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 [email protected].
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/pylons-discuss/CAB7cU6z1DqHpEazrrJ1sPHmSPQvYtfkmeKfsJP_jLmsDyPA96w%40mail.gmail.com
>  
> 
> .
>
>
>

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/e56a6f67-64ec-4a2b-9ebf-9947bdf993d6n%40googlegroups.com.

Re: [pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-16 Thread Theron Luhn 
If you aren’t using `—preload` then gunicorn should load the application fresh 
for each worker and you shouldn’t have any issues.

If you are using preload, you have to recreate any existing connections on 
fork.  For SQLAlchemy I use:

def after_fork(registry):
registry['db_engine'].pool.recreate()

def includeme(config):
os.register_at_fork(
after_in_child=functools.partial(after_fork, config.registry),
)


— Theron



> On Nov 16, 2023, at 7:41 AM, Laurent Daverio  wrote:
> 
> Hello list,
> 
> this page seems to describe perfectly a problem I've stumbled on:
> 
> https://stackoverflow.com/questions/64995178/decryption-failed-or-bad-record-mac-in-multiprocessing
> 
> Namely, if you deploy with Gunicorn a Pyramid + PostgreSQL app based on the 
> standard cookiecutter, you will run into problems, because the connection to 
> the DB can't be shared between the processes, so each process needs to have 
> its own connection to the DB.
> 
> Before I start trying to develop a workaround, has anybody encountered the 
> problem? How should I modify my app to make it compatible with both Waitress 
> and Gunicorn?
> 
> Thanks in advance,
> 
> Laurent.
> 
> -- 
> 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 [email protected] 
> .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/pylons-discuss/CAB7cU6z1DqHpEazrrJ1sPHmSPQvYtfkmeKfsJP_jLmsDyPA96w%40mail.gmail.com
>  
> .

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/A4B81718-3D59-4697-B7AE-A9ECF959AF9C%40luhn.com.

[pylons-discuss] Problems when using Gunicorn and Postgresql together

2023-11-16 Thread Laurent Daverio 
Hello list,

this page seems to describe perfectly a problem I've stumbled on:

https://stackoverflow.com/questions/64995178/decryption-failed-or-bad-record-mac-in-multiprocessing

Namely, if you deploy with Gunicorn a Pyramid + PostgreSQL app based on the
standard cookiecutter, you will run into problems, because the connection
to the DB can't be shared between the processes, so each process needs to
have its own connection to the DB.

Before I start trying to develop a workaround, has anybody encountered the
problem? How should I modify my app to make it compatible with both
Waitress and Gunicorn?

Thanks in advance,

Laurent.

-- 
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 [email protected].
To view this discussion on the web visit 
https://groups.google.com/d/msgid/pylons-discuss/CAB7cU6z1DqHpEazrrJ1sPHmSPQvYtfkmeKfsJP_jLmsDyPA96w%40mail.gmail.com.