GridGain Developers Hub

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, the SQL engine imposes a limit on the heap memory available to queries and handle the query if the limit is reached.

Memory quota size can be configured in:

  • Kilobytes - append 'K' or 'k', for example: 10k, 400K;

  • Megabytes - append 'M' or 'm', for example: 60m, 420M;

  • Gigabytes - append 'G' or 'g', for example: 7g, 2G;

  • Percent of heap - append the '%' sign, for example: 45%, 80%.

The SQL engine can handle the situation when the limit is reached in two ways, depending on if query offloading is enabled:

  • If query offloading is disabled, the SQL engine throws an IgniteSQLException letting the user application react accordingly.

  • If query offloading is enabled, the query data is offloaded to disk, and the query is 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 any part of the query can exceed the quota on the node where it’s executed.

When the limit is reached, GridGain prints 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 more information on how to catch this exception.

Global Memory Quota

By default, a global quota for SQL queries is 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 is handled by the SQL engine according to the offloading property.

You can change the global limit as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="         http://www.springframework.org/schema/beans         http://www.springframework.org/schema/beans/spring-beans.xsd         http://www.springframework.org/schema/util         http://www.springframework.org/schema/util/spring-util.xsd">
    <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>
</beans>
IgniteConfiguration cfg = new IgniteConfiguration();

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

cfg.setSqlConfiguration(sqlCfg);

Ignite ignite = Ignition.start(cfg);
This API is not presently available for C#/.NET. You can use XML configuration.
This API is not presently available for C++. You can use XML configuration.

Memory Quota for a Single Query

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

You can configure the per-query quota as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="         http://www.springframework.org/schema/beans         http://www.springframework.org/schema/beans/spring-beans.xsd         http://www.springframework.org/schema/util         http://www.springframework.org/schema/util/spring-util.xsd">
    <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>
</beans>
SqlConfiguration sqlCfg = new SqlConfiguration();

sqlCfg.setSqlQueryMemoryQuota("30M");
This API is not presently available for C#/.NET. You can use XML configuration.
This API is not presently available for C++. You can use XML configuration.

Query Offloading

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

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:util="http://www.springframework.org/schema/util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="         http://www.springframework.org/schema/beans         http://www.springframework.org/schema/beans/spring-beans.xsd         http://www.springframework.org/schema/util         http://www.springframework.org/schema/util/spring-util.xsd">
    <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>
</beans>
sqlCfg.setSqlOffloadingEnabled(true);
This API is not presently available for C#/.NET. You can use XML configuration.
This API is not presently available for C++. You can use XML configuration.

When query offloading is enabled, GridGain uses 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

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Dec 04, 2024