Improving Delayed Response Times for Requests in FastAPI with Cloud SQL

[tayamaYuto]

Question

I'm developing with FastAPI and Cloud SQL, and when I try to handle a simple GET request like in my code, it takes about 8 seconds to retrieve information. If I send consecutive GET requests, the response time is faster, but after some time, it again takes around 8 seconds.

Since this response time is slow for an optimal user experience, I’m looking to resolve this issue. I suspect the connection establishment part of my code is not placed optimally, but do you have any ideas or know of any existing Q&A that addresses this?

Code

database.py

import os
from dotenv import load_dotenv

from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from google.cloud.sql.connector import Connector

load_dotenv()

Base = declarative_base()

class Database:
    def __init__(self):
        self.connector = Connector()
        self.engine: Engine = None
        self.SessionLocal: sessionmaker = None

    def init_connection_pool(self) -> Engine:
        instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
        db_user = os.environ["DB_USER"]
        db_pass = os.environ["DB_PASS"]
        db_name = os.environ["DB_NAME"]

        def getconn():
            conn = self.connector.connect(
                instance_connection_name,  # Cloud SQL Instance Connection Name
                "pg8000",
                user=db_user,
                password=db_pass,
                db=db_name,
                ip_type="public"  # "private" for private IP
            )
            return conn

        SQLALCHEMY_DATABASE_URL = "postgresql+pg8000://"

        engine = create_engine(
            SQLALCHEMY_DATABASE_URL,
            creator=getconn
        )
        return engine

    def init(self):
        self.engine = self.init_connection_pool()
        self.SessionLocal = sessionmaker(
            autocommit=False,
            autoflush=False,
            bind=self.engine
        )

dps.py

from db.database import Database

database = Database()
database.init()

def get_db():
    db = database.SessionLocal()
    try:
        yield db
    finally:
        db.close()

main.py

from db.dps import get_db
@app.get("/")
def test(db :Session = Depends(get_db)):
    # DB process
    return test

[jackwotherspoon]

Hi @tayamaYuto thanks for the question! 😄

I'm developing with FastAPI and Cloud SQL

Can you share where you are deploying your application? Is it GKE, App Engine, Cloud Run?

If it is Cloud Run then it sounds like you are application is having slow "cold starts".

From the Cloud Run docs:

Because instances are scaled as needed, their startup time has impact on the latency of your service. While Cloud Run de-couples instance startup and request processing, it can happen that a request must wait for a new instance to be started to be processed, this notably happens when scaling from zero. This is called a "cold start".

The Cloud Run docs have some recommendations for optimizing peformance.

As a side-note for the Python Connector in a serverless environment, we recommend setting the Connector to be in "lazy refresh" mode. This may also help reduce the cold start latency.

self.connector = Connector(refresh_strategy="LAZY")

If you are not deploying with Cloud Run, or continue to see additional request latency I will happily take a deeper look 😄

[tayamaYuto]

Thank you for your response.

Currently, I’m in the verification stage in my local environment and haven’t deployed yet, but I intend to use Cloud Run.

I understand that Cloud Run starts instances in response to requests. Since the setup and connection are happening repeatedly in my local development, I thought that the slow connection issues I’m experiencing locally might also occur in the deployed environment, which is why I asked.

If, in Cloud Run, the instance startup and request processing are indeed separated, then there’s a chance that the issue I’m concerned about might not occur. After deployment, if I still encounter the issue despite implementing the solution you provided, I’ll reach out again with further questions.

[jackwotherspoon]

@tayamaYuto Another suggestion for speeding up your overall application would be to use asyncio. The asyncpg driver allows async usage of the Cloud SQL Python Connector and SQLAlchemy async_sessionmaker would help you achieve this.

[jackwotherspoon]

@tayamaYuto any luck speeding up your coldstarts?