You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "kevin.zheng" <zh...@huawei.com> on 2016/04/09 11:04:11 UTC
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?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Ignite-Sql-Query-performance-problem-tp4031.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
re: re: Ignite Sql Query performance problem
Posted by Zhengqingzheng <zh...@huawei.com>.
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:valentin.kulichenko@gmail.com]
发送时间: 2016年4月11日 13:12
收件人: user@ignite.apache.org
主题: 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.
答复: re: Ignite Sql Query performance problem
Posted by Zhengqingzheng <zh...@huawei.com>.
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:valentin.kulichenko@gmail.com]
发送时间: 2016年4月11日 13:12
收件人: user@ignite.apache.org
主题: 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.
Re: re: Ignite Sql Query performance problem
Posted by vkulichenko <va...@gmail.com>.
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.
re: Ignite Sql Query performance problem
Posted by Zhengqingzheng <zh...@huawei.com>.
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:valentin.kulichenko@gmail.com]
发送时间: 2016年4月11日 5:54
收件人: user@ignite.apache.org
主题: 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.
Re: Ignite Sql Query performance problem
Posted by vkulichenko <va...@gmail.com>.
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.