CTAS/INSERT SELECT can drive CN RSS close to cgroup limit due to incomplete memory accounting

## Summary

A `CREATE TABLE ... AS SELECT` / split `CREATE TABLE` + `INSERT INTO ... SELECT` workload in dev can drive CN RSS close to the pod memory limit. The workload does not show a classic long-term leak, but the query memory peak is not fully controlled by the current query memory limit because several large memory consumers sit outside a single per-query budget.

The immediate production symptom was an OOM alert on dev:

- Alert: `[critical] mo_oom`
- Environment: dev / freetier-01
- Alert time: `2026-06-24T12:33:08+08:00`
- OOM pod: `freetier-01-s16c64g-854877c9c-cn-kdnjx`
- Container: `main`
- Image: `v3.0.15-43e871c1b-2026-06-17`

## Problematic SQL

Original CTAS statement:

```sql
CREATE TABLE jst_flat_table.expense_detail_voucher__stg_s4__agg_ztfi AS
SELECT bukrs, gjahr, belnr, MAX(zaddr7) AS zaddr7
FROM dwd_dcp.DWD_S4_ZTFI0007
GROUP BY bukrs, gjahr, belnr;
```

Statement metadata:

- `statement_id`: `019ef7e4-8dbf-740e-ba74-159a79b598f7`
- `txn_id`: `881d7f85bb73311218bbe7ad61d556a1`
- `session_id`: `019ef7e4-12ed-7070-a635-6ae09f5de431`
- Account/user: `ws_bf2d347f / moi_core_system`
- CN: `37626238-3832-3734-6131-346234356561`
- Start/end: `2026-06-24 04:30:11.391181` - `2026-06-24 04:31:23.597335 UTC`
- Duration: `72.2s`
- Status: `Success`
- Query stats payload included approximately `1,861,317` rows scanned and `562.2576` reported scan/read metric.

DN commit for the CTAS target table:

- Target table: `jst_flat_table.expense_detail_voucher__stg_s4__agg_ztfi`
- Table id: `6121749`
- Rows written: `496255`
- Object count: `1`
- Object original size: `50.08MiB`
- Object compressed size: `5.21MiB`

The output was small. The memory pressure came from scan/group/shuffle/read, not from final result size.

## Observed Memory Behavior

### OOM incident

During the original CTAS incident:

- Container working set rose from roughly `7GiB` to `15-16GiB` near the OOM window.
- Go heap alloc rose to roughly `10-10.6GiB`.
- `mo_mem_malloc_gauge` showed large contributors around the same window:
- `memory-cache-inuse`: about `5.0GiB`
- `mpool-inuse`: about `4.3GiB`
- `hashmap-inuse`: about `1.4GiB`
- total malloc inuse: about `10.9GiB`
- The container was OOMKilled while vertical scaling / dynamic memory limit adjustments were happening. The effective limit at the exact kernel OOM moment may have been lower than the later/current pod YAML limit.

Pyroscope around the OOM window showed the major stacks:

- Go heap / query execution:
- `group -> shuffleV2 -> table_scan -> S3FS.read -> io.ReadAll`
- Catalog/cache replay also appeared during the window:
- `disttae.(*PushClient).replayCatalogCache`
- `CatalogCache.InsertColumns`
- `cache.getTableDef`
- malloc / query execution:
- `output/projection/order/mergeorder`
- `multi_update/preinsert/group/shuffleV2/table_scan`

This indicates high transient memory usage across query execution, fileservice reads, cache, and malloc/mpool/hashmap, rather than a single slow-growing leak.

### Split CREATE TABLE + INSERT SELECT retest

The workload was later split into separate `CREATE TABLE` and `INSERT INTO ... SELECT` statements. This improved observability and avoided OOM in that run, but the memory peak remained unsafe.

Recent retest on pod `freetier-01-s16c64g-854877c9c-cn-2hlck`:

- Pod memory limit: `30Gi`
- Peak time: around `2026-06-24 15:34:30 +08:00`
- Peak working set: `29867 MiB` (`~29.2GiB`)
- Peak RSS: `28082 MiB` (`~27.4GiB`)
- Peak Go heap: `10618 MiB` (`~10.4GiB`)
- Peak malloc inuse: `14675 MiB` (`~14.3GiB`)
- Peak `memory-cache-inuse`: `8606 MiB` (`~8.4GiB`)
- Peak `mpool-inuse`: `4829 MiB` (`~4.7GiB`)
- Peak `hashmap-inuse`: `1444 MiB` (`~1.4GiB`)
- No restart in this run, but the pod was within roughly `1GiB` of the cgroup limit by working set.

The dynamic memory settings were raised during the retest:

- `GoMemLimit` rose up to `28GiB`.
- Memory cache target rose up to `9018802176` bytes (`~8.4GiB`).
- The settings later dropped back to normal:
- `GoMemLimit`: `17.5GiB`
- Memory cache target: `5.25GiB`

After the query completed:

- `memory-cache-inuse` returned to about `5.25GiB`.
- `mpool-inuse` returned near zero.
- `hashmap-inuse` returned to zero.
- Working set remained around `15-16GiB` due to cache/runtime/RSS retention.

## Why This Is a Bug / Risk

The workload can push CN memory close to the pod cgroup limit even after splitting CTAS into `CREATE TABLE` + `INSERT SELECT`.

The current query memory control appears incomplete:

1. The front-end session uses a capped `pipeline-` mpool based on `GuestMmuLimitation`, so local operator allocations are partially controlled.
2. CTAS internally runs a follow-up `INSERT ... SELECT` from `Scope.CreateTable()` through internal executor with `WithDisableLog()`, so the inner statement is not recorded as a normal standalone statement in `statement_info` / logs.
3. Remote pipeline fragments create a separate mpool using `mpool.NewMPool("compile", 0, mpool.NoFixed)`, which appears not to inherit the per-query guest memory cap.
4. Fileservice memory cache has an independent target and can be dynamically raised during vertical scaling.
5. `S3FS.read -> io.ReadAll`, Go heap, default malloc, hashmap, and cache allocations are not accounted under one unified query memory budget.

As a result, a single large aggregation query can combine query mpool allocations, hashmap/group allocations, fileservice memory cache, Go heap reads, malloc/default allocator usage, and runtime/RSS retention, then exceed safe memory headroom without an early query-level memory error.

## Expected Behavior

Large CTAS / `INSERT SELECT` queries should fail or apply backpressure at the query level before CN RSS approaches the cgroup limit.

Specifically:

- CTAS inner `INSERT SELECT` should be observable as part of statement memory accounting, or at least attributed to the parent statement.
- Remote fragments should inherit an effective query memory budget, not use an uncapped compile mpool.
- Fileservice read buffers / `io.ReadAll` paths should be bounded or accounted.
- Memory cache target should not be expanded in a way that leaves insufficient headroom for active query execution.
- The system should prefer a query memory error over pod OOMKilled.

## Suggested Investigation / Fix Areas

- CTAS path:
- `pkg/sql/compile/ddl.go`, `Scope.CreateTable()`, `createAsSelectSql`, `runSqlWithResultAndOptions(... WithDisableLog())`
- Improve observability for the internal `INSERT SELECT` and attach its resource usage to the parent CTAS statement.

- Remote pipeline memory:
- `pkg/sql/compile/remoterunServer.go`, `newCompile()` currently creates `mpool.NewMPool("compile", 0, mpool.NoFixed)`.
- Propagate query/session memory limits to remote fragments.

- Fileservice read path:
- `S3FS.read -> io.ReadAll` showed large heap attribution in Pyroscope.
- Avoid unbounded reads or account these allocations against a query budget.

- Cache / vertical scaling interaction:
- During retest, cache target rose from `5.25GiB` to `8.4GiB` while query memory was also high.
- Ensure cache resizing leaves enough reserved headroom for active query execution and runtime overhead.

- Metrics / alerting:
- Add or expose per-statement peak memory metrics covering mpool, hashmap, fileservice read buffers, cache contribution, and remote fragments.
- Emit clearer logs when query memory cannot be attributed to statement-level accounting.

## Impact

This can cause CN pod OOMKills or near-OOM conditions in dev/freetier and likely any environment where large `GROUP BY` CTAS or `INSERT SELECT` workloads run near memory limits. The failure mode is especially risky during vertical scaling or dynamic cache resizing because the effective memory headroom can change while a query is already running.

## Workaround

Short-term operational workaround:

- Split CTAS into separate `CREATE TABLE` and `INSERT INTO ... SELECT` for better observability.
- Further split the `INSERT SELECT` by partition keys such as `gjahr` or `bukrs` to reduce single-query group/shuffle peak memory.
- Avoid running these large aggregation tasks while memory cache target is dynamically expanded.
- Consider lowering memory cache target for this workload class until query memory accounting is fixed.

Example safer pattern:

```sql
INSERT INTO jst_flat_table.expense_detail_voucher__stg_s4__agg_ztfi
SELECT bukrs, gjahr, belnr, MAX(zaddr7) AS zaddr7
FROM dwd_dcp.DWD_S4_ZTFI0007
WHERE gjahr = '2024'
GROUP BY bukrs, gjahr, belnr;
```

## Additional Notes

The evidence does not currently point to a classic monotonic memory leak. Memory returned after the query:

- `mpool-inuse` dropped back near zero.
- `hashmap-inuse` dropped back to zero.
- cache target and cache inuse dropped back to normal.

The main issue is high transient peak memory and incomplete query-level accounting/limiting across execution, remote fragments, fileservice, cache, Go heap, and malloc.