maoxinwu opened a new issue, #1947:
URL: https://github.com/apache/cloudberry/issues/1947
## 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
GPDB6; upgrading between them does not fix it.
## Environment
- Apache Cloudberry 2.1.0-incubating (root cause also reproduces on GPDB6)
- `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, GPDB6, 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`; GPDB6: 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 GPDB6 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 GPDB6; 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
```
--
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]
