Re: default_pipe optimizer slowness during concurrency

On 6 Sep 2017, at 10:13, Sharma, Sreejith wrote:

Hi,

I have a scenario where if I run the a SQL with 10 user concurrency in a recursive mode, the execution time is significantly slower. The SQL which completes in .8sec will end up in completing 5-10hrs during concurrency. This behavior is seen when the optimizer is set to “default_pipe” ,

This is most probably caused by resource contention. When default_pipe is used, MonetDB will run each query with the highest level of parallelism it is able to. So, if one query is heavy enough to consume all available hardware resources, adding more concurrent queries will hurt performance, because they’ll all fight for the resources. What are the hardware resources (e.g. #CPU cores, MEM size, disk size, HDD/SDD?) allocated to a single MonetDB server? What kind of queries did you run? How much data are involved?

however when set to “sequential_pipe” the timing has been drastically reduced and it end ups in seconds.

How is “sequential_pipe” optimizer works here, is it to do anything with concurrency.

With the sequential_pipe, the operators in a query are executed in sequential order, so each query might consume (much) less resources, and hence less resource contention in this case. In many cases, it’s best not run (too heavily) concurrent queries on one MonetDB server, because the server already do parallel processing internally.

NOTE – this happens when there is DISTINCT pattern in SQL.

how many columns are involved? what are their data types? how many records and how many unique records? Best, Jennie

Regards, Sreejith _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list