You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by 胡永亮/Bob <hu...@neusoft.com> on 2016/10/20 09:10:39 UTC
Some problems in test case which comparing sql query performance between Ignite and Oracle
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.
---------------------------------------------------------------------------------------------------
Re: Re: Some problems in test case which comparing sql query performance between Ignite and Oracle
Posted by 胡永亮/Bob <hu...@neusoft.com>.
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:
<beans xmlns="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">
<bean id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<!-- Partitioned cache example configuration (Atomic mode). -->
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="atomicityMode" value="ATOMIC"/>
<!-- Set cache mode. -->
<property name="cacheMode" value="PARTITIONED"/>
<property name="backups" value="0"/>
<property name="memoryMode" value="OFFHEAP_TIERED" />
</bean>
</list>
</property>
<!-- Explicitly configure TCP discovery SPI to provide list of initial nodes. -->
<property name="discoverySpi">
<bean class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean class="org.apache.ignite.spi.discovery.tcp.ipfinder.multicast.TcpDiscoveryMulticastIpFinder">
<property name="addresses">
<list>
<!-- In distributed environment, replace with actual host IP address. -->
<value>10.9.22.125:47500..47509</value>
<value>10.9.22.116:47500..47509</value>
<value>10.9.22.117:47500..47509</value>
<value>10.9.22.118:47500..47509</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
</bean>
</beans>
Cache config:
CacheConfiguration<K, V> 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
Posted by Yakov Zhdanov <yz...@apache.org>.
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 <vl...@gmail.com>:
> 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 <hu...@neusoft.com> 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
Posted by Vladislav Pyatkov <vl...@gmail.com>.
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 <hu...@neusoft.com> 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
Posted by Alexey Kuznetsov <ak...@apache.org>.
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
Posted by Jörn Franke <jo...@gmail.com>.
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 <hu...@neusoft.com> 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.
> ---------------------------------------------------------------------------------------------------