- users-list - monetdb.org

MonetDB joins
by baraa Mohamad 10 Nov '14

10 Nov '14

Hello, I'd like to know if MonetDB stores a copy of the whole table in order to reduce the joins between columns when doing select *. In fact, I'm doing a research for PhD and I appreciate the query execution time delivered by MonetDB over huge tables :) Thanks in advance,Baraa

3 3

Querying from Linux Terminal
by Vijay Krishna 09 Nov '14

09 Nov '14

Hi, Can I execute a large number of queries through shell script directly from terminal? In MySql I use, mysql -u root -ppassword -e "select * from table" in my shell script, which returns the result without logging into mysql client. Similarly, I want to execute queries in the terminal without logging into mclient. Is there any equivalent way of achieving the same? I have to do a lot of updates and instead of doing those one by one, I prefer writing a shell script to run hundreds of queries one by one automatically. Any help much appreciated. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

5 4

Arch Linux Packages for MonetDB + Language R
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I have updated my packages to the latest versions you'll need to install dependencies first there 2 for r-lang 3 for monetdb of which one is r-lang anyway, you also need base-devel installing first otherwise you won't be able to build anything. https://aur.archlinux.org/packages/r-lang - 3.1.2 https://aur.archlinux.org/packages/monetdb - 11.19.3 You should be able to build MonetDB on Arch Linux via AUR ( Arch Linux User Repository) without issues you need the abs package installed ( Arch Linux Build System ) first. Regards, PS: https://wiki.archlinux.org/index.php/Arch_Build_System Brian Hood

1 0

MonetDB - Integrated R Support
by Brian Hood 07 Nov '14

07 Nov '14

Hi All, I built Language R from source install it all went well. However when i try to compile MonetDB from source with enable r integration i get the following. make[7]: Entering directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' /bin/sh ../../../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c -o lib_rapi_la-rapi.lo `test -f 'rapi.c' || echo './'`rapi.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -I../../.. -I. -I../../../common/options -I./../../../common/options -I../../../common/stream -I./../../../common/stream -I../../../gdk -I./../../../gdk -I../../mal -I./../../mal -I../../../sql/include -I./../../../sql/include -D_FORTIFY_SOURCE=2 -DLIBRAPI -O3 -fomit-frame-pointer -pipe -march=x86-64 -mtune=generic -pipe -fstack-protector-strong --param=ssp-buffer-size=4 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c rapi.c -fPIC -DPIC -o .libs/lib_rapi_la-rapi.o rapi.c:42:23: fatal error: Rembedded.h: No such file or directory #include <Rembedded.h> ^ compilation terminated. Makefile:825: recipe for target 'lib_rapi_la-rapi.lo' failed make[7]: *** [lib_rapi_la-rapi.lo] Error 1 make[7]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:499: recipe for target 'all' failed make[6]: *** [all] Error 2 make[6]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras/rapi' Makefile:526: recipe for target 'all-recursive' failed make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:479: recipe for target 'all' failed make[4]: *** [all] Error 2 make[4]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5/extras' Makefile:522: recipe for target 'all-recursive' failed make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:475: recipe for target 'all' failed make[2]: *** [all] Error 2 make[2]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3/monetdb5' Makefile:586: recipe for target 'all-recursive' failed make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory '/data2/AUR/monetdb/src/MonetDB-11.19.3' Makefile:515: recipe for target 'all' failed make: *** [all] Error 2 My Rembedded.h exists here /usr/lib64/R/include/Rembedded.h Is there some environment variables i can set for the location of the my R header files ? Regards, Brian Hood

2 2

sys.queue()
by Guillaume Bassot 06 Nov '14

06 Nov '14

Hello, On release JAN2014, select * from sys.queue(); is returning all running process. On release OCT2014 the same command is returning SELECT: no such operator 'queue' Do i miss a new way to get process list ? Could you please tell me how to do ? Thanks Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 6

Improving performance of bulk loading data
by Dennis Pallett 06 Nov '14

06 Nov '14

Hi all, We're experiencing some problems during bulk loading of new data into our MonetDB database. We use COPY INTO to load several CSV files every day but this loading takes (too) long but more importantly the database is completely unresponsive with regards to any incoming queries therefore completely disabling our production environment. I'm currently looking into improving this proces and the hardware of our MonetDB server is obviously one of the starting points. Does anyone know which specs should be upgraded? Would adding more core's/CPU's have the most positive effect? Or perhaps more memory? Any advice or insight would be greatly appreciated! Best regards, Dennis Pallett

3 4

Can't use ORDER BY in subqueries
by Kotschenreuther, Leo 06 Nov '14

06 Nov '14

Hi all, I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation: I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this: sql>select * from test join (select * from foo order by c limit 1); The result I receive is the following: syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order” On the other hand, I can run the subquery without the main query and I receive a valid result. Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries? Regards Leo

7 16

Several problems with spatial features and query execution time measuring
by Kotschenreuther, Leo 03 Nov '14

03 Nov '14

Hi all, I’m facing several problems with the spacial extension of Monetdb. Here are the basics about my environment: I am using MonetDB v11.17.21 (Jan2014-SP3) on a Suse Linux Enterprise Server 11 (x86_64, Patchlevel 2). I installed MonetDB by compiling from source as there is no Release for SLES available. 1) First of all, I had a glance at the documentation about the spatial functions [1]. I did need some time to realise that every function that is described with the prefix “ST_” in the documentation works without the prefix. Could somebody please change the documentation so that it is according to the sql functions? 2) I would like to use the “Intersects” function that says whether two geometries intersect or not intersect. Apparently my Monetdb-Server doesn’t know the function although he knows other ones like the Equals function. Let’s create two tables with Geometries, fill them with one Polygon each and then check whether the two Polygons intersect: sql>create table one (idone INT, shape Polygon); sql>create table two (idtwo INT, figure Polygon); sql>insert into one values (0, GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)); sql>insert into two values (0, GeomFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)); sql>select Intersects(shape, figure) from one join two ON idone = idtwo; As result of the last query I receive the following: SELECT: no such binary operator 'intersects(polygon,polygon)’ I tried this on the sql console using mclient, I tried the same inside my python script using the mounted.sql API. Both times I received the same message. 3) I would like to measure the query execution time for all my queries. I found [2] where it is described how to measure the execution time. Following the steps and running the queries from the sql console, everything works fine. When I want to do the same inside my python script, I get an error on running the following statement: select id, query, avg(run) from sys.querylog_history group by id,query; This is the traceback, I skipped the parts of the trace that belong to my own code. The first line is the place where I try to access the sys.querylog_history table. Traceback (most recent call last): ... File "/Users/leokotschenreuther/EPIC/GIS benchmark/db-benchmark/db/monetdb_db.py", line 109, in runQueries self.cursor.execute("select id, query, avg(run) from sys.querylog_history group by id,query") File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 170, in execute self.__store_result(block) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 397, in __store_result values = self.__parse_tuple(line) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 440, in __parse_tuple self.description)]) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/pythonize.py", line 139, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type oid is not supported Does somebody know whether there is a chance to do the time measuring in python as well? I know that I could just use the time module in python but I want to measure the database server execution time. Thanks for reading and helping! Regards Leo [1]: https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions <https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions> [2]: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew <https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew>

2 1

"select * from sys.queue" not working in Oct2014
by Robert Koch 03 Nov '14

03 Nov '14

Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014) Database: MonetDB v11.19.3 (Oct2014), 'mapi:monetdb://server:50000/anonym' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from sys.queue; SELECT: no such operator 'queue' sql>select * from sys.queue(); SELECT: no such operator 'queue' sql>

1 0

Re: MonetDB error while restarting the database
by Ashish Kumar Singh 31 Oct '14

31 Oct '14

Hi All, I have filled an issue Bug 3610: exittimeout property not being honoured against monetdb. It looks like GDKExit error is coming too frequently due to all this and its not even waiting for default value of 60 seconds. Regards, Ashish -----Original Message----- From: Ashish Singh <ashishkumar.singh(a)altair.com> Date: Thursday, 30 October 2014 8:24 pm To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> Subject: Re: MonetDB error while restarting the database >Thanks All! > >we made sure now that database connection pool has released all >connections before we shutdown monetdb. > >But still infrequently we are seeing this error popping up and we have >double checked that client has returned all the connections. > >Before stopping the database a data load process happened from the client >to monetdb, can it be contributed to that?? > > > >Regards, >Ashish > >-----Original Message----- >From: Sjoerd Mullender <sjoerd(a)monetdb.org> >Reply-To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> >Date: Monday, 27 October 2014 11:07 pm >To: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org> >Subject: Re: MonetDB error while restarting the database > >> >> >>On 27/10/14 17:42, Dimitar Nedev wrote: >>> Hi Ashish, >>> >>> What happened there is monetdbd send a TERM signal to the mserver >>>process (the actual MonetDB database process), which killed all running >>>threads. The messages "#GDKexit: killing thread" means the the MonetDB >>>kernel (GDK) killed a thread still working at the time the process was >>>terminated. This is most likely an indication that the database was >>>still processing something at that time. Depending on what it was >>>actually doing, data corruption might occur. This can explain why >>>monetdbd reports an internal error why truing to start the mserver >>>process again. >> >>Data corruption should *never* occur. If you have proof of (and a >>recipe for) data corruption, please file a bug report. >>Of course, if a thread was still working, the transaction it was working >>on will most likely not get committed. >> >>Otherwise the above is correct. >> >>> Check the merovingian.log file again for the events when monetdbd >>>reported the internal error. There should be more information logged on >>>why mserver cannot start up any more. >>> >>> Best regards, >>> Dimitar >>> >>>> On 2014-Oct-27, at 15:55 , Ashish Kumar Singh >>>><ashishkumar.singh(a)altair.com> wrote: >>>> >>>> Thanks Dimitar, >>>> >>>> Yes we are restarting the data base and as part of that we are seeing >>>>this >>>> error. >>>> >>>> I am more worried about the error below: >>>> >>>> >>>> 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: 42014-10-27 >>>> 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing thread >>>> >>>> >>>> On java client side it says >>>> >>>> >>>> monetdbd: internal error while starting mserver, please refer to the >>>>logs >>>> >>>> >>>> >>>> >>>> Regards, >>>> Ashish >>>> >>>> >>>> -----Original Message----- >>>> From: Dimitar Nedev <D.G.Nedev(a)cwi.nl> >>>> Reply-To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>> Date: Monday, 27 October 2014 8:15 pm >>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>> Subject: Re: MonetDB error while restarting the database >>>> >>>>> Hi Ashish, >>>>> >>>>> The exit timeout only tells monetdbd how long should it wait when >>>>>trying >>>>> to gracefully shut down a mserver process. According to the log, the >>>>> mserver process was shut down within the limit, since there are no >>>>> messages like "timeout of <exittimeout> seconds expired, sending >>>>>process >>>>> <PID> the KILL signal". >>>>> >>>>> Now, the interesting entry in the log is the control process message: >>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database >>>>> 'pbsworksdb' >>>>> >>>>> This one would indicate that someone (or something) gracefully >>>>>stopped >>>>> the database using the monetdbd client. Probably with the following >>>>> command line call like: 'monetdb stop pbsworksdb'. >>>>> Do not be confused by the order of logged events - monetdbd will >>>>>first >>>>> stop the process and later log that the call to stop the a database >>>>>has >>>>> been successful. >>>>> >>>>> Best regards, >>>>> Dimitar >>>>> >>>>> >>>>>> On 2014-Oct-27, at 14:49 , Ashish Kumar Singh >>>>>> <ashishkumar.singh(a)altair.com> wrote: >>>>>> >>>>>> Thanks! >>>>>> >>>>>> >>>>>> Appreciate your response so there are few more people getting the >>>>>>same >>>>>> error :). >>>>>> Any suggestions from dev team on this issue? Can monetdbd >>>>>>exittimeout >>>>>> be also in picture for this? >>>>>> >>>>>> >>>>>> Regards, >>>>>> Ashish >>>>>> >>>>>> >>>>>> >>>>>> From: Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> >>>>>> Date: Monday, 27 October 2014 6:51 pm >>>>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>>>> Cc: "developers-list(a)monetdb.org" <developers-list(a)monetdb.org>, >>>>>>Ashish >>>>>> Singh <ashishkumar.singh(a)altair.com> >>>>>> Subject: Re: MonetDB error while restarting the database >>>>>> >>>>>>> Hello >>>>>>> >>>>>>> (google translate) >>>>>>> >>>>>>> I receive this error when there is no specific spacedisk. >>>>>>> Monetdb sometimes uses a lot of temporary diskspace >>>>>>>(500GBisnormalfor >>>>>>> 1TBof data), which arereleasedat the end oftreatment or after >>>>>>>acrash >>>>>>> >>>>>>> Error last week : >>>>>>> 2014-10-22 22:51:46 MSG merovingian[18865]: sending process 30608 >>>>>>> (database 'lemonde') the TERM signal >>>>>>> 2014-10-22 22:51:46 ERR merovingian[18865]: unknown state: >>>>>>>42014-10-22 >>>>>>> 22:51:46 ERR lemonde[30608]: #GDKexit: killing thread >>>>>>> >>>>>>> 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 >>>>>>> >>>>>>> De: "Ashish Kumar Singh" <ashishkumar.singh(a)altair.com> >>>>>>> À: "Ashish Kumar Singh" <ashishkumar.singh(a)altair.com>, >>>>>>> users-list(a)monetdb.org >>>>>>> Cc: developers-list(a)monetdb.org >>>>>>> Envoyé: Lundi 27 Octobre 2014 13:49:19 >>>>>>> Objet: Re: MonetDB error while restarting the database >>>>>>> >>>>>>> Guys, >>>>>>> >>>>>>> Any help with this will be really helpful for us? >>>>>>> >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> Ashish >>>>>>> >>>>>>> From: Ashish Singh <ashishkumar.singh(a)altair.com> >>>>>>> Date: Monday, 27 October 2014 4:06 pm >>>>>>> To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> >>>>>>> Subject: MonetDB error while restarting the database >>>>>>> >>>>>>>> >>>>>>>> All, >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> We are facing a new issue with monetdb where monetdb connections >>>>>>>>and >>>>>>>> process is being killed very frequently. Is this a known issue or >>>>>>>>any >>>>>>>> suggestion in getting more details will be helpful. >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Regards, >>>>>>>> Ashish >>>>>>>> >>>>>>>> Mervogian.log: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: arguments: >>>>>>>> >>>>>>>>/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver >>>>>>>>5 >>>>>>>> >>>>>>>>--dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pb >>>>>>>>s >>>>>>>>wor >>>>>>>> ksdata/pbsworksdb --set >>>>>>>> merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set >>>>>>>> mapi_open=false --set mapi_port=0 --set >>>>>>>> >>>>>>>>mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/ >>>>>>>>p >>>>>>>>bsw >>>>>>>> orksdata/pbsworksdb/.mapi.sock --set >>>>>>>> >>>>>>>>monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mon >>>>>>>>e >>>>>>>>tdb >>>>>>>> /pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set >>>>>>>> max_clients=64 --set sql_optimizer=default_pipe --set >>>>>>>>monet_daemon=yes >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB 5 server >>>>>>>> v11.17.17 "Jan2014-SP2" >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Serving database >>>>>>>> 'pbsworksdb', using 4 threads >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Compiled for >>>>>>>> x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Found 15.675 GiB >>>>>>>> available main-memory. >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) >>>>>>>>1993-July >>>>>>>> 2008 CWI. >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) August >>>>>>>> 2008-2014 MonetDB B.V., all rights reserved >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Visit >>>>>>>> http://www.monetdb.org/ for further information >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Listening for UNIX >>>>>>>>domain >>>>>>>> connection requests on >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock >>>>>>>> 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB/SQL module >>>>>>>>loaded >>>>>>>> 2014-10-27 09:01:00 MSG control[23583]: (local): started >>>>>>>>'pbsworksdb' >>>>>>>> 2014-10-27 09:01:00 MSG merovingian[23583]: proxying client >>>>>>>> localhost.localdomain:53844 for database 'pbsworksdb' to >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb >>>>>>>> 2014-10-27 09:01:00 MSG merovingian[23583]: target connection is >>>>>>>>on >>>>>>>> local UNIX domain socket, passing on filedescriptor instead of >>>>>>>>proxying >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): served status >>>>>>>>list >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: sending process 6351 >>>>>>>> (database 'pbsworksdb') the TERM signal >>>>>>>> 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: >>>>>>>> 42014-10-27 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing >>>>>>>>thread >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' >>>>>>>> (6351) has exited with exit status 0 >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' >>>>>>>>has >>>>>>>> shut down >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database >>>>>>>> 'pbsworksdb' >>>>>>>> 2014-10-27 09:01:26 MSG control[23583]: (local): served status >>>>>>>>list >>>>>>>> 2014-10-27 09:01:26 MSG merovingian[23583]: starting database >>>>>>>> 'pbsworksdb', up min/avg/max: 5s/14m/1h, crash average: 0.00 0.00 >>>>>>>>0.00 >>>>>>>> (648-648=0) >>>>>>>> 2014-10-27 09:01:27 MSG pbsworksdb[6575]: arguments: >>>>>>>> >>>>>>>>/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver >>>>>>>>5 >>>>>>>> >>>>>>>>--dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pb >>>>>>>>s >>>>>>>>wor >>>>>>>> ksdata/pbsworksdb --set >>>>>>>> merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set >>>>>>>> mapi_open=false --set mapi_port=0 --set >>>>>>>> >>>>>>>>mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/ >>>>>>>>p >>>>>>>>bsw >>>>>>>> orksdata/pbsworksdb/.mapi.sock --set >>>>>>>> >>>>>>>>monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mon >>>>>>>>e >>>>>>>>tdb >>>>>>>> /pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set >>>>>>>> max_clients=64 --set sql_optimizer=default_pipe --set >>>>>>>>monet_daemon=yes >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB 5 server >>>>>>>> v11.17.17 "Jan2014-SP2" >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Serving database >>>>>>>> 'pbsworksdb', using 4 threads >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Compiled for >>>>>>>> x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Found 15.675 GiB >>>>>>>> available main-memory. >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) >>>>>>>>1993-July >>>>>>>> 2008 CWI. >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) August >>>>>>>> 2008-2014 MonetDB B.V., all rights reserved >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Visit >>>>>>>> http://www.monetdb.org/ for further information >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Listening for UNIX >>>>>>>>domain >>>>>>>> connection requests on >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock >>>>>>>> 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB/SQL module >>>>>>>>loaded >>>>>>>> 2014-10-27 09:01:32 MSG merovingian[23583]: proxying client >>>>>>>> localhost.localdomain:53853 for database 'pbsworksdb' to >>>>>>>> >>>>>>>>mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/mone >>>>>>>>t >>>>>>>>db/ >>>>>>>> pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb >>>>>>>> 2014-10-27 09:01:32 MSG merovingian[23583]: target connection is >>>>>>>>on >>>>>>>> local UNIX domain socket, passing on filedescriptor instead of >>>>>>>>proxying >>>>>>>> 2014-10-27 09:01:32 MSG c >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> users-list mailing list >>>>>>> users-list(a)monetdb.org >>>>>>> https://www.monetdb.org/mailman/listinfo/users-list >>>>>>> >>>>>> _______________________________________________ >>>>>> developers-list mailing list >>>>>> developers-list(a)monetdb.org >>>>>> https://www.monetdb.org/mailman/listinfo/developers-list >>>>> >>>>> _______________________________________________ >>>>> users-list mailing list >>>>> users-list(a)monetdb.org >>>>> https://www.monetdb.org/mailman/listinfo/users-list >>>> >>>> _______________________________________________ >>>> developers-list mailing list >>>> developers-list(a)monetdb.org >>>> https://www.monetdb.org/mailman/listinfo/developers-list >>> >>> _______________________________________________ >>> developers-list mailing list >>> developers-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/developers-list >>> >> >>-- >>Sjoerd Mullender >> >

1 0

SQL_NO_CACHE equivalent?
by Vijay Krishna 30 Oct '14

30 Oct '14

Hi, Is there any equivalent to SQL_NO_CACHE in monetdb?? I wish the queries I run to return results from disk in order to check performance. Any help much appreciated. Thanks in advance. With Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

detect fragmented tables
by Pierre-Adrien Coustillas 30 Oct '14

30 Oct '14

Hello Before working with optimizer, Stethoscope and Tomograph , first I have to defragment the fragmented tables. How to detect fragmented tables ? Write a script that reads heapsize column ? select * , ( heapsize / 1021/1024/1024) as giga from sys.storage heapsize ORDER BY DESC LIMIT 20; What do you think ? or a more specific sql query by reading the column "count ", " typewidth ", " ColumnSize " and " heapsize " ? 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

2 5

Transaction Failure: BATsubselect: invalid argument: b must have a dense head
by Tim Burress 30 Oct '14

30 Oct '14

Hello, I've been using MonetDB for a couple of weeks now with no problems, but today ran into this issue when doing a series of DELETE/INSERT commands to update a table. When I issue the commands individually (autocommit on), there doesn't seem to be any difficulty, but when I do them as part of a transaction, I get this error on the second insert: START TRANSACTION; DELETE... 1 affected rows (10.145ms) INSERT... 1 affected rows (11.464ms) DELETE... 1 affected rows (3.402ms) INSERT... GDK reported error. BATsubselect: invalid argument: b must have a dense head I'm not sure what this means or what to do about it. Similar transactions were done yesterday with no problem, so I thought perhaps the table had been corrupted, but if I drop the table and recreate it from the original data, the above error still occurs. Thanks for any help you can provide, and if there is anything I can do that would give you some hints about what's happening, just tell me what to do and I'll be happy to help. Tim

1 0

MonetDB error while restarting the database
by Ashish Kumar Singh 27 Oct '14

27 Oct '14

All, We are facing a new issue with monetdb where monetdb connections and process is being killed very frequently. Is this a known issue or any suggestion in getting more details will be helpful. Regards, Ashish Mervogian.log: 2014-10-27 09:01:00 MSG pbsworksdb[6351]: arguments: /opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver5 --dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb --set merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock --set monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB 5 server v11.17.17 "Jan2014-SP2" 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Serving database 'pbsworksdb', using 4 threads 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Found 15.675 GiB available main-memory. 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) 1993-July 2008 CWI. 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Visit http://www.monetdb.org/ for further information 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock 2014-10-27 09:01:00 MSG pbsworksdb[6351]: # MonetDB/SQL module loaded 2014-10-27 09:01:00 MSG control[23583]: (local): started 'pbsworksdb' 2014-10-27 09:01:00 MSG merovingian[23583]: proxying client localhost.localdomain:53844 for database 'pbsworksdb' to mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb 2014-10-27 09:01:00 MSG merovingian[23583]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2014-10-27 09:01:26 MSG control[23583]: (local): served status list 2014-10-27 09:01:26 MSG merovingian[23583]: sending process 6351 (database 'pbsworksdb') the TERM signal 2014-10-27 09:01:26 ERR merovingian[23583]: unknown state: 42014-10-27 09:01:26 ERR pbsworksdb[6351]: #GDKexit: killing thread 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' (6351) has exited with exit status 0 2014-10-27 09:01:26 MSG merovingian[23583]: database 'pbsworksdb' has shut down 2014-10-27 09:01:26 MSG control[23583]: (local): stopped database 'pbsworksdb' 2014-10-27 09:01:26 MSG control[23583]: (local): served status list 2014-10-27 09:01:26 MSG merovingian[23583]: starting database 'pbsworksdb', up min/avg/max: 5s/14m/1h, crash average: 0.00 0.00 0.00 (648-648=0) 2014-10-27 09:01:27 MSG pbsworksdb[6575]: arguments: /opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/bin/mserver5 --dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb --set merovingian_uri=mapi:monetdb://blrec12-vm6:9200/pbsworksdb --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock --set monet_vault_key=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB 5 server v11.17.17 "Jan2014-SP2" 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Serving database 'pbsworksdb', using 4 threads 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Found 15.675 GiB available main-memory. 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) 1993-July 2008 CWI. 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Visit http://www.monetdb.org/ for further information 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock 2014-10-27 09:01:32 MSG pbsworksdb[6575]: # MonetDB/SQL module loaded 2014-10-27 09:01:32 MSG merovingian[23583]: proxying client localhost.localdomain:53853 for database 'pbsworksdb' to mapi:monetdb:///opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbsworksdata/pbsworksdb/.mapi.sock?database=pbsworksdb 2014-10-27 09:01:32 MSG merovingian[23583]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2014-10-27 09:01:32 MSG c

3 6

set optimizer
by Pierre-Adrien Coustillas 23 Oct '14

23 Oct '14

Hello , (google translate) I am a little fun with the optimizer . sql>s elec t count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple ( 3m 51s ) sql> set optimizer = ' minimal_pipe ; sql> select count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple (1m 28s ) sql> set optimizer = ' default_pipe ; sql> select count (distinct idvisiteur ) from src_inbox__at__lm__visites ; + ---------- + | L1 | + + ========== | 75528080 | + ---------- + 1 tuple ( 3m 54s ) It's very interesting . Is there a risk to change optimizer ? What are the best practices ? thank you -- 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

2 1

heapsize, speed, and vacuum
by Pierre-Adrien Coustillas 23 Oct '14

23 Oct '14

Hello, (google translate) I have a query that takes a long time : select count(distinct idvisiteur) from src_inbox__at__lm__visites; After two hours of running, it is not over , I'll kill her . if I look at the heapsize column storage ( ) sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+=============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 01/00/10025 | 315819936 | 5 | 2526559488 | 27755937792 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 76/7667 | 315819936 | 19 | 2526559488 | 80449110016 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 62/6201 | 315819936 | 2 | 1263279744 | 2222391296 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 77/7704 | 315819936 | 5 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 30/3064 | 315819936 | 19 | 2526559488 | 10741678080 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 31/3171 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 67/6706 | 315819936 | 12 | 1263279744 | 2077556736 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 40/4071 | 315819936 | 10 | 2526559488 | 22635741184 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 01/26/12673 | 315819936 | 8 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 01/44/14410 | 315819936 | 17 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 42/4234 | 315819936 | 1 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 01/41/14100 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/22/12277 | 315819936 | 0 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 65/6573 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/04/10434 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/22/12264 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 56/5615 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/02/10211 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/23/12370 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ Is the heapsize column represents the amount of data to be placed in RAM ? I have 64GB of RAM on the server , so the idvisiteur column can not be held in RAM. Is this true? ( 64 000 000 000 < 80 449 110 016 ) I execute a vacuum (experimental command ?) : https://www.monetdb.org/Documentation/UserGuide/AutoloadingScript (Table vacuum commands (experimental!)) call vacuum('sys','src_inbox__at__lm__visites'); sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 36 | 315819936 | 5 | 2526559488 | 4906811392 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 64/6435 | 315819936 | 19 | 1263279744 | 3667394560 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 66/6640 | 315819936 | 2 | 1263279744 | 40042496 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 67/6743 | 315819936 | 5 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 70/7045 | 315819936 | 19 | 2526559488 | 7576551424 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 71/7152 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 72/7252 | 315819936 | 12 | 1263279744 | 106561536 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 73/7353 | 315819936 | 10 | 1263279744 | 1362165760 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 74/7453 | 315819936 | 8 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 75/7554 | 315819936 | 17 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 76/7655 | 315819936 | 1 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 77/7761 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/00/10064 | 315819936 | 0 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 01/01/10165 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/02/10270 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/03/10372 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 01/04/10476 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/06/10600 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/07/10703 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ heapsize of idvisteur = 3 667 394 560 (3,6GB) instead of 80 449 110 016 (80 GB) my query is present MUCH faster sql>select count(distinct idvisiteur) from src_inbox__at__lm__visites; +----------+ | L1 | +==========+ | 75528081 | +----------+ 1 tuple (4m 22s) HOURA !!! but WHY WHY WHY ? I want to understand 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

3 3

table not unique
by Pierre-Adrien Coustillas 22 Oct '14

22 Oct '14

Hello, (google translate) We had a crash monetdb following a full disk. We had already found that the bug not uniq table ( corrupt catalog ) could be related to these crash . And indeed this command will return no rows before the crash , but 20 lines after the crash : echo "SELECT name FROM SYS.TABLES WHERE system=false;" | mclient base | grep " " | grep -v " name " | grep -v " tuple" | sed 's/[| ]//g' | sort | uniq -c | grep -v " 1 " 5 bdm__lm__agregats 4 bdm__lm__agregats_temp 5 bdm__tra__agregats 4 bdm__tra__agregats_temp 5 ci__data__agregats 6 ci__data__reporting .. ... ... Is the October 2014 release will fix this trouble ? We are Jan2014 - SP3 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

4 3

Drop table not existe
by Pierre-Adrien Coustillas 20 Oct '14

20 Oct '14

Hello, (sorry my english, google translate) When I drop tables that does not exist, the drop may remain in queue. sql>DROP TABLE ci__data__agregats ; DROP TABLE ci__data__reporting ;DROP TABLE ci__data__reporting_ci ; DROP TABLE ci__data__reporting_regle; DROP TABLE ci__data__resultats; DROP TABLE ci__data__resultats_datamart; DROP TABLE ci__data__traductions; DROP TABLE ci__ref__calcul_final; DROP TABLE ci__ref__traductions_agregats; DROP TABLE src__arv__achat; DROP TABLE src__bil__commandes; DROP TABLE src__eve__abo; DROP TABLE src__eve__qlf; DROP TABLE src__eve__vpc; DROP TABLE src__mia__inscrits; DROP TABLE src__sdv__inscrits; operation successful (28.442ms) DROP TABLE: no such table 'ci__data__reporting' DROP TABLE: no such table 'ci__data__reporting_ci' DROP TABLE: no such table 'ci__data__reporting_regle' operation successful (28.490ms) operation successful (28.497ms) operation successful (28.502ms) operation successful (28.507ms) operation successful (28.511ms) DROP TABLE: no such table 'src__arv__achat' DROP TABLE: no such table 'src__bil__commandes' DROP TABLE: no such table 'src__eve__abo' DROP TABLE: no such table 'src__eve__qlf' DROP TABLE: no such table 'src__eve__vpc' DROP TABLE: no such table 'src__mia__inscrits' DROP TABLE: no such table 'src__sdv__inscrits' sql>select * from sys.queue(); +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ | qtag | user | started | estimate | progress | status | tag | query | +======+=========+============================+============================+==========+=========+===================+=================================+ | 12 | monetdb | 2014-10-17 14:34:16.000000 | 2014-10-17 14:34:16.024000 | null | running | 51539607560@0 | drop table src__arv__achat; | | 13 | monetdb | 2014-10-17 14:34:16.000000 | 6453-11-03 00:20:26.928000 | null | running | 140082976780320@0 | drop table src__bil__commandes; | | 14 | monetdb | 2014-10-17 14:34:16.000000 | null | null | running | 4@0 | drop table src__eve__abo; | | 15 | monetdb | 2014-10-17 14:34:23.000000 | null | null | running | 11776651@0 | select * from sys.queue(); | +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ 4 tuples (1.504ms) We do a lot of drop followed by create table. We believe that this is related to a concern for duplication in the catalog table ( table xxx is not single , corrupt catalog ? ) 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 2

MAL script error
by Hassan, Ahmad 14 Oct '14

14 Oct '14

Hi, One executing the MAL script http://pastebin.com/VUPbhD8V , I get the following errors: !TypeException:user.s0_11[87]:'algebra.join' undefined in: (X_150:any, r1_280:any) := algebra.join(X_137:bat[:oid,:oid], X_149:bat[:oid,:any]) !TypeException:user.s0_11[88]:'algebra.leftfetchjoin' undefined in: X_152:any := algebra.leftfetchjoin(r1_280:any, X_138:bat[:oid,:oid]) !TypeException:user.s0_11[94]:'algebra.join' undefined in: (X_161:any, r1_304:any) := algebra.join(X_152:any, X_160:bat[:oid,:any]) !TypeException:user.s0_11[95]:'algebra.leftfetchjoin' undefined in: X_404:any := algebra.leftfetchjoin(X_150:any, X_135:bat[:oid,:oid]) !TypeException:user.s0_11[100]:'algebra.leftfetchjoinPath' undefined in: X_174:bat[:oid,:int] := algebra.leftfetchjoinPath(X_161:any, X_404:any, X_173:bat[:oid,:any]) !TypeException:user.s0_11[106]:'algebra.leftfetchjoin' undefined in: X_183:any := algebra.leftfetchjoin(r1_304:any, X_182:bat[:oid,:any]) !TypeException:user.s0_11[107]:'algebra.join' undefined in: (X_184:any, r1_343:any) := algebra.join(X_183:any, X_107:bat[:oid,:int]) !TypeException:user.s0_11[109]:'algebra.leftfetchjoin' undefined in: X_187:any := algebra.leftfetchjoin(X_186:bat[:oid,:oid], X_183:any) !TypeException:user.s0_11[110]:'group.subgroupdone' undefined in: (X_188:any, r1_349:any, r2_349:any) := group.subgroupdone(X_187:any) !TypeException:user.s0_11[111]:'algebra.leftfetchjoin' undefined in: X_191:any := algebra.leftfetchjoin(r1_349:any, X_187:any) !TypeException:user.s0_11[113]:'algebra.leftfetchjoinPath' undefined in: X_195:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_186:bat[:oid,:oid], r1_304:any, X_194:bat[:oid,:any]) !TypeException:user.s0_11[114]:'aggr.submin' undefined in: X_196:bat[:oid,:lng] := aggr.submin(X_195:bat[:oid,:lng], X_188:any, r1_349:any, _201:bit) !TypeException:user.s0_11[119]:'algebra.leftfetchjoin' undefined in: X_202:any := algebra.leftfetchjoin(r1_389:bat[:oid,:oid], X_191:any) !TypeException:user.main[2]:'user.s0_11' undefined in: _4:any := user.s0_11() The equivalent SQL query works fine but I am interested in running MAL script via mserver5. How can I resolve the above exceptions please? Thanks.

3 8

Re: Left/Right joins performance - Need help!!
by Vijay Krishna 10 Oct '14

10 Oct '14

Hello Martin, Thanks for the useful tip. On cross checking the trace statements of both inner and left join queries, found out that most of the time taken by the left join query is spent at the following stage. This instance has happened for at least 20 times and each is around 40-60 seconds long while executing a single left/right join query. <<<-------- start quote - sample trace left join query --------->>> 45983604 | X_2138=<tmp_14607>[64851] := bat.append(X_2128=<tmp_14607>:bat[:oid,:str][64851],X_2137=<tmp_14544>:bat[:oid,:str][64851],true); 46443428 | X_3402=<tmp_6631>[64851] := bat.append(X_3390=<tmp_6631>:bat[:oid,:str][64851],X_3401=<tmp_20102>:bat[:oid,:str][64851],true); 53520133 | X_1820=<tmp_12102>[64851] := bat.append(X_1808=<tmp_12102>:bat[:oid,:str][64851],X_1819=<tmp_10437>:bat[:oid,:str][64851],true); 64750174 | X_1864=<tmp_12404>[64851] := bat.append(X_1854=<tmp_12404>:bat[:oid,:str][64851],X_1863=<tmp_21302>:bat[:oid,:str][64851],true); <<<-------- end quote - sample trace left join query --------->>> To my understanding, I think the discrepancy is because of the different approaches on deltas in executing both the queries. Not sure if I'm right. I would also like to know if this large left/inner join discrepancy is a default Monet behaviour or this is just because of some ignorant mistake/misconfigurations of mine (considering my heavy data load). Thanks & Regards, Vijayakrishna.P. Mobile : 9500402305.

1 1

Group by and select distinct
by Vijay Krishna 09 Oct '14

09 Oct '14

Hi, The output of "*select * from table group by col1*" and "*select distinct(col1) from table*" appear to be *same*. But in SQL, group by returns all columns and distinct retrieves only that column values over which the function is ran. Is there any way to retrieve non duplicate rows based on a single column (similar to group by in SQL) in MonetDB? Thanks in advance. With Regards, Vijayakrishna.P. Mobile : 9500402305.

2 1

Left/Right joins performance - Need help!!
by Vijay Krishna 09 Oct '14

09 Oct '14

Hi all, I have been comparing MySQL with MonetDB. Obviously, queries that took minutes in MySQL got executed in a matter of few seconds in Monet. However, I found a real blockade with joins. I have 2 tables - each with 150 columns. Among these (150+150) columns, around 60 are CHARACTER LARGE OBJECT type. Both the tables are populated with around 50,000 rows - with data in all the 150 columns. The average length of data in a CLOB type column is 9,000 (varying from 2 characters to 20,000 characters). The primary key of both the tables have same values and the join is always based on the primary key. The rows are by default inserted in ascending order on the primary key. When I ran *an inner join query on these two tables with about 5 criteria and with limit 1000, Monet processed this query in 5 seconds*, which is completely impressive compared to MySQL's (19 seconds). But when I ran *the same query with same criteria and limits using a left or right joins, Monet took around 5 minutes, which is clearly way behind MySQL's (just 22 seconds)*. Having read a lot about Monet's blinding speed compared to traditional relation row based DBs, I could feel that I am missing something, but couldn't figure out what. Can anyone please tell me why there is such a huge difference in such query execution time and how I can prevent it? Much grateful to have any help. Thanks a lot in advance. P.S.: I am running Monet on Macbook Pro - 2.3 GHz Core i7 - Quad core processor with 8 GB RAM. With Regards, Vijayakrishna.P. Mobile : +91-9500402305.

2 1

MonetDB and clout-backup
by Thomas Yokota 07 Oct '14

07 Oct '14

Hi, I am interested in using MonetDB in my workflow. Currently, I am using SQLite. One reason is that I can store the db one a separate file in my dropbox dir, which allows me to work from multiple computers rather than relying on a VMware-type solution. Can MonetDB give me the same flexibility? If so, is it simply saving the DB into a dir nested within Dropbox dir? Thank you, Thomas

1 0

Can not drop the view
by Radovan Bičiště 06 Oct '14

06 Oct '14

Hello, I'm wondering if there is some hard core way to drop a view. Somehow I have three views that I'm not able to drop. I can issue drop statement several times and it returns success. Here is log from mclient: ceos@lux-drmax-ana:/u01/ceosdata/database/monetdb/drmax/crm$ mclient -d zkdev1 -u drmaxtst password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP2) Database: MonetDB v11.17.17 (Jan2014-SP2), 'mapi:monetdb://lux-drmax-ana:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql>drop view drmaxtst.prodeje_v; operation successful (3.168ms) sql>drop view drmaxtst.prodeje_v; operation successful (3.004ms) sql>drop view drmaxtst.prodeje_v; operation successful (7.501ms) sql>drop view drmaxtst.prodeje_v; operation successful (4.154ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.351ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.410ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.355ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.685ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.646ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.695ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.390ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.399ms) sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql> I guess my only option is to dump a reload the database. :( Radovan

5 11

any tips to COPY INTO from a csv with whitespace around some DOUBLE fields?
by Anthony Damico 03 Oct '14

03 Oct '14

here's the problem-- Unable to execute statement 'COPY 2626675 OFFSET 2 RECORDS INTO OPPR_ALL_DTL_GNRL_09302014 FROM 'C:\Users\anthonyd\AppData\Local\...'. Server says '!value '1 ' from line 1 field 51 not inserted, expecting type double'. i could import it as VARCHAR and then CAST it back into a DOUBLE type, but i'm wondering if anyone has an easier/more clever way to import this? handling it manually would mean that i cannot use the monet.read.csv() function, so it's a bit more work. thanks!

2 2