Yes, you are right Krunal. The sort merge join performs sorting on both sides of the join first. Looks like this won't for you until PHOENIX-990 is resolved.
What is the heap size of your Phoenix client? This is also related to the exception you got when doing hash join (the very first exception). Thanks, Maryann On Mon, Jun 29, 2015 at 7:08 PM, Krunal Varajiya <krunal.varaj...@ask.com> wrote: > Hi Maryann, > > Here is the error message with “USE_SORT_MERGE_JOIN” hint: > > java.lang.RuntimeException: java.io.IOException: Map failed > > at > org.apache.phoenix.iterate.MappedByteBufferQueue$MappedByteBufferSegmentQueue$SegmentQueueFileIterator.readNext(MappedByteBufferQueue.java:399) > > at > org.apache.phoenix.iterate.MappedByteBufferQueue$MappedByteBufferSegmentQueue$SegmentQueueFileIterator.next(MappedByteBufferQueue.java:379) > > at > org.apache.phoenix.execute.SortMergeJoinPlan$MappedByteBufferTupleQueue$2.next(SortMergeJoinPlan.java:575) > > at > org.apache.phoenix.execute.SortMergeJoinPlan$MappedByteBufferTupleQueue$2.next(SortMergeJoinPlan.java:557) > > at > org.apache.phoenix.execute.SortMergeJoinPlan$BasicJoinIterator.next(SortMergeJoinPlan.java:263) > > at > org.apache.phoenix.iterate.LookAheadResultIterator$1.advance(LookAheadResultIterator.java:47) > > at > org.apache.phoenix.iterate.LookAheadResultIterator.next(LookAheadResultIterator.java:67) > > at > org.apache.phoenix.iterate.BaseGroupedAggregatingResultIterator.next(BaseGroupedAggregatingResultIterator.java:79) > > at > org.apache.phoenix.iterate.LookAheadResultIterator$1.advance(LookAheadResultIterator.java:47) > > at > org.apache.phoenix.iterate.LookAheadResultIterator.init(LookAheadResultIterator.java:59) > > at > org.apache.phoenix.iterate.LookAheadResultIterator.next(LookAheadResultIterator.java:65) > > at > org.apache.phoenix.iterate.BaseGroupedAggregatingResultIterator.next(BaseGroupedAggregatingResultIterator.java:64) > > at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:764) > > at > org.apache.phoenix.jdbc.PhoenixConnection.executeStatements(PhoenixConnection.java:283) > > at > org.apache.phoenix.util.PhoenixRuntime.executeStatements(PhoenixRuntime.java:229) > > at org.apache.phoenix.util.PhoenixRuntime.main(PhoenixRuntime.java:175) > > Caused by: java.io.IOException: Map failed > > at sun.nio.ch.FileChannelImpl.map(FileChannelImpl.java:907) > > at > org.apache.phoenix.iterate.MappedByteBufferQueue$MappedByteBufferSegmentQueue$SegmentQueueFileIterator.readNext(MappedByteBufferQueue.java:397) > > ... 15 more > > Caused by: java.lang.OutOfMemoryError: Map failed > > at sun.nio.ch.FileChannelImpl.map0(Native Method) > > at sun.nio.ch.FileChannelImpl.map(FileChannelImpl.java:904) > > ... 16 more > > Java HotSpot(TM) 64-Bit Server VM warning: Java HotSpot(TM) 64-Bit Server > VM warning: INFO: os::commit_memory(0x00007fef5d6d7000, 12288, 0) failed; > error='Cannot allocate memory' (errno=12) > > Java HotSpot(TM) 64-Bit Server VM warning: INFO: > os::commit_memory(0x00007fee59edf000, 12288, 0) failed; error='Cannot > allocate memory' (errno=12) > > INFO: os::commit_memory(0x00007fef5f0f1000, 12288, 0) failed; > error='Cannot allocate memory' (errno=12)[thread 140661688760064 also had > an error] > > > [thread 140666069784320 also had an error] > > # > > # There is insufficient memory for the Java Runtime Environment to > continue. > > # Native memory allocation (mmap) failed to map 12288 bytes for committing > reserved memory. > > # An error report file with more information is saved as: > > # /home/varajiyak/phoenix/bin/hs_err_pid34045.log > > > Is this related to https://issues.apache.org/jira/browse/PHOENIX-990? Or > like GC issue? > > Thanks, > Krunal. > > > From: Maryann Xue <maryann....@gmail.com> > Date: Tuesday, June 16, 2015 at 3:20 PM > To: Krunal <krunal.varaj...@ask.com> > Cc: "user@phoenix.apache.org" <user@phoenix.apache.org> > Subject: Re: Join create OOM with java heap space on phoenix client > > Hi Krunal, > > Can you try with merge join by specifying the hint > "USE_SORT_MERGE_JOIN"? and if it still does not work, would you mind > posting the exact error message of running this merge-join? > > > Thanks, > Maryann > > On Tue, Jun 16, 2015 at 6:12 PM, Krunal Varajiya <krunal.varaj...@ask.com> > wrote: > >> Does anybody has any idea why below join is throwing OOM error? I will >> really appreciate any help here. We are stuck here is as none of our join >> works even with 5M rows. >> >> From: Krunal <krunal.varaj...@ask.com> >> Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org> >> Date: Wednesday, June 10, 2015 at 3:49 PM >> To: Maryann Xue <maryann....@gmail.com> >> >> Cc: "user@phoenix.apache.org" <user@phoenix.apache.org> >> Subject: Re: Join create OOM with java heap space on phoenix client >> >> Hey Maryann, >> >> Sorry I just realized that I am using Phoenix 4.3.0. >> >> I am using sample tables generated using performance.py script from >> Phoenix package! I have generated 5M, 25M, 100M using this script and >> running join in these tables! >> >> Here is table definition: >> >> * TABLE_CAT ** | ** >> TABLE_SCHEM ** | ** TABLE_NAME >> ** | ** COLUMN_NAME ** | ** >> DATA_TYPE ** |* >> >> >> *+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------+* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *HOST >> * | *1 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *DOMAIN >> * | *12 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *FEATURE >> * | *12 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *DATE >> * | *91 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *CORE >> * | *-5 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 * | *DB >> * | *-5 * |* >> >> *| * * | * >> * | *PERFORMANCE_5000000 >> >> Thank you for your time! >> - Krunal >> >> From: Maryann Xue <maryann....@gmail.com> >> Date: Monday, June 1, 2015 at 8:13 AM >> To: Krunal <krunal.varaj...@ask.com> >> Cc: "user@phoenix.apache.org" <user@phoenix.apache.org> >> Subject: Re: Join create OOM with java heap space on phoenix client >> >> Hi Krunal, >> >> Thanks for the explanation! I am back to work now. Could you please >> also post the table definition? I have some guess here, but need more >> information to confirm. And which version of Phoenix are you using? >> >> >> Thanks, >> Maryann >> >> On Wed, May 27, 2015 at 4:55 PM, Krunal Varajiya <krunal.varaj...@ask.com >> > wrote: >> >>> Hey Maryann >>> >>> Thanks for your reply! I understand it is difficult to access some >>> stuff in China! I can wait till you come back, meantime I will keep you >>> posted on if we make any progress on this! >>> >>> My comments are below in blue: >>> >>> Have a fun time in China! >>> - Krunal >>> >>> >>> From: Maryann Xue <maryann....@gmail.com> >>> Reply-To: "user@phoenix.apache.org" <user@phoenix.apache.org> >>> Date: Tuesday, May 26, 2015 at 5:45 PM >>> To: "user@phoenix.apache.org" <user@phoenix.apache.org> >>> Subject: Re: Join create OOM with java heap space on phoenix client >>> >>> Hi Krunal, >>> >>> Sorry for the late reply. I have been on vacation. >>> >>> 1. Can you make sure that the connection/statement is closed after >>> each run of your query (even with exception)? >>> >>> *I think this is related to second issue mentioned below as it happens >>> for join. I tried some other queries they are working fine!* >>> >>> 2. You might want to try switching the join tables in your query first >>> by putting the larger table as LHS, and if it still does not work, you can >>> force a merge join by adding hint "USE_SORT_MERGE_JOIN". >>> >>> *I have tried changing lhs and rhs, but no luck. I have also tried >>> "USE_SORT_MERGE_JOIN" but for some reason it crashes region server while >>> scanning result to local.* >>> *I have also tried changing some tuning parameters, but none of the >>> settings worked!* >>> *One thing I am curious is why it is dumping data in local heap, I see >>> millions of instances for org.apache.phoenix.expression.literalexpression. >>> Shouldn't it execute joins on server side? * >>> *I tried increasing memory upto 64gb and still it fails! **This is just >>> one single connection and only one query running at a time, I am not sure >>> what will happen with multiple connection and multiple queries!* >>> *A**nd if you see in example my data size for both LHS and RHS is not >>> that big. These are sample tables come with Phoenix.* >>> >>> Thanks, >>> Maryann >>> >>> On Thursday, May 21, 2015, Krunal Varajiya <krunal.varaj...@ask.com> >>> wrote: >>> >>>> Hi >>>> >>>> I have 2 issues with phoenix client: >>>> >>>> 1. Heap memory is not cleanup after each query is finished. So, it >>>> keeps increasing every time when we submit new query. >>>> 2. I am try to do a normal join operation on two tables but getting >>>> exception. Below is the details: >>>> >>>> These are some sample queries I tried: >>>> >>>> 1. select p1.host, count(1) from PERFORMANCE_5000000 p1, >>>> PERFORMANCE_25000000 p2 where p1.host = p2.host group by p1.host; >>>> 2. select p1.host from PERFORMANCE_5000000 p1, PERFORMANCE_25000000 >>>> p2 where p1.host = p2.host group by p1.host; >>>> 3. select count(1) from PERFORMANCE_5000000 p1, >>>> PERFORMANCE_25000000 p2 where p1.host = p2.host group by p1.host; >>>> >>>> Here is explain plan: >>>> >>>> explain select count(1) from PERFORMANCE_5000000 p1, >>>> PERFORMANCE_25000000 p2 where p1.host = p2.host group by p1.host; >>>> >>>> +------------------------------------------+ >>>> >>>> | PLAN | >>>> >>>> +------------------------------------------+ >>>> >>>> | CLIENT 9-CHUNK PARALLEL 1-WAY FULL SCAN OVER PERFORMANCE_5000000 | >>>> >>>> | SERVER FILTER BY FIRST KEY ONLY | >>>> >>>> | SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [HOST] | >>>> >>>> | CLIENT MERGE SORT | >>>> >>>> | PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE) | >>>> >>>> | CLIENT 18-CHUNK PARALLEL 1-WAY FULL SCAN OVER >>>> PERFORMANCE_25000000 | >>>> >>>> | SERVER FILTER BY FIRST KEY ONLY | >>>> >>>> | DYNAMIC SERVER FILTER BY HOST IN (P2.HOST) | >>>> >>>> +------------------------------------------+ >>>> >>>> 8 rows selected (0.127 seconds) >>>> >>>> Phoenix client heap size is 16GB. ( noticed that above queries are >>>> dumping data in local heap, I see millions of instances for >>>> org.apache.phoenix.expression.literalexpression) >>>> >>>> and my exceptions are: >>>> >>>> java.sql.SQLException: Encountered exception in sub plan [0] execution. >>>> >>>> at >>>> org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:156) >>>> >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:235) >>>> >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:226) >>>> >>>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >>>> >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:225) >>>> >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1066) >>>> >>>> at sqlline.Commands.execute(Commands.java:822) >>>> >>>> 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) >>>> >>>> Caused by: java.sql.SQLException: >>>> java.util.concurrent.ExecutionException: java.lang.Exception: >>>> java.lang.OutOfMemoryError: Java heap space >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:247) >>>> >>>> at >>>> org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:83) >>>> >>>> at >>>> org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:338) >>>> >>>> at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:135) >>>> >>>> at java.util.concurrent.FutureTask.run(FutureTask.java:266) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) >>>> >>>> at java.lang.Thread.run(Thread.java:745) >>>> >>>> Caused by: java.util.concurrent.ExecutionException: >>>> java.lang.Exception: java.lang.OutOfMemoryError: Java heap space >>>> >>>> at java.util.concurrent.FutureTask.report(FutureTask.java:122) >>>> >>>> at java.util.concurrent.FutureTask.get(FutureTask.java:206) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:239) >>>> >>>> ... 7 more >>>> >>>> Caused by: java.lang.Exception: java.lang.OutOfMemoryError: Java heap >>>> space >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:212) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:182) >>>> >>>> ... 4 more >>>> >>>> Caused by: java.lang.OutOfMemoryError: Java heap space >>>> >>>> May 20, 2015 4:58:01 PM ServerCommunicatorAdmin reqIncoming >>>> >>>> WARNING: The server has decided to close this client connection. >>>> >>>> 15/05/20 16:56:43 WARN client.HTable: Error calling coprocessor service >>>> org.apache.phoenix.coprocessor.generated.ServerCachingProtos$ServerCachingService >>>> for row CSGoogle\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00 >>>> >>>> java.util.concurrent.ExecutionException: java.lang.OutOfMemoryError: >>>> Java heap space >>>> >>>> at java.util.concurrent.FutureTask.report(FutureTask.java:122) >>>> >>>> at java.util.concurrent.FutureTask.get(FutureTask.java:192) >>>> >>>> at >>>> org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1620) >>>> >>>> at >>>> org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1577) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:188) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:182) >>>> >>>> at java.util.concurrent.FutureTask.run(FutureTask.java:266) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) >>>> >>>> at java.lang.Thread.run(Thread.java:745) >>>> >>>> Caused by: java.lang.OutOfMemoryError: Java heap space >>>> >>>> at com.google.protobuf.ByteString$CodedBuilder.<init> >>>> (ByteString.java:907) >>>> >>>> at com.google.protobuf.ByteString$CodedBuilder.<init> >>>> (ByteString.java:902) >>>> >>>> at com.google.protobuf.ByteString.newCodedBuilder(ByteString.java:898) >>>> >>>> at >>>> com.google.protobuf.AbstractMessageLite.toByteString(AbstractMessageLite.java:49) >>>> >>>> at >>>> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel.callExecService(RegionCoprocessorRpcChannel.java:81) >>>> >>>> at >>>> org.apache.hadoop.hbase.ipc.CoprocessorRpcChannel.callMethod(CoprocessorRpcChannel.java:57) >>>> >>>> at >>>> org.apache.phoenix.coprocessor.generated.ServerCachingProtos$ServerCachingService$Stub.addServerCache(ServerCachingProtos.java:3270) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1$1.call(ServerCacheClient.java:204) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1$1.call(ServerCacheClient.java:189) >>>> >>>> at org.apache.hadoop.hbase.client.HTable$17.call(HTable.java:1608) >>>> >>>> ... 4 more >>>> >>>> 0: jdbc:phoenix:prbhadoop004iad.io.askjeeves.> 15/05/20 16:56:43 WARN >>>> client.HTable: Error calling coprocessor service >>>> org.apache.phoenix.coprocessor.generated.ServerCachingProtos$ServerCachingService >>>> for row EUGoogle\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00 >>>> >>>> java.util.concurrent.ExecutionException: java.lang.OutOfMemoryError: >>>> Java heap space >>>> >>>> at java.util.concurrent.FutureTask.report(FutureTask.java:122) >>>> >>>> at java.util.concurrent.FutureTask.get(FutureTask.java:192) >>>> >>>> at >>>> org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1620) >>>> >>>> at >>>> org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1577) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:188) >>>> >>>> at >>>> org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:182) >>>> >>>> at java.util.concurrent.FutureTask.run(FutureTask.java:266) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) >>>> >>>> at >>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) >>>> >>>> at java.lang.Thread.run(Thread.java:745) >>>> >>>> Caused by: java.lang.OutOfMemoryError: Java heap space >>>> >>>> 15/05/20 16:59:37 WARN >>>> client.HConnectionManager$HConnectionImplementation: This client just lost >>>> it's session with ZooKeeper, closing it. It will be recreated next time >>>> someone needs it >>>> >>>> org.apache.zookeeper.KeeperException$SessionExpiredException: >>>> KeeperErrorCode = Session expired >>>> >>>> at >>>> org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.connectionEvent(ZooKeeperWatcher.java:403) >>>> >>>> at >>>> org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.process(ZooKeeperWatcher.java:321) >>>> >>>> at >>>> org.apache.zookeeper.ClientCnxn$EventThread.processEvent(ClientCnxn.java:522) >>>> >>>> at org.apache.zookeeper.ClientCnxn$EventThread.run(ClientCnxn.java:498) >>>> >>>> >>>> Error: (state=,code=0) >>>> >>>> java.sql.SQLFeatureNotSupportedException >>>> >>>> at >>>> org.apache.phoenix.jdbc.PhoenixStatement.cancel(PhoenixStatement.java:958) >>>> >>>> at sqlline.DispatchCallback.forceKillSqlQuery(DispatchCallback.java:83) >>>> >>>> at sqlline.SqlLine.begin(SqlLine.java:695) >>>> >>>> at sqlline.SqlLine.start(SqlLine.java:398) >>>> >>>> at sqlline.SqlLine.main(SqlLine.java:292) >>>> >>>> >>>> Can someone please help? >>>> >>>> >>>> Thanks! >>>> >>> >> >
