Ignite exception: SQL query ran out of memory

Question

Using GridGain CE 8.8.27 and here is the resource configuration: CPU : 8, RAM : 16 GB, Xmx : 10 GB, Nodes : 2

Data is available in persistence mode, ignite storage folder size is around ~40 GB.

Observed this OOME only when multiple queries were executed parallelly from the thick client C# application. All queries has one common table which has around 1 million records. When these queries were executed individually in sqlline each took around ~9 seconds to get results.

Tried the solution provided in https://www.gridgain.com/docs/latest/developers-guide/memory-configuration/memory-quotas by setting the below configuration which did not solve the issue,

            <property name="sqlConfiguration">
                <bean class="org.apache.ignite.configuration.SqlConfiguration">
                    <property name="sqlGlobalMemoryQuota" value="5200M"/>
                    <property name="sqlOffloadingEnabled" value="true"/>
                </bean>
            </property>

Here was the warning before the exception:

[06:15:30,557][WARNING][long-qry-#32][LongRunningQueryManager] Query execution is too long [globalQueryId=517fd8f3-2cef-413e-9931-332ef5afe131_295, duration=1165ms, type=REDUCE, distributedJoin=false, enforceJoinOrder=false, lazy=false, schema=SAMPLE, sql='SELECT TOP 10...

Error details:

---> Apache.Ignite.Core.Common.JavaException: class org.apache.ignite.IgniteCheckedException: SQL query ran out of memory: Global quota was exceeded.
              at org.apache.ignite.internal.processors.platform.utils.PlatformUtils.unwrapQueryException(PlatformUtils.java:523)
              at org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.processOutStream(PlatformAbstractQueryCursor.java:131)
              at org.apache.ignite.internal.processors.platform.cache.query.PlatformFieldsQueryCursor.processOutStream(PlatformFieldsQueryCursor.java:88)
              at org.apache.ignite.internal.processors.platform.PlatformTargetProxyImpl.outStream(PlatformTargetProxyImpl.java:92)
Caused by: org.apache.ignite.cache.query.exceptions.SqlMemoryQuotaExceededException: SQL query ran out of memory: Global quota was exceeded.
              at org.apache.ignite.internal.processors.query.h2.QueryMemoryManager.onQuotaExceeded(QueryMemoryManager.java:221)
              at org.apache.ignite.internal.processors.query.h2.QueryMemoryManager.reserve(QueryMemoryManager.java:158)
              at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker.reserveFromParent(QueryMemoryTracker.java:151)
              at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker.reserve(QueryMemoryTracker.java:121)
              at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker$ChildMemoryTracker.reserve(QueryMemoryTracker.java:353)
              at org.apache.ignite.internal.processors.query.h2.H2ManagedLocalResult.hasAvailableMemory(H2ManagedLocalResult.java:158)
              at org.apache.ignite.internal.processors.query.h2.H2ManagedLocalResult.addRow(H2ManagedLocalResult.java:389)
              at org.gridgain.internal.h2.command.dml.Select.queryFlat(Select.java:753)
              at org.gridgain.internal.h2.command.dml.Select.queryWithoutCache(Select.java:904)
              at org.gridgain.internal.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
              at org.gridgain.internal.h2.command.dml.Query.query(Query.java:415)
              at org.gridgain.internal.h2.command.dml.Query.query(Query.java:397)
              at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:288)
              at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:160)
              at org.gridgain.internal.h2.index.BaseIndex.find(BaseIndex.java:132)
              at org.gridgain.internal.h2.index.IndexCursor.find(IndexCursor.java:190)
              at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:524)
              at org.gridgain.internal.h2.command.dml.Select.gatherGroup(Select.java:536)
              at org.gridgain.internal.h2.command.dml.Select.queryGroup(Select.java:503)
              at org.gridgain.internal.h2.command.dml.Select.queryWithoutCache(Select.java:897)
              at org.gridgain.internal.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
              at org.gridgain.internal.h2.command.dml.Query.query(Query.java:415)
              at org.gridgain.internal.h2.command.dml.Query.query(Query.java:397)
              at org.gridgain.internal.h2.command.CommandContainer.query(CommandContainer.java:145)
              at org.gridgain.internal.h2.command.Command.executeQuery(Command.java:202)
              at org.gridgain.internal.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:115)
              at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:876)
              at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:987)
              at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:551)
              at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$7.iterator(IgniteH2Indexing.java:1899)
              at org.apache.ignite.internal.processors.cache.QueryCursorImpl.iter(QueryCursorImpl.java:101)
              at org.apache.ignite.internal.processors.cache.query.RegisteredQueryCursor.iter(RegisteredQueryCursor.java:96)
              at org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:138)
              at org.apache.ignite.internal.processors.platform.cache.query.PlatformAbstractQueryCursor.processOutStream(PlatformAbstractQueryCursor.java:126)
              ... 2 more
 
   at Apache.Ignite.Core.Impl.Unmanaged.Jni.Env.ExceptionCheck()
   at Apache.Ignite.Core.Impl.Unmanaged.UnmanagedUtils.TargetOutStream(GlobalRef target, Int32 opType, Int64 memPtr)
   at Apache.Ignite.Core.Impl.PlatformJniTarget.OutStream[T](Int32 type, Func`2 readAction)
   --- End of inner exception stack trace ---
   at Apache.Ignite.Core.Impl.PlatformJniTarget.OutStream[T](Int32 type, Func`2 readAction)
   at Apache.Ignite.Core.Impl.Cache.Query.QueryCursorBase`1.GetAll()

Can you provide the suggestions to overcome this issue?

Answer 1

sqlGlobalMemoryQuota is for ALL SQL queries you are running. It looks like all together they are taking more memory than you set.

Here are the actions you can do:

1)Make all your queries lazy (set lazy flag).

2)Be sure that you don't have full scans over the big tables. Usually when you have a long-running query in the logs, it also has EXPLAIN of this query. Inside the EXPLAIN message you may see something like:

SELECT
"__Z0"."ID" AS "__C0_0",
"__Z0"."DEPID" AS "__C0_1",
"__Z0"."AGE" AS "__C0_2",
"__Z0"."CITYID" AS "__C0_3",
"__Z0"."NAME" AS "__C0_4",
"__Z0"."POSITION" AS "__C0_5"
FROM "PUBLIC"."PERSON" "__Z0"
    /* PUBLIC.PERSON.__SCAN_ */
WHERE "__Z0"."AGE" = 20

In this case you need to create the correct index for AGE.

3)Try to decrease number of parallel queries.

There are some SQL tuning decribed here - https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/sql-tuning