Aaron Molitor created TRAFODION-2241:
----------------------------------------
Summary: TPC-H 100GB query10 fails to complete in less than 90
minutes
Key: TRAFODION-2241
URL: https://issues.apache.org/jira/browse/TRAFODION-2241
Project: Apache Trafodion
Issue Type: Bug
Affects Versions: 2.0-incubating
Environment: 9 node CDH 5.4.10 cluster
Dell R420
- 2 x E5-2430v2 2.5GHz (6C 12T)
- 64GB RAM
- 4x1TB SATA
Reporter: Aaron Molitor
h5. Attempting to run the following:
{noformat}
SQL>@10.sql
SQL>SELECT
+> C_CUSTKEY,
+> C_NAME,
+> SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE,
+> C_ACCTBAL,
+> N_NAME,
+> C_ADDRESS,
+> C_PHONE,
+> C_COMMENT
+>FROM
+> TPCH.CUSTOMER,
+> TPCH.ORDERS,
+> TPCH.LINEITEM,
+> TPCH.NATION
+>WHERE
+> C_CUSTKEY = O_CUSTKEY
+> AND L_ORDERKEY = O_ORDERKEY
+> AND O_ORDERDATE >= TO_DATE('1993-10-01')
+> AND O_ORDERDATE < TO_DATE('1994-01-01')
+> AND L_RETURNFLAG = 'R'
+> AND C_NATIONKEY = N_NATIONKEY
+>GROUP BY
+> C_CUSTKEY,
+> C_NAME,
+> C_ACCTBAL,
+> C_PHONE,
+> N_NAME,
+> C_ADDRESS,
+> C_COMMENT
+>ORDER BY
+> REVENUE DESC
+>LIMIT 20
+>;
{noformat}
h5. query statistics are (at the time of writing this):
{noformat}
SQL>GET STATISTICS FOR QID
MXID11004009150212341331081582351000000000206U3333300_884_SQL_CUR_6;
Qid
MXID11004009150212341331081582351000000000206U3333300_884_SQL_CUR_6
Compile Start Time 2016/09/23 03:23:15.974896
Compile End Time 2016/09/23 03:23:17.013202
Compile Elapsed Time 0:00:01.038306
Execute Start Time 2016/09/23 03:23:17.014161
Execute End Time -1
Execute Elapsed Time 1:29:13.390991
State OPEN
Rows Affected -1
SQL Error Code 0
Stats Error Code 0
Query Type SQL_SELECT_NON_UNIQUE
Sub Query Type SQL_STMT_NA
Estimated Accessed Rows 0
Estimated Used Rows 0
Parent Qid NONE
Parent Query System NONE
Child Qid NONE
Number of SQL Processes 69
Number of Cpus 9
Transaction Id -1
Source String SELECT
C_CUSTKEY,
C_NAME,
SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE,
C_ACCTBAL,
N_NAME,
C_ADDRESS,
C_PHONE,
C_COMMENT
FROM
TPCH.CUSTOMER,
TPCH.ORDERS,
TPCH.LINEITEM,
TPCH.NATION
WHERE
C_CUSTKEY = O_CUSTKEY
AND L_ORDER
SQL Source Length 536
Rows Returned 0
First Row Returned Time -1
Last Error before AQR 0
Number of AQR retries 0
Delay before AQR 0
No. of times reclaimed 0
Cancel Time -1
Last Suspend Time -1
Stats Collection Type OPERATOR_STATS
SQL Process Busy Time 3,766,170,959
UDR Process Busy Time 0
SQL Space Allocated 62,778 KB
SQL Space Used 60,654 KB
SQL Heap Allocated 910,579 KB
SQL Heap Used 909,016 KB
EID Space Allocated 0 KB
EID Space Used 0 KB
EID Heap Allocated 0 KB
EID Heap Used 0 KB
Processes Created 0
Process Create Time 0
Request Message Count 168,700
Request Message Bytes 28,540,176
Reply Message Count 167,984
Reply Message Bytes 5,115,694,240
Scr. Overflow Mode MMAP
Scr File Count 0
Scr. Buffer Blk Size 0
Scr. Buffer Blks Read 0
Scr. Buffer Blks Written 0
Scr. Read Count 0
Scr. Write Count 0
Table Name
Records Accessed Records Used HBase/Hive HBase/Hive
HBase/Hive IO HBase/Hive IO
Estimated/Actual Estimated/Actual IOs IO MBytes Sum
Time Max Time
TRAFODION.TPCH.NATION
0 25
25 25 16 0
106,049 15,340
TRAFODION.TPCH.ORDERS
0 5,798,005
150,000,000 5,730,378 15,012 9,727
2,655,323,003 378,267,484
TRAFODION.TPCH.LINEITEM
0 148,139,232
289,486,416 71,437,458 28,959 32,853
6,995,561,837 1,917,714,455
TRAFODION.TPCH.CUSTOMER
0 15,000,000
80,000 31,963 16 7
3,611,993 529,225
--- SQL operation complete.
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
