GitHub user Ahsan221B added a comment to the discussion: Task starts time out on PUT /rtif after the 3.3.2 to 3.4.0 upgrade
+1 to @OswaldoRodriguez123's analysis. The unscoped v2 DELETE plus the unconditional v1 DELETE explains both wait-event patterns you captured. A few additions that may help with the issue and with preventing a repeat: ### 1. Abandoned requests keep running on the server With `execution_api_timeout = 5` and 5 retries, the client gives up, but the API server most likely keeps executing the original request. A client disconnect doesn't cancel an in-flight SQL statement. That fits your numbers (126 task starts → 309 `PUT /rtif`, server-side durations up to 299 s): each retry added another transaction contending for the same rows while the earlier ones still held locks. Until there's a fix, it's worth bounding this server-side, so a stuck cleanup fails fast instead of piling up. For example, set a role-level `statement_timeout` / `lock_timeout` for the Airflow DB user (PgBouncer passes these through if set on the role): ```sql ALTER ROLE airflow SET lock_timeout = '5s'; ALTER ROLE airflow SET statement_timeout = '60s'; ``` Test this carefully first, because `db migrate` and `db clean` may need longer limits. Run those with a separate role or a session-level override. ### 2. The `NOT IN (keep-list)` shape is O(history) even with scoping Adding a `(dag_id, task_id)` predicate to the delete target is the main fix. But `run_id NOT IN (<N newest runs>)` still makes Postgres consider *every* historical run of that task on every task start, and that's 442k in your case. Two shapes that keep it proportional to the rows actually being evicted: - **Cutoff instead of a keep-list:** look up the `run_after` of the Nth newest run once, then delete `WHERE dag_id = $1 AND task_id = $2 AND run_after < $cutoff`. This can use a range scan. - **Batch it:** `... LIMIT 1000` per call (via a CTE / `ctid IN (SELECT ... LIMIT n)`), so one task start never pays for years of backlog. ### 3. Concurrent starts of the same task shouldn't serialize on the cleanup Even after both fixes, N mapped instances or parallel runs of one task all try to delete the same rows. Wrapping the cleanup in `pg_try_advisory_xact_lock(hash(dag_id, task_id))` and skipping it when the lock is taken (or using `FOR UPDATE SKIP LOCKED` on the rows to delete) would stop concurrent starts of the same task from queueing behind each other. Even better: move the cleanup out of the `PUT /rtif` request path, for example into the scheduler or a periodic job. The upsert is the only part that needs to happen at task start. ### 4. Capturing the plan if it happens again Since you couldn't capture the plan this time, enabling `auto_explain` on the DB during the next upgrade window would settle hypothesis 1 vs. 2 without needing a reproduction: ```sql -- postgresql.conf / ALTER SYSTEM, then reload session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '2s' auto_explain.l GitHub link: https://github.com/apache/airflow/discussions/76774#discussioncomment-19601972 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
