Search code examples
javasqlperformancecpu-usageignite

High CPU usage when filtering using Apache Ignite and SQL

I am facing a high CPU consumption related issue with Apache Ignite in-memory caching using SQL. Where clause contain 3 columns with "=" match and all three columns are indexed. It is causing all 8 cores on Intel i7 to max out. I am attaching the stack trace related to the thread(s) causing the high CPU usage. Within this stack trace, 64% of the CPU is spent in TableFilter.next() and the rest in Select.isConditionMet() - The number of rows in this table is about 6000 and 1 row needs to be filtered and returned based on the condition. SqlFieldsQuery is constructed for this purpose. This lookup happens thousands of times from the application. Is this a bad usage of Ignite SQL or is there something which is not setup correctly?

Tried the following options:

  • keepBinary() while getting the cache
  • Indexed all columns - 2 out of the three columns being looked up have a high cardinality
  • Tried lazy mode
  • Tried changing from cache.query(cacheQuery).getAll(); to cache.query(cacheQuery).iterator() with a single next()

The above changes did not help much. Please help, am I missing something?

enter image description here

Upon adding a composite index (from H2 console, I faced the following exception)

javax.cache.CacheException: Failed to execute map query on remote node [nodeId=08fe1345-5b85-4a74-bb63-67cccf67b137, errMsg=Failed to parse SQL query: SELECT
__Z0.NORMALIZEDVALUE __C0_0
FROM "DM".DM __Z0
WHERE (__Z0.DN = 'countryOfTaxationMap') AND ((__Z0.VAL = 'KOR') AND ((__Z0.DS = 'trds') AND ((__Z0.RD = 'countryCodes') AND ((__Z0.STATUS = 'A')))))]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.fail(GridReduceQueryExecutor.java:290) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onFail(GridReduceQueryExecutor.java:280) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onMessage(GridReduceQueryExecutor.java:259) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.sendError(GridMapQueryExecutor.java:1198) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest0(GridMapQueryExecutor.java:1051) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:705) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:240) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:170) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor$1.applyx(GridReduceQueryExecutor.java:168) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.util.lang.IgniteInClosure2X.apply(IgniteInClosure2X.java:38) ~[ignite-core-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.send(IgniteH2Indexing.java:3405) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1642) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:876) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$8.iterator(IgniteH2Indexing.java:1809) ~[ignite-indexing-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:95) ~[ignite-core-2.7.6.jar:2.7.6]
        at org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:114) ~[ignite-core-2.7.6.jar:2.7.6]
Solution

  • You have three conditions in your WHERE, but index is only used on one of them. Consider creating composite index (please note that Ignite can only use a single index per table per query).