- users-list - monetdb.org

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 29 Oct '14

29 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