Your question is somewhat confusing but here I share some of our experience related to the same topic.

Our experiments with MonetFB shows that the default mitosis optimization MonetDB provides (splitting the longest column in the execution plan to initiate plan graph mitosis) works quite well on multicore systems for typical queries.

We did some further work on adding an optimization module where the table(s) selected for "mitosis"-splitting (and number of splits -- always being kept to be less, in total, than the number of available cores) can vary according to some "optimization strategy" related to our estimate of total processing cost. In brief form, we published this work and some simplifications and findings last year.

Regards, Masood Mortazavi

On Sunday, January 4, 2015, Vijay Krishna vijayakrishna55@gmail.com wrote:

Hi,

I have been working on join performances with MonetDB. I tried using various optimizer pipelines.

Few costly queries which took 15 seconds with the default pipeline, returned results as fast as 5 seconds with the 'no_mitosis_optimizer' pipeline.

I am looking to study more on mitosis optimizer. Is there any reference on what does it do? From the mserver5 man page, I got this - "forcefully activate mitosis even on small tables, i.e., split small tables in as many (tiny) pieces as there are cores (threads) available;" So, does this mean with the mitosis optimizer, the tables are split and processed? If so, then why are queries slower with mitosis optimizer in the pipeline?

Also, from the monetdb man page, I was alerted that "Changing this setting is discouraged at all times." What is the disadvantage of changing the optimizer pipeline to something other than the 'default_pipe'? Though the 'no_mitosis_optimizer' is stable, is it worth for production?

Any help much appreciated.

Thanks & Regards,

Vijayakrishna.P. Mobile : (+91) 9500402305.

On 2015-01-05 at 07:50, Vijay Krishna wrote:

I have been working on join performances with MonetDB. I tried using various optimizer pipelines.

Few costly queries which took 15 seconds with the default pipeline, returned results as fast as 5 seconds with the 'no_mitosis_optimizer' pipeline.

If everyone's offering their two cents on mitosis, then I'll mention that my experience is documented in https://www.monetdb.org/bugzilla/show_bug.cgi?id=3548. That bug includes a patch implementing some broadly similar ideas to those in Masood's paper[1], although I was unaware of his work before today.

You haven't told us much about your queries, your dataset or your hardware, so all the replies you're going to get will involve guesswork.

I'm assuming that your data set easily fits in RAM, i.e. you're not I/O-bound.

Basically all cases I've found where mitosis makes things slower happen when MonetDB splits a large table into N pieces (where N=# of CPU cores) only to re-merge all (or nearly all) of it again. Roberto's bug 3437 is like this, as he confirms in comment 9, and it's even possible to come up with a query which splits a large table, does absolutely no work on it, then merges it all back together again before continuing with the rest of the plan. Ironically, the easiest way to provoke this is with the simplest query possible: select col from big_table;. As soon as you put a WHERE clause with decent selectivity on that query then the split makes sense, but for just dumping an entire table it doesn't.

Mitosis will split the largest source table in your query, whatever and wherever that is (as long as it's big enough). Think about how you would go about implementing the join you have written if you were doing it yourself, and consider whether subdividing the largest table will make the job easier or harder. MonetDB does have many tricks to make even crazy queries run well, but it also has cases where it's not quite smart enough.

Richard.

[1] http://www.researchgate.net/publication/264700632_Cost-Based_Data-Partitioni...