GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

Memory Quotas for SQL Queries

Memory quotas are a mechanism for preventing GridGain nodes from running out of memory when executing SQL queries that return large result sets. A query loads objects from caches into memory. If there are too many objects or the objects are too large, the JVM can easily run out of memory. With memory quotas, SQL engine will impose a limit on the heap memory available to queries and handle the query that if the limit is reached.

There are two ways in which the SQL Engine can handle the situation when the limit is reached, which depends on whether query offloading is configured:

  • If query offloading is disabled, the SQL Engine will throw an IgniteSQLException letting the user application react accordingly.

  • If query offloading is enabled, the query data will be offloaded to disk and the query will be eventually executed.

Quotas are configured for each node individually. You can have different limits on different nodes depending on the amount of RAM available to the nodes. It is important to remember that queries are distributed to multiple nodes in a map-reduce manner, and that any part of the query can exceed the quota on the node where it’s executed.

When the limit is reached, GridGain will output an error message to the log similar to the one below:

SEVERE: Failed to execute local query.
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:214)
    at org.apache.ignite.internal.processors.query.h2.QueryMemoryManager.reserve(QueryMemoryManager.java:151)
    at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker.reserveFromParent(QueryMemoryTracker.java:154)
    at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker.reserve(QueryMemoryTracker.java:124)
    at org.apache.ignite.internal.processors.query.h2.QueryMemoryTracker$ChildMemoryTracker.reserve(QueryMemoryTracker.java:361)
    at org.apache.ignite.internal.processors.query.h2.H2ManagedLocalResult.hasAvailableMemory(H2ManagedLocalResult.java:115)
    at org.apache.ignite.internal.processors.query.h2.H2ManagedLocalResult.addRow(H2ManagedLocalResult.java:360)
    at org.h2.command.dml.Select.queryFlat(Select.java:752)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:903)
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
    at org.h2.command.dml.Query.query(Query.java:415)
    at org.h2.command.dml.Query.query(Query.java:397)
    at org.h2.command.CommandContainer.query(CommandContainer.java:145)
    at org.h2.command.Command.executeQuery(Command.java:202)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:115)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:817)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:924)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest0(GridMapQueryExecutor.java:421)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:247)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.onMessage(IgniteH2Indexing.java:2259)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:152)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:147)
    at org.apache.ignite.internal.util.lang.IgniteInClosure2X.apply(IgniteInClosure2X.java:37)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.send(IgniteH2Indexing.java:2384)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1151)
    at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:473)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$7.iterator(IgniteH2Indexing.java:1769)
    at org.apache.ignite.internal.processors.cache.QueryCursorImpl.iter(QueryCursorImpl.java:101)
    at org.apache.ignite.internal.processors.cache.query.RegisteredQueryCursor.iter(RegisteredQueryCursor.java:73)
    at org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:120)
    at com.gridgain.test.MemoryQuotasTest.testQueryQuotas(MemoryQuotasTest.java:79)
    at com.gridgain.test.MemoryQuotasTest.main(MemoryQuotasTest.java:45)

Refer to the documentation on using SQL queries for the information on how to catch this exception.

Global Memory Quota

By default, there is a global quota for SQL queries set to 60% of the heap memory available to the node. It means that the total amount of memory required for multiple queries running simultaneously cannot exceed that amount. The query that requests additional memory beyond the configured limit will be handled by the SQL engine according to the offloading property.

You can change the global limit as follows:

<bean class="org.apache.ignite.configuration.IgniteConfiguration">

    <property name="sqlConfiguration">
        <bean class="org.apache.ignite.configuration.SqlConfiguration">

            <property name="sqlGlobalMemoryQuota" value="300M"/>

        </bean>
    </property>

</bean>
IgniteConfiguration cfg = new IgniteConfiguration();

SqlConfiguration sqlCfg = new SqlConfiguration().setSqlGlobalMemoryQuota("300M");

cfg.setSqlConfiguration(sqlCfg);

Ignite ignite = Ignition.start(cfg);

Memory Quota for a Single Query

You can set the limit of memory that will be applied to every individual query. The default value is 0 (no quota). If any query exceeds the limit, the SQL Engine will handle the query according to the offloading property. Memory quotas for queries take effect on the node where they are defined.

The per-quota can be configured as follows:

<bean class="org.apache.ignite.configuration.IgniteConfiguration">

    <property name="sqlConfiguration">
        <bean class="org.apache.ignite.configuration.SqlConfiguration">

            <property name="sqlQueryMemoryQuota" value="30M"/>

        </bean>
    </property>

</bean>
SqlConfiguration sqlCfg = new SqlConfiguration();

sqlCfg.setSqlQueryMemoryQuota("30M");

Query Offloading

When a SQL query reaches its memory quota, the SQL engine can offload the query to disk instead of throwing an exception. It means that the objects that have been already loaded in memory as a result of the query execution will be saved to disk and the occupied memory will be freed. This will affect the query execution performance, but won’t stop the query.

<bean class="org.apache.ignite.configuration.IgniteConfiguration">

    <property name="sqlConfiguration">
        <bean class="org.apache.ignite.configuration.SqlConfiguration">

            <property name="sqlQueryMemoryQuota" value="30M"/>
            <property name="sqlOffloadingEnabled" value="true"/>

        </bean>
    </property>

</bean>
sqlCfg.setSqlOffloadingEnabled(true);

When query offloading is enabled, GridGain will use the $IGNITE_WORK_DIR/tmp/spill directory to temporarily store query data for the queries that exceed the quota. Read more about the $IGNITE_WORK_DIR directory here.

Using JMX to Set Memory Quotas

You can view and change the values of memory quotas at runtime using the following JMX Bean.

Mbean’s Object Name:
group="SQL Query",name=SqlQueryMXBeanImpl
Attribute Type Description Scope

SqlGlobalMemoryQuota

String

The value of the global memory quota.

Node

SqlQueryMemoryQuota

String

The value of the per-query memory quota.

Node

SqlOffloadingEnabled

Boolean

Enable query off-loading.

Node