hussein-awala opened a new pull request, #76216: URL: https://github.com/apache/airflow/pull/76216
Every triggerer runs its assignment step about once a second. Below capacity, the normal state with the default `[triggerer] capacity` of 1000, that step runs three sorted, row-locking (`SKIP LOCKED`) claim queries, each joining or EXISTS-checking its source table, even when nothing waits to be claimed, which is almost every loop. Check first with one `SELECT trigger.id ... LIMIT 1` without the source-table joins, sorting or row locks, and run the claim queries only when it finds a row. related: #76209 The probe checks what makes a trigger claimable: `triggerer_id` is NULL or not in the alive-triggerer subquery, plus the queue and team filters. The callback, task-instance and asset claim queries ([trigger.py#L487-L535](https://github.com/apache/airflow/blob/b95498cd56/airflow-core/src/airflow/models/trigger.py#L487-L535)) only narrow that set with a join or EXISTS on the trigger's source, so a probe miss proves all three would return nothing. The probe and the claim queries take their queue and team filter from one new helper and one read of `[core] multi_team`, so the probe cannot drift from the queries it gates; `ids_for_triggerer` keeps its own copy of that filter. The probe re-evaluates the alive-triggerer subquery in the claim's transaction, so new triggers and those of a stale triggerer are still claimed on the same loop. Airflow runs MySQL at READ COMMITTED ([settings.py#L675-L681](https://github.com/apache/airflow/blob/b95498cd56/airflow-core/src/airflow/settings.py#L675-L681)), so the probe sees rows committed before it runs, like the claim queries do; a trigger committed between the probe and the claims waits one more loop. A probe hit that no claim query selects, such as an orphaned row or a row a peer holds locked, falls through to the existing path. No configuration or schema changes; `get_sorted_triggers` gains an optional `multi_team` argument, read from the config when omitted. A later PR planned in #76209 returns the claim count from `assign_unassigned`. Both merge cleanly, but its `return len(trigger_ids_query)` raises `UnboundLocalError` on every probe miss; whichever merges later initialises `trigger_ids_query` outside the probe branch. Another follow-up planned there builds on this PR to run the capacity count and the claim in one session. In a local benchmark of the triggerer's database cycle, with nothing to claim unless noted: | Scenario | Before | After | |---|---:|---:| | Idle loop, statements per loop, both backends | 7 | 5 | | Idle loop, no assigned triggers, SQLite | 1.66 ms | 1.14 ms | | Idle loop, 200 assigned triggers, SQLite | 1.90 ms | 1.33 ms | | Idle loop, no assigned triggers, PostgreSQL 16 | 21.77 ms | 10.73 ms | | At capacity (unchanged path), statements per loop | 4 | 4 | | Claiming 50 / 200 fresh triggers, total statements | 110 / 440 | 111 / 444 | | Nothing to claim, 10k + 300 owned, PostgreSQL / SQLite | 3.09 / 2.39 ms | 1.55 / 0.51 ms | | Same, 50k + 300 owned | 6.78 / 11.94 ms | 5.21 / 2.39 ms | Statements were counted engine-wide, excluding COMMIT. Claims are capped at 50 per loop, so 200 triggers take four `load_triggers` passes, one probe each. Times are medians of five rounds in rotated order, on SQLite on the macOS host and PostgreSQL 16 in Docker (2 CPUs, 2 GiB). Idle-loop min-max ranges do not overlap, except PostgreSQL with 200 assigned triggers, too noisy to call. Claiming time, the at-capacity loop and a control bench (`handle_failed_triggers`: 0.0 % on SQLite, -6.1 % on PostgreSQL) stayed within noise. These are local measurements, not end-to-end latency. With nothing to claim, the probe scans the whole `trigger` table, as main's claim queries already do on each such loop (the task-instance one in this data). The last two rows compare main's three claim queries with the probe, as sums of per-statement medians of 20 runs, with all tables analyzed on PostgreSQL 16 and capacity set above the owned count so that these statements run. New tests check that the probe stays closed (no triggers, live owner, another queue, outside the triggerer's team scope) and opens (unassigned, stale owner, same team). A query-count test caps the idle assignment step at two statements, the capacity count and the probe; its second half caps the claim path's cost and checks that a claimable trigger is still assigned. The gate-closed cases and the idle half of the query-count test fail without the change. Validation: - Triggerer job and trigger-model tests: 214 passed, 1 skipped on SQLite. - Same tests through Breeze with the CI image: 215 passed on PostgreSQL 14; 214 passed, 1 skipped on MySQL 8.0 (the skip is a PostgreSQL-only test). - Regular and manual static checks passed on the full diff. - Selected core suites and the documentation build were not run locally; CI validation is pending. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Code (Opus 5.5) Generated-by: Claude Code (Opus 5.5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
