1

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?
Stuggi
  • 3,366
  • 4
  • 17
  • 34
Amaimersion
  • 111
  • 7

1 Answers1

1

You mention:

  • worker connections = 1024
  • threads = 1

but with gevent, the number of worker connections is the total number of (asynchronous) threads running at once.

As a result, you're trying to establish workers * worker_connections number of connections, which is definitely higher than you can sustain, based on pool_size and postgres limits defined.

Start at Postgres and work backwards:

  • Postgres = 20 connections max
  • instances = 1 (it seems like)?
  • gunicorn processes = 2
  • worker_connections = 10 = (20 / 2)
  • pool_size + max_overflow = 10 [same as worker_connections!]
  • pool_size = 10, max_overflow=0 or any combo you want
    • overflow connections get cleaned up asap; pool_size connections can stick around

worker_connections and pool_size+max_overflow are shared across threads, but not processes.

Note, as soon as you add another instance or anything that talks to the DB, it will use 1 (or more) of those precious 20 Postgres connections, so you'll need to adjust accordingly.

Dan Anderson
  • 11
  • 1