3
votes

I'm running PostgreSQL 9.3 and SQLAlchemy 0.8.2 and experience database connections leaking. After deploying the app consumes around 240 connections. Over next 30 hours this number gradually grows to 500, when PostgreSQL will start dropping connections.

I use SQLAlchemy thread-local sessions:

from sqlalchemy import orm, create_engine

engine = create_engine(os.environ['DATABASE_URL'], echo=False)
Session = orm.scoped_session(orm.sessionmaker(engine))

For the Flask web app, the .remove() call to the Session proxy-object is send during request teardown:

@app.teardown_request
def teardown_request(exception=None):
    if not app.testing:
        Session.remove()

This should be the same as what Flask-SQLAlchemy is doing.

I also have some periodic tasks that run in a loop, and I call .remove() for every iteration of the loop:

def run_forever():
    while True:
        do_stuff(Session)
        Session.remove()

What am I doing wrong which could lead to a connection leak?

3
How is the app deployed? - Joe Doherty

3 Answers

4
votes

If I remember correctly from my experiments with SQLAlchemy, the scoped_session() is used to create sessions that you can access from multiple places. That is, you create a session in one method and use it in another without explicitly passing the session object around. It does that by keeping a list of sessions and associating them with a "scope ID". By default, to obtain a scope ID, it uses the current thread ID; so you have session per thread. You can supply a scopefunc to provide – for example – one ID per request:

# This is (approx.) what flask-sqlalchemy does:
from flask import _request_ctx_stack as context_stack

Session = orm.scoped_session(orm.sessionmaker(engine),
                             scopefunc=context_stack.__ident_func__)

Also, take note of the other answers and comments about doing background tasks.

0
votes

First of all, it is a really really bad way to run background tasks. Try any ASync scheduler like celery.

Not 100% sure so this is a bit of a guess based on the information provided, but I wonder if each page load is starting a new db connection which is then listening for notifications. If this is the case, I wonder if the db connection is effectively removed from the pool and so gets created on the next page load.

If this is the case, my recommendation would be to have a separate DBI database handle dedicated to listening for notifications so that these are not active in the queue. This might be done outside your workflow.

Also

Particularly, the leak is happening when making more than one simultaneous requests. At the same time, I could see some of the requests were left with uncompleted query execution and timing out. You can write something to manage this yourself.

0
votes

Had a similar problem which was solved by calling sqlalchemy.engine.Engine.dispose() in @app.teardown_request

See my question here: SQLAlchemy database session not being removed after web request