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)

Reply via email to