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
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)
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
