SQL delete command is slow and can cause OOM

classic Classic list List threaded Threaded
2 messages Options
colinc colinc
Reply | Threaded
Open this post in threaded view
|

SQL delete command is slow and can cause OOM

The Ignite SQL delete command seems to load all entries (both keys and
values) on heap before deleting them from the cache. This is slow and we
have seen it cause JVM heap to go OOM.

The docs state that a select is used to gather the keys of records being
deleted:
https://apacheignite-sql.readme.io/docs/delete

But the below stack trace indicates that the embedded select statement
retrieves both _KEY and _VAL. Is this required? Is there a recommended way
to delete entries without causing high heap usage?

Thanks,
Colin.


Caused by: org.apache.ignite.IgniteException: Failed to execute SQL query.
Out of memory.; SQL statement:
SELECT
_KEY,
_VAL
FROM "PortfolioDataAccessCompositeService:AGGREGATE_CACHE".INDEXEDMODELIMPL
WHERE SESSIONID = ?1 [90108-197]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor$3.iterator(DmlStatementsProcessor.java:645)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:95)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.doDelete(DmlStatementsProcessor.java:783)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.processDmlSelectResult(DmlStatementsProcessor.java:710)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.executeUpdateStatement(DmlStatementsProcessor.java:653)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFields(DmlStatementsProcessor.java:185)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFieldsLocal(DmlStatementsProcessor.java:387)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.doRunPrepared(IgniteH2Indexing.java:2266)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:2209)
~[ignite-indexing-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2135)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2130)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2707)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2144)
~[ignite-core-2.7.0.jar:2.7.0]
        at
org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:685)
~[ignite-core-2.7.0.jar:2.7.0]
        ... 27 more



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Stanislav Lukyanov Stanislav Lukyanov
Reply | Threaded
Open this post in threaded view
|

Re: SQL delete command is slow and can cause OOM

Hi,

You have to also fetch values to do a "compare-and-delete". Before deleting
each entry you check if it has been concurrently modified. If it was then
it's possible that the entry doesn't match your WHERE anymore.
So yes, for now deleting a large number of entries is heap-intensive.

It should improve when
https://issues.apache.org/jira/browse/IGNITE-9182?jql=text%20~%20%22delete%20lazy%22%20and%20project%20%3D%20ignite
is fixed. When it is done, you'll be able to set lazy=true for a DELETE and
avoid the OOM.

For now though I suggest to change the query in a way that it returns less
entries, or use key-value API for deletions.

Stan



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/