CalvinKirs commented on PR #68197:
URL: https://github.com/apache/doris/pull/68197#issuecomment-5749915050
## End-to-end performance through a real Doris cluster (Hive queries and
MTMV)
The earlier comment measured the client class in isolation. This one
measures what a user sees: SQL against a running Doris cluster whose FE is
switched between the base branch and this backport.
### Summary
- **Cold Hive query planning**: the `Get Partitions Time` profile phase
drops **88–94x** (5,000 partitions: 30.4 s → 0.35 s; 120,000 partitions: 701.5
s → 7.5 s). End-to-end query latency drops **3.8x / 8.5x / 23.8x** at 1k / 5k /
120k partitions; what remains is object-store file listing, which this change
does not touch.
- **MTMV on a Hive base table** (cold partition cache, 5,000 partitions): an
`AUTO` refresh that finds nothing to do drops from 30.3 s to 0.47 s (**65x**);
planning a query that is transparently rewritten to the MV drops from 29.9 s to
0.49 s (**61x**); an incremental refresh of 10 changed partitions drops from
30.6 s to 0.86 s (**36x**).
- **Same method as the master report for #67186** (`REFRESH ... COMPLETE`,
both partition caches disabled, 100 partitions): **4,090 ms → 605 ms (6.8x)**,
physical partition-object RPCs **300 → 3**. The after side and both RPC shapes
reproduce the master measurement (598 ms, 300 → 3).
- **Metastore RPCs** (counted server-side from the HMS log): N
`get_partition` calls become ⌈N/5000⌉ `get_partitions_by_names` calls — 120,000
→ 24.
- **No warm-path regression**: with the partition cache warm, before and
after are within noise and issue zero partition RPCs.
- Every before/after pair returned identical query results, and the MV was
chosen by the CBO in both builds.
### Method
| Item | Value |
|---|---|
| Cluster | 1 FE + 1 BE on one host (16 vCPU Xeon 8369B, shared machine); FE
heap 3 GB |
| A/B switch | Same cluster, same FE metadata, same BE, same catalog. Only
`fe/lib/doris-fe.jar` is swapped, then FE restarted |
| Before | `branch-4.1` @ `8d2018bdc72` (parent of this PR's commit) |
| After | this PR @ `cd984d20404` |
| Jar diff | Verified by class listing: only this change's classes differ
(plus web static files / version stamp) |
| HMS | Dockerized Hive 2.3.2 + PostgreSQL metastore, Thrift over
`127.0.0.1` |
| Storage | MinIO (S3), path-style |
| Tables | `sales_1k` / `sales_5k`: 1,000 / 5,000 partitions, one real text
file of 20 rows per partition. `pt`: 120,000 partitions, no data files |
| Cold | `REFRESH CATALOG` before every cold measurement |
| Timing | Client wall time; FE profile phases (`Plan Time`, `Get Partitions
Time`, `Get Partition Files Time`); MTMV task `DurationMs` from
`tasks('type'='mv')` |
| RPC counts | Hive Metastore log, windowed per measurement |
| Batch size | default 5,000 |
Cold measurements were run 3 times and reported as medians. Order: all of
*before*, then all of *after*; a full warm-up pass ran beforehand so OS / MinIO
/ PostgreSQL caches were warm for both.
`SELECT count(*) FROM pt WHERE p LIKE 'v2%'` was intended as an
~11k-partition case, but `LIKE` is not used for Hive partition pruning on
branch-4.1, so both builds fetched all 120,000 partitions (confirmed by the RPC
count). Those three runs are reported together with the unfiltered `SELECT
count(*) FROM pt` run as four 120k samples.
### Hive queries, cold partition cache
`SELECT count(*), sum(amount) FROM sales_Nk` / `SELECT count(*) FROM pt`
| Partitions | Build | HMS partition RPCs | Get Partitions Time | Get
Partition Files Time | Query wall time |
|---:|---|---:|---:|---:|---:|
| 1,000 | before | 1,000 × `get_partition` | 6.10 s | 1.98 s | 8.56 s |
| 1,000 | after | 1 × `get_partitions_by_names` | 0.067 s | 1.83 s | 2.26 s |
| | | | **91x** | — | **3.8x** |
| 5,000 | before | 5,000 | 30.37 s | 3.66 s | 34.21 s |
| 5,000 | after | 1 | 0.346 s | 3.48 s | 4.04 s |
| | | | **88x** | — | **8.5x** |
| 120,000 | before | 120,000 | 701.5 s | 21.1 s | 724.5 s |
| 120,000 | after | 24 | 7.50 s | 21.6 s | 30.4 s |
| | | | **94x** | — | **23.8x** |
Warm cache, 5,000 partitions (no partition RPCs in either build): before 600
/ 760 / 634 ms, after 743 / 877 / 655 ms wall. Within run-to-run noise on a
shared host.
### MTMV on a Hive base table, cold partition cache
```sql
CREATE MATERIALIZED VIEW mv BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (dt) DISTRIBUTED BY HASH(dt) BUCKETS 1
PROPERTIES ('replication_num'='1', 'refresh_partition_num'='1000')
AS SELECT dt, sum(amount) AS total, count(*) AS cnt FROM hms.perf41.sales_Nk
GROUP BY dt;
```
| Scenario | Partitions | Before | After | Speedup | Partition RPCs before →
after |
|---|---:|---:|---:|---:|---|
| `REFRESH ... AUTO`, nothing changed (`NOT_REFRESH`), task duration | 1,000
| 5.97 s | 0.141 s | **42x** | 1,000 → 1 |
| | 5,000 | 30.35 s | 0.466 s | **65x** | 5,000 → 1 |
| Query transparently rewritten to the MV, `Plan Time` | 1,000 | 6.22 s |
0.160 s | **39x** | 1,000 → 1 |
| | 5,000 | 29.86 s | 0.489 s | **61x** | 5,000 → 1 |
| Same query, client wall time | 1,000 | 6.45 s | 0.249 s | 26x | |
| | 5,000 | 30.23 s | 0.754 s | 40x | |
| `REFRESH ... AUTO` after 10 partitions changed (`PARTIAL`, 10 partitions),
task duration | 1,000 | 6.30 s | 0.635 s | **9.9x** | 1,000 → 1 |
| | 5,000 | 30.63 s | 0.856 s | **36x** | 5,000 → 1 |
| Initial full build (`COMPLETE`, all partitions), task duration | 1,000 |
14.47 s | 8.47 s | 1.7x | 1,000 → 1 |
| | 5,000 | 107.1 s | 75.0 s | 1.4x | 5,000 → 1 |
- The freshness check is the dominant cost of every cold MTMV operation on
the base branch: about 6 ms per base-table partition, regardless of how little
work the operation ends up doing. The "nothing changed" refresh and the
10-partition incremental refresh both paid the full 30 s at 5,000 partitions.
- The initial full build is dominated by the actual `INSERT OVERWRITE` work,
so the saving there is the same absolute ~30 s but a small ratio.
- "10 partitions changed" was produced by advancing `transient_lastDdlTime`
of 10 partitions in the metastore database (the value MTMV uses as the Hive
partition snapshot); both builds selected exactly those 10 partitions.
- Rewritten query with a warm cache: no difference (1k: 102–168 ms vs 99–122
ms; 5k: 354–388 ms vs 376–445 ms).
- `EXPLAIN` showed `MaterializedViewRewriteSuccessAndChose` for the MV in
both builds (`materialized_view_rewrite_enable_contain_external_table=true`).
### Full MTMV refresh A/B, same method as the master report
`REFRESH MATERIALIZED VIEW ... COMPLETE` against a Hive table whose
partitions are metadata rows with empty S3 prefixes, **both Doris partition
caches disabled** (`meta.cache.hive.partition_values.ttl-second=0`,
`meta.cache.hive.partition.ttl-second=0`), one excluded warm-up followed by
five serialized measured refreshes per FE build. The MV refreshes all
partitions in one batch, which reproduces the master report's RPC shape exactly.
| Partitions | FE build | Measured ms | Mean | Median | Partition-object
RPCs / refresh |
|---:|---|---|---:|---:|---|
| 100 | before | 4090, 4167, 4037, 4050, 4332 | 4135 ms | 4090 ms | 300
`get_partition` |
| 100 | after | 605, 631, 563, 563, 624 | 597 ms | 605 ms | 3
`get_partitions_by_names` |
| 1,000 | before | 42232, 41971, 41747, 43424, 41596 | 42194 ms | 41971 ms |
3,000 `get_partition` |
| 1,000 | after | 5348, 5175, 5600, 5093, 4989 | 5241 ms | 5175 ms | 3
`get_partitions_by_names` |
100 partitions: **6.8x**; 1,000 partitions: **8.1x**. Physical
partition-object RPCs: 3N → 3.
Compared with the master report at 100 partitions: after 598 ms vs 605 ms
here, RPCs 300 → 3 in both. The before side is slower here (2,317 ms there vs
4,090 ms) because the master report used the released 4.1.3-rc02 FE as its
baseline while this run uses the actual parent commit of this PR, which with
the partition-values cache disabled also re-lists partition names once per
partition (109 vs 10 `get_partition_names` per refresh — the batched path
removes those as well), and because the shared host was busier during this run.
### Raw measurements (milliseconds)
| Scenario | Before | After |
|---|---|---|
| Hive 1k cold, wall | 9786, 8562, 8243 | 3243, 2263, 1922 |
| Hive 1k cold, Get Partitions | 6288, 6101, 5950 | 112, 64, 67 |
| Hive 5k cold, wall | 34662, 33388, 34208 | 4554, 4044, 3841 |
| Hive 5k cold, Get Partitions | 30365, 29251, 31115 | 601, 332, 346 |
| Hive 120k cold, wall | 719111, 727075, 723750, 725189 | 31453, 30832,
29703, 29988 |
| Hive 120k cold, Get Partitions | 696000, 704000, 701000, 702000 | 7947,
7724, 7281, 7094 |
| MTMV no-change refresh 1k, task | 6199, 5955, 5971 | 141, 149, 123 |
| MTMV no-change refresh 5k, task | 30347, 31636, 30005 | 471, 466, 414 |
| MTMV rewrite query 1k cold, Plan Time | 6221, 6347, 5926 | 190, 160, 131 |
| MTMV rewrite query 5k cold, Plan Time | 30761, 29629, 29864 | 500, 455,
489 |
| MTMV rewrite query 5k cold, wall | 31556, 30037, 30232 | 5215, 754, 737 |
| MTMV 10-changed refresh, task (1k / 5k) | 6300 / 30626 | 635 / 856 |
| MTMV initial build, task (1k / 5k) | 14473 / 107130 | 8472 / 75038 |
The 5,215 ms after-build outlier is the first read of the freshly built MV
on the BE (its `Plan Time` was 500 ms, in line with the other runs).
### Limits
Loopback HMS has lower latency than a typical production deployment, and the
per-partition data here is tiny, so BE scan time is negligible. In production
the absolute saving (roughly partitions × per-RPC latency) should be at least
as large, while the end-to-end ratio for scan-heavy queries will be smaller
than in the tables above.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
