[
https://issues.apache.org/jira/browse/DRILL-4696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15303307#comment-15303307
]
david_hudavy edited comment on DRILL-4696 at 5/31/16 9:46 AM:
--------------------------------------------------------------
Add four table definitions schema:
Each table has 30 million rows.
Table structure:
CREATE TABLE `a` (
`a_1` tinyint(4) unsigned NOT NULL,
`a_2` bigint(20) unsigned NOT NULL,
`a_3` varchar(255) NOT NULL,
`a_4` varchar(255) DEFAULT NULL,
`a_5` varchar(255) DEFAULT NULL,
`a_6` varbinary(18) DEFAULT NULL,
`a_7` varbinary(8) DEFAULT NULL,
`a_8` varchar(255) DEFAULT NULL,
`a_9` varchar(255) DEFAULT NULL,
`a_10` varchar(5) DEFAULT NULL,
`a_11` int(11) DEFAULT NULL,
`a_12` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a_1`,`a_2`),
KEY `a_3` (`a_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b` (
`b_1` tinyint(4) unsigned NOT NULL,
`b_2` bigint(20) unsigned NOT NULL,
`b_3` varchar(32) NOT NULL,
`b_4` varchar(255) DEFAULT NULL,
KEY `b_1` (`b_1`,`b_2`),
KEY `b_3` (`b_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `c` (
`c_1` tinyint(4) unsigned NOT NULL,
`c_2` bigint(20) unsigned NOT NULL,
`c_3` varchar(32) NOT NULL,
`c_4` varchar(15) DEFAULT NULL,
`c_5` varchar(15) DEFAULT NULL,
PRIMARY KEY (`c_1`,`c_2`),
KEY `c_3` (`c_3`),
KEY `c_4` (`c_4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `d` (
`d_1` tinyint(4) unsigned NOT NULL,
`d_2` bigint(20) unsigned NOT NULL,
`d_3` varchar(32) NOT NULL,
`d_4` int(11) DEFAULT NULL,
`d_5` bigint(20) DEFAULT NULL,
`d_6` int(11) DEFAULT NULL,
`d_7` varchar(255) DEFAULT NULL,
`d_8` varchar(5) DEFAULT NULL,
PRIMARY KEY (`d_1`,`d_1`),
KEY `d_3` (`d_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
was (Author: david_hudavy):
Add four table definitions schema:
Each table has 30 million rows.
Table structure:
CREATE TABLE `a` (
`a_1` tinyint(4) unsigned NOT NULL,
`a_2` bigint(20) unsigned NOT NULL,
`a_3` varchar(255) NOT NULL,
`a_4` varchar(255) DEFAULT NULL,
`a_5` varchar(255) DEFAULT NULL,
`a_6` varbinary(18) DEFAULT NULL,
`a_7` varbinary(8) DEFAULT NULL,
`a_8` varchar(255) DEFAULT NULL,
`a_9` varchar(255) DEFAULT NULL,
`a_10` varchar(5) DEFAULT NULL,
`a_11` int(11) DEFAULT NULL,
`a_12` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a_1`,`a_2`),
KEY `a_3` (`a_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b` (
`b_1` tinyint(4) unsigned NOT NULL,
`b_2` bigint(20) unsigned NOT NULL,
`b_3` varchar(32) NOT NULL,
`b_4` varchar(255) DEFAULT NULL,
KEY `b_1` (`b_1`,`b_2`),
KEY `b_3` (`b_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `c` (
`c_1` tinyint(4) unsigned NOT NULL,
`c_2` bigint(20) unsigned NOT NULL,
`c_3` varchar(32) NOT NULL,
`c_4` varchar(15) DEFAULT NULL,
`c_5` varchar(15) DEFAULT NULL,
PRIMARY KEY (`c_1`,`c_2`),
KEY `c_3` (`c_3`),
KEY `c_4` (`c_4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `d` (
`d_1` tinyint(4) unsigned NOT NULL,
`d_2` bigint(20) unsigned NOT NULL,
`d_3` varchar(32) NOT NULL,
`d_4` int(11) DEFAULT NULL,
`d_5` bigint(20) DEFAULT NULL,
`d_6` int(11) DEFAULT NULL,
`d_7` varchar(255) DEFAULT NULL,
`d_8` varchar(5) DEFAULT NULL,
PRIMARY KEY (`d_1`,`d_1`),
KEY `mscId` (`d_3`)
) 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: 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.<init>(ScanBatch.java:108)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ScanBatch.<init>(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-storage-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:146)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:126)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:126)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:126)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:126)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch(ImplCreator.java:126)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getChildren(ImplCreator.java:169)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getRootExec(ImplCreator.java:100)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.physical.impl.ImplCreator.getExec(ImplCreator.java:78)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
> at
> org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:231)
> ~[drill-java-exec-1.6.0.jar:1.6.0]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
