Slow home page stats load due to inefficient DB queries.

[jcborras]

Apache Airflow version

2.1.2

What happened

When loading AirFlow v2 home page subsequent requests include GET /last_dagruns, GET /dag_stats and GET /tasks_stats which in turn query the database (PostgreSQL in this case). These DB queries (most likely generated by an ORM) have the form:

SELECT ...
FROM ..
WHERE dag_id IN ('active_dag1', 'active_dag2, ...., 'active_dag_n`)

Which have one issue: unless the postgresql query optimizer figures out that those strings are actually PKs in some table the IN operation is carried out by doing a linear search which when the list of DAGs and the number of rows in the tables is high (ie. 300 actives dags and 10M rows) results in response times in the order of minutes not seconds.

This situation has unintended consequences:

• if the number of HTTP workers is low (ie. 3) it will prevent other users from loading any page.
• if your monitoring is based on the classic frequent GET /health request you are like to get timeout hence triggering alarms.

What you think should happen instead

I've made the queries run faster by creating a tmp table storing the values in the IN operator and then JOINing. That is:

SELECT ..
FROM ..
JOIN tmp_dag_id USING (dag_id)

Then the implicit indexes take care of it all. But that of course is a lot to ask to an ORM.

How to reproduce

You'll need a setup with a few hundred DAGs. If you run them often the db will pretty soon contain tables with millions of entries thus making the issue arise.

Operating System

Linux

Versions of Apache Airflow Providers

No response

Deployment

Other Docker-based deployment

Deployment details

Docker images which operations managed under AWS ECS and Fargate.

Anything else

N/A

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[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template!

[potiuk]

Please upgrade to latest 2.3 and repeat your investigtions. There were a LOT of optimisations done since 2.1 resulting in speeding up the home page immensely, not mentioning the fact that we have a new Grid View instead of Tree View now.

[potiuk]

You are looking at quite an out-dated version simply.

[jcborras]

I repeated the investigations using AirFlow .release:2.3.3+62778bb.. running on Docker (docker-compose.yaml provided by the AirFlow project). This setup is suitable for investigating the generated SQL code by the ORM. The issue still persists. It is not noticeable because once up and running the contents of the database are small but the generated SQL will not perform well as the number of rows increase.

An example again the relevant SQL code:

SELECT anon_1.dag_dag_id AS anon_1_dag_dag_id, /* ommited */
FROM (
  SELECT dag.dag_id AS dag_dag_id, /* ommited here */ 
  FROM dag
  WHERE NOT dag.is_subdag AND dag.is_active AND 
-- this is the relevant part:
dag.dag_id IN ('example_branch_datetime_operator', 'example_branch_labels', 'example_kubernetes_executor', 'example_weekday_branch_operator', 'example_external_task_marker_parent', 'example_complex', 'tutorial_taskflow_api_etl_virtualenv', 'example_dag_decorator', 'tutorial', 'latest_only_with_trigger', 'example_xcom_args', 'example_nested_branch_dag', 'example_passing_params_via_test_command', 'example_sla_dag', 'example_subdag_operator.section-1', 'example_task_group_decorator', 'latest_only', 'example_subdag_operator.section-2', 'tutorial_taskflow_api_etl', 'tutorial_etl_dag', 'example_trigger_target_dag', 'example_python_operator', 'example_branch_datetime_operator_2', 'example_bash_operator', 'example_branch_dop_operator_v3', 'example_xcom_args_with_operators', 'example_external_task_marker_child', 'example_time_delta_sensor_async', 'example_skip_dag', 'example_subdag_operator', 'example_branch_python_operator_decorator', 'example_short_circuit_operator', 'example_task_group', 'example_branch_operator', 'example_trigger_controller_dag', 'example_xcom', 'example_local_kubernetes_executor')
-- relevant part ends here
ORDER BY dag.dag_id LIMIT 100 OFFSET 0) AS anon_1
LEFT OUTER JOIN dag_tag AS dag_tag_1 ON anon_1.dag_dag_id = dag_tag_1.dag_id
ORDER BY anon_1.dag_dag_id;

The issue is with the IN operator: as the number of DAGs (all sorts: active and inactive) increase the set operator gets large and since it is not indexed (is not a table) the SQL optimizer resorts to a regular scan which as the number of rows in the dag or dag_run tables increase which results in long response times.

The irony is avoiding the IN operator and filtering out the results in the python calling code would fix the issue.

[potiuk]

If you see a solution, PR fixing it is most welcome. But you have to be careful to do it in the way that all supported databases (MySQL/Postgres/Sqlite). We had many such improvements in various parts., and the best way is to add PR fixing it (as it happened many times in the past by various contributors).

[L41and14L]

I know this post is old but I wasn't getting anywhere with the same symptoms for the past few weeks. We have local development VMs that were working fine with the homepage UI load but migrating to our more secure AWS solution is where we ran into issues. The setting that reaches out to airflow to pass data is what was causing the problem. Runs, Last Run, and Recent Tasks all spun for minutes before finally showing results. Specifically, I changed the variable AIRFLOW__USAGE_DATA_COLLECTION__ENABLED from enabled=True to enabled=False. Those fields populate almost instantly now

[jscheffl]

Which Airflow version are you using?

[kaxil]

The Usage Data collection was removed from Webserver in Airflow 2.10.3 via #43348 / #43346

[L41and14L]

@jscheffl we are using 2.10.1 at the moment. Thank you @kaxil for the information as I was planning on updating in the near future as we introduce airflow to our test team.