SQLAlchemy takes too long to execute query in Cloud Run service connected to Cloud SQL

[JaimeFabian]

Hi everybody,

A little bit of context ...

I have deployed an API to a Cloud Run Service. This API has been developed using FastAPI and SQLAlchemy is being used to connect to a Cloud SQL Instance. A unix socket is being used to connect Cloud Run and Cloud SQL.

I'm working currently on performance and using pyinstrument to measure execution times in the code. While running the API locally, I'm getting the following results:

In this image above we can see that the execute function from the sqlalchemy connection takes 2.328 seconds which is ok for the moment but when I measure this while running on Cloud Run I get really bad results:

Here we can see that the execute function from sqlalchemy takes almost 20 seconds and I've also noticed that a socket.settimeout is taking almost 16 seconds to be executed, which is something that is not appearing when running the API locally. It is also worth noting that the queries being executed locally and on cloud run are exactly the same.

Does anybody know any performance issues while working with Cloud Run and Cloud SQL over a unix socket connection?

or if someone would have any suggestions or ideas regarding what could be causing this issue, I would very much appreciate any comments and/or help.

Best regards,

Jaime

[JaimeFabian · Accepted solution]

I'm posting some information in case someone else has similar issues 😊

• Make sure you are setting the workers for FastAPI correctly and not defaulting them to 1 if you need to process requests simultaneously without them blocking/delaying each other.
• Test your Query/Fetch times directly in your database. If you are having time differences between your database's times and your times in FastAPI using sqlalchemy then have a look at the amount of data that you need to transfer. We have a case where we are transfering 100.000+ rows and this takes around 2-3 seconds executing the query with sqlalchemy in python.

Since we have identified our bottlenecks, I'd like to set this post as resolved.

Best regards!

Jaime