一条SQL引发的惨案：60s就把内存干爆了？

一、问题现象

二、问题分析

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 --- this context holds the current command message from thefrontend, as well as any derived storage that need only live as long asthe current message (for example, in simple-Query mode the parse and plantrees can live here).  This context will be reset, and any childrendeleted, at the top of each cycle of the outer loop of PostgresMain.  Thisis kept separate from per-transaction and per-portal contexts because aquery string might need to live either a longer or shorter time than anysingle transaction or portal

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 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 *   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. */ staticvoidinheritance_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)            ...

三、优化方案

For inherited UPDATE/DELETE, instead of generating a separatesubplan for each target relation, we now generate a single subplanthat is just exactly like a SELECT's plan, then stick ModifyTableon top of that.  To let ModifyTable know which target relation agiven incoming row refers to, a tableoid junk column is added tothe row identity information.  This gets rid of the horrid hackthat was inheritance_planner(), eliminating O(N^2) planning costand memory consumption in cases where there were many unprunabletarget relations.

CREATE ORREPLACEFUNCTION create_partitions_batch(    total_partitions INTEGERDEFAULT20000,    batch_size INTEGERDEFAULT100) RETURNSVOIDAS $$DECLARE    i INTEGER := 0;    batch_count INTEGER;    current_batch INTEGER := 0;BEGIN    batch_count := ceil(total_partitions::FLOAT / batch_size);    IF NOT EXISTS (SELECT1FROM pg_tables WHERE tablename = 'batch_partitioned') THEN        CREATETABLE batch_partitioned (            partition_key INTEGER,            data JSONB,            created_at TIMESTAMPDEFAULTnow()        ) PARTITIONBYRANGE (partition_key);CREATEINDEXIFNOTEXISTS idx_batch_partitioned_partition_key ON batch_partitioned (partition_key);       CREATEINDEXIFNOTEXISTS idx_batch_partitioned_created_at ON batch_partitioned (created_at);CREATEINDEXIFNOTEXISTS idx_batch_partitioned_json_data ON batch_partitioned USING GIN (data); RAISE NOTICE '主表 batch_partitioned 创建完成';    ENDIF;    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;            EXECUTEformat(                '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            );        ENDLOOP;        PERFORM pg_sleep(0.1);                current_batch := current_batch + 1;    ENDLOOP;    RAISE NOTICE '分区表创建完成，总计 % 个分区', total_partitions;END;$$ LANGUAGE plpgsql;

小结

• 《Excessive memory usage in multi-statement queries w/ partitioning》

  https://www.postgresql.org/message-id/flat/87ftp6l2qr.fsf%40credativ.de

• 《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

• 《Rework planning and execution of UPDATE and DELETE》

  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=86dc90056

作者丨NickYoung