phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mujtaba Chohan <mujt...@apache.org>
Subject Re: Issues while Running Apache Phoenix against TPC-H data
Date Fri, 12 Aug 2016 21:57:36 GMT
Hi Amit,

* What's the heap size of each of your region servers?
* Do you see huge amount of disk reads when you do a select count(*) from
tpch.lineitem? If yes then try setting snappy compression on your table
followed by major compaction
* Were there any deleted rows in this table? What's the row count via HBase
shell?
* What's the schema of your table? How did you load your data?
* Update statistics with 100M guidepost width and check explain plan after
this async task finishes to see if this shows approximately correct row
count. update statistics TPCH.LINEITEM  SET
"phoenix.stats.guidepost.width"=100000000;

I get the following number with 600M rows (uncompressed - default phoenix
fast_diff encoded) in TPCH.LINEITEM on a 8 node HBase 0.98.20 cluster with
12G heap/12+12 core (virtual+physical). Data is fetched from OS page cache.

select count(*) from lineitem;
+------------+
|  COUNT(1)  |
+------------+
| 600037902  |
+------------+
1 row selected (*57.012 seconds*)

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 lineitem
where l_shipdate <= current_date()- 90 group by l_returnflag,l_linestatus
order by l_returnflag,l_linestatus;
+---------------+---------------+----------------+--------------------+----------------------+-----------------------+----------+-------------+-----------+--------------+
| L_RETURNFLAG  | L_LINESTATUS  |    SUM_QTY     |   SUM_BASE_PRICE   |
SUM_DISC_PRICE    |      SUM_CHARGE       | AVG_QTY  |  AVG_PRICE  |
AVG_DISC  | COUNT_ORDER  |
+---------------+---------------+----------------+--------------------+----------------------+-----------------------+----------+-------------+-----------+--------------+
| A             | F             | 3775127758     | 5660776097194.45   |
5377736398183.9374   | 5592847429515.927026  | 25.4993  | 38236.1169  |
0.05      | 148047881    |
| N             | F             | 98553062       | 147771098385.98    |
140384965965.0348    | 145999793032.775829   | 25.5015  | 38237.1993  |
0.0499    | 3864590      |
| N             | O             | 7651423419     | 11473321691083.27  |
10899667121317.2466  | 11335664103186.27932  | 25.4998  | 38236.9914  |
0.0499    | 300058170    |
| R             | F             | 3.77572497E+9  | 5661603032745.34   |
5378513563915.4097   | 5593662252666.916161  | 25.5     | 38236.6972  |
0.05      | 148067261    |
+---------------+---------------+----------------+--------------------+----------------------+-----------------------+----------+-------------+-----------+--------------+
4 rows selected (*146.677 seconds*)

explain select count(*) from lineitem ;
+---------------------------------------------------------------------------------------------+
|
PLAN                                             |
+---------------------------------------------------------------------------------------------+
| CLIENT 4204-CHUNK 589522747 ROWS 409200095701 BYTES PARALLEL 1-WAY FULL
SCAN OVER LINEITEM  |
|     SERVER FILTER BY FIRST KEY
ONLY                                                         |
|     SERVER AGGREGATE INTO SINGLE
ROW                                                        |
+---------------------------------------------------------------------------------------------+

DDL: CREATE TABLE LINEITEM(L_ORDERKEY INTEGER not null, L_PARTKEY  INTEGER,
L_SUPPKEY  INTEGER , L_LINENUMBER  INTEGER not null, L_QUANTITY
DECIMAL(15,2), L_EXTENDEDPRICE DECIMAL(15,2), L_DISCOUNT DECIMAL(15,2),
L_TAX DECIMAL(15,2), L_RETURNFLAG  CHAR(1), L_LINESTATUS  CHAR(1),
L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT
CHAR(25), L_SHIPMODE CHAR(10), L_COMMENT VARCHAR(44) constraint pk primary
key(l_orderkey, l_linenumber));

Raw data: do curl -O http://static.druid.io/data/benchmarks/tpch
/100/lineitem.tbl.$i.gz ; done

//mujtaba






On Thu, Aug 11, 2016 at 1:33 PM, Amit Mudgal <amudgal@splicemachine.com>
wrote:

>
> Hi 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
>
>
>

Mime
View raw message