Created a ticket for the issue IGNITE-4957 <https://issues.apache.org/jira/browse/IGNITE-4957>
Kind regards, Alex. On Thu, Apr 13, 2017 at 3:18 PM, Sergi Vladykin [via Apache Ignite Users] < ml-node+s70518n11942...@n6.nabble.com> wrote: > Alex, I think we have to create an issue in Jira. This behavior looks > suboptimal to me. > > Sergi > > 2017-04-13 15:12 GMT+03:00 afedotov <[hidden email] > <http:///user/SendEmail.jtp?type=node&node=11942&i=0>>: > >> Charles, >> >> In your case, you have many caches registered on server nodes (debug >> shows me about 80) which are to be created on >> the client. These caches don't participate in common activities, instead >> they are used to prepare execution plan. >> As for now, a separate request is sent to remote nodes for each >> unregistered cache. >> You can use client mode, but you need to register all the missing caches >> before running queries. >> After loading missing caches explicitly the time reduced to 50-60ms in >> client mode. >> >> I've used a trick to avoid declaring all the caches in configuration file >> or calling getOrCreateCache for each of them. >> To give it a try, just add the line below before the queries execution >> logic in your example. >> IgniteStorage.getInstance().getIgniteCache(Quote.class.getName(), 0 >> ).unwrap(IgniteCacheProxy.class).context().kernalContext(). >> cache().createMissingCaches(); >> >> Kind regards, >> Alex. >> >> On Thu, Apr 13, 2017 at 10:08 AM, woo charles [via Apache Ignite Users] >> <[hidden >> email] <http:///user/SendEmail.jtp?type=node&node=11941&i=0>> wrote: >> >>> "If you select some data from the joined table it leads to all dynamic >>> cache creation requests being sent" <-- is that mean the client node will >>> copy the selected table to local? >>> >>> If I set false to client mode, it will become server node and >>> start participate in caching, compute execution, stream processing, etc., >>> Then it will affect the performance of my client program. How can I >>> prevent this? >>> >>> 2017-04-13 14:24 GMT+08:00 afedotov <[hidden email] >>> <http:///user/SendEmail.jtp?type=node&node=11926&i=0>>: >>> >>>> Hi, >>>> >>>> Sergi, when join query is called from client >>>> It leads to createMissingCaches being called which makes a remote >>>> requests of a dynamic cache creation for each registered but not enabled >>>> cache and since there is a cache for each entity there are many requests to >>>> server nodes. >>>> >>>> Charles, >>>> If you select some data from the joined table it leads to all dynamic >>>> cache creation requests being sent therefore allowing to skip these on the >>>> next query runs. >>>> To disable client mode in your example just pass false to >>>> Ignition.setClientMode(true) >>>> >>>> Kind regards, >>>> Alex >>>> >>>> >>>> >>>> 13 апр. 2017 г. 5:22 AM пользователь "woo charles [via Apache Ignite >>>> Users]" <[hidden email] >>>> <http:///user/SendEmail.jtp?type=node&node=11925&i=0>> написал: >>>> >>>> I found that this time can be reduced to a value below 100ms if I >>>> already selected some data from join query related table. >>>> For example, >>>> if I run 2 query "select * from Quote where stock_id = xxx" & "Select * >>>> from StockInfo where stock_id = xxx" first and then run the join query, >>>> the time for 1st join query will become similar to other(around 10 -20 >>>> ms). >>>> Why will it happen? >>>> >>>> Also, How to run queries from a server node? I had try >>>> "ignite.compute().run()" but it doesn't work. >>>> >>>> >>>> thanks& best regards, >>>> Charles >>>> >>>> 2017-04-13 0:48 GMT+08:00 Sergi Vladykin <[hidden email] >>>> <http:///user/SendEmail.jtp?type=node&node=11922&i=0>>: >>>> >>>>> Alex, >>>>> >>>>> Why do we have such a huge difference between client nodes and server >>>>> nodes? Looks like we should fix it if possible. Even 7 seconds looks too >>>>> much for me. >>>>> >>>>> Sergi >>>>> >>>>> 2017-04-12 18:11 GMT+03:00 afedotov <[hidden email] >>>>> <http:///user/SendEmail.jtp?type=node&node=11922&i=1>>: >>>>> >>>>>> Hi Charles, >>>>>> >>>>>> You are running the query from a client node what implies additional >>>>>> network round trips. >>>>>> Try to run queries from a server node. In my environment it reduced >>>>>> the time from about 7 seconds to 220ms for the first run. >>>>>> >>>>>> Kind regards, >>>>>> Alex. >>>>>> >>>>>> On Wed, Apr 12, 2017 at 9:21 AM, woo charles [via Apache Ignite >>>>>> Users] <[hidden email] >>>>>> <http:///user/SendEmail.jtp?type=node&node=11908&i=0>> wrote: >>>>>> >>>>>>> *Sorry for wrong calculation * >>>>>>> *-> (i.e. 3 Server node stored 236MB [2.2MB * 20 table +3.2MB * 60 >>>>>>> table] data)* >>>>>>> >>>>>>> *Best Regards,* >>>>>>> *Charles* >>>>>>> >>>>>>> 2017-04-12 10:17 GMT+08:00 woo charles <[hidden email] >>>>>>> <http:///user/SendEmail.jtp?type=node&node=11896&i=0>>: >>>>>>> >>>>>>>> *Source >>>>>>>> code: https://drive.google.com/open?id=0B_-zUEFkybdLQVF4U2dwTE11ajA >>>>>>>> <https://drive.google.com/open?id=0B_-zUEFkybdLQVF4U2dwTE11ajA>* >>>>>>>> >>>>>>>> *Below is a screen cap on GridGain's web console & the log of my >>>>>>>> client program. * >>>>>>>> *(i.e. 3 Server node stored 756MB [2.2MB * 10 table +3.2MB * 30 >>>>>>>> table] data)* >>>>>>>> [image: 內置圖片 2] >>>>>>>> >>>>>>>> Apr 12, 2017 9:50:10 AM java.util.logging.LogManager$RootLogger log >>>>>>>> SEVERE: Failed to resolve default logging config file: >>>>>>>> config/java.util.logging.properties >>>>>>>> [09:50:10] __________ ________________ >>>>>>>> [09:50:10] / _/ ___/ |/ / _/_ __/ __/ >>>>>>>> [09:50:10] _/ // (7 7 // / / / / _/ >>>>>>>> [09:50:10] /___/\___/_/|_/___/ /_/ /___/ >>>>>>>> [09:50:10] >>>>>>>> [09:50:10] ver. 1.8.0#20161205-sha1:9ca40dbe >>>>>>>> [09:50:10] 2016 Copyright(C) Apache Software Foundation >>>>>>>> [09:50:10] >>>>>>>> [09:50:10] Ignite documentation: http://ignite.apache.org >>>>>>>> [09:50:10] >>>>>>>> [09:50:10] Quiet mode. >>>>>>>> [09:50:10] ^-- To see **FULL** console log here add >>>>>>>> -DIGNITE_QUIET=false or "-v" to ignite.{sh|bat} >>>>>>>> [09:50:10] >>>>>>>> [09:50:10] OS: Linux 3.10.0-123.el7.x86_64 amd64 >>>>>>>> [09:50:10] VM information: Java(TM) SE Runtime Environment >>>>>>>> 1.8.0_121-b13 Oracle Corporation Java HotSpot(TM) 64-Bit Server VM >>>>>>>> 25.121-b13 >>>>>>>> [09:50:10] Initial heap size is 128MB (should be no less than >>>>>>>> 512MB, use -Xms512m -Xmx512m). >>>>>>>> [09:50:10] Configured plugins: >>>>>>>> [09:50:10] ^-- None >>>>>>>> [09:50:10] >>>>>>>> [09:50:10] Security status [authentication=off, tls/ssl=off] >>>>>>>> [09:50:11] To start Console Management & Monitoring run >>>>>>>> ignitevisorcmd.{sh|bat} >>>>>>>> [09:50:11] >>>>>>>> [09:50:11] Ignite node started OK (id=b663472c) >>>>>>>> [09:50:11] Topology snapshot [ver=19, servers=3, clients=1, CPUs=8, >>>>>>>> heap=3.1GB] >>>>>>>> PreLoadDataSize = 10000 >>>>>>>> NumOfTest = 2 >>>>>>>> /----------------------- Test 0 -----------------------\ >>>>>>>> Name = SQL_Select_2_Join_10 >>>>>>>> ActionType = SQL_SELECT_2_JOIN >>>>>>>> Object class = com.performance.test.dao.Quote >>>>>>>> Thread Size = 80 >>>>>>>> [09:50:20] New version is available at ignite.apache.org: 1.9.0 >>>>>>>> MaxTime(First Time): 12710 >>>>>>>> Thread [SQL_Select_2_Join_10] Average Time: 18.913ms - 0.018913s, >>>>>>>> Max Time: 183ms >>>>>>>> \-----------------------------------------------------/ >>>>>>>> /----------------------- Test 1 -----------------------\ >>>>>>>> Name = QUERY_Quote_price_large_1000_Small_950 >>>>>>>> ActionType = QUERY >>>>>>>> Object class = com.performance.test.dao.Quote >>>>>>>> Thread Size = 80 >>>>>>>> MaxTime(First Time): 30 >>>>>>>> Thread [QUERY_Quote_price_large_1000_Small_950] Average Time: >>>>>>>> 118.752ms - 0.118752s, Max Time: 1094ms >>>>>>>> \-----------------------------------------------------/ >>>>>>>> >>>>>>>> *Also, I found that 1st **SQL J**oin query time reduced about 2 - >>>>>>>> 3s if my program's memory **increase** from 128mb to 1gb.* >>>>>>>> *Is it show that this problem is due to client side not server >>>>>>>> side?* >>>>>>>> >>>>>>>> *Best Regards,* >>>>>>>> *Charles* >>>>>>>> >>>>>>>> >>>>>>>> 2017-04-11 23:29 GMT+08:00 afedotov <[hidden email] >>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11896&i=1>>: >>>>>>>> >>>>>>>>> I'm not able to reproduce the timings you specified. Probably I do >>>>>>>>> something wrong. >>>>>>>>> Could you please provide a log for that case? >>>>>>>>> Also, it would be of great help if you provide the reproducer >>>>>>>>> sources. >>>>>>>>> >>>>>>>>> Kind regards, >>>>>>>>> Alex. >>>>>>>>> >>>>>>>>> On Tue, Apr 11, 2017 at 4:57 AM, woo charles [via Apache Ignite >>>>>>>>> Users] <[hidden email] >>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11889&i=0>> wrote: >>>>>>>>> >>>>>>>>>> My testing program: https://drive.google.com/open? >>>>>>>>>> id=0B_-zUEFkybdLcW5LR3RXZnJfOFE >>>>>>>>>> >>>>>>>>>> It runs on linux system with 8cpu 24gb ram. >>>>>>>>>> 3 Server node(each 1 gb) are created. >>>>>>>>>> 20 set tables created(ie. 1 set table contain table >>>>>>>>>> Quote, StockInfo, BidAskBrokerQueue, PreviousQuote which mean 20*4 = >>>>>>>>>> 80 >>>>>>>>>> table created) >>>>>>>>>> Each table will insert 10000 records. >>>>>>>>>> >>>>>>>>>> After inserted data, sql join "select q.stock_id, q.price , >>>>>>>>>> s.symbol from Quote0.Quote as q, StockInfo0.StockInfo as s where >>>>>>>>>> q.stock_id = s.stock_id and q.stock_id = XXX" is perform. >>>>>>>>>> >>>>>>>>>> And about 13s is needed for the first sql query. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> 2017-04-10 14:53 GMT+08:00 Sergi Vladykin <[hidden email] >>>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11870&i=0>>: >>>>>>>>>> >>>>>>>>>>> I think 13s is still too much. Can you share a reproducer? >>>>>>>>>>> >>>>>>>>>>> Sergi >>>>>>>>>>> >>>>>>>>>>> 2017-04-10 9:51 GMT+03:00 afedotov <[hidden email] >>>>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11870&i=1>>: >>>>>>>>>>> >>>>>>>>>>>> Hello, >>>>>>>>>>>> >>>>>>>>>>>> That is an expected behavior. On the first run a query is >>>>>>>>>>>> parsed and metadata is built for it what takes some additional time >>>>>>>>>>>> compared to the following runs. >>>>>>>>>>>> >>>>>>>>>>>> Kind regards, >>>>>>>>>>>> Alex >>>>>>>>>>>> >>>>>>>>>>>> 10 апр. 2017 г. 8:13 AM пользователь "woo charles [via Apache >>>>>>>>>>>> Ignite Users]" <[hidden email] >>>>>>>>>>>> <http:///user/SendEmail.jtp?type=node&node=11848&i=0>> написал: >>>>>>>>>>>> >>>>>>>>>>>>> Hi, >>>>>>>>>>>>> >>>>>>>>>>>>> I set up 3 Server nodes(8 cpu, 1gb ram) in the same server. >>>>>>>>>>>>> 2 table each 10000 rows are entered. >>>>>>>>>>>>> >>>>>>>>>>>>> When I perform a *SQL Join* query on two table and run it 100 >>>>>>>>>>>>> times, I found that the first query is much slower than >>>>>>>>>>>>> other(i.e. First >>>>>>>>>>>>> query used 13s & other used around 0.02s). >>>>>>>>>>>>> >>>>>>>>>>>>> Why will it happen? >>>>>>>>>>>>> Can I reduce the running time on 1st query? >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> ------------------------------ >>>>>>>>>>>>> If you reply to this email, your message will be added to the >>>>>>>>>>>>> discussion below: >>>>>>>>>>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>>>>>>>>>> ime-query-SQL-Join-tp11847.html >>>>>>>>>>>>> To start a new topic under Apache Ignite Users, email [hidden >>>>>>>>>>>>> email] <http:///user/SendEmail.jtp?type=node&node=11848&i=1> >>>>>>>>>>>>> To unsubscribe from Apache Ignite Users, click here. >>>>>>>>>>>>> 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> >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> ------------------------------ >>>>>>>>>>>> View this message in context: Re: Slow on 1st time query "SQL >>>>>>>>>>>> Join" >>>>>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11848.html> >>>>>>>>>>>> Sent from the Apache Ignite Users mailing list archive >>>>>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/> at >>>>>>>>>>>> Nabble.com. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> ------------------------------ >>>>>>>>>> If you reply to this email, your message will be added to the >>>>>>>>>> discussion below: >>>>>>>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>>>>>>> ime-query-SQL-Join-tp11847p11870.html >>>>>>>>>> To start a new topic under Apache Ignite Users, email [hidden >>>>>>>>>> email] <http:///user/SendEmail.jtp?type=node&node=11889&i=1> >>>>>>>>>> To unsubscribe from Apache Ignite Users, click here. >>>>>>>>>> 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> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> ------------------------------ >>>>>>>>> View this message in context: Re: Slow on 1st time query "SQL >>>>>>>>> Join" >>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11889.html> >>>>>>>>> Sent from the Apache Ignite Users mailing list archive >>>>>>>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> ------------------------------ >>>>>>> If you reply to this email, your message will be added to the >>>>>>> discussion below: >>>>>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>>>>> ime-query-SQL-Join-tp11847p11896.html >>>>>>> To start a new topic under Apache Ignite Users, email [hidden email] >>>>>>> <http:///user/SendEmail.jtp?type=node&node=11908&i=1> >>>>>>> To unsubscribe from Apache Ignite Users, click here. >>>>>>> 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> >>>>>>> >>>>>> >>>>>> >>>>>> ------------------------------ >>>>>> View this message in context: Re: Slow on 1st time query "SQL Join" >>>>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11908.html> >>>>>> Sent from the Apache Ignite Users mailing list archive >>>>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>>>> >>>>> >>>>> >>>> >>>> >>>> ------------------------------ >>>> If you reply to this email, your message will be added to the >>>> discussion below: >>>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>>> ime-query-SQL-Join-tp11847p11922.html >>>> To start a new topic under Apache Ignite Users, email [hidden email] >>>> <http:///user/SendEmail.jtp?type=node&node=11925&i=1> >>>> To unsubscribe from Apache Ignite Users, click here. >>>> 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> >>>> >>>> >>>> >>>> *first_run_from_client.png* (119K) Download Attachment >>>> <http://apache-ignite-users.70518.x6.nabble.com/attachment/11925/0/first_run_from_client.png> >>>> *first_run_from_client.png* (119K) Download Attachment >>>> <http://apache-ignite-users.70518.x6.nabble.com/attachment/11925/1/first_run_from_client.png> >>>> >>>> ------------------------------ >>>> View this message in context: Re: Slow on 1st time query "SQL Join" >>>> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11925.html> >>>> Sent from the Apache Ignite Users mailing list archive >>>> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >>>> >>> >>> >>> >>> ------------------------------ >>> If you reply to this email, your message will be added to the discussion >>> below: >>> http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-t >>> ime-query-SQL-Join-tp11847p11926.html >>> To start a new topic under Apache Ignite Users, email [hidden email] >>> <http:///user/SendEmail.jtp?type=node&node=11941&i=1> >>> To unsubscribe from Apache Ignite Users, click here. >>> 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> >>> >> >> >> ------------------------------ >> View this message in context: Re: Slow on 1st time query "SQL Join" >> <http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11941.html> >> Sent from the Apache Ignite Users mailing list archive >> <http://apache-ignite-users.70518.x6.nabble.com/> at Nabble.com. >> > > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > http://apache-ignite-users.70518.x6.nabble.com/Slow-on- > 1st-time-query-SQL-Join-tp11847p11942.html > To start a new topic under Apache Ignite Users, email > ml-node+s70518n1...@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> > -- View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Slow-on-1st-time-query-SQL-Join-tp11847p11947.html Sent from the Apache Ignite Users mailing list archive at Nabble.com.
