Hello Sergey!
You are using "NOT IN" in your query. This may cause performance drawback.
Using JOIN TABLE() is recommended, as per
https://apacheignite.readme.io/docs/sql-performance-and-debugging#section-sql-performance-and-usability-considerations
Not sure about DELETE. I guess it might be OK with DELETE.
Regards,
--
Ilya Kasnacheev
2018-01-09 14:01 GMT+03:00 Borisov Sergey <borisov...@gmail.com>:
> Hi,
> Sorry for bad english.
> Need council on configuring Ignite, which is used as a SQL Grid.
> The task is rather simple - to store in realtime information about
> connections to the services and to be able to quickly search for it.
> Tell me please in what direction to diagnose and what are the variants for
> optimizing performance?
> The workload in the production mode is expected to be about ~ 100-150k RPS
> and ~ 1 million rows in the cache.
>
> *Test Infrastructure:*
> 3 Ignite nodes (version 2.3) in kubernetes on 3 servers (4 CPUs, 16 GB RAM)
> *JVM_OPTS* = -Xms8g -Xmx8g -server -XX:+AlwaysPreTouch -XX:+UseG1GC
> -XX:+DisableExplicitGC -XX:MaxDirectMemorySize=1024M
> -XX:+ScavengeBeforeFullGC
> *IGNITE_ATOMIC_CACHE_DELETE_HISTORY_SIZE* = 1
> *IGNITE_QUIET* = false
>
> *Cache structure:*
> CREATE TABLE IF NOT EXISTS TEST
> (
> id varchar (8),
> g_id varchar (17),
> update_at bigint,
> tag varchar (8),
> ver varchar (4),
> size smallint,
> score real,
> PRIMARY KEY (id)
> ) WITH "TEMPLATE = PARTITIONED, CACHE_NAME = TEST,
> WRITE_SYNCHRONIZATION_MODE = FULL_ASYNC, BACKUPS = 0, ATOMICITY = ATOMIC";
> CREATE INDEX IF NOT EXISTS idx_g_id_v ON TEST (ver ASC, g_id ASC);
> CREATE INDEX IF NOT EXISTS idx_size ON TEST (size ASC);
> CREATE INDEX IF NOT EXISTS idx_update_at ON TEST (update_at DESC);
> CREATE INDEX IF NOT EXISTS idx_tag ON TEST (tag ASC);
>
> *Queries executed while the application is running:*
> 1) Updating rows data (60% workload)
> MERGE INTO TEST (id, g_id, update_at, tag, ver, size, score) VALUES (....) 2)
> Removing (3% workload) DELETE FROM TEST WHERE id =? 3) Once a minute,
> remove not actual rows (TTL) DELETE FROM TEST WHERE update_at <=? 4)
> Getting requested rows (37% workload) ( SELECT a.k FROM (
> SELECT id AS k, t.score AS s FROM TEST t WHERE t.update_at>
> = $ {u} AND t.ver = ${v} AND t.g_id = '${g}' AND t.size> =
> ${cc1} AND t.size <= ${cc2} AND t.tag = `${t}` AND
> id NOT IN ('', '', '', '', ...., '') ORDER BY RAND () LIMIT
> 64 ) a ORDER BY POWER ($ {pp} -a.s, 2) ASC LIMIT 16 ) UNION ALL
> ( SELECT b.k FROM ( SELECT id AS k, t.score AS s FROM TEST
> t WHERE t.update_at> = $ {u} AND t.ver = ${v} AND
> t.g_id = '${g}' AND t.size> = ${cc1} AND t.size <= ${cc2} AND
> (t.tag <> `${t}` OR t.tag IS NULL) AND id NOT IN ('', '', '',
> '', ...., '') ORDER BY RAND () LIMIT 64 ) b ORDER
> BY POWER (${pp} -a.s, 2) ASC LIMIT 16 ) LIMIT 16 *The first iteration
> was through the REST API*: https://apacheignite.readme.
> io/docs#section-sql-fields-query-execute <= 20k requests per minute -
> response time: merge 4ms, select 30ms > 20k: merge & select 300ms -
> *90000ms*, then complete degradation and fall *The second iteration was
> through jdbc and batch*: 1) every 3 seconds from 500 to 1000 rows: MERGE
> INTO T VALUES (...), (...), ... (...); 2) every 3 seconds from 0 to 150
> rows: DELETE FROM T WHERE ID in ('', '', ... ''); The performance increase
> was approximately 2.5 - 3 times, which is very small.
> ------------------------------
> Sent from the Apache Ignite Users mailing list archive
> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com.
>
