Re: DBT-3 with SF=20 got failed - Mailing list pgsql-hackers

> 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 <kaigai@ak.jp.nec.com>


> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@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 <kaigai@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