Date: Tue, 24 Mar 2020 04:13:09 +0100
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Pengzhou Tang <ptang@pivotal.io>
Cc: Richard Guo <guofenglinux@gmail.com>, Jesse Zhang <sbjesse@gmail.com>,
	Richard Guo <riguo@pivotal.io>,
	Amit Kapila <amit.kapila16@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Andres Freund <andres@anarazel.de>,
	Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Parallel grouping sets
Message-ID: <20200324031309.nryb2c6reecgeh4t@development>
References: 
 <CAN_9JTx3NM12ZDzEYcOVLFiCBvwMHyM0gENvtTpKBoOOgcs=kw@mail.gmail.com>
 <CAA4eK1L_T=4FVQ58rOFjoBLkGq2yvGRGG-CemsJX01PHExU4fg@mail.gmail.com>
 <CAGf+fX5288z0Gt_UL9FYHvNcySvG1wrKxPQ7+zretbcaUDc5Jw@mail.gmail.com>
 <CAN_9JTwtzttEmdXvMbJqXt=51kXiBTCKEPKq6kk2PZ6Xz6m5ig@mail.gmail.com>
 <CAGf+fX4SDEqbPwT9_mfJLDXkBKQTT6O-wAHeKvUAViE+56n2Gw@mail.gmail.com>
 <CAG4reASx+p3z5W59O57xhPC57Z4MW3mSnE=s-MJGromnmgg2fA@mail.gmail.com>
 <CAMbWs4-fjjfnHNJA9YbeDcku5KAYN_LTVEPOaTrKLioLK23d1w@mail.gmail.com>
 <CAG4reAQ8rFCc+i0oju3VjaW7xSOJAkvLrqa4F-NYZzAG4SW7iQ@mail.gmail.com>
 <20200319020914.zkcjpm5r4yj7umqx@development>
 <CAG4reAQRbuLd_fhjyhjHSXFDSeBmCO=rrDM53O8v04dR071uaQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="4jhy73xwzlhdkyvz"
Content-Disposition: inline
In-Reply-To: 
 <CAG4reAQRbuLd_fhjyhjHSXFDSeBmCO=rrDM53O8v04dR071uaQ@mail.gmail.com>
Precedence: bulk


--4jhy73xwzlhdkyvz
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline

On Fri, Mar 20, 2020 at 07:57:02PM +0800, Pengzhou Tang wrote:
>Hi Tomas,
>
>I rebased the code and resolved the comments you attached, some unresolved
>comments are explained in 0002-fixes.patch, please take a look.
>
>I also make the hash spill working for parallel grouping sets, the plan
>looks like:
>
>gpadmin=# explain select g100, g10, sum(g::numeric), count(*), max(g::text)
>from gstest_p group by cube (g100,g10);
>                                        QUERY PLAN
>-------------------------------------------------------------------------------------------
> Finalize MixedAggregate  (cost=1000.00..7639.95 rows=1111 width=80)
>   Filtered by: (GROUPINGSETID())
>   Group Key: ()
>   Hash Key: g100, g10
>   Hash Key: g100
>   Hash Key: g10
>   Planned Partitions: 4
>   ->  Gather  (cost=1000.00..6554.34 rows=7777 width=84)
>         Workers Planned: 7
>         ->  Partial MixedAggregate  (cost=0.00..4776.64 rows=1111 width=84)
>               Group Key: ()
>               Hash Key: g100, g10
>               Hash Key: g100
>               Hash Key: g10
>               Planned Partitions: 4
>               ->  Parallel Seq Scan on gstest_p  (cost=0.00..1367.71
>rows=28571 width=12)
>(16 rows)
>

Hmmm, OK. I think there's some sort of memory leak, though. I've tried
running a simple grouping set query on catalog_sales table from TPC-DS
scale 100GB test. The query is pretty simple:

   select count(*) from catalog_sales
   group by cube (cs_warehouse_sk, cs_ship_mode_sk, cs_call_center_sk);

with a partial MixedAggregate plan (attached). When executed, it however
allocates more and more memory, and eventually gets killed by an OOM
killer. This is on a machine with 8GB of RAM, work_mem=4MB (and 4
parallel workers).

The memory context stats from a running process before it gets killed by
OOM look like this

   TopMemoryContext: 101560 total in 6 blocks; 7336 free (6 chunks); 94224 used
     TopTransactionContext: 73816 total in 4 blocks; 11624 free (0 chunks); 62192 used
       ExecutorState: 1375731712 total in 174 blocks; 5391392 free (382 chunks); 1370340320 used
         HashAgg meta context: 315784 total in 10 blocks; 15400 free (2 chunks); 300384 used
           ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
           ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
           ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
           ...

That's 1.3GB allocated in ExecutorState - that doesn't seem right.

FWIW there are only very few groups (each attribute has fewer than 30
distinct values, so there's only about ~1000 groups. On master it works
just fine, of course.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--4jhy73xwzlhdkyvz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="plan.txt"

                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Finalize MixedAggregate  (cost=1000.00..5901854.45 rows=10416 width=20)
   Filtered by: (GROUPINGSETID())
   Group Key: ()
   Hash Key: cs_warehouse_sk, cs_ship_mode_sk, cs_call_center_sk
   Hash Key: cs_warehouse_sk, cs_ship_mode_sk
   Hash Key: cs_warehouse_sk
   Hash Key: cs_ship_mode_sk, cs_call_center_sk
   Hash Key: cs_ship_mode_sk
   Hash Key: cs_call_center_sk, cs_warehouse_sk
   Hash Key: cs_call_center_sk
   ->  Gather  (cost=1000.00..5901348.48 rows=41664 width=24)
         Workers Planned: 4
         ->  Partial MixedAggregate  (cost=0.00..5896182.08 rows=10416 width=24)
               Group Key: ()
               Hash Key: cs_warehouse_sk, cs_ship_mode_sk, cs_call_center_sk
               Hash Key: cs_warehouse_sk, cs_ship_mode_sk
               Hash Key: cs_warehouse_sk
               Hash Key: cs_ship_mode_sk, cs_call_center_sk
               Hash Key: cs_ship_mode_sk
               Hash Key: cs_call_center_sk, cs_warehouse_sk
               Hash Key: cs_call_center_sk
               ->  Parallel Seq Scan on catalog_sales  (cost=0.00..4096256.32 rows=35996432 width=12)
(22 rows)

--4jhy73xwzlhdkyvz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="stats.txt"

TopMemoryContext: 101560 total in 6 blocks; 7336 free (6 chunks); 94224 used
  TopTransactionContext: 73816 total in 4 blocks; 11624 free (0 chunks); 62192 used
    ExecutorState: 1375731712 total in 174 blocks; 5391392 free (382 chunks); 1370340320 used
      HashAgg meta context: 315784 total in 10 blocks; 15400 free (2 chunks); 300384 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
      ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
      ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
      ExprContext: 524288 total in 7 blocks; 15336 free (2 chunks); 508952 used
      ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
      ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
  Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  smgr relation table: 16384 total in 2 blocks; 4592 free (2 chunks); 11792 used
  pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 408 free (0 chunks); 7784 used
  TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
  Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  TopPortalContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
  Relcache by OID: 16384 total in 2 blocks; 3504 free (2 chunks); 12880 used
  CacheMemoryContext: 524288 total in 7 blocks; 257608 free (331 chunks); 266680 used
    index info: 3072 total in 2 blocks; 1128 free (1 chunks); 1944 used: pg_amop_opr_fam_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_operator_oid_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_aggregate_fnoid_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_proc_oid_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_type_oid_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_index_indexrelid_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_opclass_oid_index
    index info: 2048 total in 2 blocks; 672 free (2 chunks); 1376 used: pg_trigger_tgrelid_tgname_index
    index info: 2048 total in 2 blocks; 752 free (2 chunks); 1296 used: pg_rewrite_rel_rulename_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_class_oid_index
    index info: 2048 total in 2 blocks; 672 free (2 chunks); 1376 used: pg_attribute_relid_attnum_index
    index info: 3072 total in 2 blocks; 1128 free (1 chunks); 1944 used: pg_amproc_fam_proc_index
    index info: 2048 total in 2 blocks; 944 free (1 chunks); 1104 used: pg_authid_oid_index
    index info: 2048 total in 2 blocks; 720 free (3 chunks); 1328 used: pg_auth_members_member_role_index
    index info: 3072 total in 2 blocks; 1208 free (2 chunks); 1864 used: pg_shseclabel_object_index
    index info: 2048 total in 2 blocks; 1024 free (2 chunks); 1024 used: pg_database_datname_index
    index info: 2048 total in 2 blocks; 1024 free (2 chunks); 1024 used: pg_database_oid_index
    index info: 2048 total in 2 blocks; 1024 free (2 chunks); 1024 used: pg_authid_rolname_index
  WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
  Parallel worker: 8192 total in 1 blocks; 7784 free (1 chunks); 408 used
  PrivateRefCount: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
  MdSmgr: 8192 total in 1 blocks; 7736 free (4 chunks); 456 used
  LOCALLOCK hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
  ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
Grand total: 1377694648 bytes in 273 blocks; 5888440 free (763 chunks); 1371806208 used

--4jhy73xwzlhdkyvz--