sqlalchemy queue pool limit lockup/timeout

[jklaw90]

While doing some testing to see how this fastapi with sqlalchemy would hold up, my server seemed to lock up when running 100 concurrent requests. If i ran the requests sequentially it was totally fine.

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached,

Is it possible I've made some error while trying to mimic the structure of the code base?
Or is it possible the main can lock up in the middleware with the session implementation of sqlalchemy?

Has anyone tested the performance of this cookie cutter project?

[jklaw90]

Running the test attached i was able to reproduce the failed responses for a majority of the requests on this cookiecutter project.
And only a few requests can even respond before it seems to lock up the main, could this have something to do with the management of the session in the async middle-ware https://docs.sqlalchemy.org/en/13/orm/session_basics.html#is-the-session-thread-safe ?

I know this isn't the most scientific test but i just wanted to prove out this repro before switching to something like it.
Results:
200: 47 - 17 before the lock up and 30 of them after the sqlalch timeouts)
500: 53
98 - 98 requests took longer than 2 seconds to respond.

import aiohttp
import asyncio
from datetime import datetime

headers = {
    "Authorization": "Bearer xxxxxxxxxxxxxxxxxxx",
    "Content-Type": "application/json; charset=utf-8"
}

timeout = aiohttp.ClientTimeout(total=60)

async def get(url):
    async with aiohttp.ClientSession(timeout=timeout) as session:
        s = datetime.now()
        async with session.get(url, headers=headers) as resp:
            return resp.status, datetime.now() - s

loop = asyncio.get_event_loop()

coroutines = [get("http://localhost/api/v1/users/me") for i in range(100)]


results = loop.run_until_complete(asyncio.gather(*coroutines))

codes = {}
count = 0
for s, r in results:
    codes[s] = codes.get(s, 0) + 1
    if r.seconds > 2:
        count += 1

print(codes, count)

[chdsbd]

I think this is related to #56.

[jklaw90]

Yeah, I guess people just use this for convenience and don't have serious spikes of traffic with fastapi + sqlalchemy? Maybe the people who actually are using it in production are using one of the other async db clients.

[ebreton]

For information, I also ran on the 100 requests limit, which was actually due to the default configuration of the postgres DB.

Launching the DB with more connections (500 in my case) solved the issue. You just have to modify the docker-compose by adding the following line in the postgres service description:

services:
  postgres:
    ...
    command: postgres -N 500

[jklaw90]

@ebreton What was the default connection limit on that postgres container and did you also increase the pool size in sqlalchemy? I understand that would fix this issue, but the main shouldn't get blocked and timeout after 30 seconds in any condition with only 100 concurrent requests. Running all 100 requests sequentially works perfectly fine and finishes in no time.

I don't know fastapi/starlette and sqlachemy that well so I'm a little confused if the main is grabbing a session but blocking when the QueuePool is full? If so is it possible we could see better results by just changing the sqlalchemy configurations?

[ebreton]

Hi @jklaw90

The default configuration in the official postgres container is 100, there is an example of overriding this value on the docker hub repo 1. I upgrades it to 500 for the sake of doing it only once. 😄
The default size of a SQLAlchemy pool is 5 2. I also increased it to 20 (keeping the default overflow to 10)

I agree that 100 concurrent requests works fine. In my case, I have actually just realized that I made an entirely different mistake, which is to serve (a lot of) pictures directly from the app. I am going to replace this by a NGinx, and I will probably be able to come back to the default SQL values in the app...

Thanks for the question 🎉

[jklaw90]

Ahh thank you for the resources and insight!

I am still a little confused on why even with default 100 connections + pool size of 5 we would get timeouts rather than just getting responses slowly?

If there is some simple fix i'd love to know, really looking to switch from falcon to fastapi.

[unmade]

@jklaw90

That's interesting! I have slightly different setup for my app and I couldn't reproduce the error you have, but occurred with another one. I don't have middleware and don't use Depends to get session. Instead, I create session directly in the endpoint.

I refactor my app to use Depends and have exactly the same error you got. Strangely enough, but with PYTHONASYNCIODEBUG=1 everything works fine.

So, I guess the problem might be in how Depends work, although I haven't looked at it yet. You might want to try to get rid of the session middleware and Depends and use custom context manager for session instead, something like:

from contextlib import contextmanager
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(...)

@contextmanager
def SessionManager():
    session = Session()
    try:
        yield session
    finally:
        session.close()

And then you can use it in you endpoint like that:

@router.get("/")
def hello_world():
    with SessionManager() as db_session:
        ...

So, with a setup like that I also tried to fire 100 concurrent requests and got this error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already

However, if I set WEB_CONCURRENCY=1 (this env specifies number of workers for gunicorn - in this case it will run gunicorn with a single worker), everything is fine.

I think I got this error, because gunicorn spawns several workers (you can see how the number of worker is calculated here). Each worker thinks it has pool size of 100 available to it, when it is actually db_pool_size / number_of_workers:

So as a fix I created engine like this:

DB_POOL_SIZE = int(os.getenv("DB_POOL_SIZE", "100"))
WEB_CONCURRENCY = int(os.getenv("WEB_CONCURRENCY", "2"))
POOL_SIZE = max(DB_POOL_SIZE // WEB_CONCURRENCY, 5)

engine = create_engine(config.DATABASE_DSN, pool_size=POOL_SIZE, max_overflow=0)

Note: WEB_CONCURRENCY calculated a little bit more complicated

The problem is gone and everything seems to work fine

[vumaasha]

@tiangolo @dmontagu facing the same issue. When the number of concurrent requests is higher than the number of available of connections ( max pool size + overflow), many requests just time out. Are we doing something wrong? is there a way to fix it?

[unmade]

@vumaasha can you please try solution I described above and see if it helps?

[vumaasha]

@vumaasha can you please try solution I described above and see if it helps?

I make heavy use of Depends, getting rid of Depends will need lot of refactoring for me. So it is not an option.

I only tried the part corresponding to setting pool size. It didn't help.

[stratosgear]

For me it seems to work (no more timeouts and I do not exceed the default of 100 open connections).

But I have the impression that using the SessionManager slows things down. Does this make sense?

I am running some loadtesting scenarios with locust and I will report back.

[vumaasha]

@stratosgear you wont get timeout if you don't exceed number of available connections. Thats right but when it does exceed, it can be slow it shouldn't be timing out. Increasing the timeout also doesnt help

[stratosgear]

If I can explain my issue correctly it was like this:

• My requests are usually replying in 200ms (on the average)
• When I would try hitting the server with 300 concurrent users, I would exceed the 100 open connections of postgres.
• After removing the Depends(get _db) and using the SessionManager (from above) I never went more that 25-30 db connections ( I was monitoring from pgadmin) all other things being equal (number of concurrent users, duration of test etc)

Unfortunately now I see that my response rate is larger than 1000ms and I'm wondering what causes this. I still have to clean things up and try the locust scenarios again.

[stratosgear]

Sweet!
I have no extra overhead with the SessionManager, after rerunning my load testing scenarios.

One other configuration change I have is when I instantiate the sqlachemy engine:

engine = create_engine(
    config.SQLALCHEMY_DATABASE_URI, pool_pre_ping=True, pool_size=32, max_overflow=64
)

That ought to be able to utilize all 100 default Postgres connections, but my load tests anyway rarely seem to utlize more than 25-30 connections (now with the SessionManager)

🍺 to @unmade (or 🍰, 🍵 or 🥃 or whatever rocks his boat...)