About compilation time

Stefan Manegold Stefan.Manegold at cwi.nl
Wed Dec 9 08:54:09 CET 2015

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


> 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%2fSoftwa
>> reStack&data=01%7c01%7ct-mukork%40microsoft.com%7cf0402800bcf54a1afffb
>> 08d2fff55c05%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=v%2bP8RwhIal
>> 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-mu
>>> k
>>> ork%40microsoft.com%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f1
>>> 4
>>> 1af91ab2d7cd011db47%7c1&sdata=5sBbGDG7c74uNR18oJ1BrInBQQpTXQ7CDIJMTdw
>>> 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%7c72f988bf86
>> f141af91ab2d7cd011db47%7c1&sdata=ofgvw1tY7Xm9t3I8gcMpzz38zr7mqsg6kCJ2y
>> 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-muk
>> ork%40microsoft.com%7cf0402800bcf54a1afffb08d2fff55c05%7c72f988bf86f14
>> 1af91ab2d7cd011db47%7c1&sdata=%2bbGEtXb7Dnrpnd7%2be9dCHk4Vpd74JWrk2bND
>> AWX2C%2f0%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%7Cf0402800bcf54a1afffb0
>| 8d2fff55c05%7C72f988bf86f141af91ab2d7cd011db47%7C1&sdata=f2lWg7g9986vj
>| 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.monetdb.org%2fmailman%2flistinfo%2fdevelopers-list%0a&data=01%7c01%7ct-mukork%40microsoft.com%7cf0402800bcf54a1afffb08d2fff55c05%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=E2k2L4A5m9LWb2zrnS%2bl5wSwPSPOHTjRWkt%2fL57NU2s%3d
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list

| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |

More information about the developers-list mailing list