You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Borisov Sergey <bo...@gmail.com> on 2018/01/09 11:01:21 UTC
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: http://apache-ignite-users.70518.x6.nabble.com/
Re: Apache Ignite best practice
Posted by Alexey Popov <ta...@gmail.com>.
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/
Re: Apache Ignite best practice
Posted by Borisov Sergey <bo...@gmail.com>.
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/
Re: Apache Ignite best practice
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <bo...@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.
>