About compilation time

Mustafa Korkmaz t-mukork at microsoft.com
Thu Dec 10 05:56:23 CET 2015


Hi Stefan, all,

Thank you for your helpful comments.

Now assuming these phases are not clearly separate, I have 4 numbers to consider:
[run][optimize] from tracelog_history table, -I output, TRACE output. It's always  -i output > TRACE output > [run]

Just for further replies and comments,
I'll go with [run] for pure execution time and -i output - [run] for query compilation/optimization. Clearly [optimize] 
is too small to cover all query compilation/optimization.

Thank you,
Mustafa Korkmaz

-----Original Message-----
From: developers-list [mailto:developers-list-bounces+t-mukork=microsoft.com at monetdb.org] On Behalf Of Stefan Manegold
Sent: Tuesday, December 8, 2015 11:54 PM
To: Communication channel for developers of the MonetDB <developers-list at monetdb.org>
Subject: Re: About compilation time



----- On Dec 9, 2015, at 5:54 AM, Mustafa Korkmaz t-mukork at microsoft.com wrote:

> Hi Stefan, all,
> Thank you for the answer. Your pointers are really helpful.
> However, for long queries the output comes is order of seconds which 
> is not sensitive.
> 
> I also checked query history for timing. There are two pages in 
> monetdb cookbook.
> In the older one(as far as I understand)we can extract parsing and 
> optimization time from queryhistory table. However in the newer 
> version, Only optimization timing is available.

I cannot say anything about this, but cause I don't now the details of the queryhistory, neither how/what/where it measures, nor what the semantics of, e.g., "optimization" are.
I hope someone who does know might be able to answer.
(But please be patient, this is a voluntary best-effort "service".)

> Looking at querylog_history table and -i timing I can get some information.
> 
> For example, for TPC-H Q1, timings are:
> Optimization: 7177 us
> Run: 63064149 us
> Ship: 1 us
> -I timing: 1m 5s
> 
> Looking at these there is a remaining  ~2 secs which is not reported.

Keep in mind that mclient -i timing is round-trip from submitting the query til receiving the first result block. Hence, this includes client-server communication for sending the query, serializing the entire (I assume) result in the server and sending the first block of the result back to the client --- and MAPI is know not to be the fastest / most efficient protocol ---; I'm not sure, whether any of the reported numbers (other than mclient's -i) account for that.

> So is this a good way to extract the parse/compile/.. (time except execution) by
> simply subtracting "run"   from -i timing ?
> Would it be correct, accurate way?

As said, that largely depends on what the semantics of the other timings are, and on the exact definition of parsing/compilation/execution and whether they can be separated, i.e., do not overlap and happen sequentially without gap(s).
In particular if run-time (re-)optimization can happen, things become much less obvious and more complicatied ...

> In addition, looking at the results, compile time is order of tens of 
> ms. Is this what I should expect?

Sound reasonable to me.

Best,
Stefan

> Sorry for bothering again, I hope  this will be helpful for other users.
> 
> Mustafa Korkmaz
> 
> 
> 
> 
> 
> 
> -----Original Message-----
> From: developers-list
> [mailto:developers-list-bounces+t-mukork=microsoft.com at monetdb.org] On 
> Behalf Of Stefan Manegold
> Sent: Tuesday, December 8, 2015 9:30 AM
> To: Communication channel for developers of the MonetDB 
> <developers-list at monetdb.org>
> Subject: Re: About compilation time
> 
> Dear Mustafa, et al.,
> 
> here some related info than you might find helpful:
> 
> The MAL code given with EXPLAIN is the output of the second tier, 
> i.e., the fully (statically) optimized MAL plan, ready for execution.
> 
> 
> Without diving deep into the MonetDB source code, possible ways to 
> indirectly get an indication of the pure query translation cost (excluding execution) are:
> 
> - simple, but not very "accurate":
>  run the query on empty tables; if the plan is small, MAL 
> interpretation should  be small and negligible.
>  Caveat: the plan on empty tables might (will) be different than the 
> plan on full  tables,  and thus also the translation time *might* 
> differ.
> 
> - better (and more accurate?):
>  run the query via mclient with -i option for timing, and prefixed 
> with TRACE for  profiling;  the difference between the total execution 
> time reported by mclient's -i option  and  the total execution time 
> reported in the TRACE profile is a reasonable  indication for the  
> query translation time (basically, the TRACE reports the pure 
> execution time of  the MAL plan,  while mclient's -i reports 
> end-to-end wall-clock.
> 
> A caveat for both options is that MonetDB tries to cache 
> (parameterized) MAL plans to eliminate query translation time with repeatedly executed queries.
> To exclude the potential effects of potential query plan caching, you 
> might want to restart the server before each query execution.
> 
> In any case, any of such indirect measurements are to be taken with a 
> grain of salt ...
> 
> Hope this helps you further.
> 
> Best,
> Stefan
> 
> ----- On Dec 3, 2015, at 11:50 PM, Mustafa Korkmaz t-mukork at microsoft.com wrote:
> 
>> Thank you Jannie,
>> By using explore I can see the mat version of the query.
>> In software stack page,
>> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.
>> m 
>> onetdb.org%2fDocumentation%2fManuals%2fMonetDB%2fArchitecture%2fSoftw
>> a 
>> reStack&data=01%7c01%7ct-mukork%40microsoft.com%7cf0402800bcf54a1afff
>> b 
>> 08d2fff55c05%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=v%2bP8RwhIa
>> l ssVWtz6%2b5tb5AsimZCzLEdNdGkLiwHh1E%3d
>> it is written that
>> Outputs of both the first and the second layer are MAL expressions. 
>> So do you have any idea about which layer does the EXPLORE command 
>> MAL output belongs to?
>> I am especially interested in the process between query submission 
>> and actual execution. So can you suggest me a starting point in the 
>> source code to explore more?
>> Thank you,
>> Mustafa Korkmaz
>> 
>> -----Original Message-----
>> From: developers-list
>> [mailto:developers-list-bounces+t-mukork=microsoft.com at monetdb.org] 
>> On Behalf Of Ying Zhang
>> Sent: Wednesday, December 2, 2015 7:47 AM
>> To: Communication channel for developers of the MonetDB suite.
>> <developers-list at monetdb.org>
>> Subject: Re: About compilation time
>> 
>> Hai Mustafa,
>> 
>> I’m not aware that monetdb directly gives you this information.  what 
>> you could try is to prefix your query with PLAN or EXPLAIN, which 
>> will limit a monetdb server to only generate the logical or physical 
>> execution plan for the query.
>> Do this with client and server on the same machine, send the output 
>> to /dev/null, then you can measure the wall clock time.  This should 
>> give you something usable.
>> 
>> For only the parsing time, I’m afraid you need to dig into the source 
>> code to add timing…
>> 
>> Not sure what parsing/compilation details you’re looking for, but you 
>> can have a look at the output of PLAN and EXPLAIN anyway.
>> 
>> Best,
>> 
>> Jennie
>> 
>>> On Dec 01, 2015, at 20:29 , Mustafa Korkmaz <t-mukork at microsoft.com> wrote:
>>> 
>>> Hello all,
>>> I’m conducting an experiment to show pros/cons of certain DBs and I 
>>> need to investigate query compilation performance in details. For 
>>> this, Is there any way to measure the elapsed time in query 
>>> parsing/compilation time in MonetDB?
>>> Also is there any document out there to show the query 
>>> parsing/compilation details?
>>> What I find for timing is only “-i” flag which gives an overall timing stat.
>>> I’d be glad for any help.
>>> Thank you
>>> Mustafa Korkmaz
>>>  
>>> _______________________________________________
>>> developers-list mailing list
>>> developers-list at monetdb.org
>>> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.
>>> m
>>> onetdb.org%2fmailman%2flistinfo%2fdevelopers-list&data=01%7c01%7ct-m
>>> u
>>> k
>>> ork%40microsoft.com%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f
>>> 1
>>> 4
>>> 1af91ab2d7cd011db47%7c1&sdata=5sBbGDG7c74uNR18oJ1BrInBQQpTXQ7CDIJMTd
>>> w
>>> F
>>> ah0%3d
>> 
>> _______________________________________________
>> developers-list mailing list
>> developers-list at monetdb.org
>> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.
>> m
>> onetdb.org%2fmailman%2flistinfo%2fdevelopers-list%0a&data=01%7c01%7ct
>> -
>> mukork%40microsoft.com%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf8
>> 6 
>> f141af91ab2d7cd011db47%7c1&sdata=ofgvw1tY7Xm9t3I8gcMpzz38zr7mqsg6kCJ2
>> y m2mOxk%3d _______________________________________________
>> developers-list mailing list
>> developers-list at monetdb.org
>> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.
>> m 
>> onetdb.org%2fmailman%2flistinfo%2fdevelopers-list&data=01%7c01%7ct-mu
>> k
>> ork%40microsoft.com%7cf0402800bcf54a1afffb08d2fff55c05%7c72f988bf86f1
>> 4 
>> 1af91ab2d7cd011db47%7c1&sdata=%2bbGEtXb7Dnrpnd7%2be9dCHk4Vpd74JWrk2bN
>> D
>> AWX2C%2f0%3d
> 
> --
>| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
>| https://na01.safelinks.protection.outlook.com/?url=www.CWI.nl%2f~mane
>| g
>| old%2f&data=01%7C01%7Ct-mukork%40microsoft.com%7Cf0402800bcf54a1afffb
>| 0 
>| 8d2fff55c05%7C72f988bf86f141af91ab2d7cd011db47%7C1&sdata=f2lWg7g9986v
>| j TtY9O7o0n6P6zivxTjz86fe%2f3uiQBA%3d  | Science Park 123 (L321) |
>| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.m
> onetdb.org%2fmailman%2flistinfo%2fdevelopers-list%0a&data=01%7c01%7ct-
> mukork%40microsoft.com%7cf0402800bcf54a1afffb08d2fff55c05%7c72f988bf86
> f141af91ab2d7cd011db47%7c1&sdata=E2k2L4A5m9LWb2zrnS%2bl5wSwPSPOHTjRWkt
> %2fL57NU2s%3d _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.m
> onetdb.org%2fmailman%2flistinfo%2fdevelopers-list&data=01%7c01%7ct-muk
> ork%40microsoft.com%7cedb56a5e176d4e105f1908d3006e1132%7c72f988bf86f14
> 1af91ab2d7cd011db47%7c1&sdata=RxGqTQ%2braYSkQXZuhUAgunpeCrkcY%2fet9pJ8
> bCYcyhE%3d

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| https://na01.safelinks.protection.outlook.com/?url=www.CWI.nl%2f~maneg
| old%2f&data=01%7C01%7Ct-mukork%40microsoft.com%7Cedb56a5e176d4e105f190
| 8d3006e1132%7C72f988bf86f141af91ab2d7cd011db47%7C1&sdata=pwyaREZ5HazpT
| xyb%2bEXHDswPGZQa%2bIRH6pVsmVl7yhg%3d  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
_______________________________________________
developers-list mailing list
developers-list at monetdb.org
https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fwww.monetdb.org%2fmailman%2flistinfo%2fdevelopers-list%0a&data=01%7c01%7ct-mukork%40microsoft.com%7cedb56a5e176d4e105f1908d3006e1132%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=LNJD1tCjd8CjbkWaySWrfifjj%2brK%2fAaOyQJWFC4KQU0%3d


More information about the developers-list mailing list