[Bug] GPORCA long-lived backend leaks memory per query (process-level OptimizerMemoryContext, RSS never returned to OS)

## Summary

Long-lived backends running ORCA queries accumulate memory continuously (never returned to the OS). In production a single connection grew to **769 MB after 6,802 commands**, and the accumulated memory is only released at process exit, causing cascading OOM across the cluster over time (5,094 live PIDs at the OOM moment).

The same design flaw exists identically in Apache Cloudberry 2.1.0 and GPDB 6.27.1; upgrading between them does not fix it.

## Environment

- Apache Cloudberry 2.1.0-incubating (root cause also reproduces on GPDB 6.27.1)
- Test environment: **GPDB 6.27.1** (single-node, ORCA default)
- `optimizer = on` (ORCA), `optimizer_metadata_caching = on` (default), `optimizer_use_gpdb_allocators = on` (default)

## Reproduction

Setup two tables (see `orca_leak_setup.sql`, 10k + 50k rows), then repeatedly run identical simple queries on a **single psql connection** while watching the backend RSS:

```bash
./orca_leak_setup.sql        # psql -d <db> -f orca_leak_setup.sql
./orca_leak_reproduce.sh <db> 5000 default
```

### Measured results (single connection, GPDB 6.27.1, RSS of backend process)

| Iteration | RSS (MB) | Notes |
|-----------|----------|-------|
| 0         | 54.1     | baseline |
| 200       | 57.6     | ~0.018 MB/cmd |
| 1600      | 76.1     | ~0.016 MB/cmd |
| 2800      | 97.6     | ~0.018 MB/cmd |
| 4000      | 117.1    | ~0.016 MB/cmd |
| 5000      | 135.6    | ~0.019 MB/cmd |

- Total growth: **81.5 MB over 5,000 queries (~0.016 MB/cmd)**, and the growth is **accelerating** over time (fragmentation).
- Production estimate: **~0.113 MB/cmd** on real tables (more tables / larger/JSON metadata → more ORCA temp allocations).

### Controlled comparison

| Test | Config | Initial RSS | Final RSS | Growth |
|------|--------|-------------|-----------|--------|
| #1 | ORCA default (metadata caching on) | 56.5 MB | 98.5 MB | **+42.0 MB** |
| #2 | ORCA, `optimizer_metadata_caching=off` | 56.9 MB | 56.9 MB | **+0 MB** |
| #3 | ORCA off (`optimizer=off`) | 40.5 MB | 40.5 MB | **+0 MB** |
| #4 | Short-lived connections (new conn per query) | — | — | no growth (released at exit) |

Findings:
1. ORCA default long-lived connections **do** accumulate memory.
2. PostgreSQL's own planner (`optimizer=off`) does **not** grow.
3. Short connections don't grow (memory is only released at process exit).
4. Turning off metadata caching removes the growth — **cache entries are never recycled on the steady-state query path**, while temp query memory is never physically returned to the OS.

## Root cause analysis (source-level)

The leak is a **design issue**, not a one-off code bug.

1. **`OptimizerMemoryContext` has a process-level lifetime.** It is created as a child of `TopMemoryContext` (Cloudberry: lazy-created on first ORCA query in `src/backend/optimizer/plan/planner.c:388-417`; GPDB 6.27.1: at startup in `postinit.c:657`). `TopMemoryContext` is only cleaned up at process exit, so every ORCA allocation under it lives for the entire backend lifetime.

2. **Per-query pools are only *logically* freed.** Each query's `CAutoMemoryPool` creates a child `CMemoryPoolPalloc` context (`src/backend/gpopt/utils/CMemoryPoolPalloc.cpp`) whose `TearDown()` calls `MemoryContextDelete` — but glibc `free()` marks the `malloc` block free without `munmap`, so **RSS never drops**. AllocSet's `maxBlockSize` is 8 MB; large blocks can't be returned even when fully free.

3. **`CMDCache` entries are reused across queries and never cleared on the normal path.** `COptTasks::OptimizeTask()` does not call `CMDCache::Reset()`; cache pools live under the same process-level context. This is why `optimizer_metadata_caching=off` eliminates the growth.

4. **`CCacheFactory` uses its own `CMemoryPoolManager` pool** — also process-level.

5. **`optimizer_use_gpdb_allocators` cannot fix it.** Whether true (Palloc → MemoryContext, process-level) or false (glibc malloc/free, which also doesn't return memory to the OS), memory is never physically returned after a query.

So Cloudberry 2.1.0 and GPDB 6.27.1 share exactly the same defect; the only Cloudberry differences (lazy context creation, `MemoryContextDeclareAccountingRoot`) are cosmetic and don't change allocation/release behavior.

## Suggested fixes

- **Short term (ops):** cap long-lived connection lifetime (`pg_terminate_backend` on sessions older than ~4h), use application connection-pool `maxLifetime`, and/or set `optimizer_metadata_caching=off` for high-command-count ETL/COPY sessions (eliminates growth at a QPS cost — observed ~90→35 in test).
- **Long term (code):**
  - (A, recommended) Make `OptimizerMemoryContext` per-query — create at query start, delete at end — and relocate `CMDCache` / `CCacheFactory` pools so cross-query metadata caching is managed independently.
  - (B) `MemoryContextReset(OptimizerMemoryContext)` at end of `OptimizeTask()` after moving `CMDCache`'s pool out from under it.
  - (C) Use `mmap`/`munmap` for large allocations so freed blocks are returned to the OS.

## Repro scripts

`orca_leak_setup.sql`, `orca_leak_reproduce.sh`/`.py` (default/nocache/noop modes), `orca_leak_shortconn.sh`, `orca_leak_run_all.sh`.

## References / source locations (Cloudberry)

- `src/backend/optimizer/plan/planner.c:388-417` (lazy `OptimizerMemoryContext` create)
- `src/backend/utils/init/postinit.c` (cleanup at exit)
- `src/backend/gpopt/utils/CMemoryPoolPalloc.cpp` (Palloc bridge, logical-only free)
- `src/backend/gpopt/gpdbwrappers.cpp:2505-2528` (`GPDBAllocSetContextCreate` + accounting root)
- `src/backend/gpora/libgpopt/src/mdcache/CMDCache.cpp` (cross-query metadata cache)
- `src/backend/gpora/libgpos/src/memory/CCacheFactory.cpp`
- `src/backend/gpora/libgpos/src/memory/CMemoryPoolManager.cpp`

---

## Reproduction scripts

Full self-contained reproduction scripts (tested on GPDB 6.27.1; portable to Cloudberry).

### 1. Setup tables (`orca_leak_setup.sql`)

```sql
-- orca_leak_setup.sql - 创建 GPORCA 内存泄漏复现测试表
-- 用法: psql -d <dbname> -f orca_leak_setup.sql

-- 确认 ORCA 生效
SHOW optimizer;

-- 创建有足够复杂度的表，确保 ORCA 优化器被调用
DROP TABLE IF EXISTS leak_test_t2;
DROP TABLE IF EXISTS leak_test_t1;

CREATE TABLE leak_test_t1 (
    id serial PRIMARY KEY,
    name text,
    value numeric,
    created_at timestamp default now()
) DISTRIBUTED BY (id);

CREATE TABLE leak_test_t2 (
    id serial PRIMARY KEY,
    t1_id int REFERENCES leak_test_t1(id),
    status text,
    amount numeric
) DISTRIBUTED BY (id);

-- 插入基础数据
INSERT INTO leak_test_t1 (name, value)
SELECT 'user_' || i, random() * 10000
FROM generate_series(1, 10000) i;

INSERT INTO leak_test_t2 (t1_id, status, amount)
SELECT (random() * 9999 + 1)::int,
       (ARRAY['active','inactive','pending'])[ceil(random()*3)::int],
       random() * 5000
FROM generate_series(1, 50000) i;

-- 验证数据
SELECT 'leak_test_t1' AS tbl, count(*) FROM leak_test_t1
UNION ALL
SELECT 'leak_test_t2', count(*) FROM leak_test_t2;

\echo '测试表创建完成'
```

### 2. Long-lived connection leak repro (`orca_leak_reproduce.sh`)

usage: `./orca_leak_reproduce.sh <db> <iterations> <mode>` — modes: `default` | `nocache` | `noop`

```bash
#!/bin/bash
# orca_leak_reproduce.sh - 复现 GPORCA 长连接内存累积
#
# 核心思路: 生成 SQL 文件，psql -f 保持单连接执行，
# 同时后台监控进程 RSS
#
# 用法:
#   ./orca_leak_reproduce.sh [数据库名] [迭代次数] [模式]
#   模式: default (ORCA默认) | nocache (ORCA无缓存) | noop (关闭ORCA)

set -euo pipefail

DBNAME=${1:-postgres}
ITERATIONS=${2:-1000}
MODE=${3:-default}

mode_desc() {
    case "$1" in
        default) echo "ORCA默认 (optimizer=on, metadata_caching=on)" ;;
        nocache) echo "ORCA无缓存 (optimizer=on, metadata_caching=off)" ;;
        noop)    echo "无ORCA (optimizer=off)" ;;
    esac
}

echo "============================================================"
echo "GPORCA 长连接内存累积复现测试"
echo "============================================================"
echo "数据库:   $DBNAME"
echo "迭代次数: $ITERATIONS"
echo "测试模式: $(mode_desc "$MODE")"
echo ""

# 生成 SQL 文件
SQL_FILE=$(mktemp /tmp/orca_leak_XXXXXX.sql)
MONITOR_FILE=$(mktemp /tmp/orca_leak_XXXXXX.out)
trap 'rm -f "$SQL_FILE" "$MONITOR_FILE"' EXIT

# 输出 PID 标记
echo "\echo LEAK_TEST_PID_START" >> "$SQL_FILE"
echo "SELECT pg_backend_pid();" >> "$SQL_FILE"
echo "\echo LEAK_TEST_PID_END" >> "$SQL_FILE"

# 应用配置
case "$MODE" in
    nocache) echo "SET optimizer_metadata_caching=off;" >> "$SQL_FILE" ;;
    noop)    echo "SET optimizer=off;" >> "$SQL_FILE" ;;
esac

# 确认配置
echo "SHOW optimizer;" >> "$SQL_FILE"
echo "SHOW optimizer_metadata_caching;" >> "$SQL_FILE"

# 预热
echo "SELECT count(*) FROM leak_test_t1 WHERE value > 5000;" >> "$SQL_FILE"
if [[ "$MODE" != "noop" ]]; then
    echo "SELECT t1.name FROM leak_test_t1 t1 JOIN leak_test_t2 t2 ON t1.id=t2.t1_id LIMIT 1;" >> "$SQL_FILE"
    echo "SELECT * FROM leak_test_t1 WHERE id IN (SELECT t1_id FROM leak_test_t2 WHERE amount > 4000) LIMIT 1;" >> "$SQL_FILE"
fi

echo "\echo LEAK_TEST_WARMUP_DONE" >> "$SQL_FILE"

# 生成循环查询
for ((i=1; i<=$ITERATIONS; i++)); do
    case $(( i % 3 )) in
        1) echo "SELECT count(*) FROM leak_test_t1 WHERE value > 5000;" >> "$SQL_FILE" ;;
        2) if [[ "$MODE" != "noop" ]]; then
               echo "SELECT t1.name FROM leak_test_t1 t1 JOIN leak_test_t2 t2 ON t1.id=t2.t1_id LIMIT 1;" >> "$SQL_FILE"
           else
               echo "SELECT count(*) FROM leak_test_t1 WHERE name LIKE 'user_1%';" >> "$SQL_FILE"
           fi ;;
        0) if [[ "$MODE" != "noop" ]]; then
               echo "SELECT * FROM leak_test_t1 WHERE id IN (SELECT t1_id FROM leak_test_t2 WHERE amount > 4000) LIMIT 1;" >> "$SQL_FILE"
           else
               echo "SELECT max(value) FROM leak_test_t1;" >> "$SQL_FILE"
           fi ;;
    esac
    # 每200次插入进度标记
    if (( i % 200 == 0 )); then
        echo "\echo LEAK_TEST_PROGRESS:$i" >> "$SQL_FILE"
    fi
done

echo "\echo LEAK_TEST_DONE" >> "$SQL_FILE"

echo "SQL 文件已生成: $SQL_FILE ($(wc -l < "$SQL_FILE") 行)"
echo "正在执行查询..."

# 后台启动 psql
psql -X -d "$DBNAME" -f "$SQL_FILE" > "$MONITOR_FILE" 2>&1 &
PSQL_PID=$!

# 读取 RSS (KB)
get_rss() {
    awk '/VmRSS/ {print $2}' /proc/$1/status 2>/dev/null || echo 0
}

# 等待 PID 出现
BACKEND_PID=""
for attempt in $(seq 1 30); do
    if [[ -f "$MONITOR_FILE" ]]; then
        BACKEND_PID=$(sed -n '/LEAK_TEST_PID_START/,/LEAK_TEST_PID_END/{/LEAK_TEST/d;p;}' "$MONITOR_FILE" 2>/dev/null | grep -oP '\d+' | head -1 || true)
        if [[ -n "$BACKEND_PID" ]] && [[ "$BACKEND_PID" -gt 0 ]] 2>/dev/null; then
            break
        fi
    fi
    sleep 1
done

if [[ -z "$BACKEND_PID" ]] || ! [[ "$BACKEND_PID" -gt 0 ]] 2>/dev/null; then
    echo "ERROR: 无法获取 Backend PID"
    echo "psql 输出:"
    head -20 "$MONITOR_FILE"
    kill "$PSQL_PID" 2>/dev/null || true
    exit 1
fi

echo "Backend PID: $BACKEND_PID"

# 等待预热完成
for attempt in $(seq 1 30); do
    if grep -q "LEAK_TEST_WARMUP_DONE" "$MONITOR_FILE" 2>/dev/null; then
        break
    fi
    sleep 1
done

# 显示当前配置
OPT_VAL=$(sed -n '/LEAK_TEST_PID_END/,/LEAK_TEST_WARMUP_DONE/{/on\|off/p;}' "$MONITOR_FILE" 2>/dev/null | head -1 | tr -d '[:space:]' || echo "?")
echo "当前配置: optimizer=$OPT_VAL"

INIT_RSS=$(get_rss "$BACKEND_PID")
PREV_RSS=$INIT_RSS

echo ""
printf "%-8s %-12s %-12s %-12s\n" "ROUND" "RSS_MB" "DELTA_KB" "AVG_KB"
echo "--------------------------------------------------"

START_TIME=$(date +%s)
LAST_PROGRESS=0

# 监控循环
while kill -0 "$PSQL_PID" 2>/dev/null; do
    RSS=$(get_rss "$BACKEND_PID")
    if [[ "$RSS" -eq 0 ]]; then
        break
    fi

    # 从日志检测进度
    CURRENT_PROGRESS=$(grep -c 'LEAK_TEST_PROGRESS' "$MONITOR_FILE" 2>/dev/null || echo 0)
    CURRENT_PROGRESS=${CURRENT_PROGRESS//[^0-9]/}
    CURRENT_PROGRESS=${CURRENT_PROGRESS:-0}
    if [[ "$CURRENT_PROGRESS" -ne "$LAST_PROGRESS" ]]; then
        ROUNDS=$((CURRENT_PROGRESS * 200))
        DELTA=$((RSS - PREV_RSS))
        AVG_KB=$(echo "scale=1; ($RSS - $INIT_RSS) / $ROUNDS" | bc 2>/dev/null || echo "0")
        printf "%-8d %-12.1f %-12d %-12s\n" "$ROUNDS" "$(echo "scale=1; $RSS/1024" | bc)" "$DELTA" "$AVG_KB"
        PREV_RSS=$RSS
        LAST_PROGRESS=$CURRENT_PROGRESS
    fi

    sleep 3
done

# 等待 psql 完成
wait "$PSQL_PID" 2>/dev/null || true

# 最终采样
FINAL_RSS=$(get_rss "$BACKEND_PID")
if [[ "$FINAL_RSS" -eq 0 ]]; then
    FINAL_RSS=$PREV_RSS
fi

END_TIME=$(date +%s)
ELAPSED=$((END_TIME - START_TIME))

GROWTH_KB=$((FINAL_RSS - INIT_RSS))
GROWTH_MB=$(echo "scale=1; $GROWTH_KB/1024" | bc)
AVG_PER_CMD=$(echo "scale=3; $GROWTH_KB/$ITERATIONS/1024" | bc)
QPS=$(echo "scale=1; $ITERATIONS/$ELAPSED" | bc 2>/dev/null || echo "N/A")

echo ""
echo "============================================================"
echo "测试结果"
echo "============================================================"
echo "初始 RSS:       $(echo "scale=1; $INIT_RSS/1024" | bc) MB"
echo "最终 RSS:       $(echo "scale=1; $FINAL_RSS/1024" | bc) MB"
echo "总增长:         $GROWTH_MB MB"
echo "每查询平均增长: $AVG_PER_CMD MB"
echo "线上估算速率:   0.113 MB/cmd"
echo "总耗时:         ${ELAPSED}s ($QPS qps)"
echo ""

case "$MODE" in
    default)
        if (( $(echo "$AVG_PER_CMD > 0.05" | bc -l 2>/dev/null || echo 0) )); then
            echo ">>> 确认: ORCA 默认配置下长连接存在内存累积 <<<"
        elif (( $(echo "$AVG_PER_CMD < 0.01" | bc -l 2>/dev/null || echo 0) )); then
            echo ">>> 未检测到明显累积，可能需要更多迭代或更复杂查询 <<<"
        else
            echo ">>> 检测到轻微累积，与线上估算相比偏低，可能需要更长测试 <<<"
        fi
        ;;
    nocache)
        echo ">>> 对比: 关闭缓存后每查询增长 $AVG_PER_CMD MB <<<"
        ;;
    noop)
        echo ">>> 对比: 关闭 ORCA 后每查询增长 $AVG_PER_CMD MB <<<"
        ;;
esac
```

### 3. Short-connection comparison (`orca_leak_shortconn.sh`)

usage: `./orca_leak_shortconn.sh <db> <iterations>`

```bash
#!/bin/bash
# orca_leak_shortconn.sh - 短连接对比测试
# 每次查询新建连接，验证短连接模式下内存不会持续增长
#
# 用法: ./orca_leak_shortconn.sh [数据库名] [迭代次数]

set -euo pipefail

DBNAME=${1:-postgres}
ITERATIONS=${2:-200}

echo "============================================================"
echo "短连接对比测试 - 每次查询新建连接"
echo "============================================================"
echo "数据库:   $DBNAME"
echo "迭代次数: $ITERATIONS"
echo ""

QUERY="SELECT count(*) FROM leak_test_t1 WHERE value > 5000"

# 采集所有 postgres 后端进程的总 RSS
get_total_postgres_rss() {
    ps -eo rss,args 2>/dev/null | grep 'postgres:' | grep -v grep | awk '{sum+=$1} END {print sum+0}'
}

INIT_TOTAL=$(get_total_postgres_rss)
PREV_TOTAL=$INIT_TOTAL

echo "初始 postgres 总 RSS: $(echo "scale=1; $INIT_TOTAL/1024" | bc) MB"
echo ""
printf "%-8s %-14s %-12s\n" "ROUND" "TOTAL_RSS_MB" "DELTA_MB"
echo "----------------------------------"

for ((i=1; i<=$ITERATIONS; i++)); do
    # 每次新建连接
    psql -X -t -A -d "$DBNAME" -c "$QUERY" > /dev/null 2>&1

    if (( i <= 10 || i % 50 == 0 || i == ITERATIONS )); then
        TOTAL=$(get_total_postgres_rss)
        DELTA=$((TOTAL - PREV_TOTAL))
        printf "%-8d %-14.1f %-12.1f\n" "$i" "$(echo "scale=1; $TOTAL/1024" | bc)" "$(echo "scale=1; $DELTA/1024" | bc)"
        PREV_TOTAL=$TOTAL
    fi
done

FINAL_TOTAL=$(get_total_postgres_rss)
GROWTH_MB=$(echo "scale=1; ($FINAL_TOTAL - $INIT_TOTAL)/1024" | bc)

echo ""
echo "============================================================"
echo "短连接测试结果"
echo "============================================================"
echo "初始 postgres 总 RSS: $(echo "scale=1; $INIT_TOTAL/1024" | bc) MB"
echo "最终 postgres 总 RSS: $(echo "scale=1; $FINAL_TOTAL/1024" | bc) MB"
echo "总增长:               $GROWTH_MB MB"
echo ""
if (( $(echo "scale=0; $GROWTH_MB / 1" | bc 2>/dev/null || echo 0) < 50 )); then
    echo ">>> 短连接模式下无持续内存增长，连接退出后内存释放 <<<"
else
    echo ">>> 注意: 存在可观增长 ($GROWTH_MB MB)，可能有其他进程干扰 <<<"
fi
```

### 4. Run everything

```bash
# 1. setup (first run only)
psql -d <db> -f orca_leak_setup.sql

# 2. single tests
./orca_leak_reproduce.sh <db> 3000 default   # ORCA default
./orca_leak_reproduce.sh <db> 3000 nocache   # ORCA, metadata_caching=off
./orca_leak_reproduce.sh <db> 3000 noop      # ORCA off

# 3. short-connection comparison
./orca_leak_shortconn.sh <db> 200
```




Navigation Menu

Uh oh!

[Bug] GPORCA long-lived backend leaks memory per query (process-level OptimizerMemoryContext, RSS never returned to OS) #1947

Summary

Environment

Reproduction

Measured results (single connection, GPDB 6.27.1, RSS of backend process)

Controlled comparison

Root cause analysis (source-level)

Suggested fixes

Repro scripts

References / source locations (Cloudberry)

Reproduction scripts

1. Setup tables (`orca_leak_setup.sql`)

2. Long-lived connection leak repro (`orca_leak_reproduce.sh`)

3. Short-connection comparison (`orca_leak_shortconn.sh`)

4. Run everything

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Iteration	RSS (MB)	Notes
0	54.1	baseline
200	57.6	~0.018 MB/cmd
1600	76.1	~0.016 MB/cmd
2800	97.6	~0.018 MB/cmd
4000	117.1	~0.016 MB/cmd
5000	135.6	~0.019 MB/cmd

Test	Config	Initial RSS	Final RSS	Growth
#1	ORCA default (metadata caching on)	56.5 MB	98.5 MB	+42.0 MB
#2	ORCA, `optimizer_metadata_caching=off`	56.9 MB	56.9 MB	+0 MB
#3	ORCA off (`optimizer=off`)	40.5 MB	40.5 MB	+0 MB
#4	Short-lived connections (new conn per query)	—	—	no growth (released at exit)

Uh oh!

[Bug] GPORCA long-lived backend leaks memory per query (process-level OptimizerMemoryContext, RSS never returned to OS) #1947

Description

Summary

Environment

Reproduction

Measured results (single connection, GPDB 6.27.1, RSS of backend process)

Controlled comparison

Root cause analysis (source-level)

Suggested fixes

Repro scripts

References / source locations (Cloudberry)

Reproduction scripts

1. Setup tables (orca_leak_setup.sql)

2. Long-lived connection leak repro (orca_leak_reproduce.sh)

3. Short-connection comparison (orca_leak_shortconn.sh)

4. Run everything

Activity

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

1. Setup tables (`orca_leak_setup.sql`)

2. Long-lived connection leak repro (`orca_leak_reproduce.sh`)

3. Short-connection comparison (`orca_leak_shortconn.sh`)