Thanks Maryann, I have tried hint “USE_SORT_MERGE_JOIN”, but it just gets stuck for 2-3 hours. doesn’t show any errors. it does copy data in ResultSpooler on client machine, and then I see it merges some data into ResultSpooler and delete some of the ResultSpooler, but after that I don’t get any result or errors for hours.
I have tried above test case 2 times but same result. Thanks, Krunal. From: Maryann Xue <maryann....@gmail.com<mailto:maryann....@gmail.com>> Date: Tuesday, June 16, 2015 at 3:20 PM To: Krunal <krunal.varaj...@ask.com<mailto:krunal.varaj...@ask.com>> Cc: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto: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<mailto: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<mailto:krunal.varaj...@ask.com>> Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>> Date: Wednesday, June 10, 2015 at 3:49 PM To: Maryann Xue <maryann....@gmail.com<mailto:maryann....@gmail.com>> Cc: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto: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<mailto:maryann....@gmail.com>> Date: Monday, June 1, 2015 at 8:13 AM To: Krunal <krunal.varaj...@ask.com<mailto:krunal.varaj...@ask.com>> Cc: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto: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<mailto: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<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: Tuesday, May 26, 2015 at 5:45 PM To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto: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! And 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<mailto: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!
