[
https://issues.apache.org/jira/browse/IGNITE-13270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17162724#comment-17162724
]
Tanmay Ambre commented on IGNITE-13270:
---------------------------------------
CREATE TABLE IF NOT EXISTS A (
A_ID bigint,
affinityKey varchar(100),
COL1 varchar(30),
COL2 bigint,
COL3 varchar(40),
COL4 varchar(40),
COL5 varchar (100),
COL6 varchar (100),
COL7 int,
COL8 int
COL9 timestamp,
COl10 varchar(30),
COL11 bigint,
COL12 varchar(50),
COL13 timestamp,
primary key(A_ID, affinityKey)
) WITH "template=inputCacheTemplate,
CACHE_NAME=ACache,
AFFINITY_KEY=affinityKey,
CACHE_GROUP=INPUT_GROUP
KEY_TYPE=com.foo.AKey,
VALUE_TYPE=com.foo.A";
CREATE TABLE IF NOT EXISTS B (
B_ID bigint,
affinityKey varchar(100),
A_ID bigint,
COL1 varchar(30),
COL2 varchar(100),
COL3 bigint,
COL4 varchar(50),
COL5 varchar(30),
COL6 varchar(20),
COL7 timestamp,
COL8 timestamp,
COL9 timestamp,
COL10 timestamp,
primary key(B_ID, affinityKey)
) WITH "template=inputCacheTemplate,
CACHE_NAME=BCache,
AFFINITY_KEY=affinityKey,
CACHE_GROUP=INPUT_GROUP
KEY_TYPE=com.foo.BKey,
VALUE_TYPE=com.foo.B";
CREATE TABLE IF NOT EXISTS C(
C_ID bigint,
B_ID bigint,
affinityKey varchar(100),
COL1 varchar(20),
COL2 bigint,
COL3 varchar(50),
COL4 varchar(20),
COL5 varchar(20),
COL6 int,
COL7 timestamp,
primary key(C_ID, affinityKey)
) WITH "template=inputCacheTemplate,
CACHE_NAME=CCache,
AFFINITY_KEY=affinityKey,
CACHE_GROUP=INPUT_GROUP
KEY_TYPE=com.foo.CKey,
VALUE_TYPE=com.foo.C";
CREATE INDEX IF NOT EXISTS idx_A_A_ID ON A(A_ID);
CREATE INDEX IF NOT EXISTS idx_B_B_ID ON B(B_ID);
CREATE INDEX IF NOT EXISTS idx_B_A_ID ON B(A_ID);
CREATE INDEX IF NOT EXISTS idx_B_COL2 ON B(COL2) INLINE SIZE 128;
CREATE INDEX IF NOT EXISTS idx_C_C_ID on C(C_ID);
CREATE INDEX IF NOT EXISTS idx_C_B_ID on C(B_ID);
CREATE INDEX IF NOT EXISTS idx_C_COL6 on C(COL6);
inputCacheTemplate is PARTITIONED, backups=1,
atomicityMode=ATOMIC,partitionLossPolicy=READ_WRITE_SAFE,
writeSyncrhonizationMode=PRIMARY_SYNC, statisticsEnabled=true,
affinity=RendezvousAffinityFunction (partitions=256), dataRegionName=dr.input
dr.input is specified as:
persistenceEnabled=false
initialSize=64G
maxSize=200G
metricsEnabled=true
> Massive CPU burn in 2.8.0 and 2.8.1 after upgrading from 2.7.5
> --------------------------------------------------------------
>
> Key: IGNITE-13270
> URL: https://issues.apache.org/jira/browse/IGNITE-13270
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.8, 2.8.1
> Reporter: Tanmay Ambre
> Priority: Major
>
> After upgrading to ignite 2.8.0 from 2.7.5 the following query is causing
> massive CPU burn. We have a 8 node server cluster for Ignite (with 32 CPUs on
> each node). with 2.8.0, 2 nodes are maxed out on CPU. with 2.8.1 all nodes
> are above 80%. On 2.7.5 the same query was performing very nicely.
> The query is a join with 3 tables having same affinity key. however, we dont
> know the affinity key when we are querying and there this results in a merge
> scan.
> Query:
> select A.A_ID, A.AFFINITYKEY, B.B_ID from A, B, C
> where
> A.A_ID = B.A_ID AND
> B.B_ID = C.B_ID AND
> A.AFFINITYKEY = B.AFFINITYKEY AND
> B.AFFINITYKEY = C.AFFINITYKEY AND
> B.B_ID = ? AND
> C.C_ID = ?
>
> Performance:
> 2.7.5 ~ 2 to 5 ms
> 2.8.0 ~ 6 to 12 seconds
> 2.8.1 ~ within 3 seconds
>
> Volume (we have a EDA based system where messages come in burst. One burst
> has approx. 150K events - each event correspond to one query).
>
> throughput of our java based processor (that fires this query):
> 2.7.5 ~ 750 transactions per second
> 2.8.0 ~ 3 transactions per second
> 2.8.1 ~ 6 transactions per second
>
> CPU burn
> 2.7.5 ~ 10 to 15% on each node
> 2.8.0 ~ 100% on 2 nodes other nodes are idling
> 2.8.1 ~ 80 to 90% on each node
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
