Dag with high number of branches is slower than dags triggered in a cmd loop

[Alaeddine22]

Hello,

I have the same tasks in two different dags. One dag with 200 branches (DAG_BRANCH_200) and the second one having only the tasks (SIMPLE_DAG) but executed 200 times from the command line.

DAG_BRANCH_200:
dag_with_200_branches.txt
launched from the airflow UI.

SIMPLE_DAG:
simple_dag.txt
launched with the following command for i in $(seq 1 200); do airflow dags trigger SIMPLE_DAG; done.

NB:
I have the following parameters
`default_pool=128

parallelism = 100
max_active_tasks_per_dag=100

max_active_runs_per_dag=100
sql_alchemy_pool_size=100`

I'm attaching two screenshots of my postgresql dashboard when the both dags are running:
DAG_BRANCH_200:

SIMPLE_DAG:

Here we can notice that for the DAG_BRANCH_200 we have lot more sessions but only a few active and less transactions per second compared to SIMPLE_DAG?
and for the SIMPLE_DAG we have less database sessions but the transactions per second are far more frequent.

As a result the execution with cmd line finishes faster and without errors on the opposite of the execution of the dag with branches having x3 the time of execution and getting stuck with the error sorry, too many clients already and for other errors i can't find the log files (same as #21624 ?..)

I can't find an explanation for this behavior..
Shouldn't the execution with branches be faster than cmd ?
I'm missing something else ?

Thank you in advance for your reply.

[potiuk]

You are probably not using PBBouncer. See the note in the docs. https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html#setting-up-a-postgresql-database

Airflow is known to open big number of DB connections and Postgres is known to open a separate process for each connection. PGBouncer is known to provide a fantastic way to join the two - seemingly conflicting requirements. Especially when you attempt to do any kind of artifficial benchmarking like that.

I think it would be good to repeat your tests with well configured PGBouncer.

BTW. I Am note sure what you are referring to as "with branches" - the dags of your shows many task groups, but I am not sure what "branches" means in this context, this naming seems to be somewhat conflicting with "branching" concept use in Airflow DAGs, so I think you should elaborate on it a bit, if your tests with PGBouncer will show some strange results you cannot explain.

[Alaeddine22]

Thank you for the reply.
Indeed i'm not using PgBouncer. Will redo my tests.
With branches i mean parallel execution of taskgroups and i'm not referring to "branching" concept use in Airflow DAGs.

In the mean time i will mark the question as resolved

[Alaeddine22]

@potiuk, note that it is the same behavior even when using BranchPythonOperator in DAG_BRANCH_200

[potiuk]

Well if you have 200 parallel tasks then you have 200 parallel connections open to Postgres if you do not have PGBouncer. This WILL get terribly slow unless your postgres has vast amount of memory to fit all those 200 processes in. In postgress every session is a process that allocates quite some memory - and that's why PGBouncer is absolutely necessary.

What happened most likely, was that your postgres simply started to swap all those processes on disks or even your Postgres simply crashed periodically or attempted to clean-up some memory (the spikes up/down suggest it).

[Alaeddine22]

I've installed pgBouncer, changed the port value in the connection string for airflow and ran some tests with it.

As you can in the screen shot i'm no longer having ups and downs in database sessions however i'm still having errors with no log. The tasks prior to these errors have the following message in their logs INFO - 0 downstream tasks scheduled from follow-on schedule check.
I'm still also can't see a significant drop in execution time..
I'm using the following configuration for pgBouncer:
pool_mode = transaction / server_reset_query = RESET ALL / server_check_delay = 30 / max_client_conn = 3000 / default_pool_size = 60 / min_pool_size = 20 / reserve_pool_size = 5 / max_db_connections = 50

I tried to increase airflow configuration mentioned above and i had the same errors (tasks failing with no log).

Other thing that i noticed in the gantt of the executed dag is that sometimes tasks take a lot more time to execute with no obvious reason knowing that it is a simple echo for all the tasks...
I'm using a machine with 8 CPUs. Is it little ?
Any explanation, ideas to make it faster ?
Thank you in advance