Re: DBT-3 with SF=20 got failed

On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:
> Hello,
>
> I got the following error during DBT-3 benchmark with SF=20.
>
> psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824
> psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824
>
> It looks to me Hash node tries to 1GB area using palloc0(), but it exceeds
> the limitation of none-huge interface.
>
> (gdb) bt
> #0 0x00007f669d29a989 in raise () from /lib64/libc.so.6
> #1 0x00007f669d29c098 in abort () from /lib64/libc.so.6
> #2 0x000000000090ccfd in ExceptionalCondition (conditionName=0xb18130 "!(((Size) (size) <= ((Size) 0x3fffffff)))",
> errorType=0xb17efd "FailedAssertion", fileName=0xb17e40 "mcxt.c", lineNumber=856) at assert.c:54
> #3 0x000000000093ad53 in palloc0 (size=1073741824) at mcxt.c:856
> #4 0x0000000000673045 in ExecHashTableCreate (node=0x7f669de951f0, hashOperators=0x24dbf90, keepNulls=0 '\000') at nodeHash.c:391
> #5 0x00000000006752e1 in ExecHashJoin (node=0x24d74e0) at nodeHashjoin.c:169
> #6 0x000000000065abf4 in ExecProcNode (node=0x24d74e0) at execProcnode.c:477
> #7 0x0000000000681026 in ExecNestLoop (node=0x24d6668) at nodeNestloop.c:123
> #8 0x000000000065abca in ExecProcNode (node=0x24d6668) at execProcnode.c:469
> #9 0x0000000000681026 in ExecNestLoop (node=0x24d61f8) at nodeNestloop.c:123
> #10 0x000000000065abca in ExecProcNode (node=0x24d61f8) at execProcnode.c:469
> #11 0x0000000000681026 in ExecNestLoop (node=0x24d5478) at nodeNestloop.c:123
> #12 0x000000000065abca in ExecProcNode (node=0x24d5478) at execProcnode.c:469
> #13 0x0000000000681026 in ExecNestLoop (node=0x24d51d0) at nodeNestloop.c:123
> #14 0x000000000065abca in ExecProcNode (node=0x24d51d0) at execProcnode.c:469
>
> The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset().
> Indeed, this hash table is constructed towards the relation with nrows=119994544,
> so, it is not strange even if hash-slot itself is larger than 1GB.
>
> Another allocation request potentially reset of expand hash-slot may also needs
> to be "Huge" version of memory allocation, I think.
>
> Thanks,
>
> Below is the query itself and EXPLAIN result.
> --------------------------------------------------------------------
> dbt3c=# EXPLAIN VERBOSE
> dbt3c-# select
> dbt3c-# s_name,
> dbt3c-# count(*) as numwait
> dbt3c-# from
> dbt3c-# supplier,
> dbt3c-# lineitem l1,
> dbt3c-# orders,
> dbt3c-# nation
> dbt3c-# where
> dbt3c-# s_suppkey = l1.l_suppkey
> dbt3c-# and o_orderkey = l1.l_orderkey
> dbt3c-# and o_orderstatus = 'F'
> dbt3c-# and l1.l_receiptdate > l1.l_commitdate
> dbt3c-# and exists (
> dbt3c(# select
> dbt3c(# *
> dbt3c(# from
> dbt3c(# lineitem l2
> dbt3c(# where
> dbt3c(# l2.l_orderkey = l1.l_orderkey
> dbt3c(# and l2.l_suppkey <> l1.l_suppkey
> dbt3c(# )
> dbt3c-# and not exists (
> dbt3c(# select
> dbt3c(# *
> dbt3c(# from
> dbt3c(# lineitem l3
> dbt3c(# where
> dbt3c(# l3.l_orderkey = l1.l_orderkey
> dbt3c(# and l3.l_suppkey <> l1.l_suppkey
> dbt3c(# and l3.l_receiptdate > l3.l_commitdate
> dbt3c(# )
> dbt3c-# and s_nationkey = n_nationkey
> dbt3c-# and n_name = 'UNITED KINGDOM'
> dbt3c-# group by
> dbt3c-# s_name
> dbt3c-# order by
> dbt3c-# numwait desc,
> dbt3c-# s_name
> dbt3c-# LIMIT 100;
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------------------------------------------------------
> ------------------
> Limit (cost=6792765.24..6792765.24 rows=1 width=26)
> Output: supplier.s_name, (count(*))
> -> Sort (cost=6792765.24..6792765.24 rows=1 width=26)
> Output: supplier.s_name, (count(*))
> Sort Key: (count(*)) DESC, supplier.s_name
> -> HashAggregate (cost=6792765.22..6792765.23 rows=1 width=26)
> Output: supplier.s_name, count(*)
> Group Key: supplier.s_name
> -> Nested Loop Anti Join (cost=4831094.94..6792765.21 rows=1 width=26)
> Output: supplier.s_name
> -> Nested Loop (cost=4831094.37..6792737.52 rows=1 width=34)
> Output: supplier.s_name, l1.l_suppkey, l1.l_orderkey
> Join Filter: (supplier.s_nationkey = nation.n_nationkey)
> -> Nested Loop (cost=4831094.37..6792736.19 rows=1 width=38)
> Output: supplier.s_name, supplier.s_nationkey, l1.l_suppkey, l1.l_orderkey
> -> Nested Loop (cost=4831093.81..6792728.20 rows=1 width=42)
> Output: supplier.s_name, supplier.s_nationkey, l1.l_suppkey, l1.l_orderkey, l2.l_orderkey
> Join Filter: (l1.l_suppkey = supplier.s_suppkey)
> -> Hash Semi Join (cost=4831093.81..6783870.20 rows=1 width=12)
> Output: l1.l_suppkey, l1.l_orderkey, l2.l_orderkey
> Hash Cond: (l1.l_orderkey = l2.l_orderkey)
> Join Filter: (l2.l_suppkey <> l1.l_suppkey)
> -> Index Scan using lineitem_l_orderkey_idx_part1 on public.lineitem l1 (cost=0.57..1847781.73 rows
> =39998181 width=8)
> Output: l1.l_orderkey, l1.l_partkey, l1.l_suppkey, l1.l_linenumber, l1.l_quantity, l1.l_extende
> dprice, l1.l_discount, l1.l_tax, l1.l_returnflag, l1.l_linestatus, l1.l_shipdate, l1.l_commitdate, l1.l_receiptdate, l1.l_shipinstruct, l1.l_shipm
> ode, l1.l_comment
> -> Hash (cost=3331161.44..3331161.44 rows=119994544 width=8)
> Output: l2.l_orderkey, l2.l_suppkey
> -> Seq Scan on public.lineitem l2 (cost=0.00..3331161.44 rows=119994544 width=8)
> Output: l2.l_orderkey, l2.l_suppkey
> -> Seq Scan on public.supplier (cost=0.00..6358.00 rows=200000 width=34)
> Output: supplier.s_suppkey, supplier.s_name, supplier.s_address, supplier.s_nationkey, supplier.s_pho
> ne, supplier.s_acctbal, supplier.s_comment
> -> Index Scan using orders_o_orderkey_o_orderdate_idx on public.orders (cost=0.56..7.98 rows=1 width=4)
> Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate,
> orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment
> Index Cond: (orders.o_orderkey = l1.l_orderkey)
> Filter: (orders.o_orderstatus = 'F'::bpchar)
> -> Seq Scan on public.nation (cost=0.00..1.31 rows=1 width=4)
> Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment
> Filter: (nation.n_name = 'UNITED KINGDOM'::bpchar)
> -> Index Scan using lineitem_l_orderkey_idx_part1 on public.lineitem l3 (cost=0.57..13.69 rows=89 width=8)
> Output: l3.l_orderkey, l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l
> 3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct, l3.l_shipmode, l3.l_comment
> Index Cond: (l3.l_orderkey = l1.l_orderkey)
> Filter: (l3.l_suppkey <> l1.l_suppkey)

curious: what was work_mem set to?

merlin