Re: Direct HBase vs. Phoenix query performance

[Marcell Ortutay]

Thanks James! I've made a JIRA ticket here:
https://issues.apache.org/jira/projects/PHOENIX/issues/PHOENIX-4666

This is a priority for us at 23andMe as it substantially affects some of
our queries, so we'd be happy to provide a patch if Phoenix maintainers are
able to provide some guidance on the design. I've put a question in the
JIRA ticket as well regarding the approach to take.

On Thu, Mar 15, 2018 at 10:44 AM, James Taylor 
wrote:

> Hi Marcell,
> Yes, that's correct - the cache we build for the RHS is only kept around
> while the join query is being executed. It'd be interesting to explore
> keeping the cache around longer for cases like yours (and probably not too
> difficult). We'd need to keep a map that maps the RHS query to its hash
> join cache identifier and if found skip the running of the query. Would you
> mind filing a JIRA and we can explore further?
> Thanks,
> James
>
> On Wed, Mar 14, 2018 at 3:40 PM, Marcell Ortutay 
> wrote:
>
>> A quick update--I did some inspection of the Phoenix codebase, and it
>> looks like my understanding of the coprocessor cache was incorrect. I
>> thought it was meant to be used across queries, eg. that the RHS of the
>> join would be saved for subsequent queries. In fact this is not the case,
>> the coprocessor cache is meant to live only for the duration of the query.
>> This explains the performance difference--Phoenix is re-running a long
>> subquery for each join, whereas my direct to HBase script saves those
>> results across queries.
>>
>> On Tue, Mar 13, 2018 at 4:56 PM, Marcell Ortutay 
>> wrote:
>>
>>> Hi James,
>>>
>>> Thanks for the tips. Our row keys are (I think) reasonably optimized.
>>> I've made a gist which is an anonymized version of the query, and it
>>> indicates which conditions are / are not part of the PK. It is here:
>>> https://gist.github.com/ortutay23andme/12f03767db13343ee797c328a4d78c9c
>>>
>>> I don't (yet) have an anonymized version of the raw HBase Go script
>>> available, but after comparing the performance of the two, I've figured out
>>> the root cause. The query does a subquery to produce the LHS of one of the
>>> hash joins. This can be seen on L5 of the gist above. This subquery is
>>> quite long (~1sec) to execute and scans a few million rows. It is shared
>>> across all queries so in the raw HBase script I cached / re-used it for all
>>> queries. This has a (very large) performance benefit, in particular under
>>> high load.
>>>
>>> My understanding of Phoenix is that it is supposed to do the same thing.
>>> It seems like the hash join code has some mechanic for caching data for
>>> hash joining using the HBase coprocessor system. I would expect this cache
>>> to kick in, and only execute the large subquery once. Looking at the
>>> performance of the query (30sec timeouts after ~2qps), this doesn't seem to
>>> be happening.
>>>
>>> I'm wondering if my understanding of the Phoenix join cache is right. Is
>>> it correct to expect that it would cache the results of a subquery used in
>>> a join? If so, what are possible reasons why it would *not* do so? Any
>>> guidance on metrics / optimizations to look at would be appreciated.
>>>
>>> Thanks,
>>> Marcell
>>>
>>> On Thu, Mar 8, 2018 at 2:59 PM, James Taylor 
>>> wrote:
>>>
 Hi Marcell,
 It'd be helpful to see the table DDL and the query too along with an
 idea of how many regions might be involved in the query. If a query is a
 commonly run query, usually you'll design the row key around optimizing it.
 If you have other, simpler queries that have determined your row key, then
 another alternative is to add one or more secondary indexes. Another common
 technique is to denormalize your data in ways that precompute the join to
 avoid having to do it at run time.

 With joins, make sure to order your tables from post filtered largest
 (on LHS) to smallest (on RHS). Also, if you're joining on the PK of both
 tables, you should use the USE_SORT_MERGE_JOIN hint. Another common tuning
 exercise is around determining the best parallelization to use (i.e.
 guidepost width) or even disabling parallelization for more than an entire
 region's worth of data.

 It'd also be interesting to see the raw HBase code for a query of this
 complexity.

 Thanks,
 James

 On Thu, Mar 8, 2018 at 1:03 PM, Marcell Ortutay 
 wrote:

> Hi,
>
> I am using Phoenix at my company for a large query that is meant to be
> run in real time as part of our application. The query involves several
> aggregations, anti-joins, and an inner query. Here is the (anonymized)
> query plan: https://gist.github.com/ortutay23andme/1da620472cc469e
> d2d8a6fdd0cc7eb01
>
> The query performance on this is not great, it takes about 5sec to
> execute the query, and 

Re: Direct HBase vs. Phoenix query performance

[James Taylor]

Hi Marcell,
Yes, that's correct - the cache we build for the RHS is only kept around
while the join query is being executed. It'd be interesting to explore
keeping the cache around longer for cases like yours (and probably not too
difficult). We'd need to keep a map that maps the RHS query to its hash
join cache identifier and if found skip the running of the query. Would you
mind filing a JIRA and we can explore further?
Thanks,
James

On Wed, Mar 14, 2018 at 3:40 PM, Marcell Ortutay 
wrote:

> A quick update--I did some inspection of the Phoenix codebase, and it
> looks like my understanding of the coprocessor cache was incorrect. I
> thought it was meant to be used across queries, eg. that the RHS of the
> join would be saved for subsequent queries. In fact this is not the case,
> the coprocessor cache is meant to live only for the duration of the query.
> This explains the performance difference--Phoenix is re-running a long
> subquery for each join, whereas my direct to HBase script saves those
> results across queries.
>
> On Tue, Mar 13, 2018 at 4:56 PM, Marcell Ortutay 
> wrote:
>
>> Hi James,
>>
>> Thanks for the tips. Our row keys are (I think) reasonably optimized.
>> I've made a gist which is an anonymized version of the query, and it
>> indicates which conditions are / are not part of the PK. It is here:
>> https://gist.github.com/ortutay23andme/12f03767db13343ee797c328a4d78c9c
>>
>> I don't (yet) have an anonymized version of the raw HBase Go script
>> available, but after comparing the performance of the two, I've figured out
>> the root cause. The query does a subquery to produce the LHS of one of the
>> hash joins. This can be seen on L5 of the gist above. This subquery is
>> quite long (~1sec) to execute and scans a few million rows. It is shared
>> across all queries so in the raw HBase script I cached / re-used it for all
>> queries. This has a (very large) performance benefit, in particular under
>> high load.
>>
>> My understanding of Phoenix is that it is supposed to do the same thing.
>> It seems like the hash join code has some mechanic for caching data for
>> hash joining using the HBase coprocessor system. I would expect this cache
>> to kick in, and only execute the large subquery once. Looking at the
>> performance of the query (30sec timeouts after ~2qps), this doesn't seem to
>> be happening.
>>
>> I'm wondering if my understanding of the Phoenix join cache is right. Is
>> it correct to expect that it would cache the results of a subquery used in
>> a join? If so, what are possible reasons why it would *not* do so? Any
>> guidance on metrics / optimizations to look at would be appreciated.
>>
>> Thanks,
>> Marcell
>>
>> On Thu, Mar 8, 2018 at 2:59 PM, James Taylor 
>> wrote:
>>
>>> Hi Marcell,
>>> It'd be helpful to see the table DDL and the query too along with an
>>> idea of how many regions might be involved in the query. If a query is a
>>> commonly run query, usually you'll design the row key around optimizing it.
>>> If you have other, simpler queries that have determined your row key, then
>>> another alternative is to add one or more secondary indexes. Another common
>>> technique is to denormalize your data in ways that precompute the join to
>>> avoid having to do it at run time.
>>>
>>> With joins, make sure to order your tables from post filtered largest
>>> (on LHS) to smallest (on RHS). Also, if you're joining on the PK of both
>>> tables, you should use the USE_SORT_MERGE_JOIN hint. Another common tuning
>>> exercise is around determining the best parallelization to use (i.e.
>>> guidepost width) or even disabling parallelization for more than an entire
>>> region's worth of data.
>>>
>>> It'd also be interesting to see the raw HBase code for a query of this
>>> complexity.
>>>
>>> Thanks,
>>> James
>>>
>>> On Thu, Mar 8, 2018 at 1:03 PM, Marcell Ortutay 
>>> wrote:
>>>
 Hi,

 I am using Phoenix at my company for a large query that is meant to be
 run in real time as part of our application. The query involves several
 aggregations, anti-joins, and an inner query. Here is the (anonymized)
 query plan: https://gist.github.com/ortutay23andme/1da620472cc469e
 d2d8a6fdd0cc7eb01

 The query performance on this is not great, it takes about 5sec to
 execute the query, and moreover it performs badly under load. If we run
 ~4qps of this query Phoenix starts to timeout and slow down a lot (queries
 take >30sec).

 For comparison, I wrote a simple Go script that runs a similar query
 talking directly to HBase. The performance on it is substantially better.
 It executes in ~1.5sec, and can handle loads of ~50-100qps on the same
 cluster.

 I'm wondering if anyone has ideas on what might be causing this
 difference in performance? Are there configs / optimizations we can do in
 Phoenix to bring the 

Re: Direct HBase vs. Phoenix query performance

[Marcell Ortutay]

A quick update--I did some inspection of the Phoenix codebase, and it looks
like my understanding of the coprocessor cache was incorrect. I thought it
was meant to be used across queries, eg. that the RHS of the join would be
saved for subsequent queries. In fact this is not the case, the coprocessor
cache is meant to live only for the duration of the query. This explains
the performance difference--Phoenix is re-running a long subquery for each
join, whereas my direct to HBase script saves those results across queries.

On Tue, Mar 13, 2018 at 4:56 PM, Marcell Ortutay 
wrote:

> Hi James,
>
> Thanks for the tips. Our row keys are (I think) reasonably optimized. I've
> made a gist which is an anonymized version of the query, and it indicates
> which conditions are / are not part of the PK. It is here:
> https://gist.github.com/ortutay23andme/12f03767db13343ee797c328a4d78c9c
>
> I don't (yet) have an anonymized version of the raw HBase Go script
> available, but after comparing the performance of the two, I've figured out
> the root cause. The query does a subquery to produce the LHS of one of the
> hash joins. This can be seen on L5 of the gist above. This subquery is
> quite long (~1sec) to execute and scans a few million rows. It is shared
> across all queries so in the raw HBase script I cached / re-used it for all
> queries. This has a (very large) performance benefit, in particular under
> high load.
>
> My understanding of Phoenix is that it is supposed to do the same thing.
> It seems like the hash join code has some mechanic for caching data for
> hash joining using the HBase coprocessor system. I would expect this cache
> to kick in, and only execute the large subquery once. Looking at the
> performance of the query (30sec timeouts after ~2qps), this doesn't seem to
> be happening.
>
> I'm wondering if my understanding of the Phoenix join cache is right. Is
> it correct to expect that it would cache the results of a subquery used in
> a join? If so, what are possible reasons why it would *not* do so? Any
> guidance on metrics / optimizations to look at would be appreciated.
>
> Thanks,
> Marcell
>
> On Thu, Mar 8, 2018 at 2:59 PM, James Taylor 
> wrote:
>
>> Hi Marcell,
>> It'd be helpful to see the table DDL and the query too along with an idea
>> of how many regions might be involved in the query. If a query is a
>> commonly run query, usually you'll design the row key around optimizing it.
>> If you have other, simpler queries that have determined your row key, then
>> another alternative is to add one or more secondary indexes. Another common
>> technique is to denormalize your data in ways that precompute the join to
>> avoid having to do it at run time.
>>
>> With joins, make sure to order your tables from post filtered largest (on
>> LHS) to smallest (on RHS). Also, if you're joining on the PK of both
>> tables, you should use the USE_SORT_MERGE_JOIN hint. Another common tuning
>> exercise is around determining the best parallelization to use (i.e.
>> guidepost width) or even disabling parallelization for more than an entire
>> region's worth of data.
>>
>> It'd also be interesting to see the raw HBase code for a query of this
>> complexity.
>>
>> Thanks,
>> James
>>
>> On Thu, Mar 8, 2018 at 1:03 PM, Marcell Ortutay 
>> wrote:
>>
>>> Hi,
>>>
>>> I am using Phoenix at my company for a large query that is meant to be
>>> run in real time as part of our application. The query involves several
>>> aggregations, anti-joins, and an inner query. Here is the (anonymized)
>>> query plan: https://gist.github.com/ortutay23andme/1da620472cc469e
>>> d2d8a6fdd0cc7eb01
>>>
>>> The query performance on this is not great, it takes about 5sec to
>>> execute the query, and moreover it performs badly under load. If we run
>>> ~4qps of this query Phoenix starts to timeout and slow down a lot (queries
>>> take >30sec).
>>>
>>> For comparison, I wrote a simple Go script that runs a similar query
>>> talking directly to HBase. The performance on it is substantially better.
>>> It executes in ~1.5sec, and can handle loads of ~50-100qps on the same
>>> cluster.
>>>
>>> I'm wondering if anyone has ideas on what might be causing this
>>> difference in performance? Are there configs / optimizations we can do in
>>> Phoenix to bring the performance closer to direct HBase queries?
>>>
>>> I can provide context on the table sizes etc. if needed.
>>>
>>> Thanks,
>>> Marcell
>>>
>>>
>>
>

Re: Direct HBase vs. Phoenix query performance

[Marcell Ortutay]

Hi James,

Thanks for the tips. Our row keys are (I think) reasonably optimized. I've
made a gist which is an anonymized version of the query, and it indicates
which conditions are / are not part of the PK. It is here:
https://gist.github.com/ortutay23andme/12f03767db13343ee797c328a4d78c9c

I don't (yet) have an anonymized version of the raw HBase Go script
available, but after comparing the performance of the two, I've figured out
the root cause. The query does a subquery to produce the LHS of one of the
hash joins. This can be seen on L5 of the gist above. This subquery is
quite long (~1sec) to execute and scans a few million rows. It is shared
across all queries so in the raw HBase script I cached / re-used it for all
queries. This has a (very large) performance benefit, in particular under
high load.

My understanding of Phoenix is that it is supposed to do the same thing. It
seems like the hash join code has some mechanic for caching data for hash
joining using the HBase coprocessor system. I would expect this cache to
kick in, and only execute the large subquery once. Looking at the
performance of the query (30sec timeouts after ~2qps), this doesn't seem to
be happening.

I'm wondering if my understanding of the Phoenix join cache is right. Is it
correct to expect that it would cache the results of a subquery used in a
join? If so, what are possible reasons why it would *not* do so? Any
guidance on metrics / optimizations to look at would be appreciated.

Thanks,
Marcell

On Thu, Mar 8, 2018 at 2:59 PM, James Taylor  wrote:

> Hi Marcell,
> It'd be helpful to see the table DDL and the query too along with an idea
> of how many regions might be involved in the query. If a query is a
> commonly run query, usually you'll design the row key around optimizing it.
> If you have other, simpler queries that have determined your row key, then
> another alternative is to add one or more secondary indexes. Another common
> technique is to denormalize your data in ways that precompute the join to
> avoid having to do it at run time.
>
> With joins, make sure to order your tables from post filtered largest (on
> LHS) to smallest (on RHS). Also, if you're joining on the PK of both
> tables, you should use the USE_SORT_MERGE_JOIN hint. Another common tuning
> exercise is around determining the best parallelization to use (i.e.
> guidepost width) or even disabling parallelization for more than an entire
> region's worth of data.
>
> It'd also be interesting to see the raw HBase code for a query of this
> complexity.
>
> Thanks,
> James
>
> On Thu, Mar 8, 2018 at 1:03 PM, Marcell Ortutay 
> wrote:
>
>> Hi,
>>
>> I am using Phoenix at my company for a large query that is meant to be
>> run in real time as part of our application. The query involves several
>> aggregations, anti-joins, and an inner query. Here is the (anonymized)
>> query plan: https://gist.github.com/ortutay23andme/1da620472cc469e
>> d2d8a6fdd0cc7eb01
>>
>> The query performance on this is not great, it takes about 5sec to
>> execute the query, and moreover it performs badly under load. If we run
>> ~4qps of this query Phoenix starts to timeout and slow down a lot (queries
>> take >30sec).
>>
>> For comparison, I wrote a simple Go script that runs a similar query
>> talking directly to HBase. The performance on it is substantially better.
>> It executes in ~1.5sec, and can handle loads of ~50-100qps on the same
>> cluster.
>>
>> I'm wondering if anyone has ideas on what might be causing this
>> difference in performance? Are there configs / optimizations we can do in
>> Phoenix to bring the performance closer to direct HBase queries?
>>
>> I can provide context on the table sizes etc. if needed.
>>
>> Thanks,
>> Marcell
>>
>>
>

Re: Direct HBase vs. Phoenix query performance

[James Taylor]

Hi Marcell,
It'd be helpful to see the table DDL and the query too along with an idea
of how many regions might be involved in the query. If a query is a
commonly run query, usually you'll design the row key around optimizing it.
If you have other, simpler queries that have determined your row key, then
another alternative is to add one or more secondary indexes. Another common
technique is to denormalize your data in ways that precompute the join to
avoid having to do it at run time.

With joins, make sure to order your tables from post filtered largest (on
LHS) to smallest (on RHS). Also, if you're joining on the PK of both
tables, you should use the USE_SORT_MERGE_JOIN hint. Another common tuning
exercise is around determining the best parallelization to use (i.e.
guidepost width) or even disabling parallelization for more than an entire
region's worth of data.

It'd also be interesting to see the raw HBase code for a query of this
complexity.

Thanks,
James

On Thu, Mar 8, 2018 at 1:03 PM, Marcell Ortutay 
wrote:

> Hi,
>
> I am using Phoenix at my company for a large query that is meant to be run
> in real time as part of our application. The query involves several
> aggregations, anti-joins, and an inner query. Here is the (anonymized)
> query plan: https://gist.github.com/ortutay23andme/
> 1da620472cc469ed2d8a6fdd0cc7eb01
>
> The query performance on this is not great, it takes about 5sec to execute
> the query, and moreover it performs badly under load. If we run ~4qps of
> this query Phoenix starts to timeout and slow down a lot (queries take
> >30sec).
>
> For comparison, I wrote a simple Go script that runs a similar query
> talking directly to HBase. The performance on it is substantially better.
> It executes in ~1.5sec, and can handle loads of ~50-100qps on the same
> cluster.
>
> I'm wondering if anyone has ideas on what might be causing this difference
> in performance? Are there configs / optimizations we can do in Phoenix to
> bring the performance closer to direct HBase queries?
>
> I can provide context on the table sizes etc. if needed.
>
> Thanks,
> Marcell
>
>

Direct HBase vs. Phoenix query performance

[Marcell Ortutay]

Hi,

I am using Phoenix at my company for a large query that is meant to be run
in real time as part of our application. The query involves several
aggregations, anti-joins, and an inner query. Here is the (anonymized)
query plan:
https://gist.github.com/ortutay23andme/1da620472cc469ed2d8a6fdd0cc7eb01

The query performance on this is not great, it takes about 5sec to execute
the query, and moreover it performs badly under load. If we run ~4qps of
this query Phoenix starts to timeout and slow down a lot (queries take
>30sec).

For comparison, I wrote a simple Go script that runs a similar query
talking directly to HBase. The performance on it is substantially better.
It executes in ~1.5sec, and can handle loads of ~50-100qps on the same
cluster.

I'm wondering if anyone has ideas on what might be causing this difference
in performance? Are there configs / optimizations we can do in Phoenix to
bring the performance closer to direct HBase queries?

I can provide context on the table sizes etc. if needed.

Thanks,
Marcell

Re: Phoenix query performance

[Pradheep Shanmugam]

Hi Arvind,

The row key is PARENTID, OWNERORGID,  MILESTONETYPEID, PARENTTYPE
Each parentid will have a list of  MILESTONETYPEID (19661, 1, 2 , etc..). So 
your query will return all the parentids.. I am looking of rparentid that does 
not have a MILESTONETYPEID

Thanks,
Pradheep

From: Arvind S <arvind18...@gmail.com<mailto:arvind18...@gmail.com>>
Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" 
<user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Date: Thursday, February 23, 2017 at 1:19 AM
To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" 
<user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Subject: Re: Phoenix query performance

why cant you reduce your query to

  select msbo1.PARENTID
  from msbo_phoenix_comp_rowkey
  where msbo1.PARENTTYPE = 'SHIPMENT'
   and msbo1.OWNERORGID = 100
   and msbo1.MILESTONETYPEID != 19661
   and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
  group by msbo1.PARENTID
  order by msbo1.PARENTID


??

looks like that's what you get by this left outer.



Cheers !!
Arvind

On 22 February 2017 at 22:58, Pradheep Shanmugam 
<pradheep.shanmu...@infor.com<mailto:pradheep.shanmu...@infor.com>> wrote:
Hi,

We have a hbase cluster with 8 region servers with 20G memory
We have a table  with 1 column family along with a secondary index.
Following query took only few milliseconds when we had less data(< 1 million)
After adding more data(~30M rows) the performance declined and took about a 
minute or more(not stable)

select msbo1.PARENTID
  from msbo_phoenix_comp_rowkey msbo1
  left outer join (
 select PARENTID,MILESTONETYPEID
   from msbo_phoenix_comp_rowkey
  where PARENTREFERENCETIME between 1479964000 and 
1480464000<tel:01480%20464%2>
and OWNERORGID = 100
and PARENTTYPE = 'SHIPMENT'
and MILESTONETYPEID = 19661
group by PARENTID,MILESTONETYPEID
 ) msbo2
  on msbo1.PARENTID = msbo2.PARENTID
  where msbo1.PARENTTYPE = 'SHIPMENT'
   and msbo1.OWNERORGID = 100
   and msbo2.MILESTONETYPEID is null
   and msbo1.PARENTREFERENCETIME between 1479964000 and 
1480464000<tel:01480%20464%2>
group by msbo1.PARENTID
  order by msbo1.PARENTID

The RHS return about a 500K rows ..LHS about 18M rows…final result about 500K 
rows

MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
  is the index

Query plan:
CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER 
MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
 [0,'SHIPMENT',100]
SERVER FILTER BY FIRST KEY ONLY AND 
(TO_UNSIGNED_LONG("PARENTREFERENCETIME") >= 1477958400 AND 
TO_UNSIGNED_LONG("PARENTREFERENCETIME") <= 1480464000<tel:01480%20464%2>)
SERVER AGGREGATE INTO DISTINCT ROWS BY ["MSBO1.:PARENTID"]
CLIENT MERGE SORT
PARALLEL LEFT-JOIN TABLE 0
CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER 
MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
 [0,'SHIPMENT',100,19661,1,477,958,400] - [0,'SHIPMENT',100,19661,1,480,464,000]
SERVER FILTER BY FIRST KEY ONLY
SERVER AGGREGATE INTO DISTINCT ROWS BY ["PARENTID", 
"MILESTONETYPEID"]
CLIENT MERGE SORT
AFTER-JOIN SERVER FILTER BY MSBO2.MILESTONETYPEID IS NULL

Attached the phoenix log.
I see the caching to set as 100..and "maxResultSize”:2097152..is that something 
that can be tuned will help?
Is that the client merge sort consuming more time can be improved? Is there any 
other tuning possible?

Thanks,
Pradheep

Re: Phoenix query performance

[Arvind S]

why cant you reduce your query to

  select msbo1.PARENTID
  from msbo_phoenix_comp_rowkey
  where msbo1.PARENTTYPE = 'SHIPMENT'
   and msbo1.OWNERORGID = 100
   and msbo1.MILESTONETYPEID != 19661
   and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
  group by msbo1.PARENTID
  order by msbo1.PARENTID


??

looks like that's what you get by this left outer.



*Cheers !!*
Arvind

On 22 February 2017 at 22:58, Pradheep Shanmugam <
pradheep.shanmu...@infor.com> wrote:

> Hi,
>
> We have a hbase cluster with 8 region servers with 20G memory
> We have a table  with 1 column family along with a secondary index.
> Following query took only few milliseconds when we had less data(< 1
> million)
> After adding more data(~30M rows) the performance declined and took about
> a minute or more(not stable)
>
> select msbo1.PARENTID
>   from msbo_phoenix_comp_rowkey msbo1
>   left outer join (
>  select PARENTID,MILESTONETYPEID
>from msbo_phoenix_comp_rowkey
>   where PARENTREFERENCETIME between 1479964000 and 1480464000
> <01480%20464%2>
> and OWNERORGID = 100
> and PARENTTYPE = 'SHIPMENT'
> and MILESTONETYPEID = 19661
> group by PARENTID,MILESTONETYPEID
>  ) msbo2
>   on msbo1.PARENTID = msbo2.PARENTID
>   where msbo1.PARENTTYPE = 'SHIPMENT'
>and msbo1.OWNERORGID = 100
>and msbo2.MILESTONETYPEID is null
>and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
> <01480%20464%2>
> group by msbo1.PARENTID
>   order by msbo1.PARENTID
>
> The RHS return about a 500K rows ..LHS about 18M rows…final result about
> 500K rows
>
> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
>  is the index
>
> *Query plan:*
> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER MSBO_PHOENIX_COMP_ROWKEY_
> INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
> [0,'SHIPMENT',100]
> SERVER FILTER BY FIRST KEY ONLY AND 
> (TO_UNSIGNED_LONG("PARENTREFERENCETIME")
> >= 1477958400 AND TO_UNSIGNED_LONG("PARENTREFERENCETIME") <= 1480464000
> <01480%20464%2>)
> SERVER AGGREGATE INTO DISTINCT ROWS BY ["MSBO1.:PARENTID"]
> CLIENT MERGE SORT
> PARALLEL LEFT-JOIN TABLE 0
> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER
> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
> [0,'SHIPMENT',100,19661,1,477,958,400] - [0,'SHIPMENT',100,19661,1,480,
> 464,000]
> SERVER FILTER BY FIRST KEY ONLY
> SERVER AGGREGATE INTO DISTINCT ROWS BY ["PARENTID",
> "MILESTONETYPEID"]
> CLIENT MERGE SORT
> AFTER-JOIN SERVER FILTER BY MSBO2.MILESTONETYPEID IS NULL
>
> Attached the phoenix log.
> I see the caching to set as 100..and "maxResultSize”:2097152..is that
> something that can be tuned will help?
> Is that the client merge sort consuming more time can be improved? Is
> there any other tuning possible?
>
> Thanks,
> Pradheep
>

Re: Phoenix query performance

[Maryann Xue]

Hi Pradheep,

Thank you for the answers! Please see my response inline.


On Wed, Feb 22, 2017 at 12:39 PM, Pradheep Shanmugam <
pradheep.shanmu...@infor.com> wrote:

> Hi Maryann
>
> Please find my answers inline.
>
> Thanks,
> Pradheep
>
> From: Maryann Xue <maryann@gmail.com>
> Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org>
> Date: Wednesday, February 22, 2017 at 2:22 PM
> To: "user@phoenix.apache.org" <user@phoenix.apache.org>
> Subject: Re: Phoenix query performance
>
> Hi Pradheep,
>
> Thank you for posting the query and the log file! There are two things
> going on on the server side at the same time here. I think it'd be a good
> idea to isolate the problem first. So a few questions:
> 1. When you say data size went from "< 1M" to 30M, did the data from both
> LHS and RHS grow proportionately?
> *It is basically the same table..the query is like a self join..yes, you
> can say that it is proportional.*
>

Sorry that I didn't read the query well enough. Just went through it again,
but it looks to me that instead of a LEFT OUTER join, you are actually
trying to do an ANTI join, is that correct? i.e.,
SELECT PARENTID
FROM msbo_phoenix_comp_rowkey msbo1
WHERE  AND NOT EXISTS (
SELECT 1 FROM msbo_phoenix_comp_rowkey
WHERE  AND PARENTID = msbo1. PARENTID)
If the query can be rewritten to an ANTI join, the join operation can be
more efficient.


> 2. If yes to (1), what if we only increase the data in LHS, but keep it
> small for RHS? Would the query run significantly faster?
> *When RHS count is 420336, time taken is 37 seconds*
> *When RHS count is 63575, time taken is 32 seconds (not a significant
> difference)*
> 3. What if we only do group by on LHS? Would the query time be linear to
> the data size?
> After Removing group by on RHS
> *When RHS count is 420336, time taken is 34 seconds*
> *When RHS count is 63575, time taken is 32 seconds*
>

Just to confirm, are you saying that you removed GROUP BY and kept the
JOIN, and it's taking a long time? One more question, how long would it
take to further remove the JOIN?


> 4. How was GC when running the query?
> *About 12ms in 1 RS, 10ms in 1 RS, 4-5ms in couple of them and less than
> 1ms in the rest of the region servers when the query is runnning.*
>
> Thanks,
> Maryann
>
> On Wed, Feb 22, 2017 at 9:28 AM, Pradheep Shanmugam <
> pradheep.shanmu...@infor.com> wrote:
>
>> Hi,
>>
>> We have a hbase cluster with 8 region servers with 20G memory
>> We have a table  with 1 column family along with a secondary index.
>> Following query took only few milliseconds when we had less data(< 1
>> million)
>> After adding more data(~30M rows) the performance declined and took about
>> a minute or more(not stable)
>>
>> select msbo1.PARENTID
>>   from msbo_phoenix_comp_rowkey msbo1
>>   left outer join (
>>  select PARENTID,MILESTONETYPEID
>>from msbo_phoenix_comp_rowkey
>>   where PARENTREFERENCETIME between 1479964000 and 1480464000
>> and OWNERORGID = 100
>> and PARENTTYPE = 'SHIPMENT'
>> and MILESTONETYPEID = 19661
>> group by PARENTID,MILESTONETYPEID
>>  ) msbo2
>>   on msbo1.PARENTID = msbo2.PARENTID
>>   where msbo1.PARENTTYPE = 'SHIPMENT'
>>and msbo1.OWNERORGID = 100
>>and msbo2.MILESTONETYPEID is null
>>and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
>> group by msbo1.PARENTID
>>   order by msbo1.PARENTID
>>
>> The RHS return about a 500K rows ..LHS about 18M rows…final result about
>> 500K rows
>>
>> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
>>  is the index
>>
>> *Query plan:*
>> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER
>> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
>> [0,'SHIPMENT',100]
>> SERVER FILTER BY FIRST KEY ONLY AND 
>> (TO_UNSIGNED_LONG("PARENTREFERENCETIME")
>> >= 1477958400 AND TO_UNSIGNED_LONG("PARENTREFERENCETIME") <= 1480464000)
>> SERVER AGGREGATE INTO DISTINCT ROWS BY ["MSBO1.:PARENTID"]
>> CLIENT MERGE SORT
>> PARALLEL LEFT-JOIN TABLE 0
>> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER
>> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
>> [0,'SHIPMENT',100,19661,1,477,958,400] - [0,'SHIPMENT',100,19661,1,480,
>> 464,000]
>> SERVER FILTER BY FIRST KEY ONLY
>> SERVER AGGREGATE INTO DISTINCT ROWS BY ["PARENTID",
>> "MILESTONETYPEID"]
>> CLIENT MERGE SORT
>> AFTER-JOIN SERVER FILTER BY MSBO2.MILESTONETYPEID IS NULL
>>
>> Attached the phoenix log.
>> I see the caching to set as 100..and "maxResultSize”:2097152..is that
>> something that can be tuned will help?
>> Is that the client merge sort consuming more time can be improved? Is
>> there any other tuning possible?
>>
>> Thanks,
>> Pradheep
>>
>
>

Re: Phoenix query performance

[Pradheep Shanmugam]

Hi Maryann

Please find my answers inline.

Thanks,
Pradheep

From: Maryann Xue <maryann@gmail.com<mailto:maryann@gmail.com>>
Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" 
<user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Date: Wednesday, February 22, 2017 at 2:22 PM
To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" 
<user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Subject: Re: Phoenix query performance

Hi Pradheep,

Thank you for posting the query and the log file! There are two things going on 
on the server side at the same time here. I think it'd be a good idea to 
isolate the problem first. So a few questions:
1. When you say data size went from "< 1M" to 30M, did the data from both LHS 
and RHS grow proportionately?
It is basically the same table..the query is like a self join..yes, you can say 
that it is proportional.
2. If yes to (1), what if we only increase the data in LHS, but keep it small 
for RHS? Would the query run significantly faster?
When RHS count is 420336, time taken is 37 seconds
When RHS count is 63575, time taken is 32 seconds (not a significant difference)
3. What if we only do group by on LHS? Would the query time be linear to the 
data size?
After Removing group by on RHS
When RHS count is 420336, time taken is 34 seconds
When RHS count is 63575, time taken is 32 seconds
4. How was GC when running the query?
About 12ms in 1 RS, 10ms in 1 RS, 4-5ms in couple of them and less than 1ms in 
the rest of the region servers when the query is runnning.

Thanks,
Maryann

On Wed, Feb 22, 2017 at 9:28 AM, Pradheep Shanmugam 
<pradheep.shanmu...@infor.com<mailto:pradheep.shanmu...@infor.com>> wrote:
Hi,

We have a hbase cluster with 8 region servers with 20G memory
We have a table  with 1 column family along with a secondary index.
Following query took only few milliseconds when we had less data(< 1 million)
After adding more data(~30M rows) the performance declined and took about a 
minute or more(not stable)

select msbo1.PARENTID
  from msbo_phoenix_comp_rowkey msbo1
  left outer join (
 select PARENTID,MILESTONETYPEID
   from msbo_phoenix_comp_rowkey
  where PARENTREFERENCETIME between 1479964000 and 1480464000
and OWNERORGID = 100
and PARENTTYPE = 'SHIPMENT'
and MILESTONETYPEID = 19661
group by PARENTID,MILESTONETYPEID
 ) msbo2
  on msbo1.PARENTID = msbo2.PARENTID
  where msbo1.PARENTTYPE = 'SHIPMENT'
   and msbo1.OWNERORGID = 100
   and msbo2.MILESTONETYPEID is null
   and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
group by msbo1.PARENTID
  order by msbo1.PARENTID

The RHS return about a 500K rows ..LHS about 18M rows…final result about 500K 
rows

MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
  is the index

Query plan:
CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER 
MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
 [0,'SHIPMENT',100]
SERVER FILTER BY FIRST KEY ONLY AND 
(TO_UNSIGNED_LONG("PARENTREFERENCETIME") >= 1477958400 AND 
TO_UNSIGNED_LONG("PARENTREFERENCETIME") <= 1480464000)
SERVER AGGREGATE INTO DISTINCT ROWS BY ["MSBO1.:PARENTID"]
CLIENT MERGE SORT
PARALLEL LEFT-JOIN TABLE 0
CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER 
MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
 [0,'SHIPMENT',100,19661,1,477,958,400] - [0,'SHIPMENT',100,19661,1,480,464,000]
SERVER FILTER BY FIRST KEY ONLY
SERVER AGGREGATE INTO DISTINCT ROWS BY ["PARENTID", 
"MILESTONETYPEID"]
CLIENT MERGE SORT
AFTER-JOIN SERVER FILTER BY MSBO2.MILESTONETYPEID IS NULL

Attached the phoenix log.
I see the caching to set as 100..and "maxResultSize”:2097152..is that something 
that can be tuned will help?
Is that the client merge sort consuming more time can be improved? Is there any 
other tuning possible?

Thanks,
Pradheep

Re: Phoenix query performance

[Maryann Xue]

Hi Pradheep,

Thank you for posting the query and the log file! There are two things
going on on the server side at the same time here. I think it'd be a good
idea to isolate the problem first. So a few questions:
1. When you say data size went from "< 1M" to 30M, did the data from both
LHS and RHS grow proportionately?
2. If yes to (1), what if we only increase the data in LHS, but keep it
small for RHS? Would the query run significantly faster?
3. What if we only do group by on LHS? Would the query time be linear to
the data size?
4. How was GC when running the query?


Thanks,
Maryann

On Wed, Feb 22, 2017 at 9:28 AM, Pradheep Shanmugam <
pradheep.shanmu...@infor.com> wrote:

> Hi,
>
> We have a hbase cluster with 8 region servers with 20G memory
> We have a table  with 1 column family along with a secondary index.
> Following query took only few milliseconds when we had less data(< 1
> million)
> After adding more data(~30M rows) the performance declined and took about
> a minute or more(not stable)
>
> select msbo1.PARENTID
>   from msbo_phoenix_comp_rowkey msbo1
>   left outer join (
>  select PARENTID,MILESTONETYPEID
>from msbo_phoenix_comp_rowkey
>   where PARENTREFERENCETIME between 1479964000 and 1480464000
> and OWNERORGID = 100
> and PARENTTYPE = 'SHIPMENT'
> and MILESTONETYPEID = 19661
> group by PARENTID,MILESTONETYPEID
>  ) msbo2
>   on msbo1.PARENTID = msbo2.PARENTID
>   where msbo1.PARENTTYPE = 'SHIPMENT'
>and msbo1.OWNERORGID = 100
>and msbo2.MILESTONETYPEID is null
>and msbo1.PARENTREFERENCETIME between 1479964000 and 1480464000
> group by msbo1.PARENTID
>   order by msbo1.PARENTID
>
> The RHS return about a 500K rows ..LHS about 18M rows…final result about
> 500K rows
>
> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
>  is the index
>
> *Query plan:*
> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER MSBO_PHOENIX_COMP_ROWKEY_
> INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
> [0,'SHIPMENT',100]
> SERVER FILTER BY FIRST KEY ONLY AND 
> (TO_UNSIGNED_LONG("PARENTREFERENCETIME")
> >= 1477958400 AND TO_UNSIGNED_LONG("PARENTREFERENCETIME") <= 1480464000)
> SERVER AGGREGATE INTO DISTINCT ROWS BY ["MSBO1.:PARENTID"]
> CLIENT MERGE SORT
> PARALLEL LEFT-JOIN TABLE 0
> CLIENT 8-CHUNK PARALLEL 8-WAY RANGE SCAN OVER
> MSBO_PHOENIX_COMP_ROWKEY_INDEX_PARENTTYPE_OWNERORGID_MILESTONETYPEID_PARENTREFERENCETIME
> [0,'SHIPMENT',100,19661,1,477,958,400] - [0,'SHIPMENT',100,19661,1,480,
> 464,000]
> SERVER FILTER BY FIRST KEY ONLY
> SERVER AGGREGATE INTO DISTINCT ROWS BY ["PARENTID",
> "MILESTONETYPEID"]
> CLIENT MERGE SORT
> AFTER-JOIN SERVER FILTER BY MSBO2.MILESTONETYPEID IS NULL
>
> Attached the phoenix log.
> I see the caching to set as 100..and "maxResultSize”:2097152..is that
> something that can be tuned will help?
> Is that the client merge sort consuming more time can be improved? Is
> there any other tuning possible?
>
> Thanks,
> Pradheep
>

Phoenix query performance

[Sumit Nigam]

Hi,
I was benchmarking some of the phoenix queries with different compaction level 
tuning. 
A strange thing is observed when there are huge number of Hfiles on disk. The 
queries not returning any data (resultset size 0) execute very quickly (5-10 ms 
or so) but just doing a rs.next() on result set returned from such a query 
takes over 50 seconds! This is consistently reproducible.
I'd assume that when the prepared statement executes and prepared the result 
set, it has completed reading the table or memstore and/ or Hfiles on disk, so 
the time it takes to execute the query should have included that time. Hence, 
when I do rs.next() on such a result set produced, it should not take huge time 
esp. when it is empty.
What am I missing or has anyone else faced/ solved this issue?
Thanks,Sumit