High CPU usage when filtering using Apache Ignite and SQL

Question

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?

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]

Please execute EXPLAIN SELECT {your statement}, paste all columns of output to your question. — alamar
EXPLAIN ANALYZE select normalizedvalue from "DMN".DMN where status = 'A' and RD='currencyCodes' and val='AED'; PLAN SELECT NORMALIZEDVALUE FROM "DMN".DMN /* "DMN".DMN_RD_ASC_IDX: RD = 'currencyCodes' / / scanCount: 8 */ WHERE (VAL = 'AED') AND ((RD = 'currencyCodes') AND ((STATUS = 'A') )) (1 row, 1 ms) — Akshay Gehi
I will try again with a composite index. I also noticed in the logs a warning which I will address: "Indexed columns of a row cannot be fully inlined into index what may lead to slowdown due to additional data page reads, increase index inline size if needed" — Akshay Gehi
Have you tried doing that? apacheignite-sql.readme.io/docs/… — alamar
With the composite index added from the code, this is seems to be working. Based on longer runs, I can confirm in a day or two. — Akshay Gehi

alamar alamar · Accepted Answer · 2020-03-02T07:20:10

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).

High CPU usage when filtering using Apache Ignite and SQL

1 Answers