cpu usage is %10 while running query on ignite

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

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/
akorensh akorensh
Reply | Threaded
Open this post in threaded view
|

Re: cpu usage is %10 while running query on ignite

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/
cihad cihad
Reply | Threaded
Open this post in threaded view
|

Re: cpu usage is %10 while running query on ignite

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

Re: cpu usage is %10 while running query on ignite

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 <[hidden email]>:
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/