- users-list - monetdb.org

MonetDB Usage for spherical clustering
by Jiří Nádvorník 14 Jan '15

14 Jan '15

Dear MonetDB support, My name is Jiri Nadvornik and I am working for Czech astronomical institute (http://www.asu.cas.cz/en/about/about-the-institute) For more than a year I’m trying to generate identifiers for astronomical objects used when creating light curves for these objects. In general, I’m working with spherical coordinates and based on Euclidean parameters (and possibly other parameters, like magnitude) I am clustering these objects to assign multiple observations to one real object. A bottleneck when doing this in a relational database (like PostgreSQL) is not the finding of the neighbors as such (~ ms in a loop), but the inefficiency of working with disk IO when doing so. So after a little analysis of what could be done, I am quite interested into storing the data (mainly spherical coordinates) in an array DB, physically partition regions of the sky to multiple disks and then use the clustering on these partitions in parallel. My question no. 1 for you is whether MonetDB has some kind of support for spherical indexing (PgSphere, Q3C, HealPix, …) to ingest and correctly distribute the astronomical data. I need to control the way of how the data will be distributed to physical partitions. If question no.1 can be somehow answered as true, then question no 2. is: What is the easiest way to install MonetDB, ingest my data and test them? P.S.: Our processed dataset contains now cca 3e8 observations (entries for clustering) which resolve to about 6e6 real objects. We expect that number to double when we process all of our data. Thank you for your answer. Kind regards, Jiri Nadvornik Astronomical Institute of the Academy of Sciences of the Czech Republic nadvornik.ji(a)gmail.com

3 2

Bug in Query Plan??
by Vijay Krishna 09 Jan '15

09 Jan '15

Hi, I have been studying on multiple join performances in MonetDB. I am running the latest version of MonetDB Oct2014 - SP1 (MonetDB-11.19.3) on a CentOS machine with 128 GB RAM. All my tables have 100 to 300 columns and around 35 lakh rows. I have the following query with 5 joins and a few criteria. SELECT * FROM table1 INNER JOIN table2 on table1.col1=table2.col1 LEFT JOIN table3 ON table1.col2=table3.col1 LEFT JOIN table3 ON table1.col3=table3.col1 LEFT JOIN table4 ON table2.col2=table4.col1 LEFT JOIN table5 ON table2.col3=table5.col1 WHERE (((table2.col10 like '%a%' OR table5.col11 like '%s%') and ((table2.col12 like '%w%' *and table4.col13 like '%k'*) OR table2.col14 like '%h%') and (table1.col21 = 1) AND (table1.col22 IN (1,2,3))) AND (((table1.col23 >= 15842000000000000) AND (table1.col23 <= 15842999999999999)) OR ((table1.col23 >= 0) AND (table1.col23 <= 999999999999)))) ORDER BY 1 DESC LIMIT 10 OFFSET 0; This query has only 2000 matching rows and it returns the result in *2.9 seconds*. In the same query, if I remove one criteria alone (the bolded part), the result would still match only 2000 rows, but the result would come in *300 milliseconds* even. I used the PLAN statement and found that the query plan that the executor takes for the first query is a much complex path and *it performs 3 joins for all the 35 lakh rows* (i.e. before executing the criteria even) and hence the time has spiked to 3 seconds. May be the hot data of 35 lakh tuples would have not fit into the memory?? Not sure. But if I remove the highlighted criteria, the executor evaluates the criteria first and *it performs the joins for only 7000 rows* and hence it is faster 1000 times. Is this a bug? Or is there any way to optimise my query to match the executor to choose the right plan? Any help much appreciated. I could even demo this. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0

Is there a way to limit worker threads on Windows
by Bryan Senseman 06 Jan '15

06 Jan '15

All, I have tried various approaches, but nothing changes the ‘using 8 threads’ message displayed at server startup. I have a client (with a client) that is running MonetDB on a 2cpu windows box and getting various errors, I’m trying to duplicate them but cannot and am concerned it might be that my hardware (read laptop) is too “powerful” to create the error condition. I really do not want to light up a virtual machine just to reduce threads. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

5 8

no_mitosis_optimizer - is it advisable?
by Vijay Krishna 05 Jan '15

05 Jan '15

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.

4 3

EXPLAIN/TRACE statements reference
by Vijay Krishna 28 Dec '14

28 Dec '14

Hi, I have been studying on the slow query behavior with certain joins in MonetDB. I use explain & trace statements to understand the flow. But, I couldn't actually understand the MAL statements that appear as the output of EXPLAIN/TRACE statements. I tried learning MAL with this reference - https://www.monetdb.org/Documentation/Manuals/MonetDB/MALreference But it had no explanations for system defined MAL statements that appear in trace. For example, from the trace statement, I could find that a lot of time was spent in the MAL expression "language.dataflow()", but without actually knowing what does this step actually do, I am unable to optimize the query. - *Is there any reference guide to understand the output of EXPLAIN/TRACE? Or should we refer to the source code to understand this? * - *Is it possible to know the query plan that Monet arrives to execute a query? * - *Is it possible to make the output of EXPLAIN/TRACE to be in SQL format, like in MySQL?* Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

Monetdb mserver5 performance flag
by Jose Noel Mendoza Bastidas 24 Dec '14

24 Dec '14

Hello, If it is not too much trouble, could someone explain in detail what happens when you work with mserver5 with flag −−performance (JOINPROPMASK | DEADBEEFMASK)? My intention is to execute several queries and measure their performance in time using the structures provided by MonetDB in order to optimize, at the moment I have documented myself about the Optimizer Pipelines and I would like to know if somebody knows about other structures offered by MonetDB to optimize queries. The purpose of my research is to show the advantages of MonetDB, taking advantage of every resource provided to optimize queries. Thanks in advance.

4 3

Monetdb user-defined optimizer pipeline
by Jose Noel Mendoza Bastidas 23 Dec '14

23 Dec '14

Hi, I was measuring the execution time of some queries using different optimizers that provides MonetDB (minimal, default, no_mitosis, sequential and recycler). Now I want to measure these times but using user-defined optimizer pipeline For this I am testing with the following command: MonetDB Septemberoptpipe='optimizer.inline();optimizer.remap();optimizer.deadcode();optimizer.multiplex();optimizer.garbageCollector();' tesisdb According to the documentation that would be equivalent to using minimal_pipe, the fact is that when I use the expressed command above and run the query throws me the following error: Function '@ 7 ?.' not defined I wonder if someone has happened something like this or have any idea how to create different pipes to those provided by the system Thanks in advance

2 2

Enabled and disabled cracking option on monetdb
by Jose Noel Mendoza Bastidas 17 Dec '14

17 Dec '14

Hi, I am a student at Simon Bolivar University in Venezuela , i' ve been performing an experimental study using MonetDB - v11.17.21 (Jan2014-SP3). I was reading two papers by MonetDb developers about Database Cracking where authors got better results with cracking enabled than without it. In these papers, authors stated that they were able to turn on and off the cracking facilities in monetdb. However, I haven' t been able to find information on how this can be done. In my experimental study, I also need to run my queries and study the performace differences obtained using thes two options. My actual question: do you know how I can turn on and off the cracking option on monetdb? Do you have documentation that explain this? Thanks in advance,

2 1

speed query with 2 columns group by
by Pierre-Adrien Coustillas 15 Dec '14

15 Dec '14

Hello, I have an query with the same source data on Jan2014 -SP3 version of monetddb and Oct2014 - SP1 : CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA; On version Jan2014 -SP3 : Welcome to mclient , the MonetDB / SQL interactive terminal (Jan2014-SP3) Database : MonetDB v11.17.21 (Jan2014-SP3), 'mapi : monetdb :// lemondedev :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (6m 30s) On version Oct2014-SP1 : Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014-SP1) Database : MonetDB v11.19.7 (Oct2014-SP1), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (383m 20s) explain query is different , you want to explain the results of the query ? Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 7

Support of string function in Monetdb
by Leon-Constantin Fopa 14 Dec '14

14 Dec '14

Hi every body, Does Monetdb supports string function like concat_ws in Postgresql (http://www.postgresql.org/docs/9.1/static/functions-string.html) ? Best regards, Fopa Léon Constantin,

2 1