Experiencing performance problems on writing data to Ignite stack. Doing next queries
MERGE INTO ... VALUES ((...)...) ON () WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ... VALUES (...)
with 1-2 values it takes about 100-150ms in average (and growing to 600-700ms) when expected to be under 10ms. Average execution time constantly grows.
Additional details: Connecting as thin jdbc client. Use Calcite as query engine. Ingite stack has 40 data nodes. Stack sync is done with zookeeper. Connecting using r53 address. The table is partitioned with 1 backup. Query execution time are from SYS.SQL_QUERY_HISTORY
The questions here are: why this query takes so much time? and what are recomendations for stack to save dozens of millions of rows (amount of nodes, amount of CPUs, heap size...)?
Tried to turn off persistance - that didn't change exectuion time Tried to use partitionAwareness=true - that didn't change exectuion time
Table creation script
CREATE TABLE IF NOT EXISTS status (
r_id VARCHAR(50) NOT NULL,
a_id VARCHAR(30) NOT NULL,
con_e BIGINT,
con_s VARCHAR(30),
a_e BIGINT,
a_s VARCHAR(30),
a_con_st VARCHAR(30),
PRIMARY KEY (r_id, a_id)
) WITH "template=partitioned,backups=1,affinity_key=r_id";
Example of full MERGE
MERGE INTO status dst USING
(VALUES
(CAST('r1' AS VARCHAR),CAST('a1' AS VARCHAR),1464366133047,'c','n'),
(CAST('r20' AS VARCHAR),CAST('a2' AS VARCHAR),1465481887428,'c','n')
) src(r_id,a_id,con_e,con_s,a_con_st)
ON (dst.r_id = src.r_id AND dst.a_id = src.a_id)
WHEN MATCHED THEN
UPDATE SET
con_s=(CASE WHEN (dst.con_e < src.con_e OR dst.con_e IS NULL) THEN src.con_s ELSE dst.con_s END),
con_e=(CASE WHEN (dst.con_e < src.con_e OR dst.con_e IS NULL) THEN src.con_e ELSE dst.con_e END)
WHEN NOT MATCHED THEN
INSERT
(r_id,a_id,con_e,con_s,a_con_st) VALUES(src.r_id,src.a_id,src.con_e,src.con_s,src.a_con_st);
