Hi Igniters,
I am testing SQL with Ignite. With the following simple tests, queries
fails with out-of-memory error, and Ignite is not usable anymore until
restart.
It seems Ignite need get all the records to be returned in heap first
before transferring data to client side. This can be problematic since
even with large heap size, this problem may still happens depend the size
of the data to be returned and how many concurrent queries....
In my case, persistent is on for the data region(named as
storageWithPersistence); with default heap size (1GB). Here is the simple
tests:
public class JDBCTestWithTemplate_LargeDataSet {
public static void main(String[] args) {
int len = 10 * 1204 * 1024; // 10M
byte[] binaryData = new byte[len];
for (int i=0; i < len; i++) {
binaryData[i] = 1;
}
Connection conn = null;
try{
// Create a JDBC connection
conn = TestUtils.prepareIgniteJDBCConnection(args);
// Create City table based on FeatureTileCacheTemplateName_1_Day template
// This template use a data region with persistent on.
executeSQL(conn, "DROP TABLE IF EXISTS CITY ");
executeSQL(conn,
"CREATE TABLE IF NOT EXISTS CITY (id LONG PRIMARY KEY, val BINARY) " +
"with \"TEMPLATE=" +
CacheConfigurationTemplates.FeatureTileCacheTemplateName_1_Day+ "\"");
String sql = "INSERT INTO CITY (id, val) VALUES(?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
// create some binary data
String myData = "Adding test data ...";
// set value for the prepared statement
for (int i = 0; i < 1000; i++) {
pstmt.setInt(1, i);
pstmt.setBytes(2, binaryData);
pstmt.executeUpdate();
}
pstmt.close();
Thread.sleep(10000);
try (Statement stmt = conn.createStatement()) {
try (ResultSet rs =
stmt.executeQuery("SELECT id, val FROM CITY WHERE id < 100")) {
while(rs.next()) {
System.out.println(">>> Retrieved id " + rs.getLong(1));
}
}
}
} catch (Exception e) {
System.err.format("Unexpected failure: %s\n", e);
e.printStackTrace();
} finally {
if (conn != null) {
System.out.print("Dropped database objects...");
try {
executeSQL(conn, "DROP TABLE IF EXISTS CITY ");
conn.close();
} catch (Exception ignore) {
}
}
}
}
/**
* Execute SQL command.
*
* @param conn Connection.
* @param sql SQL statement.
* @throws Exception If failed.
*/
private static void executeSQL(Connection conn, String sql) throws Exception {
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(sql);
}
}
}
The following are from the Ignite logs:
2019-06-06 10:45:03,952 INFO
Metrics for local node (to disable set 'metricsLogFrequency' to 0)
^-- Node [id=9f8714ae, name=cs_ittbix3, uptime=00:04:00.038]
^-- H/N/C [hosts=1, nodes=1, CPUs=8]
^-- CPU [cur=-100%, avg=-99.07%, GC=0%]
^-- PageMemory [pages=254721]
^-- Heap [used=179MB, free=82.5%, comm=1024MB]
^-- Off-heap [used=1006MB, free=57.13%, comm=1836MB]
^-- sysMemPlc region [used=0MB, free=99.99%, comm=100MB]
^-- default region [used=4MB, free=99.61%, comm=512MB]
^-- metastoreMemPlc region [used=0MB, free=99.94%, comm=100MB]
^-- storageWithPersistence region [used=1002MB, free=2.1%,
comm=1024MB]
^-- TxLog region [used=0MB, free=100%, comm=100MB]
^-- Ignite persistence [used=11988MB]
^-- sysMemPlc region [used=0MB]
^-- metastoreMemPlc region [used=unknown]
^-- storageWithPersistence region [used=11988MB]
^-- TxLog region [used=0MB]
^-- Outbound messages queue [size=0]
^-- Public thread pool [active=0, idle=0, qSize=0]
^-- System thread pool [active=0, idle=6, qSize=0]
......
2019-06-06 10:50:20,011 DEBUG Client request received [reqId=0, addr=/
10.212.22.67:54554, req=JdbcQueryExecuteRequest
[schemaName=FEATURE_TILE_CACHE, pageSize=1024, maxRows=0, sqlQry=SELECT id,
val FROM CITY WHERE id > 500, args=[], stmtType=SELECT_STATEMENT_TYPE,
autoCommit=true]]
2019-06-06 10:50:20,011 DEBUG Parsed query: `SELECT id, val FROM CITY WHERE
id > 500` into two step query: GridCacheTwoStepQuery
[mapQrys=[GridCacheSqlQuery [qry=SELECT
__Z0.ID __C0_0,
__Z0.VAL __C0_1
FROM FEATURE_TILE_CACHE.CITY __Z0
WHERE __Z0.ID > 500, paramIdxs=[], cols={__C0_0=GridSqlType [type=5,
scale=0, precision=19, displaySize=20, sql=BIGINT], __C0_1=GridSqlType
[type=12, scale=0, precision=2147483647, displaySize=2147483647,
sql=VARBINARY]}, alias=null, sort=[], partitioned=true, node=null,
derivedPartitions=null, hasSubQries=false]], rdc=GridCacheSqlQuery
[qry=SELECT
__C0_0 ID,
__C0_1 VAL
FROM PUBLIC.__T0, paramIdxs=[], cols=null, alias=null, sort=null,
partitioned=false, node=null, derivedPartitions=null, hasSubQries=false],
pageSize=1024, explain=false, originalSql=SELECT
ID,
VAL
FROM FEATURE_TILE_CACHE.CITY
WHERE ID > 500, distributedJoins=false, skipMergeTbl=true, local=false,
mvccEnabled=false, forUpdate=false]
2019-06-06 10:50:20,014 ERROR Failed to execute SQL query [reqId=0,
req=JdbcQueryExecuteRequest [schemaName=FEATURE_TILE_CACHE, pageSize=1024,
maxRows=0, sqlQry=SELECT id, val FROM CITY WHERE id > 500, args=[],
stmtType=SELECT_STATEMENT_TYPE, autoCommit=true]]
org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to
set schema for DB connection for thread [schema=FEATURE_TILE_CACHE]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:587)
~[ignite-indexing.jar:2.7.0]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForSchema(IgniteH2Indexing.java:414)
~[ignite-indexing.jar:2.7.0]
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:656)
~[ignite-indexing.jar:2.7.0]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$8.iterator(IgniteH2Indexing.java:1803)
~[ignite-indexing.jar:2.7.0]
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:95)
~[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcQueryCursor.<init>(JdbcQueryCursor.java:61)
~[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:528)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.doHandle(JdbcRequestHandler.java:245)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:208)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:162)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:45)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:120)
[ignite-core.jar:2.7.0]
at
org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70)
[ignite-core.jar:2.7.0]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:?]
at java.lang.Thread.run(Unknown Source) [?:?]
Caused by: org.h2.jdbc.JdbcSQLException: Schema "FEATURE_TILE_CACHE" not
found; SQL statement:
SET SCHEMA "FEATURE_TILE_CACHE" [90079-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
~[h2.jar:1.4.197]
at org.h2.message.DbException.get(DbException.java:179) ~[h2.jar:1.4.197]
at org.h2.message.DbException.get(DbException.java:155) ~[h2.jar:1.4.197]
at org.h2.engine.Database.getSchema(Database.java:1808) ~[h2.jar:1.4.197]
at org.h2.command.dml.Set.update(Set.java:409) ~[h2.jar:1.4.197]
at org.h2.command.CommandContainer.update(CommandContainer.java:102)
~[h2.jar:1.4.197]
at org.h2.command.Command.executeUpdate(Command.java:261) ~[h2.jar:1.4.197]
at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:169)
~[h2.jar:1.4.197]
at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126)
~[h2.jar:1.4.197]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:579)
~[ignite-indexing.jar:2.7.0]
... 18 more
Is there anything I can do to avoid this?
Thanks,
Shane
