>
> Hi Dev team,
>
> I was evaluating Apache Phoenix against the TPC-H data based on the
> presentation given at Hadoop summit in june stating that most TPC-H queries
> should run.
> Here is the setup details i have in my local environment :
>
> 1. One master node and 3 region servers with 3.6 TB Disks space, 62.9 GB
> memory with 24 CPU cores (OS: centos-release-6-8.el6.centos.12.3.x86_64 )
> 2. I am running the phoenix parcel (4.7.0) on Cloudera
> 5.7.2-1.cdh5.7.2.p0.18.
>
> The data got uploaded and a compaction was manually triggered on hbase.
> There were 2 problems we were trying to find the answer to :
>
> 1. While doing explain plan on standard TPCH data on LINEITEM table provided
> it shows 8,649,179,394 rows but there are only 600,000,000 records uploaded.
>
> 0: jdbc:phoenix> explain select * from TPCH.LINEITEM where L_SUPPKEY = 768951;
> +---------------------------------------------------------------------------------------------------------------+
> | PLAN
> |
> +---------------------------------------------------------------------------------------------------------------+
> | CLIENT 1458-CHUNK 8649179394 ROWS 424044167376 BYTES PARALLEL 1-WAY ROUND
> ROBIN FULL SCAN OVER TPCH.LINEITEM |
> | SERVER FILTER BY L_SUPPKEY = 768951
> |
> +---------------------------------------------------------------------------------------------------------------+
> 2 rows selected (3.036 seconds)
>
> I could not do a count(*) on the table due to the fact that it always failed
> for me with the error code Error: Operation timed out. (state=TIM01,code=6000)
>
> 2. Secondly, I was not able to also run a simple query01 published by TPCH as
> it times out regularly:
>
>
> 0: jdbc:phoenix:stl-colo-srv050> select l_returnflag,
> l_linestatus,sum(l_quantity) as sum_qty,sum(l_extendedprice) as
> sum_base_price,sum(l_extendedprice * (1 - l_discount)) as
> sum_disc_price,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as
> sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) as
> avg_price,avg(l_discount) as avg_disc,count(*) as count_order from
> TPCH.lineitem where l_shipdate <= current_date()- 90 group by
> l_returnflag,l_linestatus order by l_returnflag,l_linestatus
> . . . . . . . . . . . . . . . . . . . . . . .> ;
> Error: Operation timed out. (state=TIM01,code=6000)
> java.sql.SQLTimeoutException: Operation timed out.
> at
> org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:359)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> at
> org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:728)
> at
> org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:638)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.getMinHeap(MergeSortResultIterator.java:72)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.minIterator(MergeSortResultIterator.java:93)
> at
> org.apache.phoenix.iterate.MergeSortResultIterator.next(MergeSortResultIterator.java:58)
> at
> org.apache.phoenix.iterate.BaseGroupedAggregatingResultIterator.next(BaseGroupedAggregatingResultIterator.java:64)
> at
> org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:778)
> at sqlline.BufferedRows.<init>(BufferedRows.java:37)
> at sqlline.SqlLine.print(SqlLine.java:1650)
> at sqlline.Commands.execute(Commands.java:833)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> 0: jdbc:phoenix:stl-colo-srv050>
>
> On firing smaller queries like attaching a limit the data comes in fine :
>
>
> 0: jdbc:phoenix:stl-colo-srv052> select * from TPCH.LINEITEM limit 10;
> +-------------+------------+------------+---------------+-------------+------------------+-------------+--------+---------------+---------------+--------------------------+--------------------------+---------+
> | L_ORDERKEY | L_PARTKEY | L_SUPPKEY | L_LINENUMBER | L_QUANTITY |
> L_EXTENDEDPRICE | L_DISCOUNT | L_TAX | L_RETURNFLAG | L_LINESTATUS |
> L_SHIPDATE | L_COMMITDATE | L_ |
> +-------------+------------+------------+---------------+-------------+------------------+-------------+--------+---------------+---------------+--------------------------+--------------------------+---------+
> | 1 | 15518935 | 768951 | 1 | 17 |
> 33203.72 | 0.04 | 0.02 | N | O |
> 1996-03-13 00:00:00.000 | 1996-02-12 00:00:00.000 | 1996-03 |
> | 1 | 6730908 | 730909 | 2 | 36 |
> 69788.52 | 0.09 | 0.06 | N | O |
> 1996-04-12 00:00:00.000 | 1996-02-28 00:00:00.000 | 1996-04 |
> | 1 | 6369978 | 369979 | 3 | 8 |
> 16381.28 | 0.1 | 0.02 | N | O |
> 1996-01-29 00:00:00.000 | 1996-03-05 00:00:00.000 | 1996-01 |
> | 1 | 213150 | 463151 | 4 | 28 |
> 29767.92 | 0.09 | 0.06 | N | O |
> 1996-04-21 00:00:00.000 | 1996-03-30 00:00:00.000 | 1996-05 |
> | 1 | 2402664 | 152671 | 5 | 24 |
> 37596.96 | 0.1 | 0.04 | N | O |
> 1996-03-30 00:00:00.000 | 1996-03-14 00:00:00.000 | 1996-04 |
> | 1 | 1563445 | 63448 | 6 | 32 |
> 48267.84 | 0.07 | 0.02 | N | O |
> 1996-01-30 00:00:00.000 | 1996-02-07 00:00:00.000 | 1996-02 |
> | 2 | 10616973 | 116994 | 1 | 38 |
> 71798.72 | 0 | 0.05 | N | O |
> 1997-01-28 00:00:00.000 | 1997-01-14 00:00:00.000 | 1997-02 |
> | 3 | 429697 | 179698 | 1 | 45 |
> 73200.15 | 0.06 | 0 | R | F |
> 1994-02-02 00:00:00.000 | 1994-01-04 00:00:00.000 | 1994-02 |
> | 3 | 1903543 | 653547 | 2 | 49 |
> 75776.05 | 0.1 | 0 | R | F |
> 1993-11-09 00:00:00.000 | 1993-12-20 00:00:00.000 | 1993-11 |
> | 3 | 12844823 | 344848 | 3 | 27 |
> 47713.86 | 0.06 | 0.07 | A | F |
> 1994-01-16 00:00:00.000 | 1993-11-22 00:00:00.000 | 1994-01 |
> +-------------+------------+------------+---------------+-------------+------------------+-------------+--------+---------------+---------------+--------------------------+--------------------------+---------+
> 10 rows selected (0.603 seconds)
> 0: jdbc:phoenix:stl-colo-srv052>
>
>
> I am sure i am doing something wrong here and would greatly appreciate if you
> could please point me to the same.
>
> Thanks Again
>
> Amit
