Re: Re: Some problems in test case which comparing sql query performance between Ignite and Oracle

[胡永亮/Bob]

Hi Warm-hearted person，

Thanks for your help.

According to your advice, I will query some field not using select *.  I 
will add the index in the cache. 

To Vladislav Pyatkov,

In previous mail, you said "you are always can copy data to another cache 
(with index) and drop this", 
for this, which way should be used? Broadcast message and local node 
handles local data copying, right?

To Alexey Kuznetsov,

I have not created the index in Ignite cache, due to I want to create the 
index in active cache, now I know this can't.
Cluster config:
http://www.springframework.org/schema/beans;
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd;>




















  



10.9.22.125:47500..47509
10.9.22.116:47500..47509
10.9.22.117:47500..47509
10.9.22.118:47500..47509









Cache config:
CacheConfiguration ccfg = new CacheConfiguration<>(cacheName);

ccfg.setCacheStoreFactory(storeFactory);
ccfg.setWriteThrough(true);
cfg.setBackups(0);
cfg.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
cfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
cfg.setCacheMode(CacheMode.PARTITIONED);

To Yakov, 

I have not a real scenario, only test Ignite from simple sql query. 
I also find the scenario that Ignite is best for in sql query. 





Bob



---
Confidentiality Notice: The information contained in this e-mail and any 
accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential 
and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of 
this communication is
not the intended recipient, unauthorized use, forwarding, printing,  storing, 
disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this 
communication in error,please
immediately notify the sender by return e-mail, and delete the original message 
and all copies from
your system. Thank you.
---

Re: Some problems in test case which comparing sql query performance between Ignite and Oracle

[Yakov Zhdanov]

Bob, can you please also tell what is your benchmark scenario? Do you have
enough warm-up cycles to bring your app to some steady state?

--Yakov

2016-10-20 12:40 GMT+03:00 Vladislav Pyatkov :

> Hi Bob,
>
> One way to do SQL faster this is adding indexes.
> 1) I do not think what the estimation will be a lot improve without index.
> Because of the need to serialize, deserialize and move in network data.
>
> 2) Ignite does not create index on existing data, but you are always can
> copy data to another cache (with index) and drop this. Community going to
> implement adding indexes on alive cache, but for now it is not possible.
>
> On Thu, Oct 20, 2016 at 12:10 PM, 胡永亮/Bob  wrote:
>
>> Hi, everyone
>>
>> My test environment: Ignite cluster has 8 nodes, every node has 8
>> cores CPU and 30G memory. Their network has 1000M speed.
>> Oracle is deployed in the machine which has 32G memory and  8 cores
>> CPU.
>>
>> My db table has 47535542 rows with 99 columns.
>>
>> When no index, the cost time of sql: select * from Kc21 where
>> akc273='王妍'
>> Oracle: 152s
>>  Ignite:   61s
>>
>> After creating index in the field akc273:
>> Oracle: 3s
>>
>> Problem 1：I think 61s is too long for this sql in Ignite, how can I
>> increase the performance?
>> Problem 2 :  How to create index in exsiting cache? Now I only find
>> some annotations and configuration to create index before loading data.
>>
>> Thanks.
>>
>> --
>> Bob
>>
>> 
>> ---
>> Confidentiality Notice: The information contained in this e-mail and any
>> accompanying attachment(s)
>> is intended only for the use of the intended recipient and may be
>> confidential and/or privileged of
>> Neusoft Corporation, its subsidiaries and/or its affiliates. If any
>> reader of this communication is
>> not the intended recipient, unauthorized use, forwarding, printing,
>> storing, disclosure or copying
>> is strictly prohibited, and may be unlawful.If you have received this
>> communication in error,please
>> immediately notify the sender by return e-mail, and delete the original
>> message and all copies from
>> your system. Thank you.
>> 
>> ---
>>
>
>
>
> --
> Vladislav Pyatkov
>

Re: Some problems in test case which comparing sql query performance between Ignite and Oracle

[Vladislav Pyatkov]

Hi Bob,

One way to do SQL faster this is adding indexes.
1) I do not think what the estimation will be a lot improve without index.
Because of the need to serialize, deserialize and move in network data.

2) Ignite does not create index on existing data, but you are always can
copy data to another cache (with index) and drop this. Community going to
implement adding indexes on alive cache, but for now it is not possible.

On Thu, Oct 20, 2016 at 12:10 PM, 胡永亮/Bob  wrote:

> Hi, everyone
>
> My test environment: Ignite cluster has 8 nodes, every node has 8
> cores CPU and 30G memory. Their network has 1000M speed.
> Oracle is deployed in the machine which has 32G memory and  8 cores
> CPU.
>
> My db table has 47535542 rows with 99 columns.
>
> When no index, the cost time of sql: select * from Kc21 where akc273='
> 王妍'
> Oracle: 152s
>  Ignite:   61s
>
> After creating index in the field akc273:
> Oracle: 3s
>
> Problem 1：I think 61s is too long for this sql in Ignite, how can I
> increase the performance?
> Problem 2 :  How to create index in exsiting cache? Now I only find
> some annotations and configuration to create index before loading data.
>
> Thanks.
>
> --
> Bob
>
> 
> ---
> Confidentiality Notice: The information contained in this e-mail and any
> accompanying attachment(s)
> is intended only for the use of the intended recipient and may be
> confidential and/or privileged of
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
> of this communication is
> not the intended recipient, unauthorized use, forwarding, printing,
> storing, disclosure or copying
> is strictly prohibited, and may be unlawful.If you have received this
> communication in error,please
> immediately notify the sender by return e-mail, and delete the original
> message and all copies from
> your system. Thank you.
> 
> ---
>



-- 
Vladislav Pyatkov

Re: Some problems in test case which comparing sql query performance between Ignite and Oracle

[Alexey Kuznetsov]

Bob,

1) Did you create index on Ignite cache?
2) Could you attach you cluster and cache configs?

I also agree with Jörn Franke, that such simple query (no joins, simple
"where condition") is not very suitable for benchmarking (unless all in
your app you are using such queries a lot).


-- 
Alexey Kuznetsov

Re: Some problems in test case which comparing sql query performance between Ignite and Oracle

[Jörn Franke]

You have to understand for what the database cache is good: lookups of 
single/few rows. This is due to the data structure of a cache. In this sense 
you use the cache wrongly. Aside of this I think select * is really the worst 
way to do professional performance evaluation of your architecture.

> On 20 Oct 2016, at 11:10, 胡永亮/Bob  wrote:
> 
> Hi, everyone
> 
> My test environment: Ignite cluster has 8 nodes, every node has 8 cores 
> CPU and 30G memory. Their network has 1000M speed.
> Oracle is deployed in the machine which has 32G memory and  8 cores CPU.
> 
> My db table has 47535542 rows with 99 columns.
> 
> When no index, the cost time of sql: select * from Kc21 where akc273='王妍'
> Oracle: 152s
>  Ignite:   61s
> 
> After creating index in the field akc273:
> Oracle: 3s
> 
> Problem 1：I think 61s is too long for this sql in Ignite, how can I 
> increase the performance?
> Problem 2 :  How to create index in exsiting cache? Now I only find some 
> annotations and configuration to create index before loading data.
> 
> Thanks.
> 
> Bob
> 
> ---
> Confidentiality Notice: The information contained in this e-mail and any 
> accompanying attachment(s) 
> is intended only for the use of the intended recipient and may be 
> confidential and/or privileged of 
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of 
> this communication is 
> not the intended recipient, unauthorized use, forwarding, printing,  storing, 
> disclosure or copying 
> is strictly prohibited, and may be unlawful.If you have received this 
> communication in error,please 
> immediately notify the sender by return e-mail, and delete the original 
> message and all copies from 
> your system. Thank you. 
> ---

Some problems in test case which comparing sql query performance between Ignite and Oracle

[胡永亮/Bob]

Hi, everyone

My test environment: Ignite cluster has 8 nodes, every node has 8 cores CPU 
and 30G memory. Their network has 1000M speed.
Oracle is deployed in the machine which has 32G memory and  8 cores CPU.

My db table has 47535542 rows with 99 columns.

When no index, the cost time of sql: select * from Kc21 where akc273='王妍'
Oracle: 152s
 Ignite:   61s

After creating index in the field akc273:
Oracle: 3s

Problem 1：I think 61s is too long for this sql in Ignite, how can I 
increase the performance?
Problem 2 :  How to create index in exsiting cache? Now I only find some 
annotations and configuration to create index before loading data.

Thanks.



Bob


---
Confidentiality Notice: The information contained in this e-mail and any 
accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential 
and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of 
this communication is
not the intended recipient, unauthorized use, forwarding, printing,  storing, 
disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this 
communication in error,please
immediately notify the sender by return e-mail, and delete the original message 
and all copies from
your system. Thank you.
---