一条SQL引发的惨案，内存耗尽？
问题现象
有图有真相
问题分析
对于高内存问题，可以先打印MemoryContextStat，查看内存分布。
PG14及以上版本，可以直接调用函数SELECT pg_log_backend_memory_contexts(pid);
14以下版本，可以使用gdb去打印。 gdb --batch --pid 17185 --ex 'call MemoryContextStats(TopMemoryContext)'
可以看到主要是MessageContext占用了100多G。
TopMemoryContext: 26914664 total in 821 blocks; 5252280 free (34 chunks); 21662384 used pgstat TabStatusArray lookup hash table: 4194304 total in 10 blocks; 942232 free (25 chunks); 3252072 used TopTransactionContext: 8192 total in 1 blocks; 7744 free (0 chunks); 448 used TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used Operator lookup cache: 24576 total in 2 blocks; 10760 free (3 chunks); 13816 used RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 1296 used MessageContext: 125898262984 total in 53379 blocks; 4414792 free (10 chunks); 125893848192 used partition directory: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used smgr relation table: 16777216 total in 12 blocks; 6441432 free (44 chunks); 10335784 used TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used TopPortalContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used Relcache by OID: 4194304 total in 10 blocks; 935096 free (24 chunks); 3259208 used CacheMemoryContext: 194248872 total in 36 blocks; 4815960 free (4 chunks); 189432912 used partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019999 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019998 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019997 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019996 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019995 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019994 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019993 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019992 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019991 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019990 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019989 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019988 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019987 partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019986 ...
MessageContext主要是用来存放statement及parser tree，query tree和plan tree等数据。
MessageContext --- this context holds the current command message from the frontend, as well as any derived storage that need only live as long as the current message (for example, in simple-Query mode the parse and plan trees can live here). This context will be reset, and any children deleted, at the top of each cycle of the outer loop of PostgresMain. This is kept separate from per-transaction and per-portal contexts because a query string might need to live either a longer or shorter time than any single transaction or portal
sql语句非常简单：delete from batch_partitioned where created_at < '2025-11-13'::timestamp; 那可能就是plan的环节占用内存高了。
监控进程内存的专业工具有很多，这里由于是短时间申请了大量内存，肯定有大量内存函数调用，要搞清楚调用链路使用perf分析就可以了。
执行sql，同时用perf抓取。对了，二进制最好包含符号表，抓取的信息能更完整。
perf record -e page-faults -p 17185 -g -- sleep 60 [ perf record: Woken up 144 times to write data ] [ perf record: Captured and wrote 35.845 MB perf.data (187909 samples) ]
然后分析perf.data
从调用链路看，证明了我们的推测，确实问题出在执行计划阶段。比较可疑的是inheritance_planner函数，调用占比99.40%，一直往下调用palloc申请内存。
perf report -n --stdio# To display the perf.data header info, please use --header/--header-only options.# ## Total Lost Samples: 0## Samples: 187K of event 'page-faults:u'# Event count (approx.): 30002822## Children Self Samples Command Shared Object Symbol # ........ ........ ............ ........ ................ .......................................# 99.68% 0.00% 0 postgres libc-2.17.so [.] __libc_start_main | ---__libc_start_main startup_hacks PostmasterMain ServerLoop BackendStartup ExitPostmaster PostgresMain exec_simple_query | --99.44%--pg_plan_queries pg_plan_query planner standard_planner | --99.40%--subquery_planner inheritance_planner | |--93.85%--adjust_appendrel_attrs | | | --93.84%--query_tree_mutator | | | |--65.02%--__memcpy_ssse3 | | | --28.80%--range_table_mutator | | | |--23.78%--lappend | | | | | --23.77%--new_tail_cell | | | | | --18.86%--palloc | | AllocSetAlloc | | | --5.03%--palloc | AllocSetAlloc | --5.54%--grouping_planner | --5.52%--query_planner | --5.18%--setup_simple_rel_arrays | --5.18%--__GI_memset ...
那么接下来就从inheritance_planner函数入手分析。 从代码来看这个函数主要是针对分区表的delete和update操作，给每个被扫描的子表生成子计划。调用adjust_appendrel_attrs函数复制Query结构体并将parent RTE指向child RTE，然后处理子查询RTE。 这个过程中会为每个子表分配一个Query结构体。
/*
* inheritance_planner
* Generate Paths in the case where the result relation is an
* inheritance set.
*
* We have to handle this case differently from cases where a source relation
* is an inheritance set. Source inheritance is expanded at the bottom of the
* plan tree (see allpaths.c), but target inheritance has to be expanded at
* the top. The reason is that for UPDATE, each target relation needs a
* different targetlist matching its own column set. Fortunately,
* the UPDATE/DELETE target can never be the nullable side of an outer join,
* so it's OK to generate the plan this way.
*
* Returns nothing; the useful output is in the Paths we attach to
* the (UPPERREL_FINAL, NULL) upperrel stored in *root.
*
* Note that we have not done set_cheapest() on the final rel; it's convenient
* to leave this to the caller.
*/
static void
inheritance_planner(PlannerInfo *root)
{
/* 省略 */
/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
* references to the parent RTE to refer to the current child RTE,
* then fool around with subquery RTEs.
*/
subroot->parse = (Query *)
adjust_appendrel_attrs(subroot,
(Node *) parent_parse,
1, &appinfo);
/* 省略 */
我们的例子中batch_partitioned有20000个分区表，分区键为partition_key，执行的sql没带分区键，所以操作了所有分区表。 一个分区表对应的结构体大约占4.5MB，20000个也差不多90GB了。
postgres=# \d+ batch_partitioned Partitioned table "public.batch_partitioned" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- partition_key | integer | | | | plain | | data | jsonb | | | | extended | | created_at | timestamp without time zone | | | now() | plain | | Partition key: RANGE (partition_key) Indexes: "idx_batch_partitioned_created_at" btree (created_at) "idx_batch_partitioned_json_data" gin (data) "idx_batch_partitioned_partition_key" btree (partition_key) Partitions: batch_partition_000000 FOR VALUES FROM (0) TO (1), batch_partition_000001 FOR VALUES FROM (1) TO (2), batch_partition_000002 FOR VALUES FROM (2) TO (3), batch_partition_000003 FOR VALUES FROM (3) TO (4), batch_partition_000004 FOR VALUES FROM (4) TO (5), batch_partition_000005 FOR VALUES FROM (5) TO (6), batch_partition_000006 FOR VALUES FROM (6) TO (7), batch_partition_000007 FOR VALUES FROM (7) TO (8), batch_partition_000008 FOR VALUES FROM (8) TO (9) ...
像这种问题，社区肯定有讨论，去搜索了下确实找到了一些信息。
在以下邮件中看到了2019年Tom Lane一众大佬的讨论，认为inheritance_planner的处理是有问题的，预计会在13+版本中调整。
【Excessive memory usage in multi-statement queries w/ partitioning】[1]
【memory problems and crash of db when deleting data from table with thousands of partitions 】[2]
优化方案
查找Release Notes同时进行验证，发现在PG14版本做了优化。
来自Tom Lane的commit:
【Rework planning and execution of UPDATE and DELETE】[3]
如commit log所言，去除了令人厌恶的inheritance_planner()。
For inherited UPDATE/DELETE, instead of generating a separate subplan for each target relation, we now generate a single subplan that is just exactly like a SELECT's plan, then stick ModifyTable on top of that. To let ModifyTable know which target relation a given incoming row refers to, a tableoid junk column is added to the row identity information. This gets rid of the horrid hack that was inheritance_planner(), eliminating O(N^2) planning cost and memory consumption in cases where there were many unprunable target relations.
14版本中delete访问20000个子表消耗内存5GB左右。
感兴趣的朋友可以用这个函数去复现，切勿在重要环境中测试。
CREATE ORREPLACE FUNCTION create_partitions_batch( total_partitions INTEGER DEFAULT 20000, batch_size INTEGER DEFAULT 100 ) RETURNS VOID AS $$ DECLARE i INTEGER := 0; batch_count INTEGER; current_batch INTEGER := 0; BEGIN batch_count := ceil(total_partitions::FLOAT / batch_size); IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'batch_partitioned') THEN CREATE TABLE batch_partitioned ( partition_key INTEGER, data JSONB, created_at TIMESTAMP DEFAULT now() ) PARTITION BY RANGE (partition_key); CREATE INDEX IF NOT EXISTS idx_batch_partitioned_partition_key ON batch_partitioned (partition_key); CREATE INDEX IF NOT EXISTS idx_batch_partitioned_created_at ON batch_partitioned (created_at); CREATE INDEX IF NOT EXISTS idx_batch_partitioned_json_data ON batch_partitioned USING GIN (data); RAISE NOTICE '主表 batch_partitioned 创建完成'; END IF; WHILE current_batch < batch_count LOOP RAISE NOTICE '创建批次 %, 进度 %/%', current_batch + 1, (current_batch + 1) * batch_size, total_partitions; FOR i IN 0..(batch_size - 1) LOOP EXIT WHEN (current_batch * batch_size + i) >= total_partitions; EXECUTE format( 'CREATE TABLE IF NOT EXISTS batch_partition_%s PARTITION OF batch_partitioned FOR VALUES FROM (%s) TO (%s)', lpad((current_batch * batch_size + i)::TEXT, 6, '0'), current_batch * batch_size + i, current_batch * batch_size + i + 1 ); END LOOP; PERFORM pg_sleep(0.1); current_batch := current_batch + 1; END LOOP; RAISE NOTICE '分区表创建完成，总计 % 个分区', total_partitions; END; $$ LANGUAGE plpgsql;
小结
本篇简单介绍了高内存问题的分析方法，当遇到内存消耗高问题时，先打印MemoryContext分布，再结合perf等工具进行分析。
By the way，写SQL要规范，做了分区就要带分区条件；大版本升级是相当重要的。
Reference
[1] 【Excessive memory usage in multi-statement queries w/ partitioning】:
https://www.postgresql.org/message-id/flat/87ftp6l2qr.fsf%40credativ.de
[2] 【memory problems and crash of db when deleting data from table with thousands of partitions】:
https://www.postgresql.org/message-id/flat/CAGvVEFueqXeYL0z2fTQMWYSz7Gc9czSrOvCSNubb9rHHVL-2OA%40mail.gmail.com
[3] 【Rework planning and execution of UPDATE and DELETE】:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=86dc90056
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70033867/viewspace-3107620/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Toast是insert性能杀手？
请登录后发表评论
登录
全部评论
