You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by cihad <ci...@intellica.net> on 2021/01/28 19:05:30 UTC
cpu usage is %10 while running query on ignite
hello all
My application running sql queries on db and then put the result on ignite
cache. Then I query these data from ignite cache. But the query that I run
on ignite cache taking too much time depending on my data. when I monitor my
system while running queries on ignite cache I realize that cpu usage is %10
so I search and found setQueryParallelism property while creating cache
when I change that actually noting change so is there a way to fasten my
queries and use more of my cpu.
By the way I create index most of the people suggesting creating index to
fasten queries
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: cpu usage is %10 while running query on ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
This looks like a huge query which should be optimized in parts. Please try
to simplify it until it is no longer slow, then you will find the step
which is not optimal.
Regards,
--
Ilya Kasnacheev
пт, 29 янв. 2021 г. в 08:10, cihad <ci...@intellica.net>:
> thank you for quick reply
>
> here is some part of my query it is longer then this but rest also same
> with
> small change
>
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'user_id' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.user_id) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
> null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'user_id' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.user_id) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.user_id),to_char(a.user_id),'nvl2')) <>
> UPPER(nvl2(to_char(b.user_id),to_char(b.user_id),'nvl2'))
> UNION
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'username' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.username) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
> null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'username' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.username) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.username),to_char(a.username),'nvl2')) <>
> UPPER(nvl2(to_char(b.username),to_char(b.username),'nvl2'))
> UNION
> SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
> FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
> null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'password' AS FIRST_COLUMN,'null'
> AS FIRST_CONDITION, to_char(a.password) AS FIRST_VALUE,
> '1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
> nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1,
> null AS
> SECOND_KEY_2,null AS SECOND_KEY_3, 'password' AS SECOND_COLUMN, 'null' AS
> SECOND_CONDITION, to_char(b.password) AS SECOND_VALUE,'1000000' AS
> SECOND_TABLE_COUNT
> FROM
> "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
> a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672
> b
> WHERE a.id = b.id AND
> UPPER(nvl2(to_char(a.password),to_char(a.password),'nvl2')) <>
> UPPER(nvl2(to_char(b.password),to_char(b.password),'nvl2'))
>
>
> -From your response I understood that query paralelizm not solving my
> problem because Idont use methods like sum,
>
> I already use distributed joins and lazy true. but I might try lazy false
> and see how much memory it is consuming because my data is big
>
> I should search explain statement, I didnt know that feature.
>
> Thank you soo much. If you have any other suggestion pls let me know
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: cpu usage is %10 while running query on ignite
Posted by cihad <ci...@intellica.net>.
thank you for quick reply
here is some part of my query it is longer then this but rest also same with
small change
SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'user_id' AS FIRST_COLUMN,'null'
AS FIRST_CONDITION, to_char(a.user_id) AS FIRST_VALUE,
'1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1, null AS
SECOND_KEY_2,null AS SECOND_KEY_3, 'user_id' AS SECOND_COLUMN, 'null' AS
SECOND_CONDITION, to_char(b.user_id) AS SECOND_VALUE,'1000000' AS
SECOND_TABLE_COUNT
FROM "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672 b
WHERE a.id = b.id AND
UPPER(nvl2(to_char(a.user_id),to_char(a.user_id),'nvl2')) <>
UPPER(nvl2(to_char(b.user_id),to_char(b.user_id),'nvl2'))
UNION
SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'username' AS FIRST_COLUMN,'null'
AS FIRST_CONDITION, to_char(a.username) AS FIRST_VALUE,
'1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1, null AS
SECOND_KEY_2,null AS SECOND_KEY_3, 'username' AS SECOND_COLUMN, 'null' AS
SECOND_CONDITION, to_char(b.username) AS SECOND_VALUE,'1000000' AS
SECOND_TABLE_COUNT
FROM "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672 b
WHERE a.id = b.id AND
UPPER(nvl2(to_char(a.username),to_char(a.username),'nvl2')) <>
UPPER(nvl2(to_char(b.username),to_char(b.username),'nvl2'))
UNION
SELECT 'FreeSql' AS FIRST_TABLE_NAME, nvl(to_char(a.id),'') AS
FIRST_TABLE_KEYS, 'id' AS FIRST_KEY_1,
null AS FIRST_KEY_2, null AS FIRST_KEY_3, 'password' AS FIRST_COLUMN,'null'
AS FIRST_CONDITION, to_char(a.password) AS FIRST_VALUE,
'1000000' AS FIRST_TABLE_COUNT, 'FreeSql' AS SECOND_TABLE_NAME,
nvl(to_char(b.id),'') AS SECOND_TABLE_KEYS, 'id' AS SECOND_KEY_1, null AS
SECOND_KEY_2,null AS SECOND_KEY_3, 'password' AS SECOND_COLUMN, 'null' AS
SECOND_CONDITION, to_char(b.password) AS SECOND_VALUE,'1000000' AS
SECOND_TABLE_COUNT
FROM "472372156301753_PEFDE_472372156301754_15070131349876".T_15070131349876
a, "472372156301753_PEFDE_472372156301754_15115146921672".T_15115146921672 b
WHERE a.id = b.id AND
UPPER(nvl2(to_char(a.password),to_char(a.password),'nvl2')) <>
UPPER(nvl2(to_char(b.password),to_char(b.password),'nvl2'))
-From your response I understood that query paralelizm not solving my
problem because Idont use methods like sum,
I already use distributed joins and lazy true. but I might try lazy false
and see how much memory it is consuming because my data is big
I should search explain statement, I didnt know that feature.
Thank you soo much. If you have any other suggestion pls let me know
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: cpu usage is %10 while running query on ignite
Posted by akorensh <al...@gmail.com>.
Hi,
QueryParallelism only help in certain specific instances(queries with
table scans and aggregations). Described more here:
https://ignite.apache.org/docs/latest/SQL/sql-tuning#query-parallelism
The best way to speed up your query is to add appropriate indexes, and if
you are using a cluster
then collocate the data as appropriate.
https://ignite.apache.org/docs/latest/data-modeling/affinity-collocation
https://ignite.apache.org/docs/latest/SQL/indexes
Use the EXPLAIN statement to see e execution plan of a query
https://ignite.apache.org/docs/latest/SQL/sql-tuning#using-the-explain-statement
If you are preforming a distributed join(across multiple partitioned
tables) follow this guide:
https://ignite.apache.org/docs/latest/SQL/distributed-joins
CPU utilization does not correlate one-to-one with query performance as
other latencies, like disk, memory, etc.. come into play.
You might also take a look at the LAZY flag to avoid waiting for all
results to buffer before showing the output:
https://ignite.apache.org/docs/latest/SQL/sql-tuning#lazy-loading
See guide to SQL optimization here:
https://ignite.apache.org/docs/latest/perf-and-troubleshooting/sql-tuning
Thanks, Alex
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/