Very slow cache query compared to H2

classic Classic list List threaded Threaded
12 messages Options
zaid zaid
Reply | Threaded
Open this post in threaded view
|

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
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

Looks pretty strange to me. Could you please share a simple reproducer with us?

Sergi

2017-03-03 12:51 GMT+03:00 zaid <[hidden email]>:
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.

zaid zaid
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

Could you please share a simple reproducer with us?

Can I share a maven project?
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

That would be the best way :)

-Val
zaid zaid
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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.
zaid zaid
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

Hi,
Please let me know if I need to provide anything else to reproduce the issue.
Regards,
Zaid
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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]> 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.

Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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]> 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]> 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

SlowQuery.java (9K) Download Attachment
Regards,
Andrew.
afedotov afedotov
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

CONTENTS DELETED
The author has deleted this message.
zaid zaid
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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

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

Andrey Mashenkov Andrey Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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 <[hidden email]> 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.

Igor Sapego Igor Sapego
Reply | Threaded
Open this post in threaded view
|

Re: Very slow cache query compared to H2

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 <[hidden email]> 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 <[hidden email]> 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.