airflow: MappedTasks: Very slow execution :: taskinstance.py seems to be a bottleneck.

Apache Airflow version

2.7.2

What happened

We have a Dag with 116 tasks each having some number of MappedTasks. Overall number of tasks instances is ~6000. Problem is that we encounter very slow execution. Investigation and testing with the code below has shown that we have ~110 open connections with SQL: SELECT dag_run.state AS dag_run_state, … FROM dag_run WHERE dag_run.dag_id = … AND dag_run.run_id = … FOR UPDATE .

In logs it’s seen as delay caused by taskinstance.py: image

import logging
from datetime import datetime
import pandas as pd, numpy as np
from airflow.decorators import dag, task, task_group
from airflow.operators.python import get_current_context
from airflow.models.param import Param

@dag(start_date=datetime(2023, 10, 29), schedule=None,max_active_runs=1, max_active_tasks=16,tags=["TESTS", "MAPPED TASKS"], description="",params = {"MappedNumber": Param(50,type="integer",title="Number of dynamically created Mapped tasks",},
def mapped_tests():

    @task(trigger_rule="none_failed")
    def mapped_instantiator():
        context = get_current_context()
        MappedNumber= context["params"]["MappedNumber"]
        return [x for x in range(MappedNumber)]

    @task(trigger_rule="none_failed")
    def mapped_instance(num):
        print(f"!!!!!!!!!!!!!!!!!!!!!!!! I'M NUMBER: {num} !!!!!!!!!!!!!!!!!!!!!!!")
        return num
    
    @task
    def join_all():
        num = 1
        print(f"MY NUM: {num}")
        
    first = mapped_instantiator.override(task_id="first")()    
    join_all = join_all()

    mapped_one = []
    cascade_groups = []    
    for x in range(8):
        mapped_one.append(mapped_instance.override(task_id=f"first_level_{x}").expand(num=first))    

        # do cascading
        @task_group(group_id = f"cascade_group_{x}")
        def cascade_group():
            for y in range(3):
                mapped_instance.override(task_id=f"cascade_{x}_{y}").expand(num=mapped_one[x])    
        cascade_groups.append(cascade_group())
        mapped_one[x] >> cascade_groups[x]
        
        cascade_groups[x] >> join_all
    
dag_id = "mapped_tests"
globals()[dag_id] = mapped_tests()

What you think should happen instead

Invetigation required on taskinstance.py code.

How to reproduce

See code above. With pgbouncer allow 150 connections. Then check query stat by: select query,wait_event_type,wait_event,count(*), min(now() - backend_start), max(now() - backend_start) from pg_stat_activity WHERE usename = 'airflow_user'::name GROUP BY 1,2,3 ORDER BY 4 DESC;

Operating System

Ubuntu on Linux and under WSL2

Versions of Apache Airflow Providers

2.7.2

Deployment

Docker-Compose

Deployment details

Tested on Docker, Python 3.8-310, under Celery and LocalExecutor.

Anything else

No response

Are you willing to submit PR?

  • Yes I am willing to submit a PR!

Code of Conduct

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 3
  • Comments: 20 (17 by maintainers)

Most upvoted comments

This could be related to this PR, and looks like it was released in 2.9.1:

https://github.com/apache/airflow/pull/38914

We observed that when you have a bunch of mapped tasks, running the “mini scheduler” can result in too many processes waiting for the same lock on the same table. With that change, it won’t wait if it’s already locked.

I don’t recall anything similar reported elsewhere. I can imagine the FOR UPDATE part causing this though since it can saturate the database with all workers doing it at the same time, especially if you don’t have a good combination of the database connection limit and worker count.

Is is possible to get the full SELECT … FOR UPDATE query you mentioned in the top message? That would help a lot pin down the problem.

I think 2.9.1 has another issue. We started to see:

2024-05-17 09:49:19.191 UTC [3586765]: [131-1] db=airflow,user=airflow-cloudsqlproxy@service-dev.iam ERROR:  could not obtain lock on row in relation "dag_run"

in DB logs after upgrading to 2.9.1. P.S. I’m fine to open new issue.

@dstandish, thanks a lot. I see that after upgrading to 2.9.1 my issue is gone. All queries are fast again and I don’t see locks in DB.

I managed to spot this problem a bit. When our DAG starts with 1000+ Dynamically Mapped Tasks then we are experiencing significant performance issues. UI is slow, warnings that scheduler or triggerer are not available. I found that only impacted metrics are from pgbpuncer and our postgres SQL instance(on GCP) started to report about DB locks. Below spikes correlate with the time when this DAG with Dynamically Mapped Tasks is running: Screenshot 2024-05-14 at 20 32 05 I selected what are the long running queries from pg_stat_activity: Screenshot 2024-05-14 at 20 57 44 The one of the frequently running query looks like:

SELECT dag_run.state AS dag_run_state, dag_run.id AS dag_run_id, dag_run.dag_id AS dag_run_dag_id, dag_run.queued_at AS dag_run_queued_at, dag_run.execution_date AS dag_run_execution_date, dag_run.start_date AS dag_run_start_date, dag_run.end_date AS dag_run_end_date, dag_run.run_id AS dag_run_run_id, dag_run.creating_job_id AS dag_run_creating_job_id, dag_run.external_trigger AS dag_run_external_trigger, dag_run.run_type AS dag_run_run_type, dag_run.conf AS dag_run_conf, dag_run.data_interval_start AS dag_run_data_interval_start, dag_run.data_interval_end AS dag_run_data_interval_end, dag_run.last_scheduling_decision AS dag_run_last_scheduling_decision, dag_run.dag_hash AS dag_run_dag_hash, dag_run.log_template_id AS dag_run_log_template_id, dag_run.updated_at AS dag_run_updated_at, dag_run.clear_number AS dag_run_clear_number
FROM dag_run
WHERE dag_run.dag_id = 'retryable_dag' AND dag_run.run_id = 'scheduled__2024-05-14T08:00:00+00:00' FOR UPDATE;

I tried to run this query with and without FOR UPDATE(doesn’t really matter). When dag is running it takes 3-4 min, most of the time it is waiting for the lock. When I pause the DAG exactly the same query takes ~1s. We have hundreds of other DAGs running at the same time and haven’t seen such issue. My suspicion is: running Dynamically Mapped Tasks are the source for locks in DB

I’m having similar issue with Dynamically Mapped Tasks for 2.8.4. What is a best way to fix it?