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

Question

I have a Flask-SQLAlchmey app running in Gunicorn connected to a PostgreSQL database, and I'm having trouble finding out what the pool_size value should be and how many database connections I should expect.

This is my understanding of how things work:

• Processes in Python 3.7 DON'T share memory
• Each Gunicorn worker is it's own process
• Therefore, each Gunicorn worker will get it's own copy of the database connection pool and it won't be shared with any other worker
• Threads in Python DO share memory
• Therefore, any threads within a Gunicorn worker WILL share a database connection pool

Is that correct so far? If that is correct, then for a synchronous Flask app running in Gunicorn:

• Is the maximum number of database connections = (number of workers) * (number of threads per worker)?
• And within a worker, will it ever use more connections from a pool than there are workers?

Is there a reason why pool_size should be larger than the number of threads? So, for a gunicorn app launched with gunicorn --workers=5 --threads=2 main:app should pool_size be 2? And if I am only using workers, and not using threads, is there any reason to have a pool_size greater than 1?

Sclerotomy

Answer 1

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.

Varityper

Answer 2

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).

Vinna

Answer 3

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).

Ohl

Answer 4

I think you have it completely right. The highest scored answer here is referencing gevent but your question is with sync workers. For clarity, sync workers are technically "gthread" workers as soon as you start using the "--threads" param.

As for the second highest answer by @matino, it is stated that you need to increase your max pool size if you increase your number of workers. Based on your explanation and @matino's agreement, this would not be true. You would only need to change your max pool size if you are increasing the number of threads because the pool is only shared by threads, not processes (workers).

To answer the questions clearly:

• The maximum number of connections is (number of workers) * (min(number of threads, max connection pool size))
• Phrasing is a little off here, but I believe you are asking if within a worker, will it ever use more connections from a pool than there are threads. The answer would be no. The thread would never create a new connection if there is already an idle one in the pool.

Gerhardine