safaehar commented on PR #75216: URL: https://github.com/apache/airflow/pull/75216#issuecomment-6582545175
> 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. @hussein-awala adressed -- 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]
