- developers-list - monetdb.org

Big Slowdown (15-20X ) on import functions in Monetdb October 2014 Release
by Ashish Kumar Singh 15 Nov '14

15 Nov '14

All, With October,2014 release we are stucked in to very strange performance issue while importing data on virtualised environments. On Virtual machines in our local VMware servers and amazon machine we are seeing import performance has downgraded to close to 15 to 20 while importing ~ 2000 files as compared to 1 hour time being taken with old release. This we are seeing consistently with CentOS, SLES vm and configuration is 4 Core 33 GB machine. We tried on quite a few amazon vm just to make sure its not an issue with a particular instance setup. On a physical machine of 4 core 24 GB we are able to match speed of import with old monetdb. We are issuing copy into command through JDBC connection. Can you guys help me with pointers on this issue, I will be happy to share any debug info. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

1 2

Timestamp accuracy: milli-seconds or micro-seconds
by Stefan Manegold 07 Nov '14

07 Nov '14

Hi, with the Oct2014 release of MonetDB (I did no (yet) check any other version), I just came across the following: It appears that while timestamps are rendered with 6 decimal digits, i.e., suggesting micro-seconds accuracy/resolution, only milli-seconds are considered: sql>select cast('1312-11-10 12:11:10.123456' as timestamp) , cast('1312-11-10 12:11:10' as timestamp) + 0.123456 , cast('1312-11-10 12:11:10' as timestamp) + interval '0.123456' second; +----------------------------+----------------------------+----------------------------+ | L1 | L2 | sql_add_single_value | +============================+============================+============================+ | 1312-11-10 12:11:10.123000 | 1312-11-10 12:11:10.123000 | 1312-11-10 12:11:10.123000 | +----------------------------+----------------------------+----------------------------+ 1 tuple (5.459ms) Is this a bug or a "feature"? Thanks! Stefan -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

2 1

Announcement: New Oct2014 Feature release of MonetDB suite
by Sjoerd Mullender 06 Nov '14

06 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Important consideration when upgrading to this release. In this release we have fixed bug 3474. This bug made it possible that there were duplicates despite there being a unique or primary key constraint present. This could only happen for multi-column constraints. MonetDB maintains an internal column containing a hash for multi-column constraints, and there was a bug in the maintenance code of this column. During an upgrade, this column needs to be recreated. This is done by dropping and adding back the constraints. If there are no duplicates, adding back the constrains should work without problem, but if there are duplicates, adding the constraints will fail. There is unfortunately no easy way to tell the user, so after having made the first connection to the server (when the upgrade happens), check the merovingian log (if you use merovingian -- also known as monetdbd) or the server output window (if you don't use merovingian). Jaql support is removed. The experimental code for Jaql support has been removed. If you have RPM or DEB packages for Jaql installed from an earlier version (MonetDB-jaql and monetdb5_jaql respectively), you will need to uninstall them before the upgrade. Known issue when upgrading old versions. We have noticed that upgrading directly from a version older than the Jan2014 release gave an error. Upgrading to Jan2014 first and then on to Oct2014 should work. Backup. As always, make a backup of your databases before upgrading. Oct 2014 feature release MonetDB5 Server * Removed algebra.materialize. * Removed algebra.kunique and algebra.tunique. They were subsumed by algebra.subunique. * Remove algebra.antiuselect and algebra.thetauselect. They were subsumed by algebra.subselect. * Removed algebra.topN and its imlementation BATtopN. The function was not used. * Removed aggr.histogram and its implementation BAThistogram. The function was not used, and did not produce output in the "headless" (i.e. dense-headed) format. Histograms can be created as a by-product of group.subgroup. Build Environment * Jacqueline, the MonetDB/JAQL frontend, has been removed. The frontend never grew beyond being experimental, and there is no interest anymore to maintain the code. Merovingian * monetdb create: add -p flag to set monetdb user password on creation, and therefore allow creating the database in unlocked state mapilib * Changed mapi_timeout argument from seconds to milliseconds. stream * Changed mnstr_settimeout function so that the specified timeout is now in milliseconds (used to be seconds), and that it also needs an extra argument specifying a callback function (no arguments, int result) that should return TRUE if the timeout should cause the function to abort or continue what it was doing. MonetDB Common * Added "multifarm" capability. It is now possible to separate persistent and transient BATs into different directories (presumably on different disks). This can be done by using the --dbextra option of mserver5 (see the man page). SQL * Added PostgreSQL compatible string TRIM, LTRIM, RTRIM, LPAD and RPAD functions * Stop support for upgrading directly from a database created with a server from the Oct2012 release or older. You can upgrade via the Feb2013 or Jan2014 release. Bug Fixes * 2945: evaluation of SQL "between SYMMETRIC" requires MAL iterator because there is no (bulk) MIN/MAX(a,b) * 3204: monetdb create: allow setting admin password during creation * 3390: Missing definition for pushSht in monetdb5/mal/mal_builder.h * 3402: We should have a C implementation of mal.multiplex. * 3422: Segmentation fault after large table insert * 3459: incomplete implementation of JDBC driver getNumericFunctions(), getStringFunctions(), getSystemFunctions(), getTimeDateFunctions(), getSQLKeywords() methods in MonetDatabaseM * 3471: JDBC driver: incorrect output result of SQL query: SELECT 1 ; * 3474: bulk and scalar versions of mkey.rotate_xor_hash differ * 3484: COPY INTO on a file works fine on Linux/OSX, but not on Windows * 3488: Slow SQL execution for correlated scalar subquery * 3489: SQL query with ORDER BY does not order its result as requested * 3490: SQL query kills the mserver5 (Segmentation fault) * 3491: SQL query kills the mserver5 (Segmentation fault) * 3493: Test monetdb5/modules/mal/Tests/pqueue.mal fails since recent checkins * 3494: Tests monetdb5/modules/mal/Tests/pqueue[23].mal lack correct/expected/intended output * 3495: Test sql/test/centipede/Tests/olap.sql lacks correct/expected/intended output * 3497: monetdb start reports crash in merovingian.log * 3498: SQL throws TypeException if aggregations and limit statements are both present * 3502: Database was killed by signal SIGBUS * 3504: COPY INTO does not allow OFFSET without specifying amount of records * 3505: expression with <boolean> = NOT <boolean> returns a syntax error but NOT <boolean> = <boolean> not * 3506: conversion to varchar terminates mserver * 3508: conversion of string '0 ' to type smallint or integer fails * 3510: timestamp + month interval generates bogus MAL? * 3511: When having multiple selections combined with aliases not all of them seem to be evalauted. * 3512: auto-conversion of string to `sht` type no longer works * 3513: COPY BINARY INTO fails on 6gb file; works fine on 3gb * 3516: inserting '0' into a column of datatype numeric fails * 3518: UNION with subqueries * 3521: large results of function exp() are not automatically returned as double * 3522: SQL catalog table sys.columns lists columns for table ids which do not exist in sys.tables * 3523: Window function over union gives no result * 3524: wrong error on missing aggregation column * 3527: select distinct - order by - limit 2 results in one single result * 3528: segfault at mal_session.c:521 * 3532: several geom tests crash after manifold changes * 3534: missing table name with invalid column in join using (and problems after resolving it) * 3536: program contains error with join using integer and smallint * 3542: gdk/gdk_bat.c:2904: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3543: invalid behavior and incorrect data results for SQL data type: numeric(4,4) * 3544: sys.reuse() corrupts data * 3546: Division by zero in CASE statement that should avoid it * 3547: Empty query when selecting a field from a view made of UNION ALL * 3551: Wrong ticks in TRACE * 3552: incorrect data results for "WHERE int_col <> 0" * 3554: Issue with subselect and ORDER BY * 3555: Order of evaluation inside CASE WHEN * 3558: numeric values (as strings) are incorrectly parsed/converted and invalid strings are accepted without error * 3560: Error "BATproject: does not match always" with subselect/groupby/having * 3562: mserver5: gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3563: incorrect results for scalar function locate(in_str, search_str, occurrence) * 3565: Wrong/confusing error message when trying to add a FK to a TEMP TABLE * 3572: Table names with escaped double quotes are rejected * 3573: alter table alter_not_null_test alter test set NOT NULL; is accepted when test contains null. This used to be restricted but isn't anymore * 3575: segmentation fault in mserver5 process * 3576: Dropping default value definitions from a table does not work as expected * 3577: SIGSEGV in BATins_kdiff * 3579: segmentation fault in mserver5 process * 3581: mserver5: rel_bin.c:2504: rel2bin_groupby: Assertion `0' failed. * 3582: mserver5: sql_mem.c:48: sql_ref_dec: Assertion `r->refcnt > 0' failed. * 3583: Possible buffer overflow in max(varchar) * 3585: Incorrect query terminates connection * 3586: mserver5: sql/storage/store.c:3610: sys_drop_func: Assertion `rid_func != oid_nil' failed. * 3592: SIGSEGV in MANIFOLDjob * 3593: delta_append_val: Assertion `!c || ((c)->S->count) == bat->ibase' failed. * 3594: gdk/gdk_bat.c:2855: BATassertHeadProps: Assertion `!b->H->revsorted || cmp >= 0' failed. * 3595: Race/heap corruption on thread exit * 3596: gdk_bat.c:2861: BATassertHeadProps: Assertion `!b->H->nonil || cmp != 0' failed. * 3597: SQL to MAL listing looses types * 3598: SQL bulk load should ignore leading/trailing spaces also with type decimal (as with integers & real/double) * 3599: Double-free of imprints * 3601: Trivial typo in debian/monetdb5-sql.init.d * 3603: "monetdb create -p" hangs monetdbd * 3604: Sys.queue ignored upon errors

4 3

Did not initialise the sql scenario
by Nitul Halder 04 Nov '14

04 Nov '14

I am getting the error (highlighted)when try to start the database demo. I am puzzled . What I can do..Please suggest anyone. Thank you all Nitul 2014-11-04 09:28:26 MSG demo[26510]: arguments: /usr/local/bin/mserver5 --dbpath=/RAID/main_dbfarm/demo --set merovingian_uri=mapi:monetdb://bdserver:50000/demo --set mapi_open=false --set mapi_port=0 --set mapi_usock=/RAID/main_dbfarm/demo/.mapi.sock --set monet_vault_key=/RAID/main_dbfarm/demo/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2014-11-04 09:28:26 MSG demo[26510]: # MonetDB 5 server v11.15.3 "Feb2013-SP1" 2014-11-04 09:28:26 MSG demo[26510]: # Serving database 'demo', using 4 threads 2014-11-04 09:28:26 MSG demo[26510]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2014-11-04 09:28:26 MSG demo[26510]: # Found 3.496 GiB available main-memory. 2014-11-04 09:28:26 MSG demo[26510]: # Copyright (c) 1993-July 2008 CWI. 2014-11-04 09:28:26 MSG demo[26510]: # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved 2014-11-04 09:28:26 MSG demo[26510]: # Visit http://www.monetdb.org/ for further information 2014-11-04 09:28:26 MSG demo[26510]: # Listening for UNIX domain connection requests on mapi:monetdb:///RAID/main_dbfarm/demo/.mapi.sock 2014-11-04 09:28:26 MSG merovingian[26485]: database 'demo' (26510) has exited with exit status 0 2014-11-04 09:28:26 MSG merovingian[26485]: database 'demo' has shut down *2014-11-04 09:28:26 ERR control[26485]: (local): failed to fork mserver: database 'demo' did not initialise the sql scenario*

1 0

Problems with comments in SQL code
by Robert Koch 04 Nov '14

04 Nov '14

SELECT * FROM sys.storage WHERE 1=1 AND "schema" = 'sapsr3' AND "table" = 'dfkkop' ; works in mclient and DBVisualizer (via JDBC) SELECT * FROM sys.storage WHERE 1=1 AND "schema" = 'sapsr3' --AND "table" = 'dfkkop' ; works in mclient, but does not work in DBVisualizer. error message: 11:23:37 [SELECT - 0 row(s), 0.000 secs] 1) [Error Code: 0, SQL State: 42000] syntax error, unexpected $end, expecting SCOLON in: "select. 2) [Error Code: 0, SQL State: 22000] *. 3) [Error Code: 0, SQL State: 22000] from. 4) [Error Code: 0, SQL State: 22000] sys.storage. 5) [Error Code: 0, SQL State: 22000] where 1=1. 6) [Error Code: 0, SQL State: 22000] and "schema" = 'sapsr3'. 7) [Error Code: 0, SQL State: 22000] --AND "table" = 'dfkkop';" ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] I also noticed this problem (handling comments) in other statements, especially when comments appear before the semicolon. I am not sure, if this is a problem in JDBC driver or DBVisualizer. To see, what is received by monetdb server, I would like to see log files of SQL queries. I found a directory named "/sql_logs/sql", but it consits of 2 files nearly empty. Where can I enable logging?

2 2

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

31 Oct '14

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/mserver5 >>>>--dbpath=/opt/pbsworks/12.4_rc3.1_live/portal/thirdparty/monetdb/pbswor >>>>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/pbsw >>>>orksdata/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/pbswor >>>>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/pbsw >>>>orksdata/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 >>>> >>>> >>>> >>>> >>>> >>> >>> _______________________________________________ >>> 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

3 4

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

27 Oct '14

Guys, Any help with this will be really helpful for us? Regards, Ashish From: Ashish Singh <ashishkumar.singh(a)altair.com<mailto:ashishkumar.singh@altair.com>> Date: Monday, 27 October 2014 4:06 pm To: "users-list(a)monetdb.org<mailto:users-list@monetdb.org>" <users-list(a)monetdb.org<mailto:users-list@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/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

2 2

Re: MonetDB: default - Merge with Oct2014 branch.
by Lefteris 27 Oct '14

27 Oct '14

tab space = 8 for gdk? On Mon, Oct 27, 2014 at 11:02 AM, Sjoerd Mullender <commits(a)monetdb.org> wrote: > Changeset: dfe9b28b23db for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dfe9b28b23db > Modified Files: > gdk/gdk_imprints.c > gdk/gdk_select.c > Branch: default > Log Message: > > Merge with Oct2014 branch. > > > diffs (108 lines): > > diff --git a/gdk/gdk_imprints.c b/gdk/gdk_imprints.c > --- a/gdk/gdk_imprints.c > +++ b/gdk/gdk_imprints.c > @@ -453,12 +453,12 @@ do { > \ > } \ > GETBIN##B(bin,col[i]); \ > mask = IMPSsetBit(B,mask,bin); \ > - if (col[i] != nil) { /* do not count nils */ > \ > - if (!cnt_bins[bin]++) { > \ > - min_bins[bin] = max_bins[bin] = i; > \ > - } else { > \ > - if (col[i] < col[min_bins[bin]]) > min_bins[bin] = i; \ > - if (col[i] > col[max_bins[bin]]) > max_bins[bin] = i; \ > + if (col[i] != nil) { /* do not count nils */ \ > + if (!cnt_bins[bin]++) { \ > + min_bins[bin] = max_bins[bin] = i; \ > + } else { \ > + if (col[i] < col[min_bins[bin]]) > min_bins[bin] = i; \ > + if (col[i] > col[max_bins[bin]]) > max_bins[bin] = i; \ > } \ > } \ > } \ > diff --git a/gdk/gdk_select.c b/gdk/gdk_select.c > --- a/gdk/gdk_select.c > +++ b/gdk/gdk_select.c > @@ -341,27 +341,27 @@ do { > \ > } \ > } while (0) > > -#define checkMINMAX(B) > \ > -do { > \ > - int ii; > \ > - BUN *imp_cnt = (BUN *) (((oid *)imprints->stats)+128); > \ > - imp_min = imp_max = nil; > \ > - for (ii = 0; ii < B; ii++) { > \ > - if ((imp_min == nil) && (imp_cnt[ii])) { > \ > - imp_min = src[((oid *)imprints->stats)[ii]]; > \ > - } > \ > - if ((imp_max == nil) && (imp_cnt[B-1-ii])) { > \ > - imp_max = src[(((oid > *)imprints->stats)+64)[B-1-ii]]; \ > - } > \ > - } > \ > - assert((imp_min != nil) && (imp_max != nil)); > \ > - if (!s && !VIEWtparent(b)) { /* no candidate list and no views*/ > \ > - if ((vl > imp_max || vh < imp_min) || > \ > - (anti && (vl < imp_min && vh > imp_max))) { > \ > - return 0; > \ > - } > \ > - } > \ > -} while (0) > \ > +#define checkMINMAX(B) \ > +do { \ > + int ii; \ > + BUN *imp_cnt = (BUN *) (((oid *)imprints->stats)+128); \ > + imp_min = imp_max = nil; \ > + for (ii = 0; ii < B; ii++) { \ > + if ((imp_min == nil) && (imp_cnt[ii])) { \ > + imp_min = src[((oid *)imprints->stats)[ii]]; \ > + } \ > + if ((imp_max == nil) && (imp_cnt[B-1-ii])) { \ > + imp_max = src[(((oid > *)imprints->stats)+64)[B-1-ii]]; \ > + } \ > + } \ > + assert((imp_min != nil) && (imp_max != nil)); \ > + if (!s && !VIEWtparent(b)) { /* no candidate list and no views*/ \ > + if ((vl > imp_max || vh < imp_min) || \ > + (anti && (vl < imp_min && vh > imp_max))) { \ > + return 0; \ > + } \ > + } \ > +} while (0) > > /* choose number of bits */ > #define bitswitch(CAND,TEST) \ > @@ -374,10 +374,10 @@ do { > \ > s && BATtdense(s) ? "(dense)" : "", \ > anti, #TEST); \ > switch (imprints->bits) { \ > - case 8: checkMINMAX(8); impsmask(CAND,TEST,8); break; > \ > - case 16: checkMINMAX(16); impsmask(CAND,TEST,16); break; > \ > - case 32: checkMINMAX(32); impsmask(CAND,TEST,32); break; > \ > - case 64: checkMINMAX(64); impsmask(CAND,TEST,64); break; > \ > + case 8: checkMINMAX(8); impsmask(CAND,TEST,8); break; \ > + case 16: checkMINMAX(16); impsmask(CAND,TEST,16); break; \ > + case 32: checkMINMAX(32); impsmask(CAND,TEST,32); break; \ > + case 64: checkMINMAX(64); impsmask(CAND,TEST,64); break; \ > default: assert(0); break; \ > } \ > } while (0) > @@ -484,13 +484,13 @@ NAME##_##TYPE (BAT *b, BAT *s, BAT *bn, > { \ > TYPE vl = *(const TYPE *) tl; \ > TYPE vh = *(const TYPE *) th; \ > - TYPE imp_min; > \ > - TYPE imp_max; > \ > - TYPE v; > \ > - TYPE nil = TYPE##_nil; > \ > + TYPE imp_min; \ > + TYPE imp_max; \ > + TYPE v; \ > + TYPE nil = TYPE##_nil; \ > TYPE minval = MINVALUE##TYPE; \ > TYPE maxval = MAXVALUE##TYPE; \ > - const TYPE *src = (const TYPE *) Tloc(b, 0); \ > + const TYPE *src = (const TYPE *) Tloc(b, 0); \ > oid o; \ > BUN w, p = r; \ > BUN pr_off = 0; \ > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list >

2 1

useless WHERE conditions
by gms_xy＠gmx.de 17 Oct '14

17 Oct '14

To have a human-readable SQL code, I am used in following code formatting: SELECT a FROM tbl WHERE 1=1 --<<-- absolutely useless, but nice for formatting AND col1 = 1 AND col2 = 2 AND col3 = 3 Instead of "1=1", some other databases accept "TRUE". When I have multiple OR-conditions, I start with "1=0" or "FALSE". Writing statements this way I noticed long execution times in MonetDB. Omitting the logical where-condition "1=1" results in very fast execution time. So I guess, MonetDB is really checking on every row, if 1=1.

2 1

Partitioning
by Robert Koch 17 Oct '14

17 Oct '14

Are there plans to implement a (manual) SQL-based (multilevel) partitioning? I saw great accelerations with partitions in other databases. Is there any internal/automatic partitioning? If so, can I check it?

2 1