PG Connection leaks in production
Apparently the OSUG postgres server has a lot of leaking connections in rollback state from obsportal... that alters the stability of this shared service.
The SQLAlchemy connection pool is configured with max 5 overflow 5 connections !
Please check any other usages of SQLAlchemy Engine / Connection / Transaction that could explain such connection leaks.
For example, the health.py view seems weird:
engine = engine_from_config(self.settings, 'sqlalchemy.')
engine.connect()
It seems the engine is never disposed, nor the connection closed. I tried instead:
# 1. Database availability
engine = None
try:
engine = engine_from_config(self.settings, 'sqlalchemy.')
logger.info("engine.connect")
with engine.connect() as connection:
connection.execute('SELECT 1')
services['database'] = True
except OperationalError:
services['database'] = False
finally:
if engine:
engine.dispose()
But the QueryPool is then recreated at every call:
2020-04-03 16:37:53,172 INFO [obsportal.views.health:28][waitress] engine.connect
2020-04-03 16:37:53,177 DEBUG [sqlalchemy.pool.impl.QueuePool:640][waitress] Created new connection <connection object at 0x7f19c8b9c7d0; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0>
2020-04-03 16:37:53,180 DEBUG [sqlalchemy.pool.impl.QueuePool:508][waitress] Connection <connection object at 0x7f19c8b9c7d0; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> checked out from pool
2020-04-03 16:37:53,181 DEBUG [sqlalchemy.pool.impl.QueuePool:672][waitress] Connection <connection object at 0x7f19c8b9c7d0; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> being returned to pool
2020-04-03 16:37:53,181 DEBUG [sqlalchemy.pool.impl.QueuePool:862][waitress] Connection <connection object at 0x7f19c8b9c7d0; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> rollback-on-return
2020-04-03 16:37:53,181 DEBUG [sqlalchemy.pool.impl.QueuePool:267][waitress] Closing connection <connection object at 0x7f19c8b9c7d0; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0>
2020-04-03 16:37:53,181 INFO [sqlalchemy.pool.impl.QueuePool:187][waitress] Pool disposed. Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
2020-04-03 16:37:53,181 INFO [sqlalchemy.pool.impl.QueuePool:163][waitress] Pool recreating
2020-04-03 16:37:53,184 DEBUG [sqlalchemy.pool.impl.QueuePool:508][waitress] Connection <connection object at 0x7f19c8b9c550; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> checked out from pool
2020-04-03 16:37:53,348 DEBUG [sqlalchemy.pool.impl.QueuePool:672][waitress] Connection <connection object at 0x7f19c8b9c550; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> being returned to pool
2020-04-03 16:37:53,348 DEBUG [sqlalchemy.pool.impl.QueuePool:862][waitress] Connection <connection object at 0x7f19c8b9c550; dsn: 'user=obsdb_admin password=xxx dbname=obsdb host=172.18.0.1 client_encoding=utf8', closed: 0> rollback-on-return, via agent
2020-04-03 16:37:53,348 DEBUG [txn.139749004285696:440][waitress] commit <zope.sqlalchemy.datamanager.SessionDataManager object at 0x7f19c8055290>
2020-04-03 16:37:53,349 DEBUG [txn.139749004285696:289][waitress] commit
Probably there is a conflict between Pyramid's Engine and this new created one ... and QueryPool is shared...
Maybe it is related to Transaction handling that implies sharing Engine / Connection pools ...
To be studied in depth.