> curious: what was work_mem set to? > work_mem=48GB My machine mounts 256GB physical RAM. -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei <kai...@ak.jp.nec.com>
> -----Original Message----- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: Thursday, June 11, 2015 10:52 PM > To: Kaigai Kouhei(海外 浩平) > Cc: pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] DBT-3 with SF=20 got failed > > On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai <kai...@ak.jp.nec.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
