# MaxCompute Slow-Query Table-Existence Prefetch ## Background Clinic Data Service synchronously validates AliCloud MaxCompute external tables before executing Data Proxy SQL. When the in-process table cache reaches its hard TTL, the next request runs a MaxCompute `DESC` task and then applies the existing cached `MSCK REPAIR TABLE ... ADD PARTITIONS` policy before the user query starts. Production trace samples show that this cold table-maintenance path materially increases tail latency. This optimization must stay inside Clinic-owned code. It must not patch, fork, vendor, pin, or configure a modified ODPS SDK. It also must not change query SQL, MaxCompute quota, the partition-repair cadence, or behavior for unrelated tables. ## Goal and Scope - Move the existing table-existence `DESC` out of the foreground path for an actively queried physical slow-log table. - Preserve synchronous first use, table creation, and partition repair. - Collapse concurrent work and bound detached work per Data Service process. - Limit the new path to AliCloud physical `slow_query_logs_` tables. - Leave TopSQL, views, logical `slow_query_logs`, backup names, Athena, BigQuery, and all normal query execution unchanged. ## Design For a positive `cache_ttl_seconds`: 1. Before the prefetch threshold, return the existing fresh table cache. 2. Between the prefetch threshold and hard TTL, start at most one detached table-existence `DESC`; do not update the table cache and do not run MSCK. 3. At hard TTL, consume or join a matching positive prefetch, then apply the old synchronous partition policy and publish the table cache. 4. A negative prefetch is never stored or trusted for CREATE; the hard request rechecks synchronously. If no positive prefetch exists, it failed, its cache generation differs, or it is too old, run the normal foreground `DESC` fallback. The prefetch threshold is hard TTL minus the smaller of 10% of TTL and 30 seconds. With production's 600-second TTL, prefetch becomes eligible at 570 seconds. A result is accepted through the counterfactual completion time of the old path: that generation's hard deadline plus the observed duration of the prefetched DESC. Thus a check started at 570s and taking 5s is reusable through 605s; one taking 40s is reusable through 640s. The result still expires shortly after it completes and cannot be reused hours later. The old path checks the partition cache after `DESC`. Consuming a prefetched result removes that foreground delay, so the implementation evaluates the partition at this request's counterfactual completion time: request arrival plus the observed prefetch DESC duration. - if the partition would be expired by this request's counterfactual DESC completion, the hard request waits until that instant and then runs the same MSCK; this avoids repairing too early and missing OSS partitions that arrive during the removed DESC interval; - if the partition would still be fresh at that time, the current request follows the existing cached-repair skip; - the table cache records that counterfactual completion time, capped at the prefetch result's `validUntil` so a delayed consumer cannot extend existence freshness beyond the bounded reuse window. This keeps MSCK synchronous at the same logical point for the same request that the old path would have repaired. It neither adds a repair nor lets that request observe fewer partitions under the explicit estimate that a hard-path DESC would take the same time as the prefetched DESC. That counterfactual duration cannot be observed and may vary with MaxCompute load, so this is a bounded timing approximation rather than a proof of identical visibility in every execution. Consequently, MSCK-due cycles do not get the full latency benefit; the primary foreground gain is on cycles where the legacy partition cache would have skipped MSCK. ## Concurrency and Failure Contract - A singleflight covers cache recheck, existence resolution, maintenance, and cache publication for cold/hard callers. This avoids a no-flight/no-cache window that could duplicate DESC or CREATE work. - A separate singleflight lets a hard caller join an active prefetch. - One process-wide slot bounds detached DESC work; no background queue builds up. Other tables keep the synchronous hard-TTL fallback. - One prefetch attempt is admitted per table-cache generation, preventing a failed DESC from retrying on every soft-window request. - Detached work uses `context.Background()` so it does not retain request cancellation or values. SDK panics in the detached table check are converted to errors and cannot crash the process. - The shared foreground maintenance flight uses `context.WithoutCancel` because the existing ODPS `Run`/`WaitForSuccess` path does not observe request cancellation. This prevents a canceled singleflight leader from changing the legacy result for healthy waiters. - Failed prefetches do not change either cache. The hard request retries through the legacy synchronous path. - A prefetch that starts too late and expires before completion is discarded by the background worker without another DESC; only a hard caller that joined that flight may initiate one synchronous fallback. - MSCK remains fail-open exactly as before. - `cache_ttl_seconds: 0` remains an indefinite cache; a negative TTL retains the existing synchronous behavior. ## Cost and Expected Benefit For a continuously active table, the same periodic DESC is moved about 30 seconds earlier and reused at hard TTL; MSCK frequency is unchanged. A sparse table can issue one speculative DESC if its last request lands in the 30-second prefetch window and no later request consumes it. The single background slot bounds this extra work. The benefit is intentionally limited: it removes the table-existence portion of cold maintenance from affected foreground requests. It does not optimize normal SELECT task submission, instance polling, result download, or the SDK's HTTP/TCP connection lifecycle. Therefore it reduces unlucky hard-TTL tails but does not promise every MaxCompute query will finish in three seconds. Previous production trace aggregation found the cold ensure path around 3.4s p50 and 6.8s p95, while a warm ensure was effectively zero. Those values are an opportunity bound for affected requests, not a guaranteed end-to-end gain; the post-deployment comparison must separate DESC-prefetched requests from requests that still need MSCK. ## Affected Runtime and Release Boundary - Go module: `clinic_data_service` - Package: `pkg/engine` - Runtime component/image: `clinic-data-service` - No ODPS SDK, `go.mod`, UI, Clinic API, NGM, gateway, app-delivery configuration, or MaxCompute quota change No app-delivery configuration change is required because the existing `cache_ttl_seconds` remains the hard TTL. Commit, push, PR, merge, and production release retain their normal explicit authorization boundaries. ## Verification Local deterministic checks: ```bash go test ./pkg/engine go test -race ./pkg/engine -run 'MaxCompute(SlowQuery|NonSlow)|PhysicalMaxCompute' go test ./pkg/engine_router ./pkg/service ./pkg/handlers ``` Tests cover cold and missing-table concurrency, completed/active/failed/stale prefetches, detached context and panic safety, bounded admission, the fresh-to-expired partition transition, fail-open MSCK, TTL contracts, and exact table-name scope. After review and an authorized production rollout of only `clinic-data-service`: 1. Confirm Pod readiness and no unexpected restarts. 2. Use the low-risk `ap-northeast-1` cohort and compare at least 30-50 equivalent read-only queries. 3. Use the background-prefetch completion log's duration to prove DESC moved before hard TTL; verify hard requests consume it without a second DESC. 4. Compare Data Proxy p50/p95, timeout/error rate, DESC count, MSCK count, and MaxCompute metering before and after. 5. Verify newly arriving partitions remain queryable. Do not log or publish customer SQL or result rows. Rollback is a source revert and `clinic-data-service` image rebuild; there is no persisted-data or configuration migration.