flask, gunicorn (gevent), sqlalchemy (postgresql): too many connections

Question

I created Flask WSGI-application which uses gunicorn as WSGI-server, for DB it uses PostgreSQL through Flask SQLAlchemy extension. That's all hosted on Heroku.

gunicorn configuration

• number of workers: 2;
• number of workers connections: 1024;
• number of threads: 1;
• worker class: gevent.

Heroku PostgreSQL configuration

• maximum number of connections: 20.

For everything else default configuration is used.

I'm getting this error: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: too many connections for role <id>. Obviously, i'm exceeded allowed number of DB connections.

I tried these things to fix it:

• for SQLAlchemy set poolclass to NullPool;
• for SQLAlchmey set pool_recycle to 2. Number of connections still the same even after more than 2 seconds;
• session.close() with engine.dispose();
• number of workers - 2, number of worker connections - 9;
• number of workers - 1, number of worker connections - 18;
• number of workers - 1, number of worker connections - 10, SQLAlchemy max_overflow = 0, SQLALchmey pool_size = 10 (i'm getting this error: sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 0 reached, connection timed out, timeout 30).

Nothing of this works. I'm still getting this error even with minimum gunicorn configuration (1 worker with 18 connections). I'm really started not to understand what is really going on.

I thought it worked like this: each worker have it's own instance of engine, and each engine have it's own pool size. So, if there is 2 workers with engine default config (pool size is 5), then we have 2 * 5 = 10 maximum connections to DB. But it looks like this is really not like this.

Questions

• how to fix this error?
• how SQLAlchemy pooling works with gevent workers? i.e., how can i count maximum number of DB connections?
• how should I configure it correctly so that it works as expected?

Sorry for too much questions, but it is really frustrating me.

Grantland

It sounds like there may be left over connections from previous attempts that have no gone away. Can you access the database at all (with some other user) and see what is in pg_stat_activity? – Inhalation

@Inhalation After request is ended, 5 minute later, there is many connections with state " idle in transaction". For example, if i created 10 requests, after 5 minutes these 10 connections will exists with state "idle in transaction". But in these requests i don't do any insert operations, only read. – Grantland

regardless of what you did in those transactions, you need to terminate them. Either through commit, or through closing the connection. – Inhalation

@Inhalation As i wrote in the questions: i tried this session.close() engine.dispose(); after each request. It didn't work. – Grantland

That doesn't matter of the connections are still left over from before you added that code. Has that code always been there? – Inhalation

you can create class or function to return same connection object until it is not closed by PostgreSQL or code like singleton pattern – Barde

@Barde it is always uses single object that was created at server start up, not created every time at function call – Grantland

can you share your code – Barde

@Barde github.com/Amaimersion/yandex-disk-telegram-bot/blob/master/src/… – Grantland

i think you need to manually kill the connections from the terminal. Check this question – Gourd

@SiddhantTandon They should be killed automatically, isn't? – Grantland

Sorry i said the wrong word you need to manually kill the process from the terminal. No matter the state of the connection active or idle the process still runs in the background with a specific pid. So you kill it using its pid either using the terminal or from the SQL client. – Gourd

@Immersion can you point out where you are getting error in code on which line when running you app, procedure you are using to run and endpoint, also if you can please share stack trace – Barde

Answer 1

I've been struggling with the same problem while building this project and the only way I was eventually able to address this issue was by caching MockConnection/database_engine objects returned by SQLAlchemy's create_engine method into a global variable and reusing the same MockConnection object if it has already been created.

Baun

Isn't engine always created once at app initialization stage? If yes, then your code doing same, but in more complicated manner, no? If no, then there will be no any side effects because of manually cached engine? – Grantland

Nope. It has nothing to do with app initialization. Every time your app makes a call to the function create_engine, it creates a new engine. And the problem is, that one engine cannot reuse another engine's database connections because each engine has its own isolated pool of connections. – Baun

That's why if your app makes a call to create_engine for every session, you end up having too many engines, each allocating a pool of connections but not using them. – Baun

Answer 2

i have faced the same problem. but when i set preload_app=False in gunicorn file , then it gets fixed.

Jacki