phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sumit Nigam <>
Subject Re: Hash join confusion
Date Tue, 04 Oct 2016 16:14:47 GMT
Thank you Maryann.
>From the time I have moved to sort-merge join, my use cases have stopped working. However,
if I remove the hint (and fall back to hash), then they all work. I am on phoenix 4.6/ hbase
I thought just changing the join algorithm would be enough. I would assume that changing
the hash join to sort-merge join would not alter the query results, right? Do I need to re-write
my query?
I am using global index.

      From: Maryann Xue <>
 To: Sumit Nigam <> 
Cc: "" <>
 Sent: Sunday, October 2, 2016 5:30 AM
 Subject: Re: Hash join confusion
So if either or both sides of a sort-merge-join will have to be sorted simply depends on whether
this side is already ordered on the join key.
So far we don't have any documentation specifically for explain plan yet, but the Phoenix
website does have some examples for different types of queries or functionalities, including
join queries.

On Wed, Sep 28, 2016 at 10:52 PM, Sumit Nigam <> wrote:

Thanks Maryann.
Yes let me switch to merge sort join because the other query uses lots more columns. Also,
if I just change the hint to use merge sort would that be enough or I need to sort both the
driving query and subquery with same order by for merge sort?
As an aside, is there a document to interpret explain plan?
      From: Maryann Xue <>
 To: Sumit Nigam <> 
Cc: "" <>
 Sent: Thursday, September 29, 2016 11:03 AM
 Subject: Re: Hash join confusion
Thank you Sumit, for trying this out! So right now it's very clear that the table to be cached
IS too big so there should be no point of using hash join in this case. Is the other table
much smaller, or it is about the same size or even bigger? If it's considerably smaller you
can probably rewrite your query to do the join the other way, otherwise let's just stick to
sort-merge join.

On Wed, Sep 28, 2016 at 10:29 PM, Sumit Nigam <> wrote:

Thank you Maryann.
I am not using multi-tenancy for these tables. Increasing phoenix. coprocessor.maxServerCacheTime
ToLiveMs and the corresponding cache size config just delayed the error. 
I have also started seeing some memory problem -
Caused by: org.apache.phoenix.memory.Insu fficientMemoryException: Requested memory of 22871932
bytes could not be allocated from remaining memory of 776776654 bytes from global pool of
778469376 bytes after waiting for 10000ms.
	at org.apache.phoenix.memory. GlobalMemoryManager. allocateBytes(
	at org.apache.phoenix.memory. GlobalMemoryManager.access$ 300( 30)
	at org.apache.phoenix.memory. GlobalMemoryManager$ GlobalMemoryChunk.resize(
What I am having trouble with is, that the total size of csv produced by sub-query is only
~7Mb. I have 12 region servers with 5GB heap each. So, when this result gets sent across to
all region servers to perform the server side join, not sure why a memory issue should show
up (or a time out occur). Any insights?

These tables are salted. Not sure if it is jira/browse/PHOENIX-2900 
Switching to sort merge join helped. But not sure if that is the right solution going forward.
Thanks again!Sumit

      From: Maryann Xue <>
 To: "" <>; Sumit Nigam <>

 Sent: Wednesday, September 28, 2016 11:36 PM
 Subject: Re: Hash join confusion
Yes, Sumit, the sub-query will get cached in hash join. Are you using multi-tenancy for these
tables? If yes, you might want to checkout Phoenix 4.7 or 4.8, since a related bug fix got
in the 4.7 release. https://issues. PHOENIX-2381?jql=project%20%
3D%20PHOENIX%20AND%20text%20~% 20%22hash%20cache%20id%22
Otherwise I think it's the hash cache timeout issue, in which case changing phoenix.coprocessor.m
axServerCacheTimeToLiveMs migh t be helpful.

On Tue, Sep 27, 2016 at 10:02 PM, Sumit Nigam <> wrote:

Is there any document which can help me understand explain plan output in detail? Or, which
piece of code should I look at, to get an idea?
Here is explain plan for inner join query below. Can anyone help in explaining it to me? Like,
as per the plan which table is being cached, etc.?Here, indx_exdocb is index table (on ID)
and exDocStoreb is main table with rowkey as (current_timestamp, ID).
+----------------------------- -------------+|                   PLAN        
          |+----------------------------- -------------+| CLIENT 36-CHUNK PARALLEL 36-WAY
CLIENT 36-CHUNK PARALLEL 36-WAY RANGE SCAN OVER indx_exdocb [0,' 42ecf4abd4bd7e7606025dc8eee3de
6a3cc04418cbc2619ddc01f54d88d7 c3bf'] - [0,' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7
c3bg' ||             SERVER FILTER BY FIRST KEY ONLY ||             SERVER AGGREGATE
 ||     DYNAMIC SERVER FILTER BY (A.CURRENT_TIMESTAMP, A.ID) IN ((TMP.MCT, TMP.TID)) |+-----------------------------
Also, is there a way to turn ON more verbose explain plan? Like, seeing number of bytes, rows
that each step results in?

      From: Sumit Nigam <>
 To: Users Mail List Phoenix <> 
 Sent: Tuesday, September 27, 2016 9:17 PM
 Subject: Hash join confusion
I am using hbase 1.1 with phoenix 4.6. 
I have a table with row key as (current_timestamp, id) which is salted and index on (id).
This table has ~3 million records.
I have a query like given below. 
         as a inner join (                                select max(CURRENT_TIMESTAMP)
as mct, ID as tid from TBL where ID like ' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7
c3bf%' group by ID) as tmp                        on a.ID=tmp.tid and a.CURRENT_TIMESTAMP=tmp.mct

The query hangs for long and finally fails with a timeout. I have 12 region servers each with
5GB heap and also the total records satisfying the above query is 62K whose CSV dump is ~10MB
DoNotRetryIOException: Could not find hash cache for join Id: Ӧ�8�D�. The cache might
have expired and have been removed

and - 
Caused by: java.sql.SQLException: Encountered exception in sub plan [0] execution.
	at org.apache.phoenix.execute. HashJoinPlan.iterator(
	at com.infa.products.ldm. impl. FixPhoenixIngestInputFormat.
getQueryPlan( FixPhoenixIngestInputFormat. java:94)
	... 22 more
Caused by: java.sql.SQLException:
    java.util.concurrent.Timeo utException        at org.apache.phoenix.cache.
ServerCacheClient. addServerCache(

I can try playing around with parameters such as phoenix.coprocessor. maxServerCacheTimeToLiveMs
and switching to sort_merge_join actually helped.
But my question is as per Joins | Apache Phoenix in a case such as lhs INNER JOIN rhs, it
is rhs which will be built as hash table in server cache. So, in the above query I assume
this gets cached?
 select max(CURRENT_TIMESTAMP) as mct, ID as tid from TBL where ID like ' 42ecf4abd4bd7e7606025dc8eee3de
6a3cc04418cbc2619ddc01f54d88d7 c3bf%' group by ID) as tmp 




View raw message