ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From afedotov <alexander.fedot...@gmail.com>
Subject Re: Slow on 1st time query "SQL Join"
Date Wed, 12 Apr 2017 15:11:09 GMT
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] <
ml-node+s70518n11896h10@n6.nabble.com> 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-time-query-SQL-Join-tp11847p11896.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>
>




--
View this message in context: 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 at Nabble.com.
Mime
View raw message