You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Amit Mudgal <am...@splicemachine.com> on 2016/08/11 20:33:33 UTC
Issues while Running Apache Phoenix against TPC-H data
>
> 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
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by John Leach <jl...@splicemachine.com>.
Thanks Mujtaba…
Regards,
John
> On Aug 24, 2016, at 2:29 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>
> That sounds about right for loading CSV directly on a 5-8 node cluster. As
> Gabriel/James mentioned in another thread, CSVBulkLoadTool with pre-split
> table might offer significantly better performance for large datasets.
>
> On Tue, Aug 23, 2016 at 2:17 PM, John Leach <jl...@splicemachine.com>
> wrote:
>
>> So to load a TB of data would take around 2 days? Does that seem right to
>> you?
>>
>> Regards,
>> John
>>
>>> On Aug 23, 2016, at 3:07 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>>>
>>> Since there are 100 files on which this 600M row data is split. 5
>> separate
>>> psql script running in parallel on single machine ran that loaded data
>> from
>>> files 1-20, 21-40, 41-60, 61-80, 81-100. Performance get affected as keys
>>> are in sequence in these files which lead to hot-spotting of RS, for this
>>> should also try out Salted <https://phoenix.apache.org/salted.html>
>> Phoenix
>>> table to get the best write performance. Higher batch size might offer
>>> better performance too.
>>>
>>> I've also just started data load from single psql client as loading from
>>> single machine can get bottled-necked on network I/O. I'll update on its
>>> result tomorrow and I don't think its time would be too off from loading
>>> using multiple clients.
>>>
>>>
>>> On Tue, Aug 23, 2016 at 12:49 PM, John Leach <jl...@splicemachine.com>
>>> wrote:
>>>
>>>> Mujtaba,
>>>>
>>>> Not following the import process.
>>>>
>>>> The 5 parallel psql clients means that you manually split the data into
>> 5
>>>> buckets/files/directories and then run 5 import scripts simultaneously?
>>>>
>>>> If we wanted to benchmark import performance, what would be the right
>>>> model for that?
>>>>
>>>> Thanks this is very helpful...
>>>>
>>>> Regards,
>>>> John
>>>>
>>>>
>>>>
>>>>> On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mu...@apache.org>
>> wrote:
>>>>>
>>>>> FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with
>> 64G
>>>>> total/12G HBase heap.
>>>>>
>>>>> *Data Load*
>>>>> * 5.5 hours for 600M rows
>>>>> * Method: Direct CSV load using psql.py script
>>>>> * # client machines: 1
>>>>> * Batch size 1K
>>>>> * Key order: *Sequential*
>>>>> * 5 parallel psql clients
>>>>> * No missing rows due to data load
>>>>>
>>>>> *Schema*
>>>>> CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
>>>>> INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
>>>>> B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2),
>> C.L_DISCOUNT
>>>>> DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
>>>>> C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
>>>>> C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
>>>>> C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
>>>>> l_linenumber));
>>>>>
>>>>> * 100M guidepost width
>>>>> * ~42GB data
>>>>> * Uncompressed/default fast-diff encoded
>>>>>
>>>>> *Performance with default 128 threadpool*
>>>>> select count(*) from lineitem_multicf;
>>>>> +------------+
>>>>> | COUNT(1) |
>>>>> +------------+
>>>>> | 600037902 |
>>>>> +------------+
>>>>> 1 row selected (*24.1** 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;
>>>>> 4 row selected (*185.2** seconds*)
>>>>>
>>>>> *Data*
>>>>> do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.
>>>> tbl.$i.gz
>>>>> ; done
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
>>>>> wrote:
>>>>>
>>>>>> It looks like you guys already have most of the TPCH queries running
>>>> based
>>>>>> on Enis’s talk in Ireland this year. Very cool.
>>>>>>
>>>>>> (Slide 20: Phoenix can execute most of the TPC-H queries!)
>>>>>>
>>>>>> Regards,
>>>>>> John Leach
>>>>>>
>>>>>>
>>>>>>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>>>>>
>>>>>>> It's TPC-DS, not -H, but this is what I was using way back when to
>> run
>>>>>> perf
>>>>>>> tests over Phoenix and the query server while I was developing on it.
>>>> The
>>>>>>> first project generates, loads the data via mapreduce and the second
>>>> tool
>>>>>>> wraps up use of jmeter to run queries in parallel.
>>>>>>>
>>>>>>> https://github.com/ndimiduk/tpcds-gen
>>>>>>> https://github.com/ndimiduk/phoenix-performance
>>>>>>>
>>>>>>> Probably there's dust and bit-rot to brush off of both projects, but
>>>>>> maybe
>>>>>>> it'll help someone looking for a starting point?
>>>>>>>
>>>>>>> Apologies, but I haven't had time to see what the speakers have
>> shared
>>>>>>> about their setup.
>>>>>>>
>>>>>>> -n
>>>>>>>
>>>>>>> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org>
>>>> wrote:
>>>>>>>
>>>>>>>>> Maybe there's such a test harness that already exists for TPC?
>>>>>>>>
>>>>>>>> TPC provides tooling but it's all proprietary. The generated data
>> can
>>>> be
>>>>>>>> kept separately (Druid does it at least -
>>>>>>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
>>>>>>>> ).
>>>>>>>>
>>>>>>>> I'd say there would be one time setup: generation of data sets of
>>>>>> various
>>>>>>>> sizes, conversion to compressed CSV, and upload to somewhere public
>>>>>> (S3?).
>>>>>>>> Not strictly necessary, but it would save everyone a lot of time and
>>>>>> hassle
>>>>>>>> to not have to download the TPC data generators and munge the output
>>>>>> every
>>>>>>>> time. For this one could use the TPC tools.
>>>>>>>>
>>>>>>>> Then, the most sensible avenue I think would be implementation of
>> new
>>>>>>>> Phoenix integration tests that consume that data and run uniquely
>>>>>> tweaked
>>>>>>>> queries (yeah - every datastore vendor must do that with TPC).
>> Phoenix
>>>>>> can
>>>>>>>> use hbase-it and get the cluster and chaos tooling such as it is for
>>>>>> free,
>>>>>>>> but the upsert/initialization/bulk load and query tooling would be
>> all
>>>>>>>> Phoenix based: the CSV loader, the JDBC driver.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <
>> jamestaylor@apache.org
>>>>>>>> <javascript:;>>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <
>> apurtell@apache.org
>>>>>>>> <javascript:;>>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>> I have a long interest in 'canned' loadings. Interesting ones are
>>>>>>>> hard
>>>>>>>>> to
>>>>>>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try
>>>> it.
>>>>>>>>>>
>>>>>>>>>> Likewise
>>>>>>>>>>
>>>>>>>>>>> But I don't want to be the first to try it. I am not a Phoenix
>>>>>>>> expert.
>>>>>>>>>>
>>>>>>>>>> Same here, I'd just email dev@phoenix with a report that TPC
>> query
>>>>>> XYZ
>>>>>>>>>> didn't work and that would be as far as I could get.
>>>>>>>>>>
>>>>>>>>>> I don't think the first phase would require Phoenix experience.
>> It's
>>>>>>>> more
>>>>>>>>> around the automation for running each TPC benchmark so the process
>>>> is
>>>>>>>>> repeatable:
>>>>>>>>> - pulling in the data
>>>>>>>>> - scripting the jobs
>>>>>>>>> - having a test harness they run inside
>>>>>>>>> - identifying the queries that don't work (ideally you wouldn't
>> stop
>>>> at
>>>>>>>> the
>>>>>>>>> first error)
>>>>>>>>> - filing JIRAs for these
>>>>>>>>>
>>>>>>>>> The entire framework could be built and tested using standard JDBC
>>>>>> APIs,
>>>>>>>>> and then initially run using MySQL or some other RDBMS before
>> trying
>>>> it
>>>>>>>>> with Phoenix. Maybe there's such a test harness that already exists
>>>> for
>>>>>>>>> TPC?
>>>>>>>>>
>>>>>>>>> Then I think the next phase would require more Phoenix & HBase
>>>>>>>> experience:
>>>>>>>>> - tweaking queries where possible given any limitations in Phoenix
>>>>>>>>> - adding missing syntax (or potentially using the calcite branch
>>>> which
>>>>>>>>> supports more)
>>>>>>>>> - tweaking Phoenix schema declarations to optimize
>>>>>>>>> - tweaking Phoenix & HBase configs to optimize
>>>>>>>>> - determining which secondary indexes to add (though I think
>> there's
>>>> an
>>>>>>>>> academic paper on this, I can't seem to find it)
>>>>>>>>>
>>>>>>>>> Both phases would require a significant amount of time and effort.
>>>> Each
>>>>>>>>> benchmark would likely require unique tweaks.
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> James
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Best regards,
>>>>>>>>
>>>>>>>> - Andy
>>>>>>>>
>>>>>>>> Problems worthy of attack prove their worth by hitting back. - Piet
>>>> Hein
>>>>>>>> (via Tom White)
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Mujtaba Chohan <mu...@apache.org>.
That sounds about right for loading CSV directly on a 5-8 node cluster. As
Gabriel/James mentioned in another thread, CSVBulkLoadTool with pre-split
table might offer significantly better performance for large datasets.
On Tue, Aug 23, 2016 at 2:17 PM, John Leach <jl...@splicemachine.com>
wrote:
> So to load a TB of data would take around 2 days? Does that seem right to
> you?
>
> Regards,
> John
>
> > On Aug 23, 2016, at 3:07 PM, Mujtaba Chohan <mu...@apache.org> wrote:
> >
> > Since there are 100 files on which this 600M row data is split. 5
> separate
> > psql script running in parallel on single machine ran that loaded data
> from
> > files 1-20, 21-40, 41-60, 61-80, 81-100. Performance get affected as keys
> > are in sequence in these files which lead to hot-spotting of RS, for this
> > should also try out Salted <https://phoenix.apache.org/salted.html>
> Phoenix
> > table to get the best write performance. Higher batch size might offer
> > better performance too.
> >
> > I've also just started data load from single psql client as loading from
> > single machine can get bottled-necked on network I/O. I'll update on its
> > result tomorrow and I don't think its time would be too off from loading
> > using multiple clients.
> >
> >
> > On Tue, Aug 23, 2016 at 12:49 PM, John Leach <jl...@splicemachine.com>
> > wrote:
> >
> >> Mujtaba,
> >>
> >> Not following the import process.
> >>
> >> The 5 parallel psql clients means that you manually split the data into
> 5
> >> buckets/files/directories and then run 5 import scripts simultaneously?
> >>
> >> If we wanted to benchmark import performance, what would be the right
> >> model for that?
> >>
> >> Thanks this is very helpful...
> >>
> >> Regards,
> >> John
> >>
> >>
> >>
> >>> On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mu...@apache.org>
> wrote:
> >>>
> >>> FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with
> 64G
> >>> total/12G HBase heap.
> >>>
> >>> *Data Load*
> >>> * 5.5 hours for 600M rows
> >>> * Method: Direct CSV load using psql.py script
> >>> * # client machines: 1
> >>> * Batch size 1K
> >>> * Key order: *Sequential*
> >>> * 5 parallel psql clients
> >>> * No missing rows due to data load
> >>>
> >>> *Schema*
> >>> CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
> >>> INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
> >>> B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2),
> C.L_DISCOUNT
> >>> DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
> >>> C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
> >>> C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
> >>> C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
> >>> l_linenumber));
> >>>
> >>> * 100M guidepost width
> >>> * ~42GB data
> >>> * Uncompressed/default fast-diff encoded
> >>>
> >>> *Performance with default 128 threadpool*
> >>> select count(*) from lineitem_multicf;
> >>> +------------+
> >>> | COUNT(1) |
> >>> +------------+
> >>> | 600037902 |
> >>> +------------+
> >>> 1 row selected (*24.1** 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;
> >>> 4 row selected (*185.2** seconds*)
> >>>
> >>> *Data*
> >>> do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.
> >> tbl.$i.gz
> >>> ; done
> >>>
> >>>
> >>>
> >>> On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
> >>> wrote:
> >>>
> >>>> It looks like you guys already have most of the TPCH queries running
> >> based
> >>>> on Enis’s talk in Ireland this year. Very cool.
> >>>>
> >>>> (Slide 20: Phoenix can execute most of the TPC-H queries!)
> >>>>
> >>>> Regards,
> >>>> John Leach
> >>>>
> >>>>
> >>>>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com>
> wrote:
> >>>>>
> >>>>> It's TPC-DS, not -H, but this is what I was using way back when to
> run
> >>>> perf
> >>>>> tests over Phoenix and the query server while I was developing on it.
> >> The
> >>>>> first project generates, loads the data via mapreduce and the second
> >> tool
> >>>>> wraps up use of jmeter to run queries in parallel.
> >>>>>
> >>>>> https://github.com/ndimiduk/tpcds-gen
> >>>>> https://github.com/ndimiduk/phoenix-performance
> >>>>>
> >>>>> Probably there's dust and bit-rot to brush off of both projects, but
> >>>> maybe
> >>>>> it'll help someone looking for a starting point?
> >>>>>
> >>>>> Apologies, but I haven't had time to see what the speakers have
> shared
> >>>>> about their setup.
> >>>>>
> >>>>> -n
> >>>>>
> >>>>> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org>
> >> wrote:
> >>>>>
> >>>>>>> Maybe there's such a test harness that already exists for TPC?
> >>>>>>
> >>>>>> TPC provides tooling but it's all proprietary. The generated data
> can
> >> be
> >>>>>> kept separately (Druid does it at least -
> >>>>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
> >>>>>> ).
> >>>>>>
> >>>>>> I'd say there would be one time setup: generation of data sets of
> >>>> various
> >>>>>> sizes, conversion to compressed CSV, and upload to somewhere public
> >>>> (S3?).
> >>>>>> Not strictly necessary, but it would save everyone a lot of time and
> >>>> hassle
> >>>>>> to not have to download the TPC data generators and munge the output
> >>>> every
> >>>>>> time. For this one could use the TPC tools.
> >>>>>>
> >>>>>> Then, the most sensible avenue I think would be implementation of
> new
> >>>>>> Phoenix integration tests that consume that data and run uniquely
> >>>> tweaked
> >>>>>> queries (yeah - every datastore vendor must do that with TPC).
> Phoenix
> >>>> can
> >>>>>> use hbase-it and get the cluster and chaos tooling such as it is for
> >>>> free,
> >>>>>> but the upsert/initialization/bulk load and query tooling would be
> all
> >>>>>> Phoenix based: the CSV loader, the JDBC driver.
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <
> jamestaylor@apache.org
> >>>>>> <javascript:;>>
> >>>>>> wrote:
> >>>>>>
> >>>>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <
> apurtell@apache.org
> >>>>>> <javascript:;>>
> >>>>>>> wrote:
> >>>>>>>
> >>>>>>>>> I have a long interest in 'canned' loadings. Interesting ones are
> >>>>>> hard
> >>>>>>> to
> >>>>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try
> >> it.
> >>>>>>>>
> >>>>>>>> Likewise
> >>>>>>>>
> >>>>>>>>> But I don't want to be the first to try it. I am not a Phoenix
> >>>>>> expert.
> >>>>>>>>
> >>>>>>>> Same here, I'd just email dev@phoenix with a report that TPC
> query
> >>>> XYZ
> >>>>>>>> didn't work and that would be as far as I could get.
> >>>>>>>>
> >>>>>>>> I don't think the first phase would require Phoenix experience.
> It's
> >>>>>> more
> >>>>>>> around the automation for running each TPC benchmark so the process
> >> is
> >>>>>>> repeatable:
> >>>>>>> - pulling in the data
> >>>>>>> - scripting the jobs
> >>>>>>> - having a test harness they run inside
> >>>>>>> - identifying the queries that don't work (ideally you wouldn't
> stop
> >> at
> >>>>>> the
> >>>>>>> first error)
> >>>>>>> - filing JIRAs for these
> >>>>>>>
> >>>>>>> The entire framework could be built and tested using standard JDBC
> >>>> APIs,
> >>>>>>> and then initially run using MySQL or some other RDBMS before
> trying
> >> it
> >>>>>>> with Phoenix. Maybe there's such a test harness that already exists
> >> for
> >>>>>>> TPC?
> >>>>>>>
> >>>>>>> Then I think the next phase would require more Phoenix & HBase
> >>>>>> experience:
> >>>>>>> - tweaking queries where possible given any limitations in Phoenix
> >>>>>>> - adding missing syntax (or potentially using the calcite branch
> >> which
> >>>>>>> supports more)
> >>>>>>> - tweaking Phoenix schema declarations to optimize
> >>>>>>> - tweaking Phoenix & HBase configs to optimize
> >>>>>>> - determining which secondary indexes to add (though I think
> there's
> >> an
> >>>>>>> academic paper on this, I can't seem to find it)
> >>>>>>>
> >>>>>>> Both phases would require a significant amount of time and effort.
> >> Each
> >>>>>>> benchmark would likely require unique tweaks.
> >>>>>>>
> >>>>>>> Thanks,
> >>>>>>> James
> >>>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Best regards,
> >>>>>>
> >>>>>> - Andy
> >>>>>>
> >>>>>> Problems worthy of attack prove their worth by hitting back. - Piet
> >> Hein
> >>>>>> (via Tom White)
> >>>>>>
> >>>>
> >>>>
> >>
> >>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by John Leach <jl...@splicemachine.com>.
So to load a TB of data would take around 2 days? Does that seem right to you?
Regards,
John
> On Aug 23, 2016, at 3:07 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>
> Since there are 100 files on which this 600M row data is split. 5 separate
> psql script running in parallel on single machine ran that loaded data from
> files 1-20, 21-40, 41-60, 61-80, 81-100. Performance get affected as keys
> are in sequence in these files which lead to hot-spotting of RS, for this
> should also try out Salted <https://phoenix.apache.org/salted.html> Phoenix
> table to get the best write performance. Higher batch size might offer
> better performance too.
>
> I've also just started data load from single psql client as loading from
> single machine can get bottled-necked on network I/O. I'll update on its
> result tomorrow and I don't think its time would be too off from loading
> using multiple clients.
>
>
> On Tue, Aug 23, 2016 at 12:49 PM, John Leach <jl...@splicemachine.com>
> wrote:
>
>> Mujtaba,
>>
>> Not following the import process.
>>
>> The 5 parallel psql clients means that you manually split the data into 5
>> buckets/files/directories and then run 5 import scripts simultaneously?
>>
>> If we wanted to benchmark import performance, what would be the right
>> model for that?
>>
>> Thanks this is very helpful...
>>
>> Regards,
>> John
>>
>>
>>
>>> On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>>>
>>> FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with 64G
>>> total/12G HBase heap.
>>>
>>> *Data Load*
>>> * 5.5 hours for 600M rows
>>> * Method: Direct CSV load using psql.py script
>>> * # client machines: 1
>>> * Batch size 1K
>>> * Key order: *Sequential*
>>> * 5 parallel psql clients
>>> * No missing rows due to data load
>>>
>>> *Schema*
>>> CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
>>> INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
>>> B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2), C.L_DISCOUNT
>>> DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
>>> C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
>>> C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
>>> C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
>>> l_linenumber));
>>>
>>> * 100M guidepost width
>>> * ~42GB data
>>> * Uncompressed/default fast-diff encoded
>>>
>>> *Performance with default 128 threadpool*
>>> select count(*) from lineitem_multicf;
>>> +------------+
>>> | COUNT(1) |
>>> +------------+
>>> | 600037902 |
>>> +------------+
>>> 1 row selected (*24.1** 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;
>>> 4 row selected (*185.2** seconds*)
>>>
>>> *Data*
>>> do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.
>> tbl.$i.gz
>>> ; done
>>>
>>>
>>>
>>> On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
>>> wrote:
>>>
>>>> It looks like you guys already have most of the TPCH queries running
>> based
>>>> on Enis’s talk in Ireland this year. Very cool.
>>>>
>>>> (Slide 20: Phoenix can execute most of the TPC-H queries!)
>>>>
>>>> Regards,
>>>> John Leach
>>>>
>>>>
>>>>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>>>>>
>>>>> It's TPC-DS, not -H, but this is what I was using way back when to run
>>>> perf
>>>>> tests over Phoenix and the query server while I was developing on it.
>> The
>>>>> first project generates, loads the data via mapreduce and the second
>> tool
>>>>> wraps up use of jmeter to run queries in parallel.
>>>>>
>>>>> https://github.com/ndimiduk/tpcds-gen
>>>>> https://github.com/ndimiduk/phoenix-performance
>>>>>
>>>>> Probably there's dust and bit-rot to brush off of both projects, but
>>>> maybe
>>>>> it'll help someone looking for a starting point?
>>>>>
>>>>> Apologies, but I haven't had time to see what the speakers have shared
>>>>> about their setup.
>>>>>
>>>>> -n
>>>>>
>>>>> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org>
>> wrote:
>>>>>
>>>>>>> Maybe there's such a test harness that already exists for TPC?
>>>>>>
>>>>>> TPC provides tooling but it's all proprietary. The generated data can
>> be
>>>>>> kept separately (Druid does it at least -
>>>>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
>>>>>> ).
>>>>>>
>>>>>> I'd say there would be one time setup: generation of data sets of
>>>> various
>>>>>> sizes, conversion to compressed CSV, and upload to somewhere public
>>>> (S3?).
>>>>>> Not strictly necessary, but it would save everyone a lot of time and
>>>> hassle
>>>>>> to not have to download the TPC data generators and munge the output
>>>> every
>>>>>> time. For this one could use the TPC tools.
>>>>>>
>>>>>> Then, the most sensible avenue I think would be implementation of new
>>>>>> Phoenix integration tests that consume that data and run uniquely
>>>> tweaked
>>>>>> queries (yeah - every datastore vendor must do that with TPC). Phoenix
>>>> can
>>>>>> use hbase-it and get the cluster and chaos tooling such as it is for
>>>> free,
>>>>>> but the upsert/initialization/bulk load and query tooling would be all
>>>>>> Phoenix based: the CSV loader, the JDBC driver.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
>>>>>> <javascript:;>>
>>>>>> wrote:
>>>>>>
>>>>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
>>>>>> <javascript:;>>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>> I have a long interest in 'canned' loadings. Interesting ones are
>>>>>> hard
>>>>>>> to
>>>>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try
>> it.
>>>>>>>>
>>>>>>>> Likewise
>>>>>>>>
>>>>>>>>> But I don't want to be the first to try it. I am not a Phoenix
>>>>>> expert.
>>>>>>>>
>>>>>>>> Same here, I'd just email dev@phoenix with a report that TPC query
>>>> XYZ
>>>>>>>> didn't work and that would be as far as I could get.
>>>>>>>>
>>>>>>>> I don't think the first phase would require Phoenix experience. It's
>>>>>> more
>>>>>>> around the automation for running each TPC benchmark so the process
>> is
>>>>>>> repeatable:
>>>>>>> - pulling in the data
>>>>>>> - scripting the jobs
>>>>>>> - having a test harness they run inside
>>>>>>> - identifying the queries that don't work (ideally you wouldn't stop
>> at
>>>>>> the
>>>>>>> first error)
>>>>>>> - filing JIRAs for these
>>>>>>>
>>>>>>> The entire framework could be built and tested using standard JDBC
>>>> APIs,
>>>>>>> and then initially run using MySQL or some other RDBMS before trying
>> it
>>>>>>> with Phoenix. Maybe there's such a test harness that already exists
>> for
>>>>>>> TPC?
>>>>>>>
>>>>>>> Then I think the next phase would require more Phoenix & HBase
>>>>>> experience:
>>>>>>> - tweaking queries where possible given any limitations in Phoenix
>>>>>>> - adding missing syntax (or potentially using the calcite branch
>> which
>>>>>>> supports more)
>>>>>>> - tweaking Phoenix schema declarations to optimize
>>>>>>> - tweaking Phoenix & HBase configs to optimize
>>>>>>> - determining which secondary indexes to add (though I think there's
>> an
>>>>>>> academic paper on this, I can't seem to find it)
>>>>>>>
>>>>>>> Both phases would require a significant amount of time and effort.
>> Each
>>>>>>> benchmark would likely require unique tweaks.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> James
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Best regards,
>>>>>>
>>>>>> - Andy
>>>>>>
>>>>>> Problems worthy of attack prove their worth by hitting back. - Piet
>> Hein
>>>>>> (via Tom White)
>>>>>>
>>>>
>>>>
>>
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Mujtaba Chohan <mu...@apache.org>.
Since there are 100 files on which this 600M row data is split. 5 separate
psql script running in parallel on single machine ran that loaded data from
files 1-20, 21-40, 41-60, 61-80, 81-100. Performance get affected as keys
are in sequence in these files which lead to hot-spotting of RS, for this
should also try out Salted <https://phoenix.apache.org/salted.html> Phoenix
table to get the best write performance. Higher batch size might offer
better performance too.
I've also just started data load from single psql client as loading from
single machine can get bottled-necked on network I/O. I'll update on its
result tomorrow and I don't think its time would be too off from loading
using multiple clients.
On Tue, Aug 23, 2016 at 12:49 PM, John Leach <jl...@splicemachine.com>
wrote:
> Mujtaba,
>
> Not following the import process.
>
> The 5 parallel psql clients means that you manually split the data into 5
> buckets/files/directories and then run 5 import scripts simultaneously?
>
> If we wanted to benchmark import performance, what would be the right
> model for that?
>
> Thanks this is very helpful...
>
> Regards,
> John
>
>
>
> > On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mu...@apache.org> wrote:
> >
> > FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with 64G
> > total/12G HBase heap.
> >
> > *Data Load*
> > * 5.5 hours for 600M rows
> > * Method: Direct CSV load using psql.py script
> > * # client machines: 1
> > * Batch size 1K
> > * Key order: *Sequential*
> > * 5 parallel psql clients
> > * No missing rows due to data load
> >
> > *Schema*
> > CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
> > INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
> > B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2), C.L_DISCOUNT
> > DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
> > C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
> > C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
> > C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
> > l_linenumber));
> >
> > * 100M guidepost width
> > * ~42GB data
> > * Uncompressed/default fast-diff encoded
> >
> > *Performance with default 128 threadpool*
> > select count(*) from lineitem_multicf;
> > +------------+
> > | COUNT(1) |
> > +------------+
> > | 600037902 |
> > +------------+
> > 1 row selected (*24.1** 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;
> > 4 row selected (*185.2** seconds*)
> >
> > *Data*
> > do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.
> tbl.$i.gz
> > ; done
> >
> >
> >
> > On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
> > wrote:
> >
> >> It looks like you guys already have most of the TPCH queries running
> based
> >> on Enis’s talk in Ireland this year. Very cool.
> >>
> >> (Slide 20: Phoenix can execute most of the TPC-H queries!)
> >>
> >> Regards,
> >> John Leach
> >>
> >>
> >>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com> wrote:
> >>>
> >>> It's TPC-DS, not -H, but this is what I was using way back when to run
> >> perf
> >>> tests over Phoenix and the query server while I was developing on it.
> The
> >>> first project generates, loads the data via mapreduce and the second
> tool
> >>> wraps up use of jmeter to run queries in parallel.
> >>>
> >>> https://github.com/ndimiduk/tpcds-gen
> >>> https://github.com/ndimiduk/phoenix-performance
> >>>
> >>> Probably there's dust and bit-rot to brush off of both projects, but
> >> maybe
> >>> it'll help someone looking for a starting point?
> >>>
> >>> Apologies, but I haven't had time to see what the speakers have shared
> >>> about their setup.
> >>>
> >>> -n
> >>>
> >>> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org>
> wrote:
> >>>
> >>>>> Maybe there's such a test harness that already exists for TPC?
> >>>>
> >>>> TPC provides tooling but it's all proprietary. The generated data can
> be
> >>>> kept separately (Druid does it at least -
> >>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
> >>>> ).
> >>>>
> >>>> I'd say there would be one time setup: generation of data sets of
> >> various
> >>>> sizes, conversion to compressed CSV, and upload to somewhere public
> >> (S3?).
> >>>> Not strictly necessary, but it would save everyone a lot of time and
> >> hassle
> >>>> to not have to download the TPC data generators and munge the output
> >> every
> >>>> time. For this one could use the TPC tools.
> >>>>
> >>>> Then, the most sensible avenue I think would be implementation of new
> >>>> Phoenix integration tests that consume that data and run uniquely
> >> tweaked
> >>>> queries (yeah - every datastore vendor must do that with TPC). Phoenix
> >> can
> >>>> use hbase-it and get the cluster and chaos tooling such as it is for
> >> free,
> >>>> but the upsert/initialization/bulk load and query tooling would be all
> >>>> Phoenix based: the CSV loader, the JDBC driver.
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
> >>>> <javascript:;>>
> >>>> wrote:
> >>>>
> >>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
> >>>> <javascript:;>>
> >>>>> wrote:
> >>>>>
> >>>>>>> I have a long interest in 'canned' loadings. Interesting ones are
> >>>> hard
> >>>>> to
> >>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try
> it.
> >>>>>>
> >>>>>> Likewise
> >>>>>>
> >>>>>>> But I don't want to be the first to try it. I am not a Phoenix
> >>>> expert.
> >>>>>>
> >>>>>> Same here, I'd just email dev@phoenix with a report that TPC query
> >> XYZ
> >>>>>> didn't work and that would be as far as I could get.
> >>>>>>
> >>>>>> I don't think the first phase would require Phoenix experience. It's
> >>>> more
> >>>>> around the automation for running each TPC benchmark so the process
> is
> >>>>> repeatable:
> >>>>> - pulling in the data
> >>>>> - scripting the jobs
> >>>>> - having a test harness they run inside
> >>>>> - identifying the queries that don't work (ideally you wouldn't stop
> at
> >>>> the
> >>>>> first error)
> >>>>> - filing JIRAs for these
> >>>>>
> >>>>> The entire framework could be built and tested using standard JDBC
> >> APIs,
> >>>>> and then initially run using MySQL or some other RDBMS before trying
> it
> >>>>> with Phoenix. Maybe there's such a test harness that already exists
> for
> >>>>> TPC?
> >>>>>
> >>>>> Then I think the next phase would require more Phoenix & HBase
> >>>> experience:
> >>>>> - tweaking queries where possible given any limitations in Phoenix
> >>>>> - adding missing syntax (or potentially using the calcite branch
> which
> >>>>> supports more)
> >>>>> - tweaking Phoenix schema declarations to optimize
> >>>>> - tweaking Phoenix & HBase configs to optimize
> >>>>> - determining which secondary indexes to add (though I think there's
> an
> >>>>> academic paper on this, I can't seem to find it)
> >>>>>
> >>>>> Both phases would require a significant amount of time and effort.
> Each
> >>>>> benchmark would likely require unique tweaks.
> >>>>>
> >>>>> Thanks,
> >>>>> James
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Best regards,
> >>>>
> >>>> - Andy
> >>>>
> >>>> Problems worthy of attack prove their worth by hitting back. - Piet
> Hein
> >>>> (via Tom White)
> >>>>
> >>
> >>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by John Leach <jl...@splicemachine.com>.
Mujtaba,
Not following the import process.
The 5 parallel psql clients means that you manually split the data into 5 buckets/files/directories and then run 5 import scripts simultaneously?
If we wanted to benchmark import performance, what would be the right model for that?
Thanks this is very helpful...
Regards,
John
> On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>
> FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with 64G
> total/12G HBase heap.
>
> *Data Load*
> * 5.5 hours for 600M rows
> * Method: Direct CSV load using psql.py script
> * # client machines: 1
> * Batch size 1K
> * Key order: *Sequential*
> * 5 parallel psql clients
> * No missing rows due to data load
>
> *Schema*
> CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
> INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
> B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2), C.L_DISCOUNT
> DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
> C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
> C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
> C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
> l_linenumber));
>
> * 100M guidepost width
> * ~42GB data
> * Uncompressed/default fast-diff encoded
>
> *Performance with default 128 threadpool*
> select count(*) from lineitem_multicf;
> +------------+
> | COUNT(1) |
> +------------+
> | 600037902 |
> +------------+
> 1 row selected (*24.1** 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;
> 4 row selected (*185.2** seconds*)
>
> *Data*
> do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.tbl.$i.gz
> ; done
>
>
>
> On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
> wrote:
>
>> It looks like you guys already have most of the TPCH queries running based
>> on Enis’s talk in Ireland this year. Very cool.
>>
>> (Slide 20: Phoenix can execute most of the TPC-H queries!)
>>
>> Regards,
>> John Leach
>>
>>
>>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>>>
>>> It's TPC-DS, not -H, but this is what I was using way back when to run
>> perf
>>> tests over Phoenix and the query server while I was developing on it. The
>>> first project generates, loads the data via mapreduce and the second tool
>>> wraps up use of jmeter to run queries in parallel.
>>>
>>> https://github.com/ndimiduk/tpcds-gen
>>> https://github.com/ndimiduk/phoenix-performance
>>>
>>> Probably there's dust and bit-rot to brush off of both projects, but
>> maybe
>>> it'll help someone looking for a starting point?
>>>
>>> Apologies, but I haven't had time to see what the speakers have shared
>>> about their setup.
>>>
>>> -n
>>>
>>> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org> wrote:
>>>
>>>>> Maybe there's such a test harness that already exists for TPC?
>>>>
>>>> TPC provides tooling but it's all proprietary. The generated data can be
>>>> kept separately (Druid does it at least -
>>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
>>>> ).
>>>>
>>>> I'd say there would be one time setup: generation of data sets of
>> various
>>>> sizes, conversion to compressed CSV, and upload to somewhere public
>> (S3?).
>>>> Not strictly necessary, but it would save everyone a lot of time and
>> hassle
>>>> to not have to download the TPC data generators and munge the output
>> every
>>>> time. For this one could use the TPC tools.
>>>>
>>>> Then, the most sensible avenue I think would be implementation of new
>>>> Phoenix integration tests that consume that data and run uniquely
>> tweaked
>>>> queries (yeah - every datastore vendor must do that with TPC). Phoenix
>> can
>>>> use hbase-it and get the cluster and chaos tooling such as it is for
>> free,
>>>> but the upsert/initialization/bulk load and query tooling would be all
>>>> Phoenix based: the CSV loader, the JDBC driver.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
>>>> <javascript:;>>
>>>> wrote:
>>>>
>>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
>>>> <javascript:;>>
>>>>> wrote:
>>>>>
>>>>>>> I have a long interest in 'canned' loadings. Interesting ones are
>>>> hard
>>>>> to
>>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
>>>>>>
>>>>>> Likewise
>>>>>>
>>>>>>> But I don't want to be the first to try it. I am not a Phoenix
>>>> expert.
>>>>>>
>>>>>> Same here, I'd just email dev@phoenix with a report that TPC query
>> XYZ
>>>>>> didn't work and that would be as far as I could get.
>>>>>>
>>>>>> I don't think the first phase would require Phoenix experience. It's
>>>> more
>>>>> around the automation for running each TPC benchmark so the process is
>>>>> repeatable:
>>>>> - pulling in the data
>>>>> - scripting the jobs
>>>>> - having a test harness they run inside
>>>>> - identifying the queries that don't work (ideally you wouldn't stop at
>>>> the
>>>>> first error)
>>>>> - filing JIRAs for these
>>>>>
>>>>> The entire framework could be built and tested using standard JDBC
>> APIs,
>>>>> and then initially run using MySQL or some other RDBMS before trying it
>>>>> with Phoenix. Maybe there's such a test harness that already exists for
>>>>> TPC?
>>>>>
>>>>> Then I think the next phase would require more Phoenix & HBase
>>>> experience:
>>>>> - tweaking queries where possible given any limitations in Phoenix
>>>>> - adding missing syntax (or potentially using the calcite branch which
>>>>> supports more)
>>>>> - tweaking Phoenix schema declarations to optimize
>>>>> - tweaking Phoenix & HBase configs to optimize
>>>>> - determining which secondary indexes to add (though I think there's an
>>>>> academic paper on this, I can't seem to find it)
>>>>>
>>>>> Both phases would require a significant amount of time and effort. Each
>>>>> benchmark would likely require unique tweaks.
>>>>>
>>>>> Thanks,
>>>>> James
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>>
>>>> - Andy
>>>>
>>>> Problems worthy of attack prove their worth by hitting back. - Piet Hein
>>>> (via Tom White)
>>>>
>>
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Mujtaba Chohan <mu...@apache.org>.
FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with 64G
total/12G HBase heap.
*Data Load*
* 5.5 hours for 600M rows
* Method: Direct CSV load using psql.py script
* # client machines: 1
* Batch size 1K
* Key order: *Sequential*
* 5 parallel psql clients
* No missing rows due to data load
*Schema*
CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY
INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null,
B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2), C.L_DISCOUNT
DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1),
C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE,
C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10),
C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey,
l_linenumber));
* 100M guidepost width
* ~42GB data
* Uncompressed/default fast-diff encoded
*Performance with default 128 threadpool*
select count(*) from lineitem_multicf;
+------------+
| COUNT(1) |
+------------+
| 600037902 |
+------------+
1 row selected (*24.1** 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;
4 row selected (*185.2** seconds*)
*Data*
do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.tbl.$i.gz
; done
On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jl...@splicemachine.com>
wrote:
> It looks like you guys already have most of the TPCH queries running based
> on Enis’s talk in Ireland this year. Very cool.
>
> (Slide 20: Phoenix can execute most of the TPC-H queries!)
>
> Regards,
> John Leach
>
>
> > On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com> wrote:
> >
> > It's TPC-DS, not -H, but this is what I was using way back when to run
> perf
> > tests over Phoenix and the query server while I was developing on it. The
> > first project generates, loads the data via mapreduce and the second tool
> > wraps up use of jmeter to run queries in parallel.
> >
> > https://github.com/ndimiduk/tpcds-gen
> > https://github.com/ndimiduk/phoenix-performance
> >
> > Probably there's dust and bit-rot to brush off of both projects, but
> maybe
> > it'll help someone looking for a starting point?
> >
> > Apologies, but I haven't had time to see what the speakers have shared
> > about their setup.
> >
> > -n
> >
> > On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org> wrote:
> >
> >>> Maybe there's such a test harness that already exists for TPC?
> >>
> >> TPC provides tooling but it's all proprietary. The generated data can be
> >> kept separately (Druid does it at least -
> >> http://druid.io/blog/2014/03/17/benchmarking-druid.html
> >> ).
> >>
> >> I'd say there would be one time setup: generation of data sets of
> various
> >> sizes, conversion to compressed CSV, and upload to somewhere public
> (S3?).
> >> Not strictly necessary, but it would save everyone a lot of time and
> hassle
> >> to not have to download the TPC data generators and munge the output
> every
> >> time. For this one could use the TPC tools.
> >>
> >> Then, the most sensible avenue I think would be implementation of new
> >> Phoenix integration tests that consume that data and run uniquely
> tweaked
> >> queries (yeah - every datastore vendor must do that with TPC). Phoenix
> can
> >> use hbase-it and get the cluster and chaos tooling such as it is for
> free,
> >> but the upsert/initialization/bulk load and query tooling would be all
> >> Phoenix based: the CSV loader, the JDBC driver.
> >>
> >>
> >>
> >>
> >>
> >> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
> >> <javascript:;>>
> >> wrote:
> >>
> >>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
> >> <javascript:;>>
> >>> wrote:
> >>>
> >>>>> I have a long interest in 'canned' loadings. Interesting ones are
> >> hard
> >>> to
> >>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
> >>>>
> >>>> Likewise
> >>>>
> >>>>> But I don't want to be the first to try it. I am not a Phoenix
> >> expert.
> >>>>
> >>>> Same here, I'd just email dev@phoenix with a report that TPC query
> XYZ
> >>>> didn't work and that would be as far as I could get.
> >>>>
> >>>> I don't think the first phase would require Phoenix experience. It's
> >> more
> >>> around the automation for running each TPC benchmark so the process is
> >>> repeatable:
> >>> - pulling in the data
> >>> - scripting the jobs
> >>> - having a test harness they run inside
> >>> - identifying the queries that don't work (ideally you wouldn't stop at
> >> the
> >>> first error)
> >>> - filing JIRAs for these
> >>>
> >>> The entire framework could be built and tested using standard JDBC
> APIs,
> >>> and then initially run using MySQL or some other RDBMS before trying it
> >>> with Phoenix. Maybe there's such a test harness that already exists for
> >>> TPC?
> >>>
> >>> Then I think the next phase would require more Phoenix & HBase
> >> experience:
> >>> - tweaking queries where possible given any limitations in Phoenix
> >>> - adding missing syntax (or potentially using the calcite branch which
> >>> supports more)
> >>> - tweaking Phoenix schema declarations to optimize
> >>> - tweaking Phoenix & HBase configs to optimize
> >>> - determining which secondary indexes to add (though I think there's an
> >>> academic paper on this, I can't seem to find it)
> >>>
> >>> Both phases would require a significant amount of time and effort. Each
> >>> benchmark would likely require unique tweaks.
> >>>
> >>> Thanks,
> >>> James
> >>>
> >>
> >>
> >>
> >> --
> >> Best regards,
> >>
> >> - Andy
> >>
> >> Problems worthy of attack prove their worth by hitting back. - Piet Hein
> >> (via Tom White)
> >>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by John Leach <jl...@splicemachine.com>.
It looks like you guys already have most of the TPCH queries running based on Enis’s talk in Ireland this year. Very cool.
(Slide 20: Phoenix can execute most of the TPC-H queries!)
Regards,
John Leach
> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
> It's TPC-DS, not -H, but this is what I was using way back when to run perf
> tests over Phoenix and the query server while I was developing on it. The
> first project generates, loads the data via mapreduce and the second tool
> wraps up use of jmeter to run queries in parallel.
>
> https://github.com/ndimiduk/tpcds-gen
> https://github.com/ndimiduk/phoenix-performance
>
> Probably there's dust and bit-rot to brush off of both projects, but maybe
> it'll help someone looking for a starting point?
>
> Apologies, but I haven't had time to see what the speakers have shared
> about their setup.
>
> -n
>
> On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org> wrote:
>
>>> Maybe there's such a test harness that already exists for TPC?
>>
>> TPC provides tooling but it's all proprietary. The generated data can be
>> kept separately (Druid does it at least -
>> http://druid.io/blog/2014/03/17/benchmarking-druid.html
>> ).
>>
>> I'd say there would be one time setup: generation of data sets of various
>> sizes, conversion to compressed CSV, and upload to somewhere public (S3?).
>> Not strictly necessary, but it would save everyone a lot of time and hassle
>> to not have to download the TPC data generators and munge the output every
>> time. For this one could use the TPC tools.
>>
>> Then, the most sensible avenue I think would be implementation of new
>> Phoenix integration tests that consume that data and run uniquely tweaked
>> queries (yeah - every datastore vendor must do that with TPC). Phoenix can
>> use hbase-it and get the cluster and chaos tooling such as it is for free,
>> but the upsert/initialization/bulk load and query tooling would be all
>> Phoenix based: the CSV loader, the JDBC driver.
>>
>>
>>
>>
>>
>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
>> <javascript:;>>
>> wrote:
>>
>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
>> <javascript:;>>
>>> wrote:
>>>
>>>>> I have a long interest in 'canned' loadings. Interesting ones are
>> hard
>>> to
>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
>>>>
>>>> Likewise
>>>>
>>>>> But I don't want to be the first to try it. I am not a Phoenix
>> expert.
>>>>
>>>> Same here, I'd just email dev@phoenix with a report that TPC query XYZ
>>>> didn't work and that would be as far as I could get.
>>>>
>>>> I don't think the first phase would require Phoenix experience. It's
>> more
>>> around the automation for running each TPC benchmark so the process is
>>> repeatable:
>>> - pulling in the data
>>> - scripting the jobs
>>> - having a test harness they run inside
>>> - identifying the queries that don't work (ideally you wouldn't stop at
>> the
>>> first error)
>>> - filing JIRAs for these
>>>
>>> The entire framework could be built and tested using standard JDBC APIs,
>>> and then initially run using MySQL or some other RDBMS before trying it
>>> with Phoenix. Maybe there's such a test harness that already exists for
>>> TPC?
>>>
>>> Then I think the next phase would require more Phoenix & HBase
>> experience:
>>> - tweaking queries where possible given any limitations in Phoenix
>>> - adding missing syntax (or potentially using the calcite branch which
>>> supports more)
>>> - tweaking Phoenix schema declarations to optimize
>>> - tweaking Phoenix & HBase configs to optimize
>>> - determining which secondary indexes to add (though I think there's an
>>> academic paper on this, I can't seem to find it)
>>>
>>> Both phases would require a significant amount of time and effort. Each
>>> benchmark would likely require unique tweaks.
>>>
>>> Thanks,
>>> James
>>>
>>
>>
>>
>> --
>> Best regards,
>>
>> - Andy
>>
>> Problems worthy of attack prove their worth by hitting back. - Piet Hein
>> (via Tom White)
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Nick Dimiduk <nd...@gmail.com>.
It's TPC-DS, not -H, but this is what I was using way back when to run perf
tests over Phoenix and the query server while I was developing on it. The
first project generates, loads the data via mapreduce and the second tool
wraps up use of jmeter to run queries in parallel.
https://github.com/ndimiduk/tpcds-gen
https://github.com/ndimiduk/phoenix-performance
Probably there's dust and bit-rot to brush off of both projects, but maybe
it'll help someone looking for a starting point?
Apologies, but I haven't had time to see what the speakers have shared
about their setup.
-n
On Friday, August 19, 2016, Andrew Purtell <ap...@apache.org> wrote:
> > Maybe there's such a test harness that already exists for TPC?
>
> TPC provides tooling but it's all proprietary. The generated data can be
> kept separately (Druid does it at least -
> http://druid.io/blog/2014/03/17/benchmarking-druid.html
> ).
>
> I'd say there would be one time setup: generation of data sets of various
> sizes, conversion to compressed CSV, and upload to somewhere public (S3?).
> Not strictly necessary, but it would save everyone a lot of time and hassle
> to not have to download the TPC data generators and munge the output every
> time. For this one could use the TPC tools.
>
> Then, the most sensible avenue I think would be implementation of new
> Phoenix integration tests that consume that data and run uniquely tweaked
> queries (yeah - every datastore vendor must do that with TPC). Phoenix can
> use hbase-it and get the cluster and chaos tooling such as it is for free,
> but the upsert/initialization/bulk load and query tooling would be all
> Phoenix based: the CSV loader, the JDBC driver.
>
>
>
>
>
> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <jamestaylor@apache.org
> <javascript:;>>
> wrote:
>
> > On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <apurtell@apache.org
> <javascript:;>>
> > wrote:
> >
> > > > I have a long interest in 'canned' loadings. Interesting ones are
> hard
> > to
> > > > come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
> > >
> > > Likewise
> > >
> > > > But I don't want to be the first to try it. I am not a Phoenix
> expert.
> > >
> > > Same here, I'd just email dev@phoenix with a report that TPC query XYZ
> > > didn't work and that would be as far as I could get.
> > >
> > > I don't think the first phase would require Phoenix experience. It's
> more
> > around the automation for running each TPC benchmark so the process is
> > repeatable:
> > - pulling in the data
> > - scripting the jobs
> > - having a test harness they run inside
> > - identifying the queries that don't work (ideally you wouldn't stop at
> the
> > first error)
> > - filing JIRAs for these
> >
> > The entire framework could be built and tested using standard JDBC APIs,
> > and then initially run using MySQL or some other RDBMS before trying it
> > with Phoenix. Maybe there's such a test harness that already exists for
> > TPC?
> >
> > Then I think the next phase would require more Phoenix & HBase
> experience:
> > - tweaking queries where possible given any limitations in Phoenix
> > - adding missing syntax (or potentially using the calcite branch which
> > supports more)
> > - tweaking Phoenix schema declarations to optimize
> > - tweaking Phoenix & HBase configs to optimize
> > - determining which secondary indexes to add (though I think there's an
> > academic paper on this, I can't seem to find it)
> >
> > Both phases would require a significant amount of time and effort. Each
> > benchmark would likely require unique tweaks.
> >
> > Thanks,
> > James
> >
>
>
>
> --
> Best regards,
>
> - Andy
>
> Problems worthy of attack prove their worth by hitting back. - Piet Hein
> (via Tom White)
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Andrew Purtell <ap...@apache.org>.
> Maybe there's such a test harness that already exists for TPC?
TPC provides tooling but it's all proprietary. The generated data can be
kept separately (Druid does it at least -
http://druid.io/blog/2014/03/17/benchmarking-druid.html
).
I'd say there would be one time setup: generation of data sets of various
sizes, conversion to compressed CSV, and upload to somewhere public (S3?).
Not strictly necessary, but it would save everyone a lot of time and hassle
to not have to download the TPC data generators and munge the output every
time. For this one could use the TPC tools.
Then, the most sensible avenue I think would be implementation of new
Phoenix integration tests that consume that data and run uniquely tweaked
queries (yeah - every datastore vendor must do that with TPC). Phoenix can
use hbase-it and get the cluster and chaos tooling such as it is for free,
but the upsert/initialization/bulk load and query tooling would be all
Phoenix based: the CSV loader, the JDBC driver.
On Fri, Aug 19, 2016 at 5:31 PM, James Taylor <ja...@apache.org>
wrote:
> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <ap...@apache.org>
> wrote:
>
> > > I have a long interest in 'canned' loadings. Interesting ones are hard
> to
> > > come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
> >
> > Likewise
> >
> > > But I don't want to be the first to try it. I am not a Phoenix expert.
> >
> > Same here, I'd just email dev@phoenix with a report that TPC query XYZ
> > didn't work and that would be as far as I could get.
> >
> > I don't think the first phase would require Phoenix experience. It's more
> around the automation for running each TPC benchmark so the process is
> repeatable:
> - pulling in the data
> - scripting the jobs
> - having a test harness they run inside
> - identifying the queries that don't work (ideally you wouldn't stop at the
> first error)
> - filing JIRAs for these
>
> The entire framework could be built and tested using standard JDBC APIs,
> and then initially run using MySQL or some other RDBMS before trying it
> with Phoenix. Maybe there's such a test harness that already exists for
> TPC?
>
> Then I think the next phase would require more Phoenix & HBase experience:
> - tweaking queries where possible given any limitations in Phoenix
> - adding missing syntax (or potentially using the calcite branch which
> supports more)
> - tweaking Phoenix schema declarations to optimize
> - tweaking Phoenix & HBase configs to optimize
> - determining which secondary indexes to add (though I think there's an
> academic paper on this, I can't seem to find it)
>
> Both phases would require a significant amount of time and effort. Each
> benchmark would likely require unique tweaks.
>
> Thanks,
> James
>
--
Best regards,
- Andy
Problems worthy of attack prove their worth by hitting back. - Piet Hein
(via Tom White)
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell <ap...@apache.org> wrote:
> > I have a long interest in 'canned' loadings. Interesting ones are hard to
> > come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
>
> Likewise
>
> > But I don't want to be the first to try it. I am not a Phoenix expert.
>
> Same here, I'd just email dev@phoenix with a report that TPC query XYZ
> didn't work and that would be as far as I could get.
>
> I don't think the first phase would require Phoenix experience. It's more
around the automation for running each TPC benchmark so the process is
repeatable:
- pulling in the data
- scripting the jobs
- having a test harness they run inside
- identifying the queries that don't work (ideally you wouldn't stop at the
first error)
- filing JIRAs for these
The entire framework could be built and tested using standard JDBC APIs,
and then initially run using MySQL or some other RDBMS before trying it
with Phoenix. Maybe there's such a test harness that already exists for TPC?
Then I think the next phase would require more Phoenix & HBase experience:
- tweaking queries where possible given any limitations in Phoenix
- adding missing syntax (or potentially using the calcite branch which
supports more)
- tweaking Phoenix schema declarations to optimize
- tweaking Phoenix & HBase configs to optimize
- determining which secondary indexes to add (though I think there's an
academic paper on this, I can't seem to find it)
Both phases would require a significant amount of time and effort. Each
benchmark would likely require unique tweaks.
Thanks,
James
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Andrew Purtell <ap...@apache.org>.
> I have a long interest in 'canned' loadings. Interesting ones are hard to
> come by. If Phoenix ran any or a subset of TPCs, I'd like to try it.
Likewise
> But I don't want to be the first to try it. I am not a Phoenix expert.
Same here, I'd just email dev@phoenix with a report that TPC query XYZ
didn't work and that would be as far as I could get.
On Fri, Aug 19, 2016 at 2:50 PM, Stack <st...@duboce.net> wrote:
> On Fri, Aug 19, 2016 at 1:19 PM, James Taylor <ja...@apache.org>
> wrote:
>
> > On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
> >
> > > On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
> > > wrote:
> > >
> > > > The data loaded fine for us.
> > >
> > >
> > > Mind describing what you did to get it to work and with what versions
> and
> > > configurations and with what TPC loading and how much of the workload
> was
> > > supported? Was it a one-off project?
> > >
> >
> > Mujtaba already kindly responded to this (about a week back on this
> > thread). He was able to load the data for the benchmark onto one of our
> > internal clusters. He didn't run the benchmarks. Sorry, but I don't have
> > any more specific knowledge....
>
>
>
> Thanks. I was just wondering if more than a select count was done and if
> more detail on the setup was available.
>
>
>
> > > > If TPC is not representative of real
> > > > workloads, I'm not sure there's value in spending a lot of time
> running
> > > > them.
> > >
> > >
> > > I suppose the project could just ignore TPC but I'd suggest that
> Phoenix
> > > put up a page explaining why TPC does not apply if this the case; i.e.
> it
> > > is not representative of Phoenix work loads. When people see that
> Phoenix
> > > is for "OLTP and analytical queries", they probably think the TPC
> > loadings
> > > will just work given their standing in the industry. Putting up a
> > disavowal
> > > with explanation will save folks time trying to make it work and it can
> > > also be cited when folks try to run TPC against Phoenix and they have a
> > bad
> > > experience, say bad performance.
> > >
> >
> > I haven't run the TPC benchmarks, so I have no idea how they perform. I
> > work at Salesforce where we use Phoenix (among may other technologies) to
> > support various big data use cases. The workloads I'm familiar with
> aren't
> > similar to the TPC benchmarks, so they're not relevant for my work. But
> if
> > TPC benchmarks are relevant for your work, then that'd be great if you
> > pursued this. Or maybe we can get this "Phoenix" person you mentioned to
> do
> > it (smile).
> >
> >
> I wasn't suggesting you do it James. Relax. I was just trying to gauge
> where Phoenix is regards TPC.
>
> I have a long interest in 'canned' loadings. Interesting ones are hard to
> come by. If Phoenix ran any or a subset of TPCs, I'd like to try it. I
> would like to study how the storage does under the loading to see if we can
> make the storage run better. But I don't want to be the first to try it. I
> am not a Phoenix expert. II would prefer to learn from one one what works
> and what as yet is unsupported.
>
> Thanks,
> St.Ack
>
--
Best regards,
- Andy
Problems worthy of attack prove their worth by hitting back. - Piet Hein
(via Tom White)
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Stack <st...@duboce.net>.
On Fri, Aug 19, 2016 at 1:19 PM, James Taylor <ja...@apache.org>
wrote:
> On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
>
> > On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
> > wrote:
> >
> > > The data loaded fine for us.
> >
> >
> > Mind describing what you did to get it to work and with what versions and
> > configurations and with what TPC loading and how much of the workload was
> > supported? Was it a one-off project?
> >
>
> Mujtaba already kindly responded to this (about a week back on this
> thread). He was able to load the data for the benchmark onto one of our
> internal clusters. He didn't run the benchmarks. Sorry, but I don't have
> any more specific knowledge....
Thanks. I was just wondering if more than a select count was done and if
more detail on the setup was available.
> > > If TPC is not representative of real
> > > workloads, I'm not sure there's value in spending a lot of time running
> > > them.
> >
> >
> > I suppose the project could just ignore TPC but I'd suggest that Phoenix
> > put up a page explaining why TPC does not apply if this the case; i.e. it
> > is not representative of Phoenix work loads. When people see that Phoenix
> > is for "OLTP and analytical queries", they probably think the TPC
> loadings
> > will just work given their standing in the industry. Putting up a
> disavowal
> > with explanation will save folks time trying to make it work and it can
> > also be cited when folks try to run TPC against Phoenix and they have a
> bad
> > experience, say bad performance.
> >
>
> I haven't run the TPC benchmarks, so I have no idea how they perform. I
> work at Salesforce where we use Phoenix (among may other technologies) to
> support various big data use cases. The workloads I'm familiar with aren't
> similar to the TPC benchmarks, so they're not relevant for my work. But if
> TPC benchmarks are relevant for your work, then that'd be great if you
> pursued this. Or maybe we can get this "Phoenix" person you mentioned to do
> it (smile).
>
>
I wasn't suggesting you do it James. Relax. I was just trying to gauge
where Phoenix is regards TPC.
I have a long interest in 'canned' loadings. Interesting ones are hard to
come by. If Phoenix ran any or a subset of TPCs, I'd like to try it. I
would like to study how the storage does under the loading to see if we can
make the storage run better. But I don't want to be the first to try it. I
am not a Phoenix expert. II would prefer to learn from one one what works
and what as yet is unsupported.
Thanks,
St.Ack
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by la...@apache.org.
Thanks John.
How was the region server Java process' heap configured?(Mujtaba is out today, not sure he'll listen in before Monday)
On our regular SKUs we configure the region servers with 31GB of heap (the machines have more RAM than that), but I am not sure about which test cluster we used for this.
-- Lars
From: John Leach <jl...@splicemachine.com>
To: dev@phoenix.apache.org; larsh@apache.org
Sent: Friday, August 19, 2016 2:34 PM
Subject: Re: Issues while Running Apache Phoenix against TPC-H data
Sorry for the delay on this end…
Each Region Server has 24 Gigs of Ram, 12 cores plus 12 virtual cores.
Would you please provide appropriate configurations for an analytic and data load benchmark?
I am hearing HBase 1.2 and latest Phoenix release.
If you are using open source hbase, would you be willing to attach your key xml files (hbase-site.xml) so that we are testing phoenix in the best light?
Mujtaba would you mind sharing your schema and any indexes that are appropriate?
We have a few copies of the TPCH data floating around but I appreciate the link.
Thanks,
John Leach
> On Aug 19, 2016, at 4:03 PM, <la...@apache.org> <la...@apache.org> wrote:
>
> I think Stack it trying to help and was just asking whether Mujtaba did something special to load the data (and perhaps how it took for us and on how many nodes we did that).(If it loaded fine for us and there was nothing special we had to do, I agree that there's no way (or need) to troubleshoot vendor specific benchmark setups.)
> I also agree running some subset of TPC-* would be a boon for Phoenix and boost its adoption.
>
> At the same time Phoenix is moving at an incredible speed. 4.7 is already old (considering the fixes in 4.8), 4.4 is _ancient_. In 4.9 (or 5.0) we'll have column and dense encoding, which would speed up this type of query.
>
> Now, Amit never replied about how their HBase is actually configured (heap sizes, etc). Phoenix runs inside of the region server, and hence their configuration is extremely important.
> -- Lars
>
> From: James Taylor <ja...@apache.org>
> To: "dev@phoenix.apache.org" <de...@phoenix.apache.org>
> Sent: Friday, August 19, 2016 1:19 PM
> Subject: Re: Issues while Running Apache Phoenix against TPC-H data
>
> On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
>
>> On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
>> wrote:
>>
>>> The data loaded fine for us.
>>
>>
>> Mind describing what you did to get it to work and with what versions and
>> configurations and with what TPC loading and how much of the workload was
>> supported? Was it a one-off project?
>>
>
> Mujtaba already kindly responded to this (about a week back on this
> thread). He was able to load the data for the benchmark onto one of our
> internal clusters. He didn't run the benchmarks. Sorry, but I don't have
> any more specific knowledge, but generally I think:
> - it's difficult for an OS project to troubleshoot environmental issues and
> it's even more difficult if a user is using a vendor specific distro. IMHO,
> if you ask an open source project for help, you should be using the
> artifacts that they produce (preferably the latest release).
> - using a three node cluster for HBase is not ideal for benchmarking.
> - doing full table scans over large HBase tables will be slow.
>
>
>>
>>
>>
>>> If TPC is not representative of real
>>> workloads, I'm not sure there's value in spending a lot of time running
>>> them.
>>
>>
>> I suppose the project could just ignore TPC but I'd suggest that Phoenix
>> put up a page explaining why TPC does not apply if this the case; i.e. it
>> is not representative of Phoenix work loads. When people see that Phoenix
>> is for "OLTP and analytical queries", they probably think the TPC loadings
>> will just work given their standing in the industry. Putting up a disavowal
>> with explanation will save folks time trying to make it work and it can
>> also be cited when folks try to run TPC against Phoenix and they have a bad
>> experience, say bad performance.
>>
>
> I haven't run the TPC benchmarks, so I have no idea how they perform. I
> work at Salesforce where we use Phoenix (among may other technologies) to
> support various big data use cases. The workloads I'm familiar with aren't
> similar to the TPC benchmarks, so they're not relevant for my work. But if
> TPC benchmarks are relevant for your work, then that'd be great if you
> pursued this. Or maybe we can get this "Phoenix" person you mentioned to do
> it (smile).
>
>
>>
>> On the other hand, even if an artificial loading, unless Phoenix has a
>> better means of verifying all works, I'd think it would be a useful test to
>> run before release or on a nightly basis verifying no regression in
>> performance or in utility.
>>
>
> I think the community would welcome enhancing our existing regression test
> suite. If you're up for leading that effort, that'd be great.
>
> Thanks,
> James
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by John Leach <jl...@splicemachine.com>.
Sorry for the delay on this end…
Each Region Server has 24 Gigs of Ram, 12 cores plus 12 virtual cores.
Would you please provide appropriate configurations for an analytic and data load benchmark?
I am hearing HBase 1.2 and latest Phoenix release.
If you are using open source hbase, would you be willing to attach your key xml files (hbase-site.xml) so that we are testing phoenix in the best light?
Mujtaba would you mind sharing your schema and any indexes that are appropriate?
We have a few copies of the TPCH data floating around but I appreciate the link.
Thanks,
John Leach
> On Aug 19, 2016, at 4:03 PM, <la...@apache.org> <la...@apache.org> wrote:
>
> I think Stack it trying to help and was just asking whether Mujtaba did something special to load the data (and perhaps how it took for us and on how many nodes we did that).(If it loaded fine for us and there was nothing special we had to do, I agree that there's no way (or need) to troubleshoot vendor specific benchmark setups.)
> I also agree running some subset of TPC-* would be a boon for Phoenix and boost its adoption.
>
> At the same time Phoenix is moving at an incredible speed. 4.7 is already old (considering the fixes in 4.8), 4.4 is _ancient_. In 4.9 (or 5.0) we'll have column and dense encoding, which would speed up this type of query.
>
> Now, Amit never replied about how their HBase is actually configured (heap sizes, etc). Phoenix runs inside of the region server, and hence their configuration is extremely important.
> -- Lars
>
> From: James Taylor <ja...@apache.org>
> To: "dev@phoenix.apache.org" <de...@phoenix.apache.org>
> Sent: Friday, August 19, 2016 1:19 PM
> Subject: Re: Issues while Running Apache Phoenix against TPC-H data
>
> On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
>
>> On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
>> wrote:
>>
>>> The data loaded fine for us.
>>
>>
>> Mind describing what you did to get it to work and with what versions and
>> configurations and with what TPC loading and how much of the workload was
>> supported? Was it a one-off project?
>>
>
> Mujtaba already kindly responded to this (about a week back on this
> thread). He was able to load the data for the benchmark onto one of our
> internal clusters. He didn't run the benchmarks. Sorry, but I don't have
> any more specific knowledge, but generally I think:
> - it's difficult for an OS project to troubleshoot environmental issues and
> it's even more difficult if a user is using a vendor specific distro. IMHO,
> if you ask an open source project for help, you should be using the
> artifacts that they produce (preferably the latest release).
> - using a three node cluster for HBase is not ideal for benchmarking.
> - doing full table scans over large HBase tables will be slow.
>
>
>>
>>
>>
>>> If TPC is not representative of real
>>> workloads, I'm not sure there's value in spending a lot of time running
>>> them.
>>
>>
>> I suppose the project could just ignore TPC but I'd suggest that Phoenix
>> put up a page explaining why TPC does not apply if this the case; i.e. it
>> is not representative of Phoenix work loads. When people see that Phoenix
>> is for "OLTP and analytical queries", they probably think the TPC loadings
>> will just work given their standing in the industry. Putting up a disavowal
>> with explanation will save folks time trying to make it work and it can
>> also be cited when folks try to run TPC against Phoenix and they have a bad
>> experience, say bad performance.
>>
>
> I haven't run the TPC benchmarks, so I have no idea how they perform. I
> work at Salesforce where we use Phoenix (among may other technologies) to
> support various big data use cases. The workloads I'm familiar with aren't
> similar to the TPC benchmarks, so they're not relevant for my work. But if
> TPC benchmarks are relevant for your work, then that'd be great if you
> pursued this. Or maybe we can get this "Phoenix" person you mentioned to do
> it (smile).
>
>
>>
>> On the other hand, even if an artificial loading, unless Phoenix has a
>> better means of verifying all works, I'd think it would be a useful test to
>> run before release or on a nightly basis verifying no regression in
>> performance or in utility.
>>
>
> I think the community would welcome enhancing our existing regression test
> suite. If you're up for leading that effort, that'd be great.
>
> Thanks,
> James
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by la...@apache.org.
I think Stack it trying to help and was just asking whether Mujtaba did something special to load the data (and perhaps how it took for us and on how many nodes we did that).(If it loaded fine for us and there was nothing special we had to do, I agree that there's no way (or need) to troubleshoot vendor specific benchmark setups.)
I also agree running some subset of TPC-* would be a boon for Phoenix and boost its adoption.
At the same time Phoenix is moving at an incredible speed. 4.7 is already old (considering the fixes in 4.8), 4.4 is _ancient_. In 4.9 (or 5.0) we'll have column and dense encoding, which would speed up this type of query.
Now, Amit never replied about how their HBase is actually configured (heap sizes, etc). Phoenix runs inside of the region server, and hence their configuration is extremely important.
-- Lars
From: James Taylor <ja...@apache.org>
To: "dev@phoenix.apache.org" <de...@phoenix.apache.org>
Sent: Friday, August 19, 2016 1:19 PM
Subject: Re: Issues while Running Apache Phoenix against TPC-H data
On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
> On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
> wrote:
>
> > The data loaded fine for us.
>
>
> Mind describing what you did to get it to work and with what versions and
> configurations and with what TPC loading and how much of the workload was
> supported? Was it a one-off project?
>
Mujtaba already kindly responded to this (about a week back on this
thread). He was able to load the data for the benchmark onto one of our
internal clusters. He didn't run the benchmarks. Sorry, but I don't have
any more specific knowledge, but generally I think:
- it's difficult for an OS project to troubleshoot environmental issues and
it's even more difficult if a user is using a vendor specific distro. IMHO,
if you ask an open source project for help, you should be using the
artifacts that they produce (preferably the latest release).
- using a three node cluster for HBase is not ideal for benchmarking.
- doing full table scans over large HBase tables will be slow.
>
>
>
> > If TPC is not representative of real
> > workloads, I'm not sure there's value in spending a lot of time running
> > them.
>
>
> I suppose the project could just ignore TPC but I'd suggest that Phoenix
> put up a page explaining why TPC does not apply if this the case; i.e. it
> is not representative of Phoenix work loads. When people see that Phoenix
> is for "OLTP and analytical queries", they probably think the TPC loadings
> will just work given their standing in the industry. Putting up a disavowal
> with explanation will save folks time trying to make it work and it can
> also be cited when folks try to run TPC against Phoenix and they have a bad
> experience, say bad performance.
>
I haven't run the TPC benchmarks, so I have no idea how they perform. I
work at Salesforce where we use Phoenix (among may other technologies) to
support various big data use cases. The workloads I'm familiar with aren't
similar to the TPC benchmarks, so they're not relevant for my work. But if
TPC benchmarks are relevant for your work, then that'd be great if you
pursued this. Or maybe we can get this "Phoenix" person you mentioned to do
it (smile).
>
> On the other hand, even if an artificial loading, unless Phoenix has a
> better means of verifying all works, I'd think it would be a useful test to
> run before release or on a nightly basis verifying no regression in
> performance or in utility.
>
I think the community would welcome enhancing our existing regression test
suite. If you're up for leading that effort, that'd be great.
Thanks,
James
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
On Fri, Aug 19, 2016 at 11:37 AM, Stack <st...@duboce.net> wrote:
> On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
> wrote:
>
> > The data loaded fine for us.
>
>
> Mind describing what you did to get it to work and with what versions and
> configurations and with what TPC loading and how much of the workload was
> supported? Was it a one-off project?
>
Mujtaba already kindly responded to this (about a week back on this
thread). He was able to load the data for the benchmark onto one of our
internal clusters. He didn't run the benchmarks. Sorry, but I don't have
any more specific knowledge, but generally I think:
- it's difficult for an OS project to troubleshoot environmental issues and
it's even more difficult if a user is using a vendor specific distro. IMHO,
if you ask an open source project for help, you should be using the
artifacts that they produce (preferably the latest release).
- using a three node cluster for HBase is not ideal for benchmarking.
- doing full table scans over large HBase tables will be slow.
>
>
>
> > If TPC is not representative of real
> > workloads, I'm not sure there's value in spending a lot of time running
> > them.
>
>
> I suppose the project could just ignore TPC but I'd suggest that Phoenix
> put up a page explaining why TPC does not apply if this the case; i.e. it
> is not representative of Phoenix work loads. When people see that Phoenix
> is for "OLTP and analytical queries", they probably think the TPC loadings
> will just work given their standing in the industry. Putting up a disavowal
> with explanation will save folks time trying to make it work and it can
> also be cited when folks try to run TPC against Phoenix and they have a bad
> experience, say bad performance.
>
I haven't run the TPC benchmarks, so I have no idea how they perform. I
work at Salesforce where we use Phoenix (among may other technologies) to
support various big data use cases. The workloads I'm familiar with aren't
similar to the TPC benchmarks, so they're not relevant for my work. But if
TPC benchmarks are relevant for your work, then that'd be great if you
pursued this. Or maybe we can get this "Phoenix" person you mentioned to do
it (smile).
>
> On the other hand, even if an artificial loading, unless Phoenix has a
> better means of verifying all works, I'd think it would be a useful test to
> run before release or on a nightly basis verifying no regression in
> performance or in utility.
>
I think the community would welcome enhancing our existing regression test
suite. If you're up for leading that effort, that'd be great.
Thanks,
James
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Stack <st...@duboce.net>.
On Thu, Aug 18, 2016 at 5:54 PM, James Taylor <ja...@apache.org>
wrote:
> The data loaded fine for us.
Mind describing what you did to get it to work and with what versions and
configurations and with what TPC loading and how much of the workload was
supported? Was it a one-off project?
> If TPC is not representative of real
> workloads, I'm not sure there's value in spending a lot of time running
> them.
I suppose the project could just ignore TPC but I'd suggest that Phoenix
put up a page explaining why TPC does not apply if this the case; i.e. it
is not representative of Phoenix work loads. When people see that Phoenix
is for "OLTP and analytical queries", they probably think the TPC loadings
will just work given their standing in the industry. Putting up a disavowal
with explanation will save folks time trying to make it work and it can
also be cited when folks try to run TPC against Phoenix and they have a bad
experience, say bad performance.
On the other hand, even if an artificial loading, unless Phoenix has a
better means of verifying all works, I'd think it would be a useful test to
run before release or on a nightly basis verifying no regression in
performance or in utility.
Thanks,
St.Ack
> Thanks,
> James
>
> On Thu, Aug 18, 2016 at 5:36 PM, Stack <st...@duboce.net> wrote:
>
> > On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
> > wrote:
> >
> > > On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
> > >
> > ...
> >
> > > I'm not sure how the TCP benchmarks map to the real world use
> > > cases of our user community.
> >
> >
> > I'd think the TPC loadings would be worth nailing since thats how the
> > engines get compared. If it is true Phoenix falters loading the dataset,
> > that doesn't look good. If Phoenix is punting on TPC, that'd be good to
> > know too.
> >
> > Thanks,
> > St.Ack
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
The data loaded fine for us. If TPC is not representative of real
workloads, I'm not sure there's value in spending a lot of time running
them. But if it's important to the dev/user community and gets contributed,
that'd be great too. I guess that's one of the great things about open
source.
Thanks,
James
On Thu, Aug 18, 2016 at 5:36 PM, Stack <st...@duboce.net> wrote:
> On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
> wrote:
>
> > On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
> >
> ...
>
> > I'm not sure how the TCP benchmarks map to the real world use
> > cases of our user community.
>
>
> I'd think the TPC loadings would be worth nailing since thats how the
> engines get compared. If it is true Phoenix falters loading the dataset,
> that doesn't look good. If Phoenix is punting on TPC, that'd be good to
> know too.
>
> Thanks,
> St.Ack
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Stack <st...@duboce.net>.
On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
wrote:
> On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
>
...
> I'm not sure how the TCP benchmarks map to the real world use
> cases of our user community.
I'd think the TPC loadings would be worth nailing since thats how the
engines get compared. If it is true Phoenix falters loading the dataset,
that doesn't look good. If Phoenix is punting on TPC, that'd be good to
know too.
Thanks,
St.Ack
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Peter Conrad <pc...@salesforce.com>.
Got it. So in the mean time I will try to keep my eyes on the questions as
they come in and I'll figure out a way to capture the answers. I'm pretty
focused on the Tuning Guide for now, but maybe I'll start looking at other
ways to improve the docs (unless I get swamped by other priorities).
Have you had a chance to look at the Tuning Guide?
Thanks,
Peter
Peter Conrad
Staff Technical Writer: Infrastructure | salesforce.com
Office: (415) 471-5265
[image: http://www.salesforce.com/signature]
<http://www.salesforce.com/signature>
--
On Thu, Aug 18, 2016 at 4:13 PM, James Taylor <ja...@apache.org>
wrote:
> Thanks, Peter. The main means of interaction at Apache are email and JIRAs.
> These can then lead to commits (including website updates). I think it's
> less about the medium of communication and more about the defining the
> right processes, coordination, workflow, and automation that would need to
> be put in place; It'd be like figuring out how to setup and run a test lab:
> determining the right structure (does Pherf provide enough and if not,
> where are the gaps and what are the alternatives), getting representative
> use cases with queries and validating that they work, setting up and
> monitoring the jobs that run them; having some self service mechanism to
> tweak them, etc. Often the queries and data sizes would be considered
> proprietary so users would not be able to provide them.
>
>
> On Thu, Aug 18, 2016 at 3:10 PM, Peter Conrad <pc...@salesforce.com>
> wrote:
>
> > James:
> >
> > Is there a formalized way that people from the community can get me
> > information that I can then collate, restructure, and rewrite into docs?
> I
> > am on the email lists, and I'm doing what I can to collect information
> from
> > there, but a more focused effort might also be productive.
> >
> > Peter
> >
> >
> >
> > Peter Conrad
> >
> > Staff Technical Writer: Infrastructure | salesforce.com
> >
> > Office: (415) 471-5265
> >
> >
> > [image: http://www.salesforce.com/signature]
> > <http://www.salesforce.com/signature>
> >
> > --
> >
> >
> > On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
> > wrote:
> >
> > > On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
> > > >
> > > >
> > > > Would be cool if there was a page on how to do tpc-h along with what
> > > works
> > > > and what does not from the suite, even if it was just for the latest
> > > > release.
> > >
> > >
> > > Yes, agreed. That'd be a good first contribution - a one pager on how
> to
> > > configure and run tpc-h.
> > >
> > > In general, it'd be great if in our user community we could begin to
> > > collect these kind of use case simulations. The entire community could
> > > learn a lot from each other. One potential way this could be structured
> > > would be as Pherf[1] scenarios where the config parameters and tuning
> is
> > > captured. I'm not sure how the TCP benchmarks map to the real world use
> > > cases of our user community. FWIW, there are some outlined here[2] from
> > > Sony and eHarmony (linked in the comments section).
> > >
> > > [1] http://phoenix.apache.org/pherf.html
> > > [2]
> > > http://www.meetup.com/SF-Bay-Area-Apache-Phoenix-Meetup/
> > events/230545182/
> > >
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
Thanks, Peter. The main means of interaction at Apache are email and JIRAs.
These can then lead to commits (including website updates). I think it's
less about the medium of communication and more about the defining the
right processes, coordination, workflow, and automation that would need to
be put in place; It'd be like figuring out how to setup and run a test lab:
determining the right structure (does Pherf provide enough and if not,
where are the gaps and what are the alternatives), getting representative
use cases with queries and validating that they work, setting up and
monitoring the jobs that run them; having some self service mechanism to
tweak them, etc. Often the queries and data sizes would be considered
proprietary so users would not be able to provide them.
On Thu, Aug 18, 2016 at 3:10 PM, Peter Conrad <pc...@salesforce.com>
wrote:
> James:
>
> Is there a formalized way that people from the community can get me
> information that I can then collate, restructure, and rewrite into docs? I
> am on the email lists, and I'm doing what I can to collect information from
> there, but a more focused effort might also be productive.
>
> Peter
>
>
>
> Peter Conrad
>
> Staff Technical Writer: Infrastructure | salesforce.com
>
> Office: (415) 471-5265
>
>
> [image: http://www.salesforce.com/signature]
> <http://www.salesforce.com/signature>
>
> --
>
>
> On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
> wrote:
>
> > On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
> > >
> > >
> > > Would be cool if there was a page on how to do tpc-h along with what
> > works
> > > and what does not from the suite, even if it was just for the latest
> > > release.
> >
> >
> > Yes, agreed. That'd be a good first contribution - a one pager on how to
> > configure and run tpc-h.
> >
> > In general, it'd be great if in our user community we could begin to
> > collect these kind of use case simulations. The entire community could
> > learn a lot from each other. One potential way this could be structured
> > would be as Pherf[1] scenarios where the config parameters and tuning is
> > captured. I'm not sure how the TCP benchmarks map to the real world use
> > cases of our user community. FWIW, there are some outlined here[2] from
> > Sony and eHarmony (linked in the comments section).
> >
> > [1] http://phoenix.apache.org/pherf.html
> > [2]
> > http://www.meetup.com/SF-Bay-Area-Apache-Phoenix-Meetup/
> events/230545182/
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Peter Conrad <pc...@salesforce.com>.
James:
Is there a formalized way that people from the community can get me
information that I can then collate, restructure, and rewrite into docs? I
am on the email lists, and I'm doing what I can to collect information from
there, but a more focused effort might also be productive.
Peter
Peter Conrad
Staff Technical Writer: Infrastructure | salesforce.com
Office: (415) 471-5265
[image: http://www.salesforce.com/signature]
<http://www.salesforce.com/signature>
--
On Thu, Aug 18, 2016 at 3:00 PM, James Taylor <ja...@apache.org>
wrote:
> On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
> >
> >
> > Would be cool if there was a page on how to do tpc-h along with what
> works
> > and what does not from the suite, even if it was just for the latest
> > release.
>
>
> Yes, agreed. That'd be a good first contribution - a one pager on how to
> configure and run tpc-h.
>
> In general, it'd be great if in our user community we could begin to
> collect these kind of use case simulations. The entire community could
> learn a lot from each other. One potential way this could be structured
> would be as Pherf[1] scenarios where the config parameters and tuning is
> captured. I'm not sure how the TCP benchmarks map to the real world use
> cases of our user community. FWIW, there are some outlined here[2] from
> Sony and eHarmony (linked in the comments section).
>
> [1] http://phoenix.apache.org/pherf.html
> [2]
> http://www.meetup.com/SF-Bay-Area-Apache-Phoenix-Meetup/events/230545182/
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
On Thu, Aug 18, 2016 at 10:48 AM, Stack <st...@duboce.net> wrote:
>
>
> Would be cool if there was a page on how to do tpc-h along with what works
> and what does not from the suite, even if it was just for the latest
> release.
Yes, agreed. That'd be a good first contribution - a one pager on how to
configure and run tpc-h.
In general, it'd be great if in our user community we could begin to
collect these kind of use case simulations. The entire community could
learn a lot from each other. One potential way this could be structured
would be as Pherf[1] scenarios where the config parameters and tuning is
captured. I'm not sure how the TCP benchmarks map to the real world use
cases of our user community. FWIW, there are some outlined here[2] from
Sony and eHarmony (linked in the comments section).
[1] http://phoenix.apache.org/pherf.html
[2]
http://www.meetup.com/SF-Bay-Area-Apache-Phoenix-Meetup/events/230545182/
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Stack <st...@duboce.net>.
It was 4.7 phoenix on what version of hadoop/hbase Amit?
Seven hours seems too long to load the data.
> It varies greatly on a use case by use case basis and requires
experimentation.
Would be cool if there was a page on how to do tpc-h along with what works
and what does not from the suite, even if it was just for the latest
release. A list of suggested configs for this 'defined' use case would help
too. I'd like to try it but would like to follow the footsteps of someone
who knows better.
Thanks,
St.Ack
On Tue, Aug 16, 2016 at 3:59 AM, Amit Mudgal <am...@splicemachine.com>
wrote:
> Hi Teams,
>
> Apologies for the late reply but i was trying to upload the data in
> LINEITEM table and my experience was not very good with the older version
> of phoenix 4.7 but we did have a beefy cluster as pointed by my colleague
> earlier.
>
> After the jobs got completed i have seen some erratic behavior. If we drop
> the then we can see partial data in the LINEITEM table below. Also it took
> me 7 hours to load the LINEITEM table file, I wanted to check if thats the
> time it took for you as well ?
>
> 0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
> +------------------------------------------+
> | COUNT(1) |
> +------------------------------------------+
> | 0 |
> +------------------------------------------+
> 1 row selected (1.648 seconds)
> 0: jdbc:phoenix:stl-colo-srv073.> drop index L_SHIPDATE_IDX on
> TPCH.LINEITEM;
> No rows affected (6.769 seconds)
> 0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
> +------------------------------------------+
> | COUNT(1) |
> +------------------------------------------+
> | 5038442 |
> +------------------------------------------+
> 1 row selected (122.127 seconds)
>
> Could you please let us know if there are any TPCH benchmarks you have
> stored with system architecture details as well ?
>
> To answer some questions posed :
>
> 1. We are loading the data via HDFS directly using the phoenix-client’s
> CsvBulk loader tool. We monitor the jobs later to see its completion. Do
> you recommend direct csv upload compared to hdfs upload , is there a
> preference for one against the other ?
> 2. There were no deleted rows from HBASE.
> 3. With the new cluster, as per your suggestion i did run the update stats
> and for the LINEITEM table but did not get the desired result.
>
> 0: jdbc:phoenix:stl-colo-srv073> update statistics TPCH.LINEITEM SET
> . . . . . . . . . . . . . . . . . . . . . . .> "phoenix.stats.guidepost.
> width"=100000000;
> No rows affected (122.254 seconds)
> 0: jdbc:phoenix:stl-colo-srv073> select count(*) from TPCH.LINEITEM;
> +------------------------------------------+
> | COUNT(1) |
> +------------------------------------------+
> | 5038442 |
> +------------------------------------------+
> 1 row selected (122.137 seconds)
> 0: jdbc:phoenix:stl-colo-srv073>
>
> Did you also face issues while loading the csv files ?
> 4. We have automatic compression enabled and also did manual compression
> on the individual tables from base shell.
> 5. What all queries worked for you on the TPCH data and which ones didn’t
> based on the comment that most of the TPCH queries work on phoenix.
>
> I would greatly appreciate any input on this.
>
> Many thanks
>
> Amit
>
>
>
>
> On Aug 15, 2016, at 9:58 AM, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance
> enhancements in here, especially around usage of DISTINCT. We've also got
> some new encoding schemes to reduce table sizes in our encodecolumns branch
> which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
> CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
> here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you may
> want to use multiple column families. This prevents having to scan data
> that you don't need. More info on how to declare column families can be
> found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
> necessary. We haven't yet enhanced our optimizer to do this automatically,
> so some experience in understanding what HBase is doing under the covers
> will help. For example, for point lookups, use the SMALL and SERIAL hint.
> For large scans, use the NO_CACHE hint. For low cardinality columns in the
> PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#create_table
> [3] https://phoenix.apache.org/language/index.html#select_statement
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org>
> wrote:
>
> 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 <am...@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
>
>
>
>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Amit Mudgal <am...@splicemachine.com>.
Hi Teams,
Apologies for the late reply but i was trying to upload the data in LINEITEM table and my experience was not very good with the older version of phoenix 4.7 but we did have a beefy cluster as pointed by my colleague earlier.
After the jobs got completed i have seen some erratic behavior. If we drop the then we can see partial data in the LINEITEM table below. Also it took me 7 hours to load the LINEITEM table file, I wanted to check if thats the time it took for you as well ?
0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row selected (1.648 seconds)
0: jdbc:phoenix:stl-colo-srv073.> drop index L_SHIPDATE_IDX on TPCH.LINEITEM;
No rows affected (6.769 seconds)
0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 5038442 |
+------------------------------------------+
1 row selected (122.127 seconds)
Could you please let us know if there are any TPCH benchmarks you have stored with system architecture details as well ?
To answer some questions posed :
1. We are loading the data via HDFS directly using the phoenix-client’s CsvBulk loader tool. We monitor the jobs later to see its completion. Do you recommend direct csv upload compared to hdfs upload , is there a preference for one against the other ?
2. There were no deleted rows from HBASE.
3. With the new cluster, as per your suggestion i did run the update stats and for the LINEITEM table but did not get the desired result.
0: jdbc:phoenix:stl-colo-srv073> update statistics TPCH.LINEITEM SET
. . . . . . . . . . . . . . . . . . . . . . .> "phoenix.stats.guidepost.width"=100000000;
No rows affected (122.254 seconds)
0: jdbc:phoenix:stl-colo-srv073> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 5038442 |
+------------------------------------------+
1 row selected (122.137 seconds)
0: jdbc:phoenix:stl-colo-srv073>
Did you also face issues while loading the csv files ?
4. We have automatic compression enabled and also did manual compression on the individual tables from base shell.
5. What all queries worked for you on the TPCH data and which ones didn’t based on the comment that most of the TPCH queries work on phoenix.
I would greatly appreciate any input on this.
Many thanks
Amit
> On Aug 15, 2016, at 9:58 AM, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance
> enhancements in here, especially around usage of DISTINCT. We've also got
> some new encoding schemes to reduce table sizes in our encodecolumns branch
> which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
> CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
> here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you may
> want to use multiple column families. This prevents having to scan data
> that you don't need. More info on how to declare column families can be
> found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
> necessary. We haven't yet enhanced our optimizer to do this automatically,
> so some experience in understanding what HBase is doing under the covers
> will help. For example, for point lookups, use the SMALL and SERIAL hint.
> For large scans, use the NO_CACHE hint. For low cardinality columns in the
> PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#create_table
> [3] https://phoenix.apache.org/language/index.html#select_statement
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>
>> 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 <am...@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
>>>
>>>
>>>
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Amit Mudgal <am...@splicemachine.com>.
Hi Teams,
Apologies for the late reply but i was trying to upload the data in LINEITEM table and my experience was not very good with the older version of phoenix 4.7 but we did have a beefy cluster as pointed by my colleague earlier.
After the jobs got completed i have seen some erratic behavior. If we drop the then we can see partial data in the LINEITEM table below. Also it took me 7 hours to load the LINEITEM table file, I wanted to check if thats the time it took for you as well ?
0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row selected (1.648 seconds)
0: jdbc:phoenix:stl-colo-srv073.> drop index L_SHIPDATE_IDX on TPCH.LINEITEM;
No rows affected (6.769 seconds)
0: jdbc:phoenix:stl-colo-srv073.> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 5038442 |
+------------------------------------------+
1 row selected (122.127 seconds)
Could you please let us know if there are any TPCH benchmarks you have stored with system architecture details as well ?
To answer some questions posed :
1. We are loading the data via HDFS directly using the phoenix-client’s CsvBulk loader tool. We monitor the jobs later to see its completion. Do you recommend direct csv upload compared to hdfs upload , is there a preference for one against the other ?
2. There were no deleted rows from HBASE.
3. With the new cluster, as per your suggestion i did run the update stats and for the LINEITEM table but did not get the desired result.
0: jdbc:phoenix:stl-colo-srv073> update statistics TPCH.LINEITEM SET
. . . . . . . . . . . . . . . . . . . . . . .> "phoenix.stats.guidepost.width"=100000000;
No rows affected (122.254 seconds)
0: jdbc:phoenix:stl-colo-srv073> select count(*) from TPCH.LINEITEM;
+------------------------------------------+
| COUNT(1) |
+------------------------------------------+
| 5038442 |
+------------------------------------------+
1 row selected (122.137 seconds)
0: jdbc:phoenix:stl-colo-srv073>
Did you also face issues while loading the csv files ?
4. We have automatic compression enabled and also did manual compression on the individual tables from base shell.
5. What all queries worked for you on the TPCH data and which ones didn’t based on the comment that most of the TPCH queries work on phoenix.
I would greatly appreciate any input on this.
Many thanks
Amit
> On Aug 15, 2016, at 9:58 AM, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance
> enhancements in here, especially around usage of DISTINCT. We've also got
> some new encoding schemes to reduce table sizes in our encodecolumns branch
> which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
> CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
> here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you may
> want to use multiple column families. This prevents having to scan data
> that you don't need. More info on how to declare column families can be
> found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
> necessary. We haven't yet enhanced our optimizer to do this automatically,
> so some experience in understanding what HBase is doing under the covers
> will help. For example, for point lookups, use the SMALL and SERIAL hint.
> For large scans, use the NO_CACHE hint. For low cardinality columns in the
> PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#create_table
> [3] https://phoenix.apache.org/language/index.html#select_statement
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org> wrote:
>
>> 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 <am...@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
>>>
>>>
>>>
>>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
Hi Aaron,
For commercial distros, you need to talk to the vendor. HDP 2.4.2 has a
very old version of Phoenix - 4.4 which is 4 minor releases back (an eon in
OS time). If you need something with commercial support, maybe you can get
an early access of the next HDP release, but I'd recommend just using
Apache HBase 1.2.0 and Apache Phoenix 4.8.0. That'll give you the most
flexibility. To install Phoenix, you just copy the server lib into the
HBase lib dir. More on that here:
https://phoenix.apache.org/installation.htm
For best values of hbase-site.xml, you can ask here. It varies greatly on a
use case by use case basis and requires experimentation. Mujtaba gave you
some good info in his response. I'd start by answering all those questions.
Thanks,
James
On Mon, Aug 15, 2016 at 10:33 AM, Aaron Molitor <am...@splicemachine.com>
wrote:
> James,
>
> I am working with Amit on this task. We have switched to an 9 node (8 RS)
> cluster running HP 2.4.2 with a mostly vanilla install. I think our next
> steps are to incorporate Mujtaba's changes into our cluster config and
> re-run, we'll factor in your suggestions as well.
>
> Is there a "recommended" HBase config (that I admittedly may have missed)
> documented anywhere? Clearly looking for something geared toward TPC-H
> type workloads.
>
> Is there a commercially available platform that includes Phoenix 4.8.0
> yet? If not are there instructions on how to install it on an existing HDP
> 2.4.2 cluster?
>
> Thanks,
> Aaron
>
>
> On Aug 15, 2016, at 11:58, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance
> enhancements in here, especially around usage of DISTINCT. We've also got
> some new encoding schemes to reduce table sizes in our encodecolumns branch
> which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
> CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
> here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you
> may want to use multiple column families. This prevents having to scan data
> that you don't need. More info on how to declare column families can be
> found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
> necessary. We haven't yet enhanced our optimizer to do this automatically,
> so some experience in understanding what HBase is doing under the covers
> will help. For example, for point lookups, use the SMALL and SERIAL hint.
> For large scans, use the NO_CACHE hint. For low cardinality columns in the
> PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#create_table
> [3] https://phoenix.apache.org/language/index.html#select_statement
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org>
> wrote:
>
>> 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 <am...@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.BaseGroupedAggregatingResultItera
>> tor.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
>> >
>> >
>> >
>>
>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
Hi Aaron,
For commercial distros, you need to talk to the vendor. HDP 2.4.2 has a
very old version of Phoenix - 4.4 which is 4 minor releases back (an eon in
OS time). If you need something with commercial support, maybe you can get
an early access of the next HDP release, but I'd recommend just using
Apache HBase 1.2.0 and Apache Phoenix 4.8.0. That'll give you the most
flexibility. To install Phoenix, you just copy the server lib into the
HBase lib dir. More on that here:
https://phoenix.apache.org/installation.htm
For best values of hbase-site.xml, you can ask here. It varies greatly on a
use case by use case basis and requires experimentation. Mujtaba gave you
some good info in his response. I'd start by answering all those questions.
Thanks,
James
On Mon, Aug 15, 2016 at 10:33 AM, Aaron Molitor <am...@splicemachine.com>
wrote:
> James,
>
> I am working with Amit on this task. We have switched to an 9 node (8 RS)
> cluster running HP 2.4.2 with a mostly vanilla install. I think our next
> steps are to incorporate Mujtaba's changes into our cluster config and
> re-run, we'll factor in your suggestions as well.
>
> Is there a "recommended" HBase config (that I admittedly may have missed)
> documented anywhere? Clearly looking for something geared toward TPC-H
> type workloads.
>
> Is there a commercially available platform that includes Phoenix 4.8.0
> yet? If not are there instructions on how to install it on an existing HDP
> 2.4.2 cluster?
>
> Thanks,
> Aaron
>
>
> On Aug 15, 2016, at 11:58, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance
> enhancements in here, especially around usage of DISTINCT. We've also got
> some new encoding schemes to reduce table sizes in our encodecolumns branch
> which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
> CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
> here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you
> may want to use multiple column families. This prevents having to scan data
> that you don't need. More info on how to declare column families can be
> found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
> necessary. We haven't yet enhanced our optimizer to do this automatically,
> so some experience in understanding what HBase is doing under the covers
> will help. For example, for point lookups, use the SMALL and SERIAL hint.
> For large scans, use the NO_CACHE hint. For low cardinality columns in the
> PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#create_table
> [3] https://phoenix.apache.org/language/index.html#select_statement
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org>
> wrote:
>
>> 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 <am...@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.BaseGroupedAggregatingResultItera
>> tor.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
>> >
>> >
>> >
>>
>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Aaron Molitor <am...@splicemachine.com>.
James,
I am working with Amit on this task. We have switched to an 9 node (8 RS) cluster running HP 2.4.2 with a mostly vanilla install. I think our next steps are to incorporate Mujtaba's changes into our cluster config and re-run, we'll factor in your suggestions as well.
Is there a "recommended" HBase config (that I admittedly may have missed) documented anywhere? Clearly looking for something geared toward TPC-H type workloads.
Is there a commercially available platform that includes Phoenix 4.8.0 yet? If not are there instructions on how to install it on an existing HDP 2.4.2 cluster?
Thanks,
Aaron
> On Aug 15, 2016, at 11:58, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance enhancements in here, especially around usage of DISTINCT. We've also got some new encoding schemes to reduce table sizes in our encodecolumns branch which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you may want to use multiple column families. This prevents having to scan data that you don't need. More info on how to declare column families can be found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as necessary. We haven't yet enhanced our optimizer to do this automatically, so some experience in understanding what HBase is doing under the covers will help. For example, for point lookups, use the SMALL and SERIAL hint. For large scans, use the NO_CACHE hint. For low cardinality columns in the PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering <https://phoenix.apache.org/#Altering>
> [2] https://phoenix.apache.org/language/index.html#create_table <https://phoenix.apache.org/language/index.html#create_table>
> [3] https://phoenix.apache.org/language/index.html#select_statement <https://phoenix.apache.org/language/index.html#select_statement>
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mujtaba@apache.org <ma...@apache.org>> wrote:
> 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 <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 <ma...@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
> >
> >
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Aaron Molitor <am...@splicemachine.com>.
James,
I am working with Amit on this task. We have switched to an 9 node (8 RS) cluster running HP 2.4.2 with a mostly vanilla install. I think our next steps are to incorporate Mujtaba's changes into our cluster config and re-run, we'll factor in your suggestions as well.
Is there a "recommended" HBase config (that I admittedly may have missed) documented anywhere? Clearly looking for something geared toward TPC-H type workloads.
Is there a commercially available platform that includes Phoenix 4.8.0 yet? If not are there instructions on how to install it on an existing HDP 2.4.2 cluster?
Thanks,
Aaron
> On Aug 15, 2016, at 11:58, James Taylor <ja...@apache.org> wrote:
>
> Hi Amit,
> Couple more performance tips on top of what Mujtaba already mentioned:
> - Use the latest Phoenix (4.8.0). There are some great performance enhancements in here, especially around usage of DISTINCT. We've also got some new encoding schemes to reduce table sizes in our encodecolumns branch which will make it into master in a few weeks.
> - Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See here for more info[1].
> - Make sure to create secondary indexes to prevent full table scans
> - Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
> - Depending on the use case/query patterns and the schema involved, you may want to use multiple column families. This prevents having to scan data that you don't need. More info on how to declare column families can be found here[2].
> - Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as necessary. We haven't yet enhanced our optimizer to do this automatically, so some experience in understanding what HBase is doing under the covers will help. For example, for point lookups, use the SMALL and SERIAL hint. For large scans, use the NO_CACHE hint. For low cardinality columns in the PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering <https://phoenix.apache.org/#Altering>
> [2] https://phoenix.apache.org/language/index.html#create_table <https://phoenix.apache.org/language/index.html#create_table>
> [3] https://phoenix.apache.org/language/index.html#select_statement <https://phoenix.apache.org/language/index.html#select_statement>
>
> On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mujtaba@apache.org <ma...@apache.org>> wrote:
> 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 <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 <ma...@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
> >
> >
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
Hi Amit,
Couple more performance tips on top of what Mujtaba already mentioned:
- Use the latest Phoenix (4.8.0). There are some great performance
enhancements in here, especially around usage of DISTINCT. We've also got
some new encoding schemes to reduce table sizes in our encodecolumns branch
which will make it into master in a few weeks.
- Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
here for more info[1].
- Make sure to create secondary indexes to prevent full table scans
- Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
- Depending on the use case/query patterns and the schema involved, you may
want to use multiple column families. This prevents having to scan data
that you don't need. More info on how to declare column families can be
found here[2].
- Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
necessary. We haven't yet enhanced our optimizer to do this automatically,
so some experience in understanding what HBase is doing under the covers
will help. For example, for point lookups, use the SMALL and SERIAL hint.
For large scans, use the NO_CACHE hint. For low cardinality columns in the
PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
Thanks,
James
[1] https://phoenix.apache.org/#Altering
[2] https://phoenix.apache.org/language/index.html#create_table
[3] https://phoenix.apache.org/language/index.html#select_statement
On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org> wrote:
> 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 <am...@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
> >
> >
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by James Taylor <ja...@apache.org>.
Hi Amit,
Couple more performance tips on top of what Mujtaba already mentioned:
- Use the latest Phoenix (4.8.0). There are some great performance
enhancements in here, especially around usage of DISTINCT. We've also got
some new encoding schemes to reduce table sizes in our encodecolumns branch
which will make it into master in a few weeks.
- Specify the UPDATE_CACHE_FREQUENCY property in your CREATE TABLE and
CREATE INDEX calls. This will greatly reduce the amount of RPC traffic. See
here for more info[1].
- Make sure to create secondary indexes to prevent full table scans
- Declare tables as IMMUTABLE_ROWS=true if they're write-once/append-only.
- Depending on the use case/query patterns and the schema involved, you may
want to use multiple column families. This prevents having to scan data
that you don't need. More info on how to declare column families can be
found here[2].
- Consider using the SMALL, SERIAL, NO_CACHE, and SKIP_SCAN hints as
necessary. We haven't yet enhanced our optimizer to do this automatically,
so some experience in understanding what HBase is doing under the covers
will help. For example, for point lookups, use the SMALL and SERIAL hint.
For large scans, use the NO_CACHE hint. For low cardinality columns in the
PK, try the SKIP_SCAN hint. For more on these hints, see here[3].
Thanks,
James
[1] https://phoenix.apache.org/#Altering
[2] https://phoenix.apache.org/language/index.html#create_table
[3] https://phoenix.apache.org/language/index.html#select_statement
On Fri, Aug 12, 2016 at 2:57 PM, Mujtaba Chohan <mu...@apache.org> wrote:
> 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 <am...@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
> >
> >
> >
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Mujtaba Chohan <mu...@apache.org>.
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 <am...@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
>
>
>
Re: Issues while Running Apache Phoenix against TPC-H data
Posted by Mujtaba Chohan <mu...@apache.org>.
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 <am...@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
>
>
>