Choosing DB pool_size for a Flask-SQLAlchemy app running on Gunicorn

Just adding some of my own recent experience to @matino's answer. WSGI applications can also benefit from async workers. I will add some points about async workers and connection pools here.

We recently faced some similar issues on our production. Our traffic sky-jumped in 1-2 days and all the requests were getting clogged for some reason. We were using gunicorn with gevent async workers for our django application. Turned out psql connections were being the reason for many of the requests getting stalled (and eventually timing out).

The suggested number of concurrent requests is (2*CPU)+1. So in a sync scenario, your calculations would be like: (workers_num * threads_num) <= (2 * cores_num) + 1

And you will get (workers_num * threads_num) max connections to your database. (say, all requests have db queries). Therefore you will need to set your psql pool_size setting to something greater than this number. But when you use async workers, calculations will be a little different. Look at this gunicorn command:

gunicorn --worker-class=gevent --worker-connections=1000 --workers=3 django:app

In this case, maximum number of concurrent requests can get upto 3000 requests. So you should need to set your pool_size to something greater than 3000. If your application is IO bound, you will get a better performance with async workers. This way, you will be able to utilize your CPU more efficiently.

And about connection pooling, when you use a solution like PgBouncer, you are getting rid of overhead of opening and closing connections all the time. So it will not affect your decision about setting your pool_size. The effects might not be noticeable in low traffics, but it will be a necessity for handling higher rates of traffic.

Adding my 2 cents. Your understanding is correct but some thoughts to consider:

• in case your application is IO bound (e.g. talking to the database) you really want to have more than 1 thread. Otherwise your CPU wont ever reach 100% of utilization. You need to experiment with number of threads to get the right amout, usually with load test tool and comparing requests per second and CPU utilization.

• Having in mind the relation between number of workers and connections, you can see that when changing the number of workers, you will need to adjust the max pool size. This can be easy to forget, so maybe a good idea is to set the pool size a little above the number of workers e.g. twice of that number.

• postgresql creates a process per connection and might not scale well, when you will have lots of gunicorn processes. I would go with some connection pool that sits between your app and the database (pgbouncer being the most popular I guess).

I'd say your understanding is pretty good. Threads within a single WSGI worker will indeed share a connection pool; so theoretically the maximum number of database connections is (number of workers) * N where N = pool_size + max_overflow. (I'm not sure what Flask-SQLAlchemy sets max_overflow to, but it's an important part of the equation here - see the QueuePool documentation for what it means.)

In practice, if you only ever use the thread-scoped Session provided to you by Flask-SQLAlchemy, you will have a maximum of one connection per thread; so if your thread count is less than N then your upper bound will indeed be (number of workers) * (number of threads per worker).