About compilation time

Mustafa Korkmaz

1 Dec 2015 1 Dec '15

7:29 p.m.

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

Attachments:

attachment.html (text/html — 2.4 KB)

Show replies by date

Ying Zhang

2 Dec 2 Dec

3:46 p.m.

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 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@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Mustafa Korkmaz

3 Dec 3 Dec

10:50 p.m.

Thank you Jannie, By using explore I can see the mat version of the query. In software stack page, https://www.monetdb.org/Documentation/Manuals/MonetDB/Architecture/SoftwareS... 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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f14 1af91ab2d7cd011db47%7c1&sdata=5sBbGDG7c74uNR18oJ1BrInBQQpTXQ7CDIJMTdwF ah0%3d

_______________________________________________ developers-list mailing list developers-list@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%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=ofgvw1tY7Xm9t3I8gcMpzz38zr7mqsg6kCJ2ym2mOxk%3d

Stefan Manegold

8 Dec 8 Dec

5:30 p.m.

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@microsoft.com wrote:

...

Thank you Jannie, By using explore I can see the mat version of the query. In software stack page, https://www.monetdb.org/Documentation/Manuals/MonetDB/Architecture/SoftwareS... 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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f14 1af91ab2d7cd011db47%7c1&sdata=5sBbGDG7c74uNR18oJ1BrInBQQpTXQ7CDIJMTdwF ah0%3d

_______________________________________________ developers-list mailing list developers-list@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%7c959abb246ef04a3355c508d2fb2fd7b6%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=ofgvw1tY7Xm9t3I8gcMpzz38zr7mqsg6kCJ2ym2mOxk%3d _______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Mustafa Korkmaz

9 Dec 9 Dec

4:54 a.m.

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. 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. 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? In addition, looking at the results, compile time is order of tens of ms. Is this what I should expect? 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@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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@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@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

Stefan Manegold

7:54 a.m.

----- On Dec 9, 2015, at 5:54 AM, Mustafa Korkmaz t-mukork@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@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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@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@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@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Mustafa Korkmaz

10 Dec 10 Dec

4:56 a.m.

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@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 11:54 PM To: Communication channel for developers of the MonetDB Subject: Re: About compilation time ----- On Dec 9, 2015, at 5:54 AM, Mustafa Korkmaz t-mukork@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.

...

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.

...

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?

...

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@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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@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@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@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@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@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

Eyal Rozenberg

5:09 p.m.

On 09-12-15 08:54, Stefan Manegold wrote:

...

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

Sound reasonable to me.

An additional note from my experience... Mustafa, I would guess you're configuring all of the DBMSes in your comparison to utilize the CPU as much as possible, right? With MonetDB, that would specifically mean setting your DB's nthreads value to match what your hardware supports. So, in my experience the compilation time changes, often significantly, for different values of nthreads. In my old group at Hua Wei research, we actually added timing hooks for measurement, and a few of these are visible here: http://is.gd/Ktd74q bottom-right chart (note all columns are normalized to 100%; the brownish-dark-red blocks at the bottom are MonetDB's query comiplation/optimization, with all columns named 'AXE' being nthread=1 for reasons I won't go into). We observed a ratio of over 2:1 between 32-thread and single-thread query compilation. We also observed some non-negligible variance in timing. Caveat: This was with a version of MonetDB from 2013, so things may have changed.

Mustafa Korkmaz

9 Dec 9 Dec

6:11 a.m.

Hello again Stephan and all, Following the trace idea, I got even stranger timings: +-------------+--------------+-----------------------+-----------------------+ | l_returnfla | l_linestatus | sum_qty | sum_base_price |> : g : : : :> +=============+==============+=======================+=======================+ | A | F | 377518399.00 | 566065727797.25 | | N | F | 9851614.00 | 14767438399.17 | | N | O | 743124873.00 | 1114302286901.88 | | R | F | 377732830.00 | 566431054976.00 | +-------------+--------------+-----------------------+-----------------------+ 4 tuples (49443.396ms) !6 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 +----------+------------------------------------------------------------------+ | ticks | statement | +==========+==================================================================+ | 1 | X_170=0@0:void := querylog.define("trace\n\nselect\n\tl_returnfl | : : ag,\n\tl_linestatus,\n\tsum(l_quantity) as sum_qty,\n\tsum(l_ext : : : endedprice) as sum_base_price,\n\tsum(l_extendedprice * (1 - l_d : : ://other functions : | 49413975 | X_4=0@0:void := user.s1_1(1:bte,1:bte,1:bte,"1998-12-01":date,77 | : : 76000000:lng); : +----------+------------------------------------------------------------------+ 714 tuples (49443.589ms) Apparently, trace timing is smaller than -i option timing, which doesn't make sense. Also tick number of the last MAL function is always the largest and the closest the overall TRACE timing. Is there any relation between these two numbers? If yes, why they are different? -----Original Message----- From: developers-list [mailto:developers-list-bounces+t-mukork=microsoft.com@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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

7:34 a.m.

----- On Dec 9, 2015, at 7:11 AM, Mustafa Korkmaz t-mukork@microsoft.com wrote:

...

Hello again Stephan and all,

Following the trace idea, I got even stranger timings:

+-------------+--------------+-----------------------+-----------------------+ | l_returnfla | l_linestatus | sum_qty | sum_base_price |> : g : : : :> +=============+==============+=======================+=======================+ | A | F | 377518399.00 | 566065727797.25 | | N | F | 9851614.00 | 14767438399.17 | | N | O | 743124873.00 | 1114302286901.88 | | R | F | 377732830.00 | 566431054976.00 | +-------------+--------------+-----------------------+-----------------------+ 4 tuples (49443.396ms) !6 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1

+----------+------------------------------------------------------------------+ | ticks | statement | +==========+==================================================================+ | 1 | X_170=0@0:void := querylog.define("trace\n\nselect\n\tl_returnfl | : : ag,\n\tl_linestatus,\n\tsum(l_quantity) as sum_qty,\n\tsum(l_ext : : : endedprice) as sum_base_price,\n\tsum(l_extendedprice * (1 - l_d : : ://other functions : | 49413975 | X_4=0@0:void := user.s1_1(1:bte,1:bte,1:bte,"1998-12-01":date,77 | : : 76000000:lng); : +----------+------------------------------------------------------------------+ 714 tuples (49443.589ms)

Apparently, trace timing is smaller than -i option timing, which doesn't make sense.

I don't see / understand what you mean. 49443.589ms is IMHO larger than 49443.396ms, and 49443.396ms is IMHO larger than 49413975us, isn't it?

...

Also tick number of the last MAL function is always the largest and the closest the overall TRACE timing.

Indeed. That's because ...

...

Is there any relation between these two numbers? If yes, why they are different?

... the last MAL statement is the created function that contains the query plan; hence, executing that function is executing the query, hence, that's the pure execution cost (incl. potential runtime (re-)optimization); thus, 49443.396ms - 49413975us = 29421us = 29.421ms is (an indication of) the query translation time. Best, Stefan

...

-----Original Message----- From: developers-list [mailto:developers-list-bounces+t-mukork=microsoft.com@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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@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@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@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Mustafa Korkmaz

10 Dec 10 Dec

4:47 a.m.

Thank you Stefan, Your comments helped me a lot. Mustafa Korkmaz -----Original Message----- From: developers-list [mailto:developers-list-bounces+t-mukork=microsoft.com@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 11:34 PM To: Communication channel for developers of the MonetDB Subject: Re: About compilation time ----- On Dec 9, 2015, at 7:11 AM, Mustafa Korkmaz t-mukork@microsoft.com wrote:

...

Hello again Stephan and all,

Following the trace idea, I got even stranger timings:

+-------------+--------------+-----------------------+-----------------------+ | l_returnfla | l_linestatus | sum_qty | sum_base_price |> : g : : : :> +=============+==============+=======================+================ +=======+ | A | F | 377518399.00 | 566065727797.25 | | N | F | 9851614.00 | 14767438399.17 | | N | O | 743124873.00 | 1114302286901.88 | | R | F | 377732830.00 | 566431054976.00 | +-------------+--------------+-----------------------+-----------------------+ 4 tuples (49443.396ms) !6 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1

+----------+------------------------------------------------------------------+ | ticks | statement | +==========+========================================================== +========+ | 1 | X_170=0@0:void := | querylog.define("trace\n\nselect\n\tl_returnfl | : : ag,\n\tl_linestatus,\n\tsum(l_quantity) as sum_qty,\n\tsum(l_ext : : : endedprice) as sum_base_price,\n\tsum(l_extendedprice * (1 - l_d : : ://other functions : | 49413975 | X_4=0@0:void := | user.s1_1(1:bte,1:bte,1:bte,"1998-12-01":date,77 | : : 76000000:lng); : +----------+------------------------------------------------------------------+ 714 tuples (49443.589ms)

Apparently, trace timing is smaller than -i option timing, which doesn't make sense.

I don't see / understand what you mean. 49443.589ms is IMHO larger than 49443.396ms, and 49443.396ms is IMHO larger than 49413975us, isn't it?

...

Also tick number of the last MAL function is always the largest and the closest the overall TRACE timing.

Indeed. That's because ...

...

Is there any relation between these two numbers? If yes, why they are different?

...

-----Original Message----- From: developers-list [mailto:developers-list-bounces+t-mukork=microsoft.com@monetdb.org] On Behalf Of Stefan Manegold Sent: Tuesday, December 8, 2015 9:30 AM To: Communication channel for developers of the MonetDB 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@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@monetdb.org] On Behalf Of Ying Zhang Sent: Wednesday, December 2, 2015 7:47 AM To: Communication channel for developers of the MonetDB suite. 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 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@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@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@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@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@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@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%7c92957abaa0664b92b19508d3006b41b6%7c72f988bf86f14 1af91ab2d7cd011db47%7c1&sdata=zms62hZi9eeYk9ftVgDQ0XTsYeoWuEnTLSeeZpCz 98c%3d

-- | Stefan.Manegold@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%7C92957abaa0664b92b1950 | 8d3006b41b6%7C72f988bf86f141af91ab2d7cd011db47%7C1&sdata=66dhT2rmy4R9R | 8NKhiNEvWqZkGDKSEANZM798%2b54uU0%3d | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ developers-list mailing list developers-list@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%7c92957abaa0664b92b19508d3006b41b6%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=ZUapIBOmhAhMYzVjk%2bXst6abzOc6spvS6NH7MfExrCk%3d

3254

Age (days ago)

3263

Last active (days ago)

List overview

Download

10 comments

4 participants

participants (4)

Eyal Rozenberg
Mustafa Korkmaz
Stefan Manegold
Ying Zhang