potiuk commented on issue #27296:
URL: https://github.com/apache/airflow/issues/27296#issuecomment-1455062224
> Hi, may I know if there is anything else that should be taken into account
regarding this issue? Actually we are running our Airflow (2.5.0) environment
with the KubernetesExecutor and lock wait timeouts are happening quite often
too. We are setting:
>
> * parallelism = 1024
> * max_active_tasks_per_dag = 16 (for some DAG, it is 32)
> * max_active_runs_per_dag = 8
> * max_tis_per_query = 1024
>
> If setting parallelism to a high value is considered an issue, may I know
the reason why? We are using MySQL 8, transaction_isolation = REPEATABLE-READ,
innodb_lock_wait_timeout=50s. One difference is that after failing, the tasks
are being retried. We have not tried disabling `schedule_after_task_execution`
yet, but as far as I notice, lock wait timeouts are happening in various cases,
not only when running mini scheduler, but also when it tries deleting old
records of `rendered_task_instance_fields` table. A related issue: #29687
(however, we are not hitting deadlock errors but timeouts instead
>
> ```
> sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1205, 'Lock
wait timeout exceeded; try restarting transaction')
> [SQL: DELETE FROM rendered_task_instance_fields WHERE
rendered_task_instance_fields.dag_id = %s AND
rendered_task_instance_fields.task_id = %s AND
((rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id,
rendered_task_instance_fields.run_id) NOT IN (SELECT anon_1.dag_id,
anon_1.task_id, anon_1.run_id
> FROM (SELECT DISTINCT rendered_task_instance_fields.dag_id AS dag_id,
rendered_task_instance_fields.task_id AS task_id,
rendered_task_instance_fields.run_id AS run_id, dag_run.execution_date AS
execution_date
> FROM rendered_task_instance_fields INNER JOIN dag_run ON
rendered_task_instance_fields.dag_id = dag_run.dag_id AND
rendered_task_instance_fields.run_id = dag_run.run_id
> WHERE rendered_task_instance_fields.dag_id = %s AND
rendered_task_instance_fields.task_id = %s ORDER BY dag_run.execution_date DESC
> LIMIT %s) AS anon_1))]
> [parameters: ('my_dag', 'my_task', 'my_dag', 'my_task', 30)]
> ```
>
> Currently we have been running a crontab to kill long-sleeping connections
to MySQL from Airflow and it seems to work but we have not found the root cause
yet.
@tanvn Yes I tihnk #29687 is the right one. I think what could help
temporarily is to set the `max_num_rendered_ti_fields_per_task` to 0 . We
realize that this query might be problematic on mysql especially and we are
discussing with @Taragolis and @kaxil the right way of solving it.
The side effect of setting this value to 0 is that if you have a lot of
rendered task instance fields and their size is significant, you might end up
with quickly growing size of the Database. But you can in modern airflow to
periodically run `airflow db clean` cleaning that table.
Can you please set it and observe your DB and let us know - ideally in
#29687 the db size growth and whether it fixed your problems ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]