Webserver is slow after upgrading to v2.9.3

[TreasureMaster]

Apache Airflow version

2.9.3

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

No response

What happened?

We have updated the version from 2.6.2 to 2.9.3. After the update the main page opens very slowly. The 'failed' button causes Airflow to fail due to a timeout.
Start the discussion here

What you think should happen instead?

We have 3200 Dags now. And their number is increasing.
The slowdown is caused by line 855 in the file /airflow/www/views.py:

status_count_failed = get_query_count(failed_dags, session=session)

But not all of it, but only that part of it that filters dags by access rights. We commented out line 799 to prevent filtering by IN. This row:

dags_query = dags_query.where(DagModel.dag_id.in_(filter_dag_ids))

This filter condition is not optimal when querying and is very slow. The slow part is highlighted in the query

It is converted from this line:

WHERE NOT dag.is_subdag AND dag.is_active AND dag.dag_id IN (__[POSTCOMPILE_dag_id_1])

The entire request takes between 60 and 70 seconds on our main page. But without this filtering part, the request takes just over 1 second.
My dbeaver hangs during DB query due to lack of memory.

Is it possible to optimize this query? Or split it into parts and filter by permissions it using Python.

How to reproduce

You need to have a large number of dags and dagruns.

Operating System

CentOS 7

Versions of Apache Airflow Providers

No response

Deployment

Docker-Compose

Deployment details

Our infrastructure:

1. database server (Postgres 13)
2. server with Airflow webserver, scheduler and Redis
3. three servers with workers

Modified bitnami images are used. Additional libraries installed.

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.

[tirkarthi]

Related

#38776
#40547

[jscheffl]

I would assume a setup with 3000+ DAGs is nothing that is running w/o special attention in regards of performance. Re-writing the queries of course is possible but also the complexity of data selection might be expensive.

Plans are ongoing to re-write the UI and also make async calls in a future Airflow 3, I would not expect a major investment in current Airflow 2.10-line.

Some options that might help compensating - as you are already in level of patching code:

• Have you attempted to analyze the query and add more resources to the DB to improve queries or add specific indexes?
• Do you use DAG level permissions? If not, as you are patching the code, removing the DAG access level filter might be a simple option to improve query
• Have you tested with Airflow 2.10 if this improves the situation?
• Would you be willing to supply a PR as performance patch?

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days with no response from the author. It will be closed in next 7 days if no further activity occurs from the issue author.

[github-actions]

This issue has been closed because it has not received response from the issue author.