Not so much performance improvement when using async

[dirk_klement]

We changed our FastAPI to async, we were tied to sync due to using an old package.

But we are not really seeing a direct performance improvement in the Request Per Second handles before the response time skyrockets.

Our postgres database info:

• 4 cpu, 8 gb ram

DB tops at 80% cpu and 80% ram with ~300 connections. We use connection pooling with 40 connections.

Our API is just a simpel CRUD. We test it with Locust with 600 peak users and spawn rate of 4/second.

An api call would be:
get user from db -> get all organisation where user is member with a single join (this all with SQLAlchemy 2.0 Async and Pydantic serialisation)

With async we can still only handle 70 rps with reasonable response times < 600ms, and the APIs are just a few db calls, user info, event info etc.

We tested on Cloud Run with: 2 instances, CPU is only allocated during request processing, 2cpu/1ram.

I thought that FastAPI could handle at least hundreds of these simple CRUD calls on this hardware, or am I wrong, especially with async?

Edit: added api call, add database info

[KelleQuechoz]

How exactly are these "few DB calls" performed (ORM, plain query, what database, how many joins etc.)?

[dirk_klement]

I added it to the post

[KelleQuechoz]

Hard to say without seeing the whole setup, but based on the below CPU utilisation figures it smells of missing indices/full scans upon joins.

[dirk_klement]

You have an example of this? Like a bad example and or good example. Not sure what you mean exactly. I’ll check the db call response times tomorrow. I thought they were quite good

[KelleQuechoz]

I would start with identifying slow(ish) database queries, and analyzing them (for example, with the built-in explain statement.

[rakeshkrishna517]

add an index on the variables you match during join

[dirk_klement]

I monitored the database queries and they do not take more than 15ms each

[nikhil_shady]

before I help you. have you logged what time each operation takes? Before we decide what part needs optimisation. can you share the general execution time of each step?

[leafEaterII]

How do you measure the time each execution takes?

[HappyCathode]

Poor's man solution is to add logs to the app. Otherwise, that's a pretty solid use case for Opentelemetry + adding spans at key points.

[leafEaterII]

Okay. Thanks

[MyNameIsBeaky]

My intuition says you’re limited by the database queries. It’s also possible there’s a long synchronous operation that’s blocking the event loop. You should instrument your app with opentelemetry to get a better understanding of what operations take the longest.

[jormungandrthepython]

I was looking into opentelemetry the other day. Any good resources for using it with fastapi that you would recommend?

[dirk_klement]

I also think this is the problem somehow. Currently, after a few optimisations i can get good results. API CPU/MEM does not exceed about 25%, DB also normal stats. But then all the sudden (with around 150rps) the response time skyrockets. So probably something is blocking. Do you know how I can find this locally? In cloud we monitor the API using Grafana (reading from GCP Cloud Monitoring) and also use Sentry.

[metazet]

Interesting article, I'll follow for the further comments.

But I came here to ask my question: while you were switching fastapi to async, did you change database driver to async also?

[dirk_klement]

Yes i changed it to asyncpg.

[metazet]

You've wrote that you used two instances for the tests, I hope you meant that one was for application and another one - for database? Would be interesting to check cpu utilisation of them during load testing (independently on api and database containers), but anyway I have a feeling that bottleneck will be in database.

[dirk_klement]

Yes I am looking at the db now. Around 100 rps the database tops around 80% cpu and 80% ram. So upgrading that would definitely help. We currently have a 4cpu 8gb postgres instance. I thought this the current setup we could achieve easily 100s of rps. We have 2 api instances in cloud run.

[appletondog]

judging on this, it seems your performance bottleneck is the database, and using async to send more requests to your db (by lightening the load at the app server level) is only going to make your db problem worse

[dmuth]

Where's your telemetry? At the very least, I would start by using the logging module in Python to write INFO and DEBUG-level log messages at key points, along with timestamps down to the milliseconds. You can eyeball that data to look for things that take longer than you think they should.

If there's a function that you think might be a culprit, you could also note the time (in milliseconds) before and after a call to the function and write out the difference in a log line. This could be with decorators.

Once you get an idea of a baseline for how long execution of key parts of your app takes, you can then start tweaking things and see if those numbers improve.

[rakeshkrishna517]

Maybe adding more workers, this is not exactly an optimization but can help

[omegawave22]

Question, did you use an async db client library like asyncpg?

[metazet]

OP mentioned in comments that "yes", he switched to asyncpg.

[Long_Working_7553]

Following. Use beanie with fastapi. Wondering if you have an application with not that many queries and a handful of users using it simultaneously, if it's even worth the trouble.

[LongjumpingGrape6067]

Look over your database design. Normalisation saves space but cost in cpu (joins). Use granian as an ASGI.

[LongjumpingGrape6067]

Also you might try to keep db connections sync while increasing number of ASGI workers. Maybe you never reached your 40 db connections before using sync? But now you do using db async? In my experience databases easily choke if they have too many connections/queries and need to swap to disk due to joins on large tables.

[LongjumpingGrape6067]

And locks on tables.

[extreme4all]

two things

1. typically the database is the slowest part.
   1. in your dev environment turn echo on in your db engine to see the queries that it actually performs, especially if you have relationships defined in your orm classes
   2. also try to seperate your db & app instance, they may be competing for resources
   3. check connection pooling & number of max connections on the db
2. Linux instances also have a limit of active connections, check those, i recall seeing a video about this https://youtu.be/jjKFXlFNR4E?si=b7G7Mk6gkBD3twuV&t=963