You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by zaid <za...@gmail.com> on 2017/03/03 09:51:54 UTC
Very slow cache query compared to H2
Hi,
I am running a query using MySQL DB and Ignite SQL Grid. MySQL takes *56
millisecons* to execute the query whereas SQL grid takes *7220 millisecons*.
I have one client and one server node.
[11:52:48] Topology snapshot [ver=14, servers=1, clients=1, CPUs=8,
heap=11.0GB].
When I ran the same cache query in the embedded H2 console started from
Ignite node, it took *174 millisecons* for the first run and next run took
74 millisecons.
My caches configuration looks like as follows:
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="cache1" />
<property name="cacheMode" value="REPLICATED" />
<property name="indexedTypes">
<list>
<value>java.lang.String</value>
<value>com.poc.sqlgrid.cacheserver.vos.Cache1VO</value>
</list>
</property>
</bean>
My question is why SQL grid is taking too much time as compared to H2 to
execute the same query when I am using replicated mode? Also please note
that I am selecting only first 10 entries using the SQL LIMIT clause so
network overhead would be very minimal.
Do I need to add some configuration parameter to the cache config?
Thanks in advance.
Regards,
Zaid
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by Igor Sapego <is...@gridgain.com>.
Also, I'm pretty sure that 7220 milliseconds is the result you are getting
on the "cold"
JVM. Try warming it up before you are making any benchmarks (i.e. try
running at least
50000 queries before you run actual benchmark).
Best Regards,
Igor
On Fri, Mar 24, 2017 at 11:14 AM, Andrey Mashenkov <am...@gridgain.com>
wrote:
> Hi Zaid,
>
> Have you try to run query on MySQL or H2 in client server mode?
> In your test, there is an additional overhead on network round-trip and
> data serialization\deserialization. Ignite is designed as distributed
> storage, so complex SQL queries should run faster while you adding new
> nodes.
>
> You can try add more nodes and make some of your cached partitioned to get
> a speed-up due to query will be run in multiple threads (on multiple
> nodes). Speed-up will be as much noticeable as more data should be
> looked-up by data engine. Also you can try to rise up SQl query parallelizm
> level that available from ignite-1.9 to run query in multiple threads
> within single node.
>
> Comparing to Mysql, Ignite gives you failover distributed storage with
> full ACID out-of-the-box for free.
> Of course, it has lacks in some scenarios. E.g. for now, it has bigger
> overhead for simple queries than anyone would expected, but most of SQL
> engine lacks can be overcome with
> carefully data distribution planing and using fast key-value engine in
> some cases.
>
> So, if you plan to use some distributed solution (not Ignite only) you
> should test it in environment as much closer to production as possible, not
> just a execution time of one query on single node.
>
> Thanks.
>
>
>
> On Fri, Mar 24, 2017 at 9:06 AM, zaid <za...@gmail.com> wrote:
>
>> Hi,
>> Thanks for your time looking into the issue. I have modified SlowQuery
>> program and now you should get 10 records as query response.
>>
>> SlowQuery.java
>> <http://apache-ignite-users.70518.x6.nabble.com/file/n11405/
>> SlowQuery.java>
>>
>> My concern is that we need to decide whether we should continue using
>> MySQL
>> or switch to Ignite SQL.
>> MySQL takes 56 milliseconds to execute the query whereas SQL grid takes
>> 7220
>> milliseconds in client server mode for my use case.
>> Are there any optimizations I can do to reduce Ignite SQL execution time
>> with respect to configuration?
>>
>> Regards,
>> Zaid
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11405.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: Very slow cache query compared to H2
Posted by Andrey Mashenkov <am...@gridgain.com>.
Hi Zaid,
Have you try to run query on MySQL or H2 in client server mode?
In your test, there is an additional overhead on network round-trip and
data serialization\deserialization. Ignite is designed as distributed
storage, so complex SQL queries should run faster while you adding new
nodes.
You can try add more nodes and make some of your cached partitioned to get
a speed-up due to query will be run in multiple threads (on multiple
nodes). Speed-up will be as much noticeable as more data should be
looked-up by data engine. Also you can try to rise up SQl query parallelizm
level that available from ignite-1.9 to run query in multiple threads
within single node.
Comparing to Mysql, Ignite gives you failover distributed storage with full
ACID out-of-the-box for free.
Of course, it has lacks in some scenarios. E.g. for now, it has bigger
overhead for simple queries than anyone would expected, but most of SQL
engine lacks can be overcome with
carefully data distribution planing and using fast key-value engine in some
cases.
So, if you plan to use some distributed solution (not Ignite only) you
should test it in environment as much closer to production as possible, not
just a execution time of one query on single node.
Thanks.
On Fri, Mar 24, 2017 at 9:06 AM, zaid <za...@gmail.com> wrote:
> Hi,
> Thanks for your time looking into the issue. I have modified SlowQuery
> program and now you should get 10 records as query response.
>
> SlowQuery.java
> <http://apache-ignite-users.70518.x6.nabble.com/file/n11405/SlowQuery.java
> >
>
> My concern is that we need to decide whether we should continue using MySQL
> or switch to Ignite SQL.
> MySQL takes 56 milliseconds to execute the query whereas SQL grid takes
> 7220
> milliseconds in client server mode for my use case.
> Are there any optimizations I can do to reduce Ignite SQL execution time
> with respect to configuration?
>
> Regards,
> Zaid
>
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-
> tp11013p11405.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Very slow cache query compared to H2
Posted by zaid <za...@gmail.com>.
Hi,
Thanks for your time looking into the issue. I have modified SlowQuery
program and now you should get 10 records as query response.
SlowQuery.java
<http://apache-ignite-users.70518.x6.nabble.com/file/n11405/SlowQuery.java>
My concern is that we need to decide whether we should continue using MySQL
or switch to Ignite SQL.
MySQL takes 56 milliseconds to execute the query whereas SQL grid takes 7220
milliseconds in client server mode for my use case.
Are there any optimizations I can do to reduce Ignite SQL execution time
with respect to configuration?
Regards,
Zaid
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11405.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by afedotov <al...@gmail.com>.
Hi Zaid,
Also, note that only the first run takes longer than following runs since a
query has to be parsed.
In my environment, running the query from a client takes about 500 ms at
first run and about 250 ms at following runs.
From a server, it takes 80ms and 10ms correspondingly.
When running the query from client it mostly waits for a response from the
server node.
On Thu, Mar 23, 2017 at 4:33 PM, Andrew Mashenkov [via Apache Ignite Users]
<ml...@n6.nabble.com> wrote:
> Hi Zaid,
>
> It looks like you start query from client node, while cache data resides
> on server node.
> So, there is an overhead on data transfer from server node to client.
>
> I've simplified your test, but query return no records for me.
>
> On Fri, Mar 17, 2017 at 10:19 PM, Denis Magda <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=11392&i=0>> wrote:
>
>> Thanks, Zaid,
>>
>> One of Ignite SQL gurus will take a look at provided example and share
>> suggestions.
>>
>> —
>> Denis
>>
>> > On Mar 17, 2017, at 2:42 AM, zaid <[hidden email]
>> <http:///user/SendEmail.jtp?type=node&node=11392&i=1>> wrote:
>> >
>> > Hi,
>> > Please let me know if I need to provide anything else to reproduce the
>> > issue.
>> > Regards,
>> > Zaid
>> >
>> >
>> >
>> > --
>> > View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11268.html
>> > Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>
> *SlowQuery.java* (9K) Download Attachment
> <http://apache-ignite-users.70518.x6.nabble.com/attachment/11392/0/SlowQuery.java>
> Regards, Andrew.
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
> http://apache-ignite-users.70518.x6.nabble.com/Very-slow-
> cache-query-compared-to-H2-tp11013p11392.html
> To start a new topic under Apache Ignite Users, email
> ml-node+s70518n1h65@n6.nabble.com
> To unsubscribe from Apache Ignite Users, click here
> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=1&code=YWxleGFuZGVyLmZlZG90b2ZmQGdtYWlsLmNvbXwxfC0xMzYxNTU0NTg=>
> .
> NAML
> <http://apache-ignite-users.70518.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
Kind regards,
Alex.
query_second_execution_time_visualvm.png (191K) <http://apache-ignite-users.70518.x6.nabble.com/attachment/11396/0/query_second_execution_time_visualvm.png>
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11396.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Zaid,
It looks like you start query from client node, while cache data resides on
server node.
So, there is an overhead on data transfer from server node to client.
I've simplified your test, but query return no records for me.
On Fri, Mar 17, 2017 at 10:19 PM, Denis Magda <dm...@apache.org> wrote:
> Thanks, Zaid,
>
> One of Ignite SQL gurus will take a look at provided example and share
> suggestions.
>
> —
> Denis
>
> > On Mar 17, 2017, at 2:42 AM, zaid <za...@gmail.com> wrote:
> >
> > Hi,
> > Please let me know if I need to provide anything else to reproduce the
> > issue.
> > Regards,
> > Zaid
> >
> >
> >
> > --
> > View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-
> tp11013p11268.html
> > Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
>
--
Best regards,
Andrey V. Mashenkov
Re: Very slow cache query compared to H2
Posted by Denis Magda <dm...@apache.org>.
Thanks, Zaid,
One of Ignite SQL gurus will take a look at provided example and share suggestions.
—
Denis
> On Mar 17, 2017, at 2:42 AM, zaid <za...@gmail.com> wrote:
>
> Hi,
> Please let me know if I need to provide anything else to reproduce the
> issue.
> Regards,
> Zaid
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11268.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by zaid <za...@gmail.com>.
Hi,
Please let me know if I need to provide anything else to reproduce the
issue.
Regards,
Zaid
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11268.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by zaid <za...@gmail.com>.
sqlgrid-perf-test.zip
<http://apache-ignite-users.70518.x6.nabble.com/file/n11176/sqlgrid-perf-test.zip>
I am attaching sample maven/eclipse project to reproduce performance issue.
I ran query four times using SQL Grid and following are the execution time:
1] 1114 millis
2] 910 millis
3] 1216 millis
4] 628 millis
Average time take = 967 millis
I ran same query four times using H2 console and following are the execution
time:
1] 214 millis
2] 102 millis
3] 151 millis
4] 62 millis
Average time take = ~ 132 millis
Following are the instructions to run query using SQL Grid:
1] Populate cache by running App.java.
2] Comment annotation @EventListener on method fillCacheUpfront in
MyDataStreamer class to avoid filling caches again.
3] Recompile the project and run Test.java which will run query and display
execution time on console.
4] Test.java also has query in readable form in comment because string
concatenated query is not readable. This readable
query can be run in H2 console.
Please let me know if I need to add some configuration parameter which can
speed up SQL Grid performance so that it comes closer to H2.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11176.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by vkulichenko <va...@gmail.com>.
That would be the best way :)
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11036.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by zaid <za...@gmail.com>.
*Could you please share a simple reproducer with us?
*
Can I share a maven project?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013p11035.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Very slow cache query compared to H2
Posted by Sergi Vladykin <se...@gmail.com>.
Looks pretty strange to me. Could you please share a simple reproducer with
us?
Sergi
2017-03-03 12:51 GMT+03:00 zaid <za...@gmail.com>:
> Hi,
>
> I am running a query using MySQL DB and Ignite SQL Grid. MySQL takes *56
> millisecons* to execute the query whereas SQL grid takes *7220
> millisecons*.
> I have one client and one server node.
>
> [11:52:48] Topology snapshot [ver=14, servers=1, clients=1, CPUs=8,
> heap=11.0GB].
>
> When I ran the same cache query in the embedded H2 console started from
> Ignite node, it took *174 millisecons* for the first run and next run took
> 74 millisecons.
>
> My caches configuration looks like as follows:
>
> <bean class="org.apache.ignite.configuration.CacheConfiguration">
> <property name="name" value="cache1" />
> <property name="cacheMode" value="REPLICATED" />
> <property name="indexedTypes">
> <list>
> <value>java.lang.String</value>
> <value>com.poc.sqlgrid.cacheserver.vos.Cache1VO</
> value>
> </list>
> </property>
> </bean>
>
> My question is why SQL grid is taking too much time as compared to H2 to
> execute the same query when I am using replicated mode? Also please note
> that I am selecting only first 10 entries using the SQL LIMIT clause so
> network overhead would be very minimal.
>
> Do I need to add some configuration parameter to the cache config?
>
> Thanks in advance.
>
> Regards,
> Zaid
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Very-slow-cache-query-compared-to-H2-tp11013.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>