From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Pengzhou Tang <ptang@pivotal.io>
Cc: Richard Guo <guofenglinux@gmail.com>
Cc: Jesse Zhang <sbjesse@gmail.com>
Cc: Richard Guo <riguo@pivotal.io>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Parallel grouping sets
Date: Tue, 24 Mar 2020 04:13:09 +0100
Message-ID: <20200324031309.nryb2c6reecgeh4t@development> (raw)
In-Reply-To: <CAG4reAQRbuLd_fhjyhjHSXFDSeBmCO=rrDM53O8v04dR071uaQ@mail.gmail.com>
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>
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
Attachments:plan.txt (text/plain, 1.3K) download
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)
stats.txt (text/plain, 4.2K) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, ptang@pivotal.io, guofenglinux@gmail.com, sbjesse@gmail.com, riguo@pivotal.io, amit.kapila16@gmail.com, michael@paquier.xyz, andres@anarazel.de, andrew@tao11.riddles.org.uk
Subject: Re: Parallel grouping sets
In-Reply-To: <20200324031309.nryb2c6reecgeh4t@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox