java - 使用 Apache Ignite 和 SQL 进行过滤时 CPU 使用率较高

Question

我面临着使用 SQL 的 Apache Ignite 内存缓存的高 CPU 消耗相关问题。 Where 子句包含 3 列，其中“=”匹配，并且所有三列均已建立索引。它导致 Intel i7 上的所有 8 个核心都达到最大负载。我附加了与导致高 CPU 使用率的线程相关的堆栈跟踪。在此堆栈跟踪中，64% 的 CPU 花费在 TableFilter.next() 上，其余的 CPU 花费在 Select.isConditionMet() 上 - 该表中的行数约为 6000，需要根据以下条件过滤并返回 1 行:健康)状况。 SqlFieldsQuery 就是为此目的而构建的。该查找在应用程序中发生了数千次。这是 Ignite SQL 的错误使用还是有什么设置不正确？

尝试了以下选项:

• 获取缓存时keepBinary()
• 对所有列建立索引 - 所查找的三列中有两列具有高基数
• 尝试过懒惰模式
• 尝试更改cache.query(cacheQuery).getAll();使用单个 next() 到 cache.query(cacheQuery).iterator()

上述更改并没有多大帮助。请帮忙，我错过了什么吗？

添加复合索引后(从 H2 控制台，我遇到了以下异常)

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]

Answer 1

您的 WHERE 中有三个条件，但索引仅用于其中之一。考虑创建复合索引(请注意，Ignite 只能在每个查询的每个表中使用单个索引)。