hussein-awala commented on PR #75216:
URL: https://github.com/apache/airflow/pull/75216#issuecomment-6520553233
I ran local before/after benchmarks on PostgreSQL 14.24 and MySQL 8.0.46,
comparing `5761c2ac` with `a315979`.
The benchmark used the full Airflow schema with **1 million task rows and
100 benchmark pools**, preserving indexes and foreign keys. Each case had three
warmups and **20 measured samples per implementation**, with processes ordered
before/after/after/before. All responses matched.
Median API request latency:
| Database | Workload | Request | Before | This PR |
|---|---|---|---:|---:|
| PostgreSQL | 10,000 active tasks | 50 pools | 202.4 ms | 10.4 ms |
| PostgreSQL | 188,200 active tasks | 50 pools | 239.7 ms | 24.3 ms |
| MySQL | 10,000 active tasks | 50 pools | 177.1 ms | 63.9 ms |
| MySQL | 188,200 active tasks | 50 pools | 591.1 ms | 494.3 ms |
| PostgreSQL | 901,000 active tasks | 10 filtered pools | **43.6 ms** |
**56.3 ms** |
For a 50-pool response, SQL statement counts dropped from **304 to 6**.
The shared scheduler aggregation, `Pool.slots_stats(lock_rows=True)`, also
showed workload-dependent results:
| Database | Active tasks | Before | This PR |
|---|---:|---:|---:|
| PostgreSQL | 10,000 | 3.21 ms | 3.65 ms |
| PostgreSQL | 188,200 | 17.88 ms | 20.45 ms |
| MySQL | 10,000 | 21.70 ms | 24.92 ms |
| MySQL | 188,200 | 5,420.75 ms | 1,065.26 ms |
These are local, sequential TestClient measurements, including
authentication and serialization—not production throughput estimates. Airflow
ran in an AMD64 container on an ARM64 VM.
The PostgreSQL filtered-page regression is the remaining concern. In that
case, the requested ten pools contained only **1,000 active tasks**, while
**900,000 active tasks belonged to unrelated pools**. PostgreSQL chose a
parallel scan of the entire task table before joining the selected pools.
Could we retain `TaskInstance.pool.in_(pool_names)` alongside the
`Pool.pool` filter?
```python
if pool_names is not None:
state_count_query = state_count_query.filter(
Pool.pool.in_(pool_names),
TaskInstance.pool.in_(pool_names),
)
```
Adding this predicate preserved the aggregate results and used `ti_pool` to
read only the 1,000 matching task rows. A separate aggregate-only diagnostic
dropped from **51.8 ms to 0.97 ms**. This retains the canonical-name
join/grouping that fixes MySQL collation handling while restoring efficient
PostgreSQL filtering.
--
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]
