drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deneche A. Hakim (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-3307) Query with window function runs out of memory
Date Thu, 18 Jun 2015 14:25:01 GMT

    [ https://issues.apache.org/jira/browse/DRILL-3307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14591874#comment-14591874
] 

Deneche A. Hakim commented on DRILL-3307:
-----------------------------------------

Looking at the data, especially the partition-by column "ss_store_sk" is skewed. It contains
too many null values:
{noformat}
2: jdbc:drill:zk=local> SELECT ss.ss_store_sk, COUNT(*) FROM store_sales ss GROUP BY ss.ss_store_sk;
+--------------+-----------+
| ss_store_sk  |  EXPR$1   |
+--------------+-----------+
| 32           | 1366549   |
| 319          | 1365813   |
| 367          | 1369181   |
              ...
| null         | 12950651  |
              ...
| 376          | 1364691   |
| 38           | 1375102   |
| 308          | 1367071   |
+--------------+-----------+
{noformat}

 those "null" rows end up being sorted by a single fragment and runs out of memory, all other
fragments finish their execution fine.

To confirm this you can try the following query, it should run fine:
{noformat}
SELECT SUM(ss.ss_net_paid_inc_tax) OVER (PARTITION BY ss.ss_store_sk) AS TotalSpend FROM store_sales
ss WHERE ss.ss_store_sk IS NOT NULL ORDER BY 1 LIMIT 20;
{noformat}

> Query with window function runs out of memory
> ---------------------------------------------
>
>                 Key: DRILL-3307
>                 URL: https://issues.apache.org/jira/browse/DRILL-3307
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 1.1.0
>         Environment: Data set: TPC-DS SF 100 Parquet
> Number of Nodes: 4
>            Reporter: Abhishek Girish
>            Assignee: Deneche A. Hakim
>         Attachments: drillbit.log.txt
>
>
> Query with window function runs out of memory:
> {code:sql}
>  SELECT SUM(ss.ss_net_paid_inc_tax) OVER (PARTITION BY ss.ss_store_sk) AS TotalSpend
FROM store_sales ss ORDER BY 1 LIMIT 20;
> java.lang.RuntimeException: java.sql.SQLException: RESOURCE ERROR: One or more nodes
ran out of memory while executing the query.
> Fragment 3:0
> [Error Id: 9af19064-9175-46a4-b557-714d1c77cd76 on abhi6.qa.lab:31010]
> 	at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
> 	at sqlline.TableOutputFormat$ResizingRowsProvider.next(TableOutputFormat.java:85)
> 	at sqlline.TableOutputFormat.print(TableOutputFormat.java:116)
> 	at sqlline.SqlLine.print(SqlLine.java:1583)
> 	at sqlline.Commands.execute(Commands.java:852)
> 	at sqlline.Commands.sql(Commands.java:751)
> 	at sqlline.SqlLine.dispatch(SqlLine.java:738)
> 	at sqlline.SqlLine.begin(SqlLine.java:612)
> 	at sqlline.SqlLine.start(SqlLine.java:366)
> 	at sqlline.SqlLine.main(SqlLine.java:259)
> {code}
> Plan:
> {code}
> 00-00    Screen : rowType = RecordType(ANY TotalSpend): rowcount = 2.87997024E8, cumulative
cost = {4.3487550824E9 rows, 5.7539970079068695E10 cpu, 0.0 io, 7.077814861824E12 network,
4.607952384E9 memory}, id = 142297
> 00-01      SelectionVectorRemover : rowType = RecordType(ANY TotalSpend): rowcount =
2.87997024E8, cumulative cost = {4.31995538E9 rows, 5.751117037666869E10 cpu, 0.0 io, 7.077814861824E12
network, 4.607952384E9 memory}, id = 142296
> 00-02        Limit(fetch=[20]) : rowType = RecordType(ANY TotalSpend): rowcount = 2.87997024E8,
cumulative cost = {4.031958356E9 rows, 5.722317335266869E10 cpu, 0.0 io, 7.077814861824E12
network, 4.607952384E9 memory}, id = 142295
> 00-03          SingleMergeExchange(sort0=[0 ASC]) : rowType = RecordType(ANY TotalSpend):
rowcount = 2.87997024E8, cumulative cost = {4.031958336E9 rows, 5.722317327266869E10 cpu,
0.0 io, 7.077814861824E12 network, 4.607952384E9 memory}, id = 142294
> 01-01            SelectionVectorRemover : rowType = RecordType(ANY TotalSpend): rowcount
= 2.87997024E8, cumulative cost = {3.743961312E9 rows, 5.261522088866869E10 cpu, 0.0 io, 5.89817905152E12
network, 4.607952384E9 memory}, id = 142293
> 01-02              TopN(limit=[20]) : rowType = RecordType(ANY TotalSpend): rowcount
= 2.87997024E8, cumulative cost = {3.455964288E9 rows, 5.232722386466869E10 cpu, 0.0 io, 5.89817905152E12
network, 4.607952384E9 memory}, id = 142292
> 01-03                Project(TotalSpend=[$0]) : rowType = RecordType(ANY TotalSpend):
rowcount = 2.87997024E8, cumulative cost = {3.167967264E9 rows, 4.734841414759049E10 cpu,
0.0 io, 5.89817905152E12 network, 4.607952384E9 memory}, id = 142291
> 01-04                  HashToRandomExchange(dist0=[[$0]]) : rowType = RecordType(ANY
TotalSpend, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost = {3.167967264E9
rows, 4.734841414759049E10 cpu, 0.0 io, 5.89817905152E12 network, 4.607952384E9 memory}, id
= 142290
> 02-01                    UnorderedMuxExchange : rowType = RecordType(ANY TotalSpend,
ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost = {2.87997024E9 rows,
4.274046176359049E10 cpu, 0.0 io, 3.538907430912E12 network, 4.607952384E9 memory}, id = 142289
> 03-01                      Project(TotalSpend=[$0], E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($0))])
: rowType = RecordType(ANY TotalSpend, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8,
cumulative cost = {2.591973216E9 rows, 4.245246473959049E10 cpu, 0.0 io, 3.538907430912E12
network, 4.607952384E9 memory}, id = 142288
> 03-02                        Project(TotalSpend=[CASE(>($2, 0), CAST($3):ANY, null)])
: rowType = RecordType(ANY TotalSpend): rowcount = 2.87997024E8, cumulative cost = {2.303976192E9
rows, 4.130047664359049E10 cpu, 0.0 io, 3.538907430912E12 network, 4.607952384E9 memory},
id = 142287
> 03-03                          Window(window#0=[window(partition {1} order by [] range
between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT($0), $SUM0($0)])]) : rowType
= RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk, BIGINT w0$o0, ANY w0$o1): rowcount
= 2.87997024E8, cumulative cost = {2.015979168E9 rows, 4.014848854759049E10 cpu, 0.0 io, 3.538907430912E12
network, 4.607952384E9 memory}, id = 142286
> 03-04                            SelectionVectorRemover : rowType = RecordType(ANY ss_net_paid_inc_tax,
ANY ss_store_sk): rowcount = 2.87997024E8, cumulative cost = {1.727982144E9 rows, 3.928449747559049E10
cpu, 0.0 io, 3.538907430912E12 network, 4.607952384E9 memory}, id = 142285
> 03-05                              Sort(sort0=[$1], dir0=[ASC]) : rowType = RecordType(ANY
ss_net_paid_inc_tax, ANY ss_store_sk): rowcount = 2.87997024E8, cumulative cost = {1.43998512E9
rows, 3.899650045159049E10 cpu, 0.0 io, 3.538907430912E12 network, 4.607952384E9 memory},
id = 142284
> 03-06                                Project(ss_net_paid_inc_tax=[$0], ss_store_sk=[$1])
: rowType = RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk): rowcount = 2.87997024E8,
cumulative cost = {1.151988096E9 rows, 6.623931552E9 cpu, 0.0 io, 3.538907430912E12 network,
0.0 memory}, id = 142283
> 03-07                                  HashToRandomExchange(dist0=[[$1]]) : rowType =
RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount
= 2.87997024E8, cumulative cost = {1.151988096E9 rows, 6.623931552E9 cpu, 0.0 io, 3.538907430912E12
network, 0.0 memory}, id = 142282
> 04-01                                    UnorderedMuxExchange : rowType = RecordType(ANY
ss_net_paid_inc_tax, ANY ss_store_sk, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8,
cumulative cost = {8.63991072E8 rows, 2.015979168E9 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 142281
> 05-01                                      Project(ss_net_paid_inc_tax=[$0], ss_store_sk=[$1],
E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($1))]) : rowType = RecordType(ANY ss_net_paid_inc_tax,
ANY ss_store_sk, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost
= {5.75994048E8 rows, 1.727982144E9 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 142280
> 05-02                                        Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=maprfs:///drill/testdata/tpcds_sf100/parquet/store_sales]], selectionRoot=/drill/testdata/tpcds_sf100/parquet/store_sales,
numFiles=1, columns=[`ss_net_paid_inc_tax`, `ss_store_sk`]]]) : rowType = RecordType(ANY ss_net_paid_inc_tax,
ANY ss_store_sk): rowcount = 2.87997024E8, cumulative cost = {2.87997024E8 rows, 5.75994048E8
cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 142279
> {code}
> Log attached. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message