[jira] [Commented] (DRILL-4696) select four table inner join result.Waiting for a long time after drill report error java.lang.OutOfMemoryError: Java heap space
[ https://issues.apache.org/jira/browse/DRILL-4696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15351403#comment-15351403 ] Arina Ielchiieva commented on DRILL-4696: - Queries issued via jdbc in fact being pushed down completely to MySQL. Using batches to return data might resolve the issue. Solution is described in Jira DRILL-4177. > select four table inner join result.Waiting for a long time after drill > report error java.lang.OutOfMemoryError: Java heap space > > > Key: DRILL-4696 > URL: https://issues.apache.org/jira/browse/DRILL-4696 > Project: Apache Drill > Issue Type: Bug > Components: Functions - Drill >Affects Versions: 1.6.0 > Environment: Test Environment: > SUSE Linux Enterprise Server 11 SP3 (x86_64) cluster > MySQL 5.7.11 Enterprise Server - Advanced Edition > Drill cluster >Reporter: david_hudavy > > Test Environment: > cluster 10-3 > MySQL 5.7.11 Enterprise Server - Advanced Edition > Drill cluster > Test Scope: > select performance of huge table(30M records). > MySQL table: a b c d inner join (Four table each have 30M records) > -- four table inner join: (take time Drill Crash) > 0: jdbc:drill:zk=SC-1:6181,SC-2:6181,PL-3:618> select > . . . . . . . . . . . . . . . . . . . . . . .> d.d_5, > . . . . . . . . . . . . . . . . . . . . . . .> c.c_3, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_4, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_5, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_6, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_7, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_8 , > . . . . . . . . . . . . . . . . . . . . . . .> a.a_9 , > . . . . . . . . . . . . . . . . . . . . . . .> a.a_10, > . . . . . . . . . . . . . . . . . . . . . . .> a.a_11 , > . . . . . . . . . . . . . . . . . . . . . . .> a.a_12, > . . . . . . . . . . . . . . . . . . . . . . .> b.b_4 > . . . . . . . . . . . . . . . . . . . . . . .> from > mysql.user_data.a,mysql.user_data.b,mysql.user_data.c,mysql.user_data.d > . . . . . . . . . . . . . . . . . . . . . . .> where c.c_3=a.a_3 and > a.a_3 =b.b_3 and b.b_3=d.d_3 > . . . . . . . . . . . . . . . . . . . . . . .> and c.c_3='0'; > Drill Crash > 2016-05-13 09:52:35,131 [28cacd19-0f04-cbb1-b418-73a76dcd6ebe:frag:0:0] ERROR > o.a.drill.common.CatastrophicFailure - Catastrophic Failure Occurred, > exiting. Information message: Unable to handle out of memory condition in > FragmentExecutor. > java.lang.OutOfMemoryError: Java heap space > at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2157) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1964) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3316) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:463) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at > com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3040) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2288) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2681) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at > org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) > ~[commons-dbcp-1.4.jar:1.4] > at > org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) > ~[commons-dbcp-1.4.jar:1.4] > at > org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:177) > ~[drill-jdbc-storage-1.6.0.jar:1.6.0] > at > org.apache.drill.exec.physical.impl.ScanBatch.(ScanBatch.java:108) > ~[drill-java-exec-1.6.0.jar:1.6.0] > at > org.apache.drill.exec.physical.impl.ScanBatch.(ScanBatch.java:136) > ~[drill-java-exec-1.6.0.jar:1.6.0] > at > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch(JdbcBatchCreator.java:40) > ~[drill-jdbc-storage-1.6.0.jar:1.6.0] > at > org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch(JdbcBatchCreator.java:33) > ~[drill-jdbc-storag
[jira] [Commented] (DRILL-4696) select four table inner join result.Waiting for a long time after drill report error java.lang.OutOfMemoryError: Java heap space
[ https://issues.apache.org/jira/browse/DRILL-4696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15303307#comment-15303307 ] david_hudavy commented on DRILL-4696: - Add four table definitions schema: Each table has 30 million rows. Table structure: CREATE TABLE `Eps` ( `DSG` tinyint(4) unsigned NOT NULL, `ENTRY_KEY` bigint(20) unsigned NOT NULL, `mscId` varchar(255) NOT NULL, `EpsMmeAddr` varchar(255) DEFAULT NULL, `EpsMmeRealm` varchar(255) DEFAULT NULL, `EpsLastInsertSent` varbinary(18) DEFAULT NULL, `EpsMobilityNotifInfo` varbinary(8) DEFAULT NULL, `EpsAaaAddr` varchar(255) DEFAULT NULL, `EpsAaaRealm` varchar(255) DEFAULT NULL, `EpsMmeRegServ` varchar(5) DEFAULT NULL, `EpsHomoImsVoip` int(11) DEFAULT NULL, `EpsUeSrVccCap` varchar(255) DEFAULT NULL, PRIMARY KEY (`DSG`,`ENTRY_KEY`), KEY `mscId` (`mscId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `Eps_EpsImei` ( `DSG` tinyint(4) unsigned NOT NULL, `ENTRY_KEY` bigint(20) unsigned NOT NULL, `mscId` varchar(32) NOT NULL, `EpsImeiSv` varchar(255) DEFAULT NULL, KEY `DSG` (`DSG`,`ENTRY_KEY`), KEY `mscId` (`mscId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `EpsStatic` ( `DSG` tinyint(4) unsigned NOT NULL, `ENTRY_KEY` bigint(20) unsigned NOT NULL, `mscId` varchar(32) NOT NULL, `EpsAccessRestriction` int(11) DEFAULT NULL, `EpsIndDefContextId` bigint(20) DEFAULT NULL, `EpsOdb` int(11) DEFAULT NULL, `EpsProfileId` varchar(255) DEFAULT NULL, `EpsRoamAllow` varchar(5) DEFAULT NULL, PRIMARY KEY (`DSG`,`ENTRY_KEY`), KEY `mscId` (`mscId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `mscIden` ( `DSG` tinyint(4) unsigned NOT NULL, `ENTRY_KEY` bigint(20) unsigned NOT NULL, `mscId` varchar(32) NOT NULL, `IMSI` varchar(15) DEFAULT NULL, `MSISDN` varchar(15) DEFAULT NULL, PRIMARY KEY (`DSG`,`ENTRY_KEY`), KEY `mscId` (`mscId`), KEY `MSISDN` (`MSISDN`), KEY `IMSI` (`IMSI`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > select four table inner join result.Waiting for a long time after drill > report error java.lang.OutOfMemoryError: Java heap space > > > Key: DRILL-4696 > URL: https://issues.apache.org/jira/browse/DRILL-4696 > Project: Apache Drill > Issue Type: Bug > Components: Functions - Drill >Affects Versions: 1.6.0 > Environment: Test Environment: > SUSE Linux Enterprise Server 11 SP3 (x86_64) cluster > MySQL 5.7.11 Enterprise Server - Advanced Edition > Drill cluster >Reporter: david_hudavy > > Test Environment: > cluster 10-3 > MySQL 5.7.11 Enterprise Server - Advanced Edition > Drill cluster > Test Scope: > select performance of huge table(30M records). > MySQL table: Eps Eps_EpsImei mscIden EpsStatic inner join (Four table > each have 30M records) > -- four table inner join: (take time Drill Crash) > 0: jdbc:drill:zk=SC-1:6181,SC-2:6181,PL-3:618> select > . . . . . . . . . . . . . . . . . . . . . . .> EpsStatic.EpsProfileId, > . . . . . . . . . . . . . . . . . . . . . . .> mscIden.mscId, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsMmeAddr, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsMmeRealm, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsLastInsertSent, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsMobilityNotifInfo, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsAaaAddr , > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsAaaRealm , > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsMmeRegServ, > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsHomoImsVoip , > . . . . . . . . . . . . . . . . . . . . . . .> Eps.EpsUeSrVccCap, > . . . . . . . . . . . . . . . . . . . . . . .> Eps_EpsImei.EpsImeiSv > . . . . . . . . . . . . . . . . . . . . . . .> from > mysql.user_data.Eps,mysql.user_data.Eps_EpsImei,mysql.user_data.mscIden,mysql.user_data.EpsStatic > . . . . . . . . . . . . . . . . . . . . . . .> where > mscIden.mscId=Eps.mscId and Eps.mscId =Eps_EpsImei.mscId and > Eps_EpsImei.mscId=EpsStatic.mscId > . . . . . . . . . . . . . . . . . . . . . . .> and mscIden.mscId='0'; > Drill Crash > 2016-05-13 09:52:35,131 [28cacd19-0f04-cbb1-b418-73a76dcd6ebe:frag:0:0] ERROR > o.a.drill.common.CatastrophicFailure - Catastrophic Failure Occurred, > exiting. Information message: Unable to handle out of memory condition in > FragmentExecutor. > java.lang.OutOfMemoryError: Java heap space > at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2157) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38] > at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1964) > ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
