Slow API server website load times

[trau-sca]

Apache Airflow version

3.1.0

If "Other Airflow 2/3 version" selected, which one?

No response

What happened?

Hi, we've been battling issues for the past few weeks on our api-server pod being very slow to display the website information (up to a minute to load the dags card screen). Sometimes the api server pod even restarts due to the liveness probe. I don't see any errors when it happens either.

Quite a few requests look long, but the biggest one is dags?dag_runs_limit=14&limit=50&offset=0&exclude_stale=true&order_by=-dag_display_name taking 51s, but even dags?is_favorite=true takes 4 seconds.

When remoting into the dag-processor and doing a curl command for the first one - it also takes 51s. I used the private endpoint assigned to the api-server while I was in the dag-processor.

We are running on airflow 3.1.0 and the 1.18.0 helm chart in an Azure Kubernetes cluster with an Azure PostgreSQL database.
No pods are going past their resource requests when this issue occurs, and the resources for the database are very low in usage.

I have disabled sqlalchemy pooling, and use pgbouncer in airflow as well as in the PostgreSQL database.
I have the following configuration for airflow:

pgbouncer:
    enabled: true
    maxClientConn: 500
    metadataPoolSize: 50
    resultBackendPoolSize: 10
    extraIni: |
      pool_mode = transaction

On PostgreSQL, I have pgbouncer's pool_mode set to session, and the default_pool_size is 100.

We have 16 DAGs, and have a daily job that runs a db clean for anything older than 90 days.

What you think should happen instead?

No response

How to reproduce

Clicking on DAGs card screen - I have 16 DAGs running with an Azure PostgreSQL backend.

Operating System

Azure Kubernetes

Versions of Apache Airflow Providers

No response

Deployment

Official Apache Airflow Helm Chart

Deployment details

Azure PostgreSQL 16.9, Azure Kubernetes v1.32.7

Anything else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[ronaldorcampos]

We are experiencing the same on both 3.0.6 and 3.1.0, running on AWS EKS backed by mysql. Loading dags takes a long time.

[vikramkoka]

@bbovenzi and @pierrejeambrun Wanted to put this on your radar

[ashb]

@trau-sca Can you include the full URLs that are particularly slow please?

[trau-sca]

@ashb, Sure, I used the following url in my curl command within the dag-processor pod: 10.244.4.234:8080/ui/dags?dag_runs_limit=14&limit=50&offset=0&exclude_stale=true&order_by=-dag_display_name. This was the endpoint assigned to the api-server

[bbovenzi]

@trau-sca How many dag runs do you have? I suspect that querying the dag runs table is the issue

[trau-sca]

@bbovenzi We have 565 rows in our dag_run table

[bbovenzi]

Huh, yeah that shouldn't matter at all. What about manually pinging /api/v2/dags?