users-list December 2014

users-list@monetdb.org

18 participants
16 discussions

by Vijay Krishna

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.

7 years, 7 months

Monetdb mserver5 performance flag

by Jose Noel Mendoza Bastidas

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.

7 years, 7 months

Monetdb user-defined optimizer pipeline

by Jose Noel Mendoza Bastidas

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

7 years, 7 months

Enabled and disabled cracking option on monetdb

by Jose Noel Mendoza Bastidas

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,

7 years, 7 months

speed query with 2 columns group by

by Pierre-Adrien Coustillas

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

7 years, 7 months

Support of string function in Monetdb

by Leon-Constantin Fopa

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,

7 years, 7 months

RAM usage of MonetDB

by Vijay Krishna

Hi, I have been testing MonetDB for quite a while and I am a bit quizzed by its RAM utilisation. I tested MonetDB with some complex data and queries as per my requirement in 2 machines - *one with 16 GB RAM and the other with 128 GB RAM *with no memory restrictions for applications. There are no applications other than MySQL and MonetDB running in those machines. The puzzling part was *all the queries took the same time with both the machines*. I have read quite a few documentations wherein I found that the MonetDB performance heavily depends upon RAM memory available. What am I missing? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

7 years, 7 months

Inserting arrays with SciQL

by Dimitar Misev

Hi, I've been experimenting with loading arrays into SciQL, but hit a wall at approx. 200MB TIFF size. Not sure if I'm doing something wrong or if there is a better way to import arrays? sql>CALL rs.attach('grey2.tif'); sql>CALL rs.import(2); Connection terminated Here's some metadata of the file: $ gdalinfo grey2.tif Driver: GTiff/GeoTIFF Files: grey2.tif Size is 21600, 10800 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] GeoTransform = 291.065568377342, 3.938632460246877e-06, 8.68784597348722e-07 -22.27513940185346, 8.250100004406028e-07, -3.669324727326387e-06 Metadata: AREA_OR_POINT=Point Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left ( 291.066, -22.275) (291d 3'56.05"E, 22d16'30.50"S) Lower Left ( 291.075, -22.315) (291d 4'29.82"E, 22d18'53.17"S) Upper Right ( 291.151, -22.257) (291d 9' 2.31"E, 22d15'26.35"S) Lower Right ( 291.160, -22.297) (291d 9'36.09"E, 22d17'49.01"S) Center ( 291.113, -22.286) (291d 6'46.07"E, 22d17' 9.76"S) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray I managed to import a similarly sized file previously, but copying it to another file and importing again fails with "Connection terminated" as well. $ gdalinfo grey.tif Driver: GTiff/GeoTIFF Files: grey.tif Size is 21600, 10800 Coordinate System is `' Origin = (-180.016666666666652,90.016666666666666) Pixel Size = (0.016666666666665,-0.016666666666665) Metadata: TIFFTAG_DATETIME=2008:06:22 09:01:55 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CS3 Macintosh TIFFTAG_XRESOLUTION=72 TIFFTAG_YRESOLUTION=72 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0166667, 90.0166667) Lower Left (-180.0166667, -89.9833333) Upper Right ( 179.9833333, 90.0166667) Lower Right ( 179.9833333, -89.9833333) Center ( -0.0166667, 0.0166667) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray Thanks, Dimitar

7 years, 7 months

Re: inner join very slow

by Stefan Manegold

Of what type(s) are the join attributes? How big is the expected join result? If you don't know, select count(*), sum(cnt) from (select count(*) as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; select count(*), sum(cnt) from (select count(*) as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; might help to give some estimation / idea ... Best, Stefan On December 1, 2014 6:16:11 PM CET, Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> wrote: >Hello, > > >With this query : >SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres >SUB ON SRC.siteniveau1=SUB.site AND >SRC.chapitreniveau1=SUB.idchapitre1; > > >SELECT count(*) FROM tmp__pierre2; >+------------+ >| L1 | >+============+ >| 1 061 964 254 | > >SELECT count(*) FROM ref__at_lm_chapitres; >+---------+ >| L1 | >+=========+ >| 1 331 867 | > > >after several hours of running the query is still not finished and I >stopped > > >how to find out where the problem? > > >thanks > > >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 > > > >------------------------------------------------------------------------ > >_______________________________________________ >users-list mailing list >users-list(a)monetdb.org >https://www.monetdb.org/mailman/listinfo/users-list

7 years, 7 months

COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead

by Pierre-Adrien Coustillas

Hello, is this a bug or a feature I do not understand ? I have a script bash : ------------------------------------ pcoustillas@monetdb:~$ cat bug_commit.sh #!/bin/bash i=1 while [ $i -le 3 ] do create="CREATE TABLE tmp_schampy_odc__lemondeclient_$i AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA " echo $create mclient lemonde --statement="$create" & i=$(($i+1)) done ------------------------------------ I monetdb restarts before : service monetdb5-sql stop ; sleep 10 ; service monetdb5-sql start And i run script : pcoustillas@monetdb:~$ ./bug_commit.sh CREATE TABLE tmp_schampy_odc__lemondeclient_1 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_2 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_3 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA pcoustillas@monetdb:~$ operation successful operation successful COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead operation successful why is there a conflict while they are different tables that are created? I use the last version Database: MonetDB v11.19.7 (Oct2014-SP1) I have the same bug on : MonetDB v11.17.21 (Jan2014-SP3) Thank 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

7 years, 7 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list December 2014