| Message ID | 20240220062705.3611869-1-tobiasha@axis.com |
|---|---|
| State | New |
| Headers | show |
| Series | Re-enable connection pooling with psycopg 3 driver | expand |
On Mon, Feb 19, 2024 at 11:27 PM Tobias Hagelborn <tobias.hagelborn@axis.com> wrote: > > Re-enable connection pooling in case `postgresql+psygopg` driver > is used. Async connection pooling is supported in psycopg 3 [psycopg] > driver in SQLAlchemy. > > Signed-off-by: Tobias Hagelborn <tobiasha@axis.com> > --- > > Connection pooling was disabled since asyncpg does not support it. > With psycopg3 driver it is possible to re-enable connection pooling again. > > I wanted to show that it is possible to get the performance back again > and avoid using other, more complex deployments such as pgbouncer to > achieve connection pooling. > > Checking the driver url is possibly a bit hackish. > Having some command line parameter would result in a larger change > and also force the user to know which drivers do support pooling properly. > > I tested this setup with some of my stress tests and with a batch of builds > and it seems to hold. > > lib/hashserv/sqlalchemy.py | 6 +++++- > 1 file changed, 5 insertions(+), 1 deletion(-) > > diff --git a/lib/hashserv/sqlalchemy.py b/lib/hashserv/sqlalchemy.py > index b17a17621..e33527e80 100644 > --- a/lib/hashserv/sqlalchemy.py > +++ b/lib/hashserv/sqlalchemy.py > @@ -93,7 +93,11 @@ class DatabaseEngine(object): > > async def create(self): > self.logger.info("Using database %s", self.url) > - self.engine = create_async_engine(self.url, poolclass=NullPool) > + if self.url.drivername == 'postgresql+psycopg': > + # Psygopg 3 (psygopg) driver can handle async connection pooling > + self.engine = create_async_engine(self.url) > + else: > + self.engine = create_async_engine(self.url, poolclass=NullPool) This approach is fine with me. If we need an argument in the future we can make one that does "yes", "no", "auto" options. Do you happen to know if this is version dependent or if it's just because I didn't test with the psycopg driver? > > async with self.engine.begin() as conn: > # Create tables > -- > 2.30.2 > > > -=-=-=-=-=-=-=-=-=-=-=- > Links: You receive all messages sent to this group. > View/Reply Online (#15944): https://lists.openembedded.org/g/bitbake-devel/message/15944 > Mute This Topic: https://lists.openembedded.org/mt/104463321/3616693 > Group Owner: bitbake-devel+owner@lists.openembedded.org > Unsubscribe: https://lists.openembedded.org/g/bitbake-devel/unsub [JPEWhacker@gmail.com] > -=-=-=-=-=-=-=-=-=-=-=- >
>This approach is fine with me. If we need an argument in the future we >can make one that does "yes", "no", "auto" options. Do you happen to >know if this is version dependent or if it's just because I didn't >test with the psycopg driver? Since I saw your commit earlier on removing the use of pool [1] , I assumed asyncpg did not support it and it also seems to be aligned with whatever was written in different forums. I have only tested psycopg v3 (package: psycopg) and verified that this works. I intend to commit a new patch-set since the default pool is very limited. I suggest allowing an unlimited pool size when required. [1] https://github.com/JoshuaWatt/bb-hashserver/commit/1a5483d0d96e2a429b1af0dca5a523955c8aeb1f
diff --git a/lib/hashserv/sqlalchemy.py b/lib/hashserv/sqlalchemy.py index b17a17621..e33527e80 100644 --- a/lib/hashserv/sqlalchemy.py +++ b/lib/hashserv/sqlalchemy.py @@ -93,7 +93,11 @@ class DatabaseEngine(object): async def create(self): self.logger.info("Using database %s", self.url) - self.engine = create_async_engine(self.url, poolclass=NullPool) + if self.url.drivername == 'postgresql+psycopg': + # Psygopg 3 (psygopg) driver can handle async connection pooling + self.engine = create_async_engine(self.url) + else: + self.engine = create_async_engine(self.url, poolclass=NullPool) async with self.engine.begin() as conn: # Create tables
Re-enable connection pooling in case `postgresql+psygopg` driver is used. Async connection pooling is supported in psycopg 3 [psycopg] driver in SQLAlchemy. Signed-off-by: Tobias Hagelborn <tobiasha@axis.com> --- Connection pooling was disabled since asyncpg does not support it. With psycopg3 driver it is possible to re-enable connection pooling again. I wanted to show that it is possible to get the performance back again and avoid using other, more complex deployments such as pgbouncer to achieve connection pooling. Checking the driver url is possibly a bit hackish. Having some command line parameter would result in a larger change and also force the user to know which drivers do support pooling properly. I tested this setup with some of my stress tests and with a batch of builds and it seems to hold. lib/hashserv/sqlalchemy.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-)