Ignite SQL查询内存不足问题求助（GridGain CE 8.8.27）

环境配置 #

• GridGain CE版本：8.8.27
• 单节点硬件与JVM配置：
  • CPU：8核
  • RAM：16GB
  • JVM Xmx：10GB
• 集群节点数：2
• 存储模式：启用持久化，Ignite存储文件夹约40GB
• 数据规模：涉及的通用表约100万条记录

问题现象 #

• 仅在C#厚客户端并行执行多个查询时触发内存不足异常（OOME）
• 单条查询在sqlline中执行耗时约9秒，无异常

已尝试的无效配置 #

配置了SQL内存配额与磁盘卸载，但未解决问题：

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

异常相关信息 #

前置警告 #

[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...

错误堆栈 #

---> 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()

解决建议 #

1. 优化查询逻辑

   • 仅返回必要字段，避免查询无关列，减少单条查询的内存占用
   • 为查询的过滤、分组、排序字段创建索引，避免全表扫描，降低内存处理的数据量
   • 在C#客户端启用懒加载：设置查询参数Lazy = true，避免一次性加载所有结果到内存，按需获取数据

2. 调整内存配额与卸载配置

   • 增大sqlGlobalMemoryQuota：当前5200M在并行场景下不足，可尝试调整至6-8GB（不超过JVM Xmx的70%，避免挤占其他堆内存区域）
   • 添加单查询配额：配置sqlQueryMemoryQuota限制单条查询内存，比如设置为2000M，防止个别大查询耗尽全局配额
   • 验证磁盘卸载有效性：确保节点磁盘有足够空间，确认sqlOffloadingEnabled=true时，中间结果能正确写入磁盘

3. 控制并发查询数量

   • 在C#客户端限制并行查询的并发度，根据集群配置调整合理的并发数，避免资源耗尽
   • 实现查询队列机制，对并发请求进行限流，均衡资源分配

4. JVM与堆内存优化

   • 确保Xmx与Xms设置合理，避免堆内存频繁扩容
   • 使用G1垃圾收集器，优化垃圾回收效率，及时释放查询占用的内存

5. 升级GridGain版本

   • 8.8.27可能存在SQL内存管理相关bug，升级到较新的CE版本，获取官方修复与性能优化

内容的提问来源于stack exchange，提问作者Prem