Impala Performance Regression After a CDP Upgrade
After upgrading a 16-node CDP Data Warehouse from CDH 5.16 to CDP 7.1.9 with Impala 4.x, a retail BI team reports that 22 dashboards now take 45–90 seconds instead of 12–20 seconds. The cluster serves 40 concurrent analysts during the 8 a.m. peak; 70% of queries join a 2 TB Kudu fact table to 300 GB Parquet dimensions. Admission control is configured with one pool and a 20-query limit. The team copied Hive metastore statistics during migration but did not run COMPUTE STATS afterward. Query profiles show large row estimates for dimension scans, no runtime filters on several joins, and frequent spill to local disk. CPU utilization is 85%, but HDFS and Kudu disk I/O are below 40%. The business wants a fix before month-end close, and change freeze allows only configuration and statistics changes-no cluster expansion. What should the performance engineer investigate and change first?
1. Given the profile symptoms, build a prioritized diagnosis and explain why CPU is high while disk I/O is low.
Read the worked answer
The evidence points first to plan quality, not capacity. Copying metastore statistics during migration does not create Impala-compatible column and partition statistics after an upgrade; missing or stale stats produce bad row estimates, poor join order, and no runtime filters. High CPU with low HDFS/Kudu I/O suggests Impala is CPU-bound in joins, expression evaluation, decompression, or spill processing, not waiting on storage. Frequent spill means memory is insufficient for the chosen plan-often because the build side is underestimated or skewed. Prioritize: (1) run COMPUTE STATS (or COMPUTE INCREMENTAL STATS for partitioned Parquet) on the Kudu fact and all joined dimensions; (2) re-run the worst five queries and compare estimated vs actual rows and runtime-filter effectiveness in SUMMARY/PROFILE; (3) check join order, broadcast vs partitioned join, and skew; (4) only then tune admission control. Do not start by adding nodes; that hides the plan defect.
2. Which Impala and admission-control configuration changes would you test within the freeze, and what metrics would prove they helped?
Read the worked answer
Within a config/statistics freeze, test RUNTIME_FILTER_MODE=GLOBAL and a sensible RUNTIME_FILTER_WAIT_TIME_MS so filters arrive before scans start; confirm in profiles that filters are applied and reduce rows. For spill, check MEM_LIMIT and MAX_ROW_SIZE; if the build side is genuinely large, a partitioned join may be better than broadcast. For admission control, split the single pool into BI and ETL pools, cap concurrent queries per pool, set queue timeouts, and reserve memory for BI during peak; one 20-query pool lets heavy ETL starve dashboards. Prove impact with p95/p99 query duration, queue time, spill bytes, rows estimated vs actual, runtime-filter rows filtered, CPU utilization, and failed/queued queries. Compare a 48-hour baseline to a 48-hour test at the same concurrency; do not accept a change that improves one dashboard but raises queue time for the rest.
3. The BI team asks whether adding nodes would be the right long-term fix. How would you answer using the evidence?
Read the worked answer
Adding nodes is not the first fix because the cluster is CPU-bound with idle storage; more nodes could add CPU, but they would also multiply an inefficient plan and raise cost. The immediate leverage is statistics and runtime filters, then admission control to protect BI from ETL. Expansion becomes justified if, after those fixes, p95 latency still misses the target at 40 concurrent users and CPU stays above 80% with queue time growing. Even then, model the bottleneck: if spill remains high, memory per node may matter more than node count. Present the business with a staged decision: fix plans now, measure for two weeks, and expand only against a documented concurrency and memory gap. That is a lower-risk path before month-end close than a hardware change during freeze.