Apache Ignite best practice

classic Classic list List threaded Threaded
4 messages Options
Borisov Sergey Borisov Sergey
Reply | Threaded
Open this post in threaded view
|

Apache Ignite best practice

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 at Nabble.com.
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Apache Ignite best practice

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 <[hidden email]>:
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 at Nabble.com.

Borisov Sergey Borisov Sergey
Reply | Threaded
Open this post in threaded view
|

Re: Apache Ignite best practice

Hello Ilya!

Thanks for the advice with "JOIN TABLE".

Can you recommend something for the configuration and diagnostics of the
Apache Ignite cluster?
For example, what metrics should I look for when the response time starts to
grow?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Alexey Popov Alexey Popov
Reply | Threaded
Open this post in threaded view
|

Re: Apache Ignite best practice

Hi Sergey,

There could not be an exact answer to your question. It depends mostly on
your use-case.

1. first of all, you should look at CPU/mem/network usage
2. and then you should check SQL debugging guide, starting from EXPLAIN for
your query. Please see [1] for details.
3. you could enable dataRegionMetrics and dataStorageMetrics as it described
at [2] and compare values with small/huge load/data capacity
4. you could also enable Cache metrics [3]

[1] https://apacheignite-sql.readme.io/docs/performance-and-debugging
[2] https://apacheignite.readme.io/docs/memory-metrics
[3] https://apacheignite.readme.io/docs/cache-metrics

Thank you,
Alexey



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/