- users-list - monetdb.org

Fixed size table performance
by Gustavo Brian 23 Feb '15

23 Feb '15

Hi, I want to implement a calendar based big table with prices for hotel accommodation with MonetDB. Once I have all the data in the table the performance is amazing but now I'm worried about insert/delete/update performance. I've asking at stackoverflow: http://stackoverflow.com/questions/28122178/monetdb-refresh-data-in-backgro… and had a nice approach but now I wonder about having a fixed size table because I can approximate the needed records for having a year of data and update them each time I have to: enable/disable, change free units, update prices, ... Some of the columns will be used for search: "destination", "date", "category", ... others for refinement: "units", "blocked", ... and others as values for the aggregations: "price", "breakfast", ... Is a fixed size table a good approach ? Updating the columns used for refinement or calculation will affect performance ? Thanks, Gustavo Brian Developer

2 1

Table as function parameter
by Bart Lenoir 20 Feb '15

20 Feb '15

To be more precise: SQL table data 'converted' to R data.frame data inside MonetDB (functions).

1 0

Table as function parameter
by Bart Lenoir 20 Feb '15

20 Feb '15

Hi Martin, Thank you for your quick response. I was hoping to find a way to use 'data.frame' in MonetDB's SQL with embedded R functions. Column oriented RDBMS with integrated/embedded R are serious contenders in the (Big) Data Science arena. Kind regards, Bart

1 0

Table as function parameter
by Bart Lenoir 19 Feb '15

19 Feb '15

Do MonetDB functions accept tables as parameters?

2 1

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Hi Stefan, I filed a bug report: 3672 Thanks. Cheers, Bart

1 0

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Hi Stefan, Thank you for your quick response. The config output has 600+ lines and the config.log is over 11k lines. Can I send you these as attachment by email? Cheers, Bart

2 1

Compilation Error MonetDB-11.19.9
by Bart Lenoir 15 Feb '15

15 Feb '15

Compilation of MonetDB-11.19.9 generates error in 'make' on Oracle Linux (2.6.18-371.11.1.0.1.el5 - x86_64). /usr/bin/ld: .libs/libbat_la-gdk_utils.o: relocation R_X86_64_PC32 against `MT_global_exit' can not be used when making a shared object; recompile with -fPIC

2 1

Bulk loading with NULL values in a column
by Scott Mathieson 13 Feb '15

13 Feb '15

Hi, Is it possible to perform a binary load of column data that contains NULLs - the documentation seems to indicate that the data is just a binary file of a C array of the appropriate type, but that would imply you can't have NULL values in any of the columns, is that correct? Regards, Scott ________________________________

2 1

Bug : msqldump, drop, create
by Pierre-Adrien Coustillas 07 Feb '15

07 Feb '15

Hello, I finally get to reproduce a table conflict bug still present in the 2014 version of OCT SP1 I run these commands simultaneously in two terminals : ---------------------------------------------------------------------------- terminal 1 : $ msqldump --database=lemonde --table=test_integrite_tra > backup_test_integrite_tra.sql terminal 2 : $ mclient lemonde --statement="SELECT count(*) FROM test_integrite_tra" +----------+ | L1 | +==========+ | 19681703 | +----------+ $ mclient lemonde --statement="DROP TABLE test_integrite_tra" operation successful $ mclient lemonde --statement="CREATE TABLE test_integrite_tra AS SELECT id_unique,device,nbvisistes FROM sys.visiteurs_device_2 WITH NO DATA" operation successful ---------------------------------------------------------------------------- I count the number of table test_integrite_tra sql>SELECT count(*) FROM SYS.TABLES WHERE name='test_integrite_tra'; +------+ | L1 | +======+ | 1 | +------+ And I restart monetdb : # service monetdb5-sql stop ; sleep 30 ; service monetdb5-sql start Stopping MonetDB SQL server: monetdbd. Starting MonetDB SQL server: monetdbd. I count the number of table test_integrite_tra sql>SELECT count(*) FROM SYS.TABLES WHERE name='test_integrite_tra'; +------+ | L1 | +======+ | 2 | +------+ 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 1

strange bug different count(*) two terminals
by Pierre-Adrien Coustillas 07 Feb '15

07 Feb '15

Hi, I have the same request that I run on the same server, the same database in two different mclient . The results are not the same. There is no activity on the server , no other query running. pcoustillas@groupelemonde:~$ mclient lemonde Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1) Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://groupelemonde:50000/lemonde' Type \q to quit, \? for a list of available commands auto commit mode: on sql>TRACE SELECT count(*) FROM ref__cookies__lemonde__os; +------+ | L1 | +======+ | 97 | +------+ 1 tuple (14.885ms) +-------+---------------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+=====================================================================================================================+ | 2 | X_2 := sql.mvc(); | | 13 | X_3:bat[:oid,:oid] =<tmp_32275>[97] := sql.tid(X_2=0,"sys","ref__cookies__lemonde__os"); | | 32 | X_6:bat[:oid,:int] =<tmp_5355>[97] := sql.bind(X_2=0,"sys","ref__cookies__lemonde__os","id",0); | | 9 | X_9=<tmp_26020>[97] := algebra.leftfetchjoin(X_3=<tmp_32275>:bat[:oid,:oid][97],X_6=<tmp_5355>:bat[:oid,:int][97]); | | 1 | X_10 := aggr.count(X_9=<tmp_26020>[97]); | | 503 | barrier X_31 := language.dataflow(); | | 4 | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_10=97:wrd,""); | | 0 | end s2_4; | | 555 | function user.s2_4(); | | 627 | X_5:void := user.s2_4(); | +-------+---------------------------------------------------------------------------------------------------------------------+ 10 tuples (14.976ms) pcoustillas@groupelemonde:~$ mclient lemonde Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1) Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://groupelemonde:50000/lemonde' Type \q to quit, \? for a list of available commands auto commit mode: on sql>TRACE SELECT count(*) FROM ref__cookies__lemonde__os; +------+ | L1 | +======+ | 0 | +------+ 1 tuple (6.841ms) +-------+------------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+==================================================================================================================+ | 2 | X_2 := sql.mvc(); | | 13 | X_3:bat[:oid,:oid] =<tmp_47161>[0] := sql.tid(X_2=0,"sys","ref__cookies__lemonde__os"); | | 9 | X_6:bat[:oid,:int] =<tmp_4226>[0] := sql.bind(X_2=0,"sys","ref__cookies__lemonde__os","id",0); | | 9 | X_9=<tmp_17466>[0] := algebra.leftfetchjoin(X_3=<tmp_47161>:bat[:oid,:oid][0],X_6=<tmp_4226>:bat[:oid,:int][0]); | | 1 | X_10 := aggr.count(X_9=<tmp_17466>[0]); | | 531 | barrier X_29 := language.dataflow(); | | 21 | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_10=0:wrd,""); | | 0 | end s2_1; | | 727 | function user.s2_1(); | | 868 | X_5:void := user.s2_1(); | +-------+------------------------------------------------------------------------------------------------------------------+ 10 tuples (6.951ms) any ideas ? thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

2 1

Database crash recovery??
by Vijay Krishna 06 Feb '15

06 Feb '15

Hi, While I was running a query today, I got this error today - *Allocation failedGDKmallocmax: failed for 18446744072964793488 bytes* I ignored it thinking it was some runtime error and repeatedly tried to run the same query 3 or 4 times. Finally, mclient got crashed. I tried to reconnect to mclient, but it was not successful. So I tried restarting the database, only to find that the database has already been shut down. When I tried starting the database, I am receiving this error - *starting database 'testDB'... FAILEDstart: starting 'testDB' failed: database 'testDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* I tried after restarting the demon multiple times, but no use. I checked merovingian.log file in the db farm folder, but it showed only this - *2015-02-04 05:59:50 ERR control[7829]: (local): failed to fork mserver: database 'testDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* I tried to check the monetdbd's log file, but I am unable to find the log folder in var. Has the database crashed irrecoverably? How to run MonetDB again with at least a new database?? Any help much appreciated. P.S.: I run MonetDB Oct2014-SP1 version in a CentOS machine with 128 GB RAM. Thanks & Regards, Vijay.

3 2

Disk I/O
by Tim Burress 02 Feb '15

02 Feb '15

Hello, I have a large database that is commonly used for relatively simple SELECT queries. Some of these can take quite a bit of time (hours... overnight) so I've been looking into it a bit. What seems clear is that mserver5 (Oct2014-SP1) is disk-bound, but one curious thing is that most of the time it is write-bound. Despite doing only SELECT queries (no updates or inserts), the amount of data being written is nearly two times as much as data read. Why is that? Is the server restructuring the database on the fly in order to get better performance in later queries? 3X the disk I/O seems excessive so I wonder if there is some way to tune this, or investigate further to see what is happening? Thanks! Tim

6 14

Upserts in MonetDB
by shamsul hassan 29 Jan '15

29 Jan '15

Hi All, I have been looking into UPSERTING records in MonetDB but didn't find anything. Only few years before somebody had asked the same question - https://www.monetdb.org/pipermail/users-list/2011-May/004916.html but no solution for that. Since its been few years from then , so I was wondering if MonetDB does provide the ability to do UPSERTS. Any pointers here will be really helpful. Thanks

2 1

Announcement: New Oct2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Jan '15

29 Jan '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP2 bugfix 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/>. Oct 2014-SP2 bugfix release Client Package * Changes to the Perl interface, thanks to Stefan O'Rear: 1. removes "use sigtrap", because this has global effects and should not be used by modules, only by the application. 2. allows Perl 5.8.1+ Unicode strings to be passed to quote() and included in statements (UTF-8 encoded, as expected by Monet's str module) 3. quote and unquote now use the same quoting rules as the MonetDB server, allowing for all characters except NUL to be round-tripped 4. several character loops have been reimplemented in regex for much greater performance 5. micro-optimizations to the result fetch loop 6. block boundaries are preserved in piggyback data so that Xclose is not appended or prepended to a SQL command 7. diagnostic messages #foo before a result header are ignored, this is necessary to use recycler_pipe 8. fail quickly and loudly if we receive a continuation prompt (or any other response that starts with a non-ASCII character) 9. header lines must start with %, not merely contain %, fixing a bug when querying a table where string values contain % 10. after closing a large resultset, account for the fact that a reply will come and do not lose sync 11. allow a MAPI_TRACE environment variable to dump wire protocol frames to standard output 12. fixes maximum MAPI block size to match the server limit of 16k. previously would crash on blocks larger than 16k SQL * Fixed a typo in a column name of the sys.tablestoragemodel view (auxillary changed to auxiliary). Bug Fixes * 3467: Field aliases with '#' character excise field names in result set. * 3605: relational query without result * 3619: Missing dll on MonetDB Start * 3622: Type resolution error * 3624: insert of incomplete or invalid ip address values in inet column is silently accepted but the values are not stored (they become/show nil) * 3626: casting a type without alias results in program contains errors * 3628: mclient and ODBC driver report 'type mismatch' when stddev_pop used in a select which returns 0 rows * 3629: IF THEN ELSEIF always evaluates the first test as true * 3630: segv on rel_order_by_column_exp * 3632: running make clean twice gives an error in clients/ruby/adapter * 3633: Wrong result for HAVING with floating-point constant * 3640: Missing implementation of scalar function: sql_sub(<date>, <month interval>) * 3641: SQL lexer fails to detect string end if it the last character is U+FEFF ZERO WIDTH NO-BREAK SPACE * 3642: Combined WHERE conditions less-than plus equals-to produce incorrect results * 3643: Missing implementations of scalar function: sql_sub(<timetz>, arg2) * 3644: COPY INTO fails to import "inet" data type when value has prefix length in CIDR notation * 3646: ORDER BY clause does not produce proper results on 'inet' datatype * 3649: recycler crashes with concurrent transactions

2 1

Multiple insert into error
by Art Apl 27 Jan '15

27 Jan '15

Hi! I am trying to convert Lucene index into MonetDB with my app ( https://github.com/hjortron/LuceneToMonetDBConverter) App iterates through all Lucene docs and creates "insert into" query for each. First time problem appears on 80k row(and then dramatically falls down to 250 row), app throws error: INSERT INTO sys."EventTracking"(datetime,Hash,UserIP,EventType,SearchResultState,ViewType,PageNumber,ResultsCount,ElapsedTime) VALUES('2014-12-10 13:22:41','hod','127.0.0.1','Search','>No',0,0,2368195,85) Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [HY000] [MonetDB][ODBC Driver 11.19.7]General error In merovingian.log I have only: "2015-01-26 00:43:03 ERR discovery[846]: cannot retrieve name info: Temporary failure in name resolution", that repeats each 30 minutes. On another instance of MonetDB my app works fine. Please help.

2 2

Database sharding across cluster in MonetDB
by Alastair McKinley 27 Jan '15

27 Jan '15

Hi all, I am wondering if there are any plans to develop horizontal sharding support for MonetDB? There aren't really other open source databases out there that support horizontal sharding with anywhere close to the performance of MonetDB. I realise there are a lot of challenges and scalability issues with a lot of typical DB operators, but for certain use cases that I have seen it could work really well, likely significantly faster than HBase/SparkSQL and it's rivals. Does anyone else think this would be a desirable feature? Best regards, Alastair

1 0

Best background refresh strategy
by Gustavo Brian 27 Jan '15

27 Jan '15

Hi, No changes done to the basic setup. I'm running OLAP queries and not all 54 columns are used in any query, it depends on the query which columns are used. The query is kind of SELECT SUM(),AVG(), SUM() + WHERE + GROUP BY The WHERE columns have a great compression so it's faster to find by them. My last test runs in 340 ms and aggregates between 900 and 2000 rows per result. Care: "select * from table where key = 123456" (not an expert but this is pure OLTP) Same for me, "select * from bigcalendar limit 2" -> Sometimes I have to kill it, :) but it's something that I don't need at all. I'm using MonetDB as a kind of cache of my OLTP system. IMO MonetDB is perfect for OLAP (not only analytical, operational as well! Now I'm working on the refresh approach) if you are looking for a fast search engine by key, maybe a pure key/value-store is more appropriate. Hope this helps. Gustavo Brian Developer -----Original Message----- From: users-list [mailto:users-list-bounces+gustavo.brian=sunhotels.net@monetdb.org] On Behalf Of users-list-request(a)monetdb.org Sent: martes, 27 de enero de 2015 12:00 To: users-list(a)monetdb.org Subject: users-list Digest, Vol 29, Issue 20 Send users-list mailing list submissions to users-list(a)monetdb.org To subscribe or unsubscribe via the World Wide Web, visit https://www.monetdb.org/mailman/listinfo/users-list or, via email, send a message with subject or body 'help' to users-list-request(a)monetdb.org You can reach the person managing the list at users-list-owner(a)monetdb.org When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..." Today's Topics: 1. Re: Best background refresh strategy (shamsul hassan) 2. Join index structure (Joseph D'silva) ---------------------------------------------------------------------- Message: 1 Date: Mon, 26 Jan 2015 11:44:49 +0000 From: shamsul hassan <shamsulbuddy(a)gmail.com> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Best background refresh strategy Message-ID: <CAONo-dtZpv4LfhKrM+cCh7tZcsDM4daTU80rLigcQR-ruYEbag(a)mail.gmail.com> Content-Type: text/plain; charset="utf-8" *338 million rows / 54 columns table and ran queries in ms* -- WOW .. Do you mind sharing your server configuartion and the setting for mserver5. Also what kind of queries are you firing .. ?? I find it bit slow on line level queries , something like select * from table where key = 123456 Thanks On Sun, Jan 25, 2015 at 6:56 AM, Martin Kersten <martin(a)monetdb.org> wrote: > On 25/01/15 06:48, Gustavo Brian wrote: > >> Hi, >> > Hi, > > thanks for the interest and appraisal. > >> >> I'm trying monetdb as a bigtable for calendar based queries. >> I've created a 338 million rows / 54 columns table and ran queries in ms. >> That's perfect! >> I wonder to see if monetdb is suitable for a search service with high >> traffic (several hundred request per second) and which will the best >> data update strategy because during the "updates/insert/copy into" >> actions everything gets blocked. >> > Queries should not be a problem, but for updates you should read and > understand > https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme > https://www.monetdb.org/Documentation/Manuals/SQLreference/Transaction > s > > I would like to know how cluster mode will help as well. >> > Depends on the complexity of the query and your machine specs. > regards, Martin > > >> Thanks, >> Gustavo Brian >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list >

1 0

Join index structure
by Joseph D'silva 26 Jan '15

26 Jan '15

Hello, Does anyone know the Join index structure used in the database ? (implementation) and how it is being utilized by the optimizer ? I couldn't find any documentation / papers out there other than a single sentence mentioned at the end of this page, which is not much. https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers/Landscape "*Foreign Key replacements Goal: to improve multi-attribute joins over foreign key constraints Rationale: the code produced by the SQL frontend involves foreign key constraints, which provides many opportunities for speedy code using a join index. Impact: large Status: Implemented in the SQL strategic optimizer.*" Thanks Joseph

1 0

Best background refresh strategy
by Gustavo Brian 26 Jan '15

26 Jan '15

Hi, I'm trying monetdb as a bigtable for calendar based queries. I've created a 338 million rows / 54 columns table and ran queries in ms. That's perfect! I wonder to see if monetdb is suitable for a search service with high traffic (several hundred request per second) and which will the best data update strategy because during the "updates/insert/copy into" actions everything gets blocked. I would like to know how cluster mode will help as well. Thanks, Gustavo Brian

3 2

Not utilising Mutithreads
by shamsul hassan 22 Jan '15

22 Jan '15

Hi All, I have recently install monetdb "OCT 2014-SP1" release on my Oracle Enterprise 6 Linux server. Server configuration - 8 CPU Cores 64 GB RAM I have loaded a data set with 70 columns having 15 Millions Rows. I have started mserver5 as below -- mserver5 --debug=10 --set *gdk_nr_threads=8* --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set mapi_usock=/data/monetdb/testdb/.mapi.sock --set monet_vault_key=/data/monetdb/testdb/.vaultkey --set sql_optimizer=default_pipe --set max_clients=64 Now I have ran a query as below -- select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group by product_type; This query took around 90 Secs .. So I started tomograph and checked plotted graph ... which shows the parallelism usage as 39.9 % only and its utilizing only 1 thread with maximum time went in the step - group.subgroupdone almost 98%. My Question is that even I have started the server with gdk_nr_threads=8 , then why it is just using single thread. More info -- mserver5 --version MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 63.0GiB available memory, 8 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: root@localhost (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Any pointers ?? Thanks

4 7

MonetDB Sharding
by gms_xy＠gmx.de 21 Jan '15

21 Jan '15

As mentioned in previous posts, I have some use cases for MonetDB, which act on tables with a billion rows. MonetDB works fast and stable on straight SELECT statements or aggregates on single tables Problems occured when doing big joins, which should produce a result set of 30 rows only, but running hours before failing. SELECT tbl1.*, tbl2.* FROM tbl1, tbl2 WHERE tbl1.xid = tbl2.xid AND tbl1.pk = 12345678 (same behavior, if written with JOIN explicitly) So I am wondering, if I can help MonetDB with partitioning or sharding. Asked already here: https://www.monetdb.org/pipermail/developers-list/2014-October/004325.html What is the state regarding manual partitioning? During last weeks I saw significant acceleration with sharding on other DBMS. Is there anybody, who can give some introduction or share experiences with sharding on MonetDB? 3rd party tools? What ist the maximum number of tables within a MonetDB database? What is the estimated overhead of a single table, multiplied a million times? Thank you Robert

3 2

GDK
by Brian Hood 20 Jan '15

20 Jan '15

Hi Guys, I have seen it around so much in Monetdb what does the acronym gdk actually stand for ? Regards, Brian Hood

2 1

Re: users-list Digest, Vol 29, Issue 14
by bhuvan Mitra 20 Jan '15

20 Jan '15

Thank you stefan. Is there any way through which I can get to know about those implementations or buying it from actian is the only way? Actually I want to contribute to c-store, open source column oriented database in this compression/decompression area. This answer from you will be very worthy to me. Thanks in advance. On Mon, Jan 19, 2015 at 9:29 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: users-list Digest, Vol 29, Issue 12 (Stefan Manegold) > 2. Next release? (g4(a)novadsp.com) > 3. Re: Not utilising Mutithreads (shamsul hassan) > 4. Re: Next release? (Sjoerd Mullender) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 19 Jan 2015 14:58:13 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: users-list Digest, Vol 29, Issue 12 > Message-ID: <895297092.253122.1421675893625.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > > Hi, > > MonetDB and Actian/VectorWise are two different systems that do not share > any code. > > MonetDB does not use any compression in the way Actian/VectorWise does. > > Best, > Stefan > > > ----- Original Message ----- > > Thank you Martin. > > So, did monetdb bought the codes from actian code base and included in > it's > > source code? > > Also kindly tell me if monetdb is following any other compression > techniques > > apart from pfor, pfor-delta and pdict. > > Thanks in advance. > > > > On Mon, Jan 19, 2015 at 4:30 PM, < users-list-request(a)monetdb.org > > wrote: > > > > > > Send users-list mailing list submissions to > > users-list(a)monetdb.org > > > > To subscribe or unsubscribe via the World Wide Web, visit > > https://www.monetdb.org/mailman/listinfo/users-list > > or, via email, send a message with subject or body 'help' to > > users-list-request(a)monetdb.org > > > > You can reach the person managing the list at > > users-list-owner(a)monetdb.org > > > > When replying, please edit your Subject line so it is more specific > > than "Re: Contents of users-list digest..." > > > > > > Today's Topics: > > > > 1. Re: Not utilising Mutithreads (Martin Kersten) > > 2. regarding source code (bhuvan Mitra) > > 3. Re: regarding source code (Martin Kersten) > > 4. information about compression/decompression techniques of > > monetdb (bhuvan Mitra) > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Sun, 18 Jan 2015 12:20:15 +0100 > > From: Martin Kersten < martin(a)monetdb.org > > > To: users-list(a)monetdb.org > > Subject: Re: Not utilising Mutithreads > > Message-ID: < 54BB96EF.3030906(a)monetdb.org > > > Content-Type: text/plain; charset=windows-1252; format=flowed > > > > Hi > > > > Each MAL instruction is executed by a single thread. > > Some operators require the complete input to be available, > > such as in your case the group.subgroup(). > > > > martin > > > > On 18/01/15 01:27, shamsul hassan wrote: > > > Hi All, > > > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > > > Enterprise 6 Linux server. > > > > > > Server configuration - > > > > > > 8 CPU Cores > > > 64 GB RAM > > > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > > > I have started mserver5 as below -- > > > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb > > > --set mapi_open=true --set mapi_port=54321 --set > > > merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > > > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > > > sql_optimizer=default_pipe --set max_clients=64 > > > > > > Now I have ran a query as below -- > > > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > > > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' > group > > > by product_type; > > > > > > This query took around 90 Secs .. > > > So I started tomograph and checked plotted graph ... which shows the > > > parallelism usage as 39.9 % only and its utilizing only 1 thread with > > > maximum time went in the step - group.subgroupdone almost 98%. > > > > > > My Question is that even I have started the server with > gdk_nr_threads=8 , > > > then why it is just using single thread. > > > > > > More info -- > > > > > > mserver5 --version > > > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > > > Copyright (c) 1993-July 2008 CWI > > > > > > Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved > > > > > > Visit http://www.monetdb.org/ for further information > > > > > > Found 63.0GiB available memory, 8 available cpu cores > > > > > > Libraries: > > > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips > 11 > > > Feb 2013) > > > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > > > Any pointers ?? > > > > > > Thanks > > > > > > > > > > > > _______________________________________________ > > > users-list mailing list > > > users-list(a)monetdb.org > > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > > > > > > ------------------------------ > > > > Message: 2 > > Date: Mon, 19 Jan 2015 12:28:05 +0530 > > From: bhuvan Mitra < bhuvanrjb(a)gmail.com > > > To: users-list(a)monetdb.org > > Subject: regarding source code > > Message-ID: > > < CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com > > > Content-Type: text/plain; charset="utf-8" > > > > Hello, > > > > I am very interested to know and learn about implementations of > > compression/decompression techniques that monetdb is following. I have > seen > > the monetdb's light weight compression algorithms(pfor, pfor-delta, > pdict), > > cache involved decompression techniques and excited to know how actually > > are they implemented in form of codes. Kindly tell me in which > folder/file > > is the source code of this compression/decompression algorithms present. > I > > have downloaded the source tarball from the website and could not find > > these code snippets. Plese help me to identify the path. > > > > Regards, > > mg > >

2 1

Next release?
by g4＠novadsp.com 19 Jan '15

19 Jan '15

Hello - is there any timeframe for a next release? There are a couple of post Oct2014-SP1 bug-fixes which would be really good to have included. Many thanks.

2 1

Re: users-list Digest, Vol 29, Issue 12
by bhuvan Mitra 19 Jan '15

19 Jan '15

Thank you Martin. So, did monetdb bought the codes from actian code base and included in it's source code? Also kindly tell me if monetdb is following any other compression techniques apart from pfor, pfor-delta and pdict. Thanks in advance. On Mon, Jan 19, 2015 at 4:30 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: Not utilising Mutithreads (Martin Kersten) > 2. regarding source code (bhuvan Mitra) > 3. Re: regarding source code (Martin Kersten) > 4. information about compression/decompression techniques of > monetdb (bhuvan Mitra) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 18 Jan 2015 12:20:15 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Not utilising Mutithreads > Message-ID: <54BB96EF.3030906(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi > > Each MAL instruction is executed by a single thread. > Some operators require the complete input to be available, > such as in your case the group.subgroup(). > > martin > > On 18/01/15 01:27, shamsul hassan wrote: > > Hi All, > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > Enterprise 6 Linux server. > > > > Server configuration - > > > > 8 CPU Cores > > 64 GB RAM > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > I have started mserver5 as below -- > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 > --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > sql_optimizer=default_pipe --set max_clients=64 > > > > Now I have ran a query as below -- > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group > by product_type; > > > > This query took around 90 Secs .. > > So I started tomograph and checked plotted graph ... which shows the > parallelism usage as 39.9 % only and its utilizing only 1 thread with > maximum time went in the step - group.subgroupdone almost 98%. > > > > My Question is that even I have started the server with gdk_nr_threads=8 > , then why it is just using single thread. > > > > More info -- > > > > mserver5 --version > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > Copyright (c) 1993-July 2008 CWI > > > > Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved > > > > Visit http://www.monetdb.org/ for further information > > > > Found 63.0GiB available memory, 8 available cpu cores > > > > Libraries: > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL > 1.0.1e-fips 11 Feb 2013) > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > Any pointers ?? > > > > Thanks > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > ------------------------------ > > Message: 2 > Date: Mon, 19 Jan 2015 12:28:05 +0530 > From: bhuvan Mitra <bhuvanrjb(a)gmail.com> > To: users-list(a)monetdb.org > Subject: regarding source code > Message-ID: > < > CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hello, > > I am very interested to know and learn about implementations of > compression/decompression techniques that monetdb is following. I have seen > the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), > cache involved decompression techniques and excited to know how actually > are they implemented in form of codes. Kindly tell me in which folder/file > is the source code of this compression/decompression algorithms present. I > have downloaded the source tarball from the website and could not find > these code snippets. Plese help me to identify the path. > > Regards, > mg >

2 1

information about compression/decompression techniques of monetdb
by bhuvan Mitra 19 Jan '15

19 Jan '15

I have been studying about various column-oriented databases and found monetdb to be very interesting as it is efficient and open source. Please tell me in which file/folder does source code of compression/decompression techniques written in monetdb. I am interested to learn the implementations of light weight compression algorithms and cache-decompression. I have source code of monetdb downloaded, just let me know the path of requested codes.

1 0

regarding source code
by bhuvan Mitra 19 Jan '15

19 Jan '15

Hello, I am very interested to know and learn about implementations of compression/decompression techniques that monetdb is following. I have seen the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), cache involved decompression techniques and excited to know how actually are they implemented in form of codes. Kindly tell me in which folder/file is the source code of this compression/decompression algorithms present. I have downloaded the source tarball from the website and could not find these code snippets. Plese help me to identify the path. Regards, mg

2 1

analyse performance slow query
by Pierre-Adrien Coustillas 16 Jan '15

16 Jan '15

Hi, I try to analyze the slowness of a query with EXPLAIN and stethoscope The query is : SELECT count (*) FROM ( SELECT id_unique, siteniveau1, siteniveau2, '0000-00-00', 0, 0, MAX( debutvisite ), 0, 0 FROM tmp __ at __ lm __visites_ agregees _id_unique GROUP BY id_unique,siteniveau1,siteniveau2 ) tbl ; the beginning of explain is ( there is 272 tuples ) +============================================================================================================================================================================================+ | function user.s2_5{ autoCommit = true }(A0: str ,A1: bte ,A2: bte ,A3: bte ,A4: bte ): void ; | | barrier X_429 := language . dataflow (); | | X_7 := sql . mvc (); | | X_55:bat[: oid ,: oid ] := sql . tid (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique",0,8); | | X_70:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",0,0,8); | | (X_78:bat[: oid ,: oid ] ,X_79:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",2,0,8); | | X_161 := sql .delta(X_70,X_78,X_79); | | X_169 := algebra . leftfetchjoin (X_55,X_161); | | X_96:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",0,0,8); | | (X_111:bat[: oid ,: oid ] ,X_112:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",2,0,8); | | X_184 := sql .delta(X_96,X_111,X_112); | | X_193 := algebra . leftfetchjoin (X_55,X_184); | | X_129:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",0,0,8); | | (X_144:bat[: oid ,: oid ] ,X_145:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",2,0,8); | | X_201 := sql .delta(X_129,X_144,X_145); | | X_209 := algebra . leftfetchjoin (X_55,X_201); | | (X_224,X_225,X_226) := group. subgroup (X_209); | | (X_248,X_249,X_250) := group. subgroup (X_193,X_224); | | (X_272,X_273,X_274) := group. subgroupdone (X_169,X_248); | | X_275 := algebra . leftfetchjoin (X_273,X_169); | | X_304 := algebra . leftfetchjoin (X_273,X_248); | | X_331 := algebra . leftfetchjoin (X_304,X_249); group. subgroupdone (X_169,X_248); set 10 minutes to run . Why ? how to understand this function ? 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 2

MSVCR80.dll Error Question
by Zola Javzmaa 16 Jan '15

16 Jan '15

Hello All, I am having trouble installing Monetdb on to my computer. Would anybody have any suggestions to fixing it? I'm on a Window 7, 64 bit operating system and I get the following error message when I try to load the server program: "Error message: This application has failed to start because MSVCR80.dll was not found. Re-installing the application might fix the problem." Thanks, Zola

3 2

rename database
by Pierre-Adrien Coustillas 15 Jan '15

15 Jan '15

Hi, Is it possible to rename a database ? I thought to do like this: monetdb stop database mv /var/lib/monetdb/database /var/lib/monetdb/new_database monetdb start new_database it's okay ? 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

4 3

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

14 Jan '15

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

3 2

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

09 Jan '15

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

1 0

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

06 Jan '15

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

5 8

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

05 Jan '15

Hi, I have been working on join performances with MonetDB. I tried using various optimizer pipelines. Few costly queries which took 15 seconds with the default pipeline, returned results as fast as 5 seconds with the 'no_mitosis_optimizer' pipeline. I am looking to study more on mitosis optimizer. Is there any reference on what does it do? >From the mserver5 man page, I got this - "forcefully activate mitosis even on small tables, i.e., split small tables in as many (tiny) pieces as there are cores (threads) available;" So, does this mean with the mitosis optimizer, the tables are split and processed? If so, then why are queries slower with mitosis optimizer in the pipeline? Also, from the monetdb man page, I was alerted that "Changing this setting is discouraged at all times." What is the disadvantage of changing the optimizer pipeline to something other than the 'default_pipe'? Though the 'no_mitosis_optimizer' is stable, is it worth for production? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 3

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

28 Dec '14

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

2 2

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

24 Dec '14

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

4 3

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

23 Dec '14

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

2 2

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

17 Dec '14

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

2 1

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

15 Dec '14

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

3 7

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

14 Dec '14

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

2 1

RAM usage of MonetDB
by Vijay Krishna 13 Dec '14

13 Dec '14

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

2 1

Inserting arrays with SciQL
by Dimitar Misev 11 Dec '14

11 Dec '14

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

1 1

Re: inner join very slow
by Stefan Manegold 08 Dec '14

08 Dec '14

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

2 3

COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead
by Pierre-Adrien Coustillas 08 Dec '14

08 Dec '14

Hello, is this a bug or a feature I do not understand ? I have a script bash : ------------------------------------ pcoustillas@monetdb:~$ cat bug_commit.sh #!/bin/bash i=1 while [ $i -le 3 ] do create="CREATE TABLE tmp_schampy_odc__lemondeclient_$i AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA " echo $create mclient lemonde --statement="$create" & i=$(($i+1)) done ------------------------------------ I monetdb restarts before : service monetdb5-sql stop ; sleep 10 ; service monetdb5-sql start And i run script : pcoustillas@monetdb:~$ ./bug_commit.sh CREATE TABLE tmp_schampy_odc__lemondeclient_1 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_2 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_3 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA pcoustillas@monetdb:~$ operation successful operation successful COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead operation successful why is there a conflict while they are different tables that are created? I use the last version Database: MonetDB v11.19.7 (Oct2014-SP1) I have the same bug on : MonetDB v11.17.21 (Jan2014-SP3) Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 4

Grant select on all tables
by Dennis Pallett 08 Dec '14

08 Dec '14

Hi all, I'm playing around with the authorisation/privileges functionality in MonetDB and I'm wondering if it's possible to grant a user the SELECT privilege on ALL tables (present and future tables) in a schema? At the moment I'm only able to grant this privilege on specific tables and I'd ideally like to create a user with full SELECT privileges and nothing more or less. Best regards, Dennis

1 1

SciQL-2 branch
by Dimitar Misev 04 Dec '14

04 Dec '14

Hi, I wanted to try SciQL so I got the SciQL-2 branch, but unfortunately it doesn't seem to be working. Following the tutorial at https://www.monetdb.org/Documentation/UserGuide/Tutorial I got stuck at $ mclient -u monetdb -d voc password: 3F000!CREATE PROCEDURE: no such schema 'rs' ParseException:SQLparser:3F000!CREATE PROCEDURE: no such schema 'rs' The default branch works ok. Any ideas on this problem, is there maybe a specific commit known to work? Thanks, Dimitar

4 9

Release Disk Space
by baraa Mohamad 03 Dec '14

03 Dec '14

Hello, I'm using MonetDB (on ubuntu) for doing some performance tests. I had added and deleted a lot of tables and right now I have no more free disk space since it seems that MonetDB does not clean up disk space after dropping tables. I would like to know the best way to clean up (as much as possible) disk space on MonetDB. Thank you for your help,Baraa

2 2

Error in mserver5 standalone
by Qing Guo 02 Dec '14

02 Dec '14

Hello, I was doing a performance profiling on MonetDB server so I run mserver5 in standalone. The command line I used is: mserver5 --dbpath=[path_to_your_db] --set mapi_open=false --set mapi_port=0 --set single-user=yes --set gdk_nr_threads=1 --set max_clients=1 --set sql_optimizer=default_pipe --set monet_daemon=no < [input] It worked for previous releases but not for the Oct2014. Instead, it gives the following message: "error initSQLreferences" The problem appears that "algebraRef" is not assigned a value before calling "initSQLreferences(void)". Any ideas to solve this problem? Much appreciate, thanks! Kevin

2 2

GROUP BY with JOIN produces unexpected results
by Jonas Goerlich 02 Dec '14

02 Dec '14

Hello, I am using MonetDB in a university project dealing with analysis of twitter data. "stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....) What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query: SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long; The result set however still contains rows with duplicate values for "lat" and "long", e.g. userid lat long ----------------------------- 19703 41 -74 19703 41 -74 If I remove the INNER JOIN part from the query, the grouping works as expected. To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...) Can anybody point me in the right direction? Am I doing something wrong here? Best regards and thanks in advance, Jonas

2 2

inner join very slow
by Pierre-Adrien Coustillas 01 Dec '14

01 Dec '14

Hello, With this query : SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres SUB ON SRC.siteniveau1=SUB.site AND SRC.chapitreniveau1=SUB.idchapitre1; SELECT count(*) FROM tmp__pierre2; +------------+ | L1 | +============+ | 1 061 964 254 | SELECT count(*) FROM ref__at_lm_chapitres; +---------+ | L1 | +=========+ | 1 331 867 | after several hours of running the query is still not finished and I stopped how to find out where the problem? thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

1 0

espace disk after crash
by Pierre-Adrien Coustillas 28 Nov '14

28 Nov '14

Hello, I try to understand the use of disk space after disk full. I look at the files in the folder base /bat/ root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/676.* -rw------- 1 monetdb monetdb 1579876352 oct. 15 17:42 bat/06/676.tail -rw------- 1 monetdb monetdb 3606183936 nov. 28 15:03 bat/06/676.theap And i look sys.storage SELECT location,columnsize,heapsize FROM sys.storage WHERE location LIKE '06/676'; +----------+------------+------------+ | location | columnsize | heapsize | +==========+============+============+ | 06/676 | 1579850648 | 3606183936 | 676.tail is columnsize column 676.theap is heapsize column but I have 2 large file (> 100 Go) root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/670.* -rw------- 1 monetdb monetdb 120641335296 nov. 28 14:28 bat/06/670.tail root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/636.* -rw------- 1 monetdb monetdb 144702111744 nov. 28 12:32 bat/06/636.tail sql>select location,columnsize,heapsize from sys.storage WHERE location IN ('06/636','06/670'); 0 tuples (34.050ms) the modification time of two files is identical to the full disk on my server. Is what I can delete these 2 files ? 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

1 0

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

28 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

5 17

handling of (indirect) division by zero: nulls or an error?
by g4＠novadsp.com 27 Nov '14

27 Nov '14

I posted earlier about a problem with 'type mismatch' being reported. The root cause is quite simple: it is returned if the stddev_pop() function is used in a select that returns zero rows. Given that the count of the population (N) is used as the denominator in the calculation this makes sense. A question arising then is this. If one has assembled a more complex list of aggregate functions in a query, would it be better for null to be returned instead of an error? i.e. select count(weight), sum(weight), average(weight) where (no rows are selected) => 0,null,null Should this not also be the case if stddev_pop is included? select count(weight), sum(weight), average(weight), stddev_pop(weight) where (no rows are selected) => 0,null,null,null Instead of 'type mismatch' ? TIA. J.

1 0

Announcement: New Oct2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Nov '14

27 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP1 bugfix 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/>. Oct 2014-SP1 bugfix release MonetDB5 Server * Fixed adding of 0 intervals to dates. Build Environment * The libraries included in the Windows installers have been upgraded. We now use libxml2-2.9.2, openssl-1.0.1j, pcre-8.36, and zlib-1.2.8. MonetDB Common * Implemented a change to the way in which string bats are appended. We now try harder to limit the growth of the string heap. * Fixed some problems with BATsample. It was possible for BATsample to return a value that was just beyond the end of the sampled BAT. Also, on some systems the range of the rand() function is rather limited (0..32767) and trying to get a sample larger than this range would result in an infinite loop. SQL * Fixed sys.queue() implementation to report on other queries being executed. * A number of bugs were fixed in the code to upgrade a database from previous releases. This version should fix the upgrade of a database that had been upgraded to the Oct2014 release, but also properly upgrade directly from Jan2014 and Feb2013 releases. Bug Fixes * 3580: cosmetic change (append newline) * 3609: Incorrect use of generate_series * 3611: quantile() and median() commands crash when used 2x on the same variable on a null table * 3612: assertion failure when deleting rows from table to which a FK constraint is defined * 3620: ORDER BY broken when using UNION ALL * 3621: Hexadecimal literal vs decimal literal

1 0

'type mismatch' error
by g4＠novadsp.com 26 Nov '14

26 Nov '14

Using both mclient and C#/C++ test clients connected via 64bit ODBC I am encountering this type mismatch error for no obvious reason. This is something of a show-stopper for what had proved to be a very interesting experiment! # MonetDB 5 server v11.19.3 "Oct2014" # Serving database 'dbfarm', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 15.982 GiB available main-memory. An example query below, weight is a double, all other columns are integer. There are no NULL values, nor any oddities sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3)); Type mismatch But a family of closely related queries do succeed: sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 11)); | L1 | L2 | L3 |> | 290517 | 290517 | 1 | 1 tuple (200.115ms) !1 column dropped! Enabling ODBC logging does not reveal a great deal more than this: dbc-con.vshost 13d8-2ac4 ENTER SQLExecDirectW HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 odbc-con.vshost 13d8-2ac4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 DIAG [HY000] [MonetDB][ODBC Driver 11.19.3]Type mismatch (0) odbc-con.vshost 13d8-2ac4 ENTER SQLGetDiagRecW SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 SQLINTEGER * 0x000000001C39DB34 SQLWCHAR * 0x000000001B6246C0 SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 odbc-con.vshost 13d8-2ac4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 [ 5] "HY000" SQLINTEGER * 0x000000001C39DB34 (0) SQLWCHAR * 0x000000001B6246C0 [ 43] "[MonetDB][ODBC Driver 11.19.3]Type mismatch" SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 (43)

1 0

Loading RDF data
by Rinke Hoekstra 26 Nov '14

26 Nov '14

Hi All, After some fiddling, I managed to compile and install the latest version of MonetDB with RDF support enabled (turns out it requires an old version of Raptor... 1.4). My next step would be to load some actual RDF data... but I cannot find any mention of how to do this on the MonetDB website, nor in the mailinglist archives. Pointers very welcome. Thanks, Rinke ---- Rinke Hoekstra VU University Amsterdam/University of Amsterdam

1 0

corrupt catalog Oct 2014
by Pierre-Adrien Coustillas 26 Nov '14

26 Nov '14

Hello, we have again duplicated table on version Oct2014. pcoustillas@ monetdb :~$ mclient lemonde Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014) Database : MonetDB v11.19.3 (Oct2014), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql >\d src __ at __ lm __visites_ agregees table sys . src __ at __ lm __visites_ agregees is not unique, corrupt catalog ? sql > echo " SELECT name FROM SYS .TABLES WHERE system = false ;" | mclient lemonde | grep " " | grep -v " name " | grep -v " tuple " | sed 's /[| ]// g' | sort | uniq -c | grep -v " 1 " 2 src __ at __ lm __visites_ agregees 2 src __ at __ tra __visites_ agregees 2 tmp _ lm _ suppr _id 4 tmp _ lm _ suppr _id_unique 2 tmp _ tra _ suppr _id 4 tmp _ tra _ suppr _id_unique we do a lot of drop and create table . Is the trouble can come of it ? it must take a sleep after a drop ? 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 1

support for window aggregates and window frames ...
by Jay Prasad 24 Nov '14

24 Nov '14

Does MonetDB support window aggregates and window frames? >From the documentation it looks like it supports only window rank functions. Queries of the form: SELECT saleyear, salequarter, sum(saleamount), sum(saleamount) OVER ( PARTITION BY saleyear ORDER BY salequarter ASC) AS partitionedsaleamount FROM ds_1108_null_advent_1580276965 group by saleyear, salequarter; fail with this error: SELECT: function 'sum' not found However, the code seems to include branches that process window aggregates. See sql_parser.y (line 3530) and rel_select.c (line 4658). I am also seeing a similar issue with window frames: SELECT saleyear, salequarter, EXTRACT (MONTH FROM orderdate) AS monthofsale, SUM(saleamount) AS totalsales, RANK() OVER ( PARTITION BY monthofsale ORDER BY SUM(saleamount) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS rank FROM ds_1108_null_advent_1580276965 GROUP BY saleyear,salequarter,monthofsale ORDER BY saleyear ASC LIMIT 20; TypeException:user.s16_1[60]:'algebra.subsort' undefined in: (_103:any, r1_103:any, r2_103:any) := algebra.subsort(_92:int, r1_100:bat[:oid,:oid], r2_100:bat[:o id,:oid], _83:bit, _99:bit) program contains errors NOTICE: This e-mail message and any included attachments are from Infogix, Inc. ("Infogix") and are intended solely for use by the individual(s) to whom the message was addressed. The information contained herein may include privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using the information contained in this message is strictly prohibited. If you have received this message in error, or have reason to believe that you are not authorized to receive it, please promptly notify the sender by e-mail, delete the message from your computer, and do not copy or disclose the information to anyone else. If you properly received this e-mail as an addressee, please maintain its contents in confidence to protect confidentiality. Thank you.

1 0

How to use JSON in MonetDB
by Art Apl 24 Nov '14

24 Nov '14

Hello! MonetDB documentation says, that JSON is supported in MonetDB, but I can`t find any example. When I try to implement this query in MonetDB client: json.filter({ "store": { "bicycle": { "color": "red", "price": 19.95 }}},'$') I get this error: syntax error, unexpected IDENT in: "json" THX

2 4

JDBC driver: Statement#setMaxRows()
by Robert Koch 24 Nov '14

24 Nov '14

I have big tables with billions of rows. If I click accidentally on DBVisualizer's data tab, or, if the data tab of a small table is opened and I click on a big table, DBVisualizer tries to retrieve all rows. Then I have to kill DBVisualizer process and/or the DB server process. Answer of DBVisualizer's team: "Hi Robert, It seems like the JDBC driver does not implement the Statement#setMaxRows() method. Please contact your driver developer about this problem." Is the Statement#setMaxRows() method implemented?

2 2

Resource on the PHP API for monetdb
by Leon-Constantin Fopa 24 Nov '14

24 Nov '14

Hi every one, I'm interested in accessing monetdb from a PHP script. Can someone provide me a link on resource with introduce that. The research of the keyword "monetdb" from the the http://php.net website search engine give no related result. thanks in advance, Best regards, Fopa Léon Constantin

3 3

Whicht GUIs/Frontends are you using? / MonetDB support in DBVisualizer
by Robert Koch 24 Nov '14

24 Nov '14

Which tools are you using? As mentioned, I am using DBVisualizer for work with MonetDB. I asked for a better support of MonetDB in DBVisualizer: "MonetDB is the leading open-source, column-oriented, analytical in-memory database. MonetDB's community is growing and MonetDB does not come along with a frontend. It would be great to have a native support out of DBVis." This is the answer: "Hi Robert, We have an open ticket for addressing support for MonetDB in DbVisualizer. Currently the number of requests for it is low so I cannot say when or if it will happen. Most of the database specific support is expressed in a so called database profile in DbVisualizer. This is a single XML file which defines up to 80% of the functionality for a database. The database profile framework is documented in the users guide and if you are looking for improved MonetDB support I suggest you look into building a database profile for it. http://confluence.dbvis.com/display/UG91/Database+Profiles If you have any related questions please don't hesitate to contact us." I somebody out there, who is interested in MonetDB support in DBVisualizer, willing to build a database profile XML file?

1 0

JDBC driver: sorting columns? - DatabaseMetaData#getColumns()
by Robert Koch 24 Nov '14

24 Nov '14

I am using DBVisualizer via JDBC to work with MonetDB. When exporting tables with DBVisualizers @export function, columns are sorted alphabetically, which causes problems when LOADING the exported csv into another database, and the import tool is expecting the original order of columns. I wrote to the DBVisualizer team, they answered as follows: "Hi Robert, Export uses the column order reported by the JDBC driver, so please consult your driver developer to see if you can get the driver fixed. More specifically, DbVisualizer uses the column order as reported by the DatabaseMetaData#getColumns() method." If MonetDB JDBC driver is actually sorting columns, could you please return the given order of table columns?

2 1

To:11/19/2014 6:25:44 PM 1
by drrock1982 19 Nov '14

19 Nov '14

http://teamspl.com.sg/kq/vzxadbrpevtcdjxfltlghewxx.kyorlorbxfhoubwawxvbtvgf… drrock1982(a)rocketmail.com

1 0

Multithreaded prob
by Doug Service 18 Nov '14

18 Nov '14

I have installed MonetDB and and started using it through MAPI. The documentation indicates that opening multiple connections from separate threads in a single process is supported. I perused the MAPI source and it appears that all of the Monet data structures used are associated with the connection. In our application the threads will be independent and processing similar but unique data sets each with its own connection to the database. No threads will share a database connection nor attempt to use another threads connection. Does anyone have experience using multiple threads in a single process each accessing a separate database MonetDB MAPI connection they would like to share? Did you have any problems? Doug Service

5 6

CREATE TEMPORARY TABLE WITH DATA seems not working - Jan2014-SP3
by Satoshi Nagayasu 18 Nov '14

18 Nov '14

Hi all, As shown below, despite CREATE TABLE WITH DATA works fine, CREATE TEMPORARY TABLE WITH DATA is not able to create a temp table with data. --------------------------------------------------------- $ mclient mydb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/mydb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE TABLE t2 AS SELECT 1 WITH DATA; operation successful (1.143ms) sql>SELECT * FROM t2; +------+ | v | +======+ | 1 | +------+ 1 tuple (0.453ms) sql>CREATE TEMPORARY TABLE t AS SELECT 1 WITH DATA; operation successful (0.985ms) sql>SELECT * FROM t; +---+ | v | +===+ +---+ 0 tuples (0.439ms) sql> --------------------------------------------------------- Is this a bug? or something I misunderstand? Regards, -- NAGAYASU Satoshi <snaga(a)uptime.jp>

2 2

[ftin.india.altair.com] Validate Email
by ashishkumar.singh＠altair.com 17 Nov '14

17 Nov '14

Validate Email Address ====================== Email validation to download secure attachment at ftin.india.altair.com. Please click on the following link to view the message: https://ftin.india.altair.com:8443/validate/eJCZlisrascBO00ffEVsQu/LasnXi5p… Or alternatively, enter this validation codein the browser window where you entered your email address: LasnXi5p0Y6ExdEYOSxmmU This code and link is valid for 20 minutes from the request, until 17:23. Please click on the original link in the message email if the link has expired to request a new validation email.

1 0

Operations on Network Addresses
by Tim Burress 14 Nov '14

14 Nov '14

Hello, I'm a relatively new user and have run into a problem which may just be a misunderstanding. I'm using the Oct2014 version, installed on Fedora 20 from the MonetDB rpm repository. When I try to query a database using inet "containment" operators like << or <<= it seems that the operators are not recognized: sql> select src,dst from raw.ids where src << inet'xxx.xxx.xxx.xxx/27'; SELECT: no such binary operator 'left_shift(inet,inet)' sql> select src,dst from raw.ids where src <<= inet'xxx.xxx.xxx.xxx/27'; syntax error, unexpected '=' in: "select src,dst from raw.ids where src <<=" Other ordinary binary inet operators seem OK: sql> select src,dst from raw.ids where src = inet'xxx.xxx.xxx.xxx'; (prints out table) sql> select src,dst from raw.ids where src < dst; (prints out table) However the documented functions do not seem to be recognized: sql> select masklen(inet'192.168.0.0/24') SELECT: no such unary operator 'masklen(inet)' sql> select netmask(inet'192.168.0.0/24') SELECT: no such unary operator 'netmask(inet)' So it seems that support for network address types is only partially available in this release. Is that normal? Do I need to rebuild from source in order to get them? Thanks! Tim

3 7

Security Precautions Of Using Monet DB
by kannan rbk 13 Nov '14

13 Nov '14

Hi, I am started using of MonetDB in our development environment, we decided to move this to our production environment. Before deploying into production environment, I want to know about existing security vulnerabilities and precautions to be taken care of using MonetDB. I had searched in www.monetdb.org, but I haven't found required things. Could you please suggest me any security advisory documents or help guide? I can't find anything in nvd.nist.gov vulnerability database. -- Regards, Kannan RBK

2 2

Struggling with the MINUS operator
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! Here is the query I ran : sql> SELECT s,o FROM "prop_XXX" MINUS SELECT s,o FROM "class_XXX" ; and here is the error I got : syntax error, unexpected SELECT, expecting SCOLON in: "select s,o from "prop_XXX" minus select" Did I missed something ? best regards,

1 1

how to obtain the number of row affected by an INSERT query ?
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! When using the user-interface mclient it quite useful to see the numbers of rows affected by your INSERT query. sql>INSERT INTO ..... 125 affected rows (832.608ms) I wonder if there is a way to obtain this information within a server-side SQL program in MonetDB like Oracle does with SQL%ROWCOUNT in PG/SQL? thanks in advance !

1 0

Monet Bulk load does not work from Java after upgrade to Oct2014
by Radovan Bičiště 13 Nov '14

13 Nov '14

Hello, I hate to be a bugger again. :( We use Pentaho PDI (KETTLE) for loading data from our Oracle DWH to MonetDB. After upgrade from Oct2013-SP6 to Oct2014 bulk loaders stopped working. Any attempt to use ends with Server connection lost message. Here is log message from KETTLE: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - COPY 1 RECORDS INTO drmaxtst.typy_pohybu_stage FROM STDIN USING DELIMITERS '|',' 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ','"' NULL AS 'null'; 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Error in step, asking everyone to stop because of: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleException: 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - An error occurred writing data to the MonetDB API (MAPI) process 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - Connection to server lost! 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeBufferToMonetDB(MonetDBBulkLoader.java:506) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeRowToMonetDB(MonetDBBulkLoader.java:230) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.processRow(MonetDBBulkLoader.java:207) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at java.lang.Thread.run(Thread.java:744) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - Caused by: java.io.IOException: Connection to server lost! 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:197) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - at org.pentaho.di.trans.steps.monetdbbulkloader.MonetDBBulkLoader.writeBufferToMonetDB(MonetDBBulkLoader.java:494) 2014/11/13 11:13:02 - MonetDB Bulk Loader.0 - ... 4 more To be honest the problem first showed up after upgrade from Jan2014SP1 to Jan2014SP2. I was hoping it might be temporary which seems it is not. Thank you for any help, Radovan

2 2

server-side scripting in MonetDB
by Leon-Constantin Fopa 13 Nov '14

13 Nov '14

Hi ! I read Here (http://db-engines.com/en/system/MonetDB%3BOracle%3BSQLite) that MonetDB support server-side script (like PG/SQL for Oracle). However I'm not able to found any related documentation on the MonetDB website Can some one have any pointers, documentation, tutorial about server-side scripting in monetdb ? thanks Best regards, Fopa Léon Constantin

2 2

New Oct2014 - JDBC unable to connect to the database
by Radovan Bičiště 11 Nov '14

11 Nov '14

Hello, Today I've started to test Oct2014 release. I upgraded the database with no issues and downloaded latest JDBC driver monet-jdbc-2.12.jar . When I try to connect to the upgrade database I get an error about tracelog. Here is the log file: 2014-11-11 08:54 INFO Creating new connection for [{Default group}/DRMAX - DEV] for driver=nl.cwi.monetdb.jdbc.MonetDriver and URL=[jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1] 2014-11-11 08:54 INFO Adding ClassLoader URL=file:/home/rbiciste/tools/lib/monetdb-jdbc-2.12.jar 2014-11-11 08:54 ERROR Error connecting to the database using URL=jdbc:monetdb://lux-dev1-ana.dev.ceosdata.com:50000/zkdev1, username=drmaxtst SELECT: no such operator 'tracelog' [SQL State=08001] java.sql.SQLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:254) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:171) at workbench.db.DbDriver.connect(DbDriver.java:429) at workbench.db.ConnectionMgr.connect(ConnectionMgr.java:228) at workbench.db.ConnectionMgr.getConnection(ConnectionMgr.java:158) at workbench.gui.components.ConnectionSelector.doConnect(ConnectionSelector.java:233) at workbench.gui.components.ConnectionSelector$2.run(ConnectionSelector.java:133) Caused by: nl.cwi.monetdb.mcl.MCLException: SELECT: no such operator 'tracelog' at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:311) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:398) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:252) at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:232) ... 6 more Any help would be appreciated. Thank you, Radovan

3 4

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

11 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

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