Ignite Sql Query performance problem

classic Classic list List threaded Threaded
6 messages Options
kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

Ignite Sql Query performance problem

Hi there,
I am a new ignite user. I noticed that the sql query performance is much slower that the general cache.get(x) method. In my case, I run a simple query twice: in the first time, I called a sql query 100 times and it toke 235ms( average 23.5ms per sql); in the second time, I called the query 1000 times and it toke 9162ms (average 9.1ms per sql).
but if I called get method from cache, it toke no time (0ms) to get the result.

I have two questions:
1. is it normal to have 10 ~ 20 ms to get a sql query result?
2. if I want to speed up the sql query, what  should I do?

by the way, I did notice  Mr. Sergi Vladykin's  explaination :
       
Basically Ignite SQL works the following way:
It receives SQL query parses it into AST, then splits this query in two parts:
map and reduce. Map query runs on each participating cache node, reduce
query aggregates results received from all nodes running map query.
In reduce query you will see function __Z0() which is basically
a function that returns results of map queries from all the nodes.



can anyone give me a detailed explanation on how Ignite SQL query works?
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: Ignite Sql Query performance problem

Hi,

Most likely you don't have indexes and the query has to scan the whole cache to find the data you're requesting. Can you show your configuration and the SQL statement that you're executing?

-Val
kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

re: Ignite Sql Query performance problem

Hi Val,
Thank you for your quick response.
I checked my java class annotation, I did not add index options on the definition.
My original java setting looks like this:
    @QuerySqlField
    private String objId;
After adding index flag:
    @QuerySqlField(index = true)
    private String objId;
it does speed up my query demo. Now, one thousand time query only take 397ms, on average less than 1ms per query (my example dataset contains only 17,000 data records).
Thank you for your help.

One more question: what if I have multiple indexes on my table, after setting index=true, what is the cost for the memory usage?


Best regards,
Kevin
-----邮件原件-----
发件人: vkulichenko [mailto:[hidden email]]
发送时间: 2016年4月11日 5:54
收件人: [hidden email]
主题: Re: Ignite Sql Query performance problem

Hi,

Most likely you don't have indexes and the query has to scan the whole cache to find the data you're requesting. Can you show your configuration and the SQL statement that you're executing?

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-Sql-Query-performance-problem-tp4031p4041.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: re: Ignite Sql Query performance problem

Kevin,

Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general indexes do not add more than 20-30% to the overall data size.

BTW, if you can replace query with a simple get(), you should do this. It will be more effective from both performance and memory consumption standpoint. Use SQL for more complicated requests.

-Val
kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

re: re: Ignite Sql Query performance problem

Dear Val,
Thank you for your suggestion.
May I ask you one more question disregards with this one.
The performance issue that I asked was test on my own machine (server ip is 127.0.0.1).
Today, when I test my program on a remote server (let's say 10.xx.xx.xxx), I got an exception on server side:
===========================================================================================
[14:18:38] Ignite node started OK (id=f1ac6685)
[14:18:38] Topology snapshot [ver=1, servers=1, clients=0, CPUs=8, heap=1.0GB]
[14:18:53] Topology snapshot [ver=2, servers=1, clients=1, CPUs=12, heap=1.5GB]
[14:18:57,058][SEVERE][tcp-disco-msg-worker-#2%null%][TcpDiscoverySpi] Failed to unmarshal discovery custom message.
class org.apache.ignite.IgniteCheckedException: Failed to find class with given class loader for unmarshalling (make sure same versions of all classes are available on all nodes or enable peer-class-loading): sun.misc.Launcher$AppClassLoader@784be29
        at org.apache.ignite.marshaller.jdk.JdkMarshaller.unmarshal(JdkMarshaller.java:108)
        at org.apache.ignite.marshaller.AbstractMarshaller.unmarshal(AbstractMarshaller.java:78)
        at org.apache.ignite.spi.discovery.tcp.messages.TcpDiscoveryCustomEventMessage.message(TcpDiscoveryCustomEventMessage.java:78)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.notifyDiscoveryListener(ServerImpl.java:4731)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.processCustomMessage(ServerImpl.java:4592)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.processMessage(ServerImpl.java:2276)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$MessageWorkerAdapter.body(ServerImpl.java:5784)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.body(ServerImpl.java:2161)
        at org.apache.ignite.spi.IgniteSpiThread.run(IgniteSpiThread.java:62)
Caused by: java.lang.ClassNotFoundException: org.apache.ignite.examples.datagrid.store.auto.CacheAutoStoreExample_new$CacheJdbcPojoStoreExampleFactory
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:278)
        at org.apache.ignite.internal.util.IgniteUtils.forName(IgniteUtils.java:8172)
        at org.apache.ignite.marshaller.jdk.JdkMarshallerObjectInputStream.resolveClass(JdkMarshallerObjectInputStream.java:54)
        at java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1612)
        at java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1517)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1771)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.readObject(ObjectInputStream.java:370)
        at java.util.ArrayList.readObject(ArrayList.java:771)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at java.io.ObjectStreamClass.invokeReadObject(ObjectStreamClass.java:1058)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1897)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.readObject(ObjectInputStream.java:370)
        at org.apache.ignite.marshaller.jdk.JdkMarshaller.unmarshal(JdkMarshaller.java:102)
        ... 8 more
[14:18:57,064][SEVERE][tcp-disco-msg-worker-#2%null%][TcpDiscoverySpi] Failed to unmarshal discovery custom message.
class org.apache.ignite.IgniteCheckedException: Failed to find class with given class loader for unmarshalling (make sure same versions of all classes are available on all nodes or enable peer-class-loading): sun.misc.Launcher$AppClassLoader@784be29
        at org.apache.ignite.marshaller.jdk.JdkMarshaller.unmarshal(JdkMarshaller.java:108)
        at org.apache.ignite.marshaller.AbstractMarshaller.unmarshal(AbstractMarshaller.java:78)
        at org.apache.ignite.spi.discovery.tcp.messages.TcpDiscoveryCustomEventMessage.message(TcpDiscoveryCustomEventMessage.java:78)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.processCustomMessage(ServerImpl.java:4608)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.processMessage(ServerImpl.java:2276)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$MessageWorkerAdapter.body(ServerImpl.java:5784)
        at org.apache.ignite.spi.discovery.tcp.ServerImpl$RingMessageWorker.body(ServerImpl.java:2161)
        at org.apache.ignite.spi.IgniteSpiThread.run(IgniteSpiThread.java:62)
Caused by: java.lang.ClassNotFoundException: org.apache.ignite.examples.datagrid.store.auto.CacheAutoStoreExample_new$CacheJdbcPojoStoreExampleFactory
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:278)
        at org.apache.ignite.internal.util.IgniteUtils.forName(IgniteUtils.java:8172)
        at org.apache.ignite.marshaller.jdk.JdkMarshallerObjectInputStream.resolveClass(JdkMarshallerObjectInputStream.java:54)
        at java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1612)
        at java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1517)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1771)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.readObject(ObjectInputStream.java:370)
        at java.util.ArrayList.readObject(ArrayList.java:771)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at java.io.ObjectStreamClass.invokeReadObject(ObjectStreamClass.java:1058)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1897)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.defaultReadFields(ObjectInputStream.java:1997)
        at java.io.ObjectInputStream.readSerialData(ObjectInputStream.java:1921)
        at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1798)
        at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1350)
        at java.io.ObjectInputStream.readObject(ObjectInputStream.java:370)
        at org.apache.ignite.marshaller.jdk.JdkMarshaller.unmarshal(JdkMarshaller.java:102)
===========================================================================================

at first, I searched the community archive and found one issue (<a href="https://issues.apache.org/jira/browse/IGNITE-1190">https://issues.apache.org/jira/browse/IGNITE-1190</a>) is exactly the same as mine. but there is no solution yet, or I did not see the solution.
===========================================================================================
my server side configuration contains information like this:
<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">
    <!--
        Alter configuration below as needed.
    -->
    <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">
    <!--
        Alter configuration below as needed.
    -->
    <bean id="grid.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">

        <property name="peerClassLoadingEnabled" value="true"/>
        <property name="marshaller">
                <bean class="org.apache.ignite.marshaller.optimized.OptimizedMarshaller">
                        <property name="requireSerializable" value="false"/>
                </bean>
        </property>


        </bean>


</beans>
===========================================================================================        

my client side config file contains the following information:
===========================================================================================
 <bean abstract="true" id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
       
                <property name="clientMode" value="true"/>
        <!-- Set to true to enable distributed class loading for examples, default is false. -->
        <property name="peerClassLoadingEnabled" value="true"/>
               
                <property name="marshaller">
                        <bean class="org.apache.ignite.marshaller.optimized.OptimizedMarshaller">
                                <property name="requireSerializable" value="false"/>
                        </bean>
                </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">
                    <!--
                        Ignite provides several options for automatic discovery that can be used
                        instead os static IP based discovery. For information on all options refer
                        to our documentation: http://apacheignite.readme.io/docs/cluster-config
                    -->
                    <!-- Uncomment static IP finder to enable static-based discovery of initial nodes. -->
                    <!--<bean class="org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">-->
                    <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>127.0.0.1:47500..47509</value> -->
                                                            <value>10.101.112.13:47500..47509</value>
                            </list>
                        </property>
                    </bean>
                </property>
            </bean>
        </property>
    </bean>
===========================================================================================
I am wondering, what kinds of configurations I should take to run cache sql remotely?

best regards,
Kevin

-----邮件原件-----
发件人: vkulichenko [mailto:[hidden email]]
发送时间: 2016年4月11日 13:12
收件人: [hidden email]
主题: Re: re: Ignite Sql Query performance problem

Kevin,

Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general indexes do not add more than 20-30% to the overall data size.

BTW, if you can replace query with a simple get(), you should do this. It will be more effective from both performance and memory consumption standpoint. Use SQL for more complicated requests.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-Sql-Query-performance-problem-tp4031p4045.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

答复: re: Ignite Sql Query performance problem

In reply to this post by vkulichenko
Dear Val,
I found another post that you have answered : http://apache-ignite-users.70518.x6.nabble.com/problem-run-jar-file-td1973.html
And solved my problem by adding several extra jar files on server side libs folder.

Best regards,
Kevin

-----邮件原件-----
发件人: vkulichenko [mailto:[hidden email]]
发送时间: 2016年4月11日 13:12
收件人: [hidden email]
主题: Re: re: Ignite Sql Query performance problem

Kevin,

Each index add memory overhead. It's hard to estimate without knowing the details of the application, but in general indexes do not add more than 20-30% to the overall data size.

BTW, if you can replace query with a simple get(), you should do this. It will be more effective from both performance and memory consumption standpoint. Use SQL for more complicated requests.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-Sql-Query-performance-problem-tp4031p4045.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.