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
toNullPool
; - for SQLAlchmey set
pool_recycle
to 2. Number of connections still the same even after more than 2 seconds; session.close()
withengine.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
, SQLALchmeypool_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 withgevent
workers? i.e., how can i count maximum number of DB connections? - How should I configure it correctly so that it works as expected?