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/
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/
