ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From woo charles <ignite.charl...@gmail.com>
Subject Re: Slow on 1st time query "SQL Join"
Date Thu, 13 Apr 2017 07:08:32 GMT
"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 <alexander.fedotoff@gmail.com>:

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

Mime
View raw message