- users-list - monetdb.org

how to write binary data into BAT tail?
by hlfwm hlfwm 28 Jun '13

28 Jun '13

i want to create a monetdb function to help write my astronomical *binary*processing result into database,this function will load the binary result from memory directly into monetdb to save time, so i defined a UDF binaryCopy() , svom.c int binaryCopy(str columnname, str buffer, int count) { mvc *sql = NULL; sql_table *tmatch = NULL; /*BATs in which the data of the columns of the output table will be stored*/ BAT *tmatch_column = NULL; BUN nr_rows; /* pointer to the tail of above BAT, which is just a normal C-array */ lng *tmatch_column_lng_t = NULL; flt *tmatch_column_flt_t = NULL; /*_bind_table is just a wrapper that tries to see if it can find the table match_table under three default schemas*/ if(!(tmatch = _bind_table(sql, NULL, "testtable"))) //return sql_message("42S02!BINARYLOAD():no such table test"); return 0; //nr_match = 20000; nr_rows = count; /* create BATs for all columns of the output table 'tmatch' */ if(strcmp(columnname,"starid")==0 || strcmp(columnname,"crossid")==0 ||strcmp(columnname,"catid")==0 ) { tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows); }else { tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_rows); } /* now tmatch_starid_t points to the first element of the BAT's tails */ tmatch_column_lng_t = (lng*)Tloc(tmatch_column,BUNfirst(tmatch_column)); /* now we can write data into the BAT tail */ if((strcmp(columnname,"starid")==0) || (strcmp(columnname,"crossid")==0) ||(strcmp(columnname,"catid")==0) ) { *memcpy(tmatch_column_lng_t,buffer,nr_rows*sizeof(lng)); //is it right to write binary data into BAT???* }else { memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt)); } /* Set proper properties for all BATs */ BATsetcount(tmatch_column,nr_rows); BATseqbase(tmatch_column,0); /* sorted:1, column is sorted in ascending order */ tmatch_column->tsorted = 0; /* if the tail is reverse sorted. */ tmatch_column->trevsorted = 0; /* if the tail contains NIL values */ tmatch_column->T->nil = 0; /* if the tail doesn't contain NIL values */ tmatch_column->T->nonil = 0; /* if the values in the tail are unique.Since BATkey only works with BAT heads, * BATmirror is used to switch the head and tail of the BATs */ if(strcmp(columnname,"starid")==0) { BATkey(BATmirror(tmatch_column),1); //only starid is 1 }else { BATkey(BATmirror(tmatch_column),0); } /* finally, append the result BATs to the result table * ignore errors, since we are finishing */ _append_bat(sql,tmatch,columnname,tmatch_column); /* The BATs now contain the data for the columns of the output tables, * append_bat registers in the system where to find the BAT for those columns. */ /* To avoid memory leak, BAT reference counter must be decreased after use. * That can be done using BBPunfix. */ if(tmatch_column) BBPunfix(tmatch_column->batCacheid); return 1; //success } Thanks!

2 2

Windows: Listen on addresses other than 127.0.0.1.
by Neil French 27 Jun '13

27 Jun '13

Hi, I am trying to get MonetDB Server for Windows x64 to listen on something other than 127.0.0.1. In the Getting started guide it mentions setting "mapi_open=yes" which I have added to M5Server.bat as: "%MONETDB%\bin\mserver5.exe" --set "mapi_open=yes" --set "prefix=%MONETDB%" --set "exec_prefix=%MONETDB%" %MONETDBFARM% %* However when I start the server I still see: # MonetDB 5 server v11.15.7 "Feb2013-SP2" # Serving database 'Data', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 11.989 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/GIS module loaded # MonetDB/JAQL module loaded # MonetDB/SQL module loaded When I do a netstat –a I see port 50000 is listening only on 127.0.0.1 with nothing else using port 50000 on any other address. When I perform a select * from environment(); I see: mapi_port | 50000 mapi_open | yes mapi_autosense | false I have Windows Firewall disabled and have tried allowing m5server.exe through the firewall. Could somebody please tell me how I get MonetDB to listen on anything other than 127.0.0.1 on Windows? Thanks, Neil. IMPORTANT The information contained in this e-mail and any attachments is intended only for the named recipient and may be privileged or confidential. If you are not the intended recipient, please notify us immediately on +44 (0)1908 425000 and do not disclose, copy, distribute or take any action based on the contents of this e-mail. You should understand and accept that, when communicating with us by e-mail, it is not a totally secure communication medium. We accept no liability for any direct, indirect or consequential loss arising from any action taken in reliance on the information contained in this e-mail and give no warranty or representation as to its accuracy or reliability. DIGITALK has the facility to monitor and read both incoming and outgoing communications by e-mail. In line with industry efforts to reduce the proliferation of un-solicited SPAM, DIGITALK uses methods and ban-lists to prevent malicious content reaching our users. This message and any attachments has been scanned for known viruses. However, we would advise you to ensure the content is indeed virus free. We do not, to the extent permitted by law, accept any liability (whether in contract, negligence or otherwise) for any virus infection and/or external compromise of security and/or breach of confidentiality in relation to transmissions sent by e-mail. VAT No: GB 876 3287 81. Reg No: 3080801 Place of Registration: England Registered Office Address: 2 Radian Court, Knowlhill, Milton Keynes.

2 1

Indexing in monetdb
by Anshuman Kumar 27 Jun '13

27 Jun '13

Hello everyone on the mailing list, Please find some time to go through my queries. I created a table with some fields out of which 1 is timestamp. I wanted to have indexing on this table. While going through the available documentation for monetdb, I found out that it creates its own indexing and might ignore user provided indexing. I went ahead and started pushing in data into the table with single inserts (10000 of them accumulated in a transaction). My table is pretty big now and it contains about 250 million records (over 2 months of data). Even simple queries to find records between specified timestamps takes a lot of time(12-13 mins in some cases) We really feel the need of indexing on the table(don't know if that can help). I happened to stumble upon an article on monetdb mailers: <http://mail.monetdb.org/pipermail/users-list/2008-March/001726.html> http://mail.monetdb.org/pipermail/users-list/2008-March/001726.html According to this article if I insert data into the table using - INSERT INTO table_name (...) ORDER BY X,Y. The order by here will enforce indexing on X, Y. I would like to confirm whether this really enforce indexing? I have a few other questions pertaining to my case: 1) Is there a predefined way of speeding up such queries in monetdb? 2) If the insert with an order by clause is a work around then what if going further, I start inserting with the insert query with the order by clause. a)Will that enforce indexing on the whole table or on only those records which were inserted using such inserts? b) Or should I just move the data from my table with 250 million records into another table using the insert with order by clause? 3)One more question regarding the setup of monetdb - I have initiated monetdb using the M5server.bat on my windows 2008 R2 64 bit server. This makes it run as a service and starts off at the server start-up. This is all nice and acceptable. The problem comes when I need to restart the monetdb server. The only way I was able to do this was to kill the process (mServer5.exe) since I was not able to figure out the service (in the windows services) which starts monetdb. So my simple question is how to restart monetdb server if it gets installed by M5server.bat? Can somebody please help me with the queries? Regards, Anshuman

2 1

RE: Indexing in monetdb
by Baskar Duraikannu 27 Jun '13

27 Jun '13

Anshuman I do not have answer to your queries. We are trying to move monetdb and would be extremely helpful if you could let me know 1. How big is your database? 2. Are you running in production? --- Original Message --- From: "Anshuman Kumar" <anshuman.kumar(a)flowedge.in> Sent: June 27, 2013 5:44 AM To: users-list(a)monetdb.org Subject: Indexing in monetdb Hello everyone on the mailing list, Please find some time to go through my queries. I created a table with some fields out of which 1 is timestamp. I wanted to have indexing on this table. While going through the available documentation for monetdb, I found out that it creates its own indexing and might ignore user provided indexing. I went ahead and started pushing in data into the table with single inserts (10000 of them accumulated in a transaction). My table is pretty big now and it contains about 250 million records (over 2 months of data). Even simple queries to find records between specified timestamps takes a lot of time(12-13 mins in some cases) We really feel the need of indexing on the table(don't know if that can help). I happened to stumble upon an article on monetdb mailers: <http://mail.monetdb.org/pipermail/users-list/2008-March/001726.html> http://mail.monetdb.org/pipermail/users-list/2008-March/001726.html According to this article if I insert data into the table using - INSERT INTO table_name (...) ORDER BY X,Y. The order by here will enforce indexing on X, Y. I would like to confirm whether this really enforce indexing? I have a few other questions pertaining to my case: 1) Is there a predefined way of speeding up such queries in monetdb? 2) If the insert with an order by clause is a work around then what if going further, I start inserting with the insert query with the order by clause. a)Will that enforce indexing on the whole table or on only those records which were inserted using such inserts? b) Or should I just move the data from my table with 250 million records into another table using the insert with order by clause? 3)One more question regarding the setup of monetdb - I have initiated monetdb using the M5server.bat on my windows 2008 R2 64 bit server. This makes it run as a service and starts off at the server start-up. This is all nice and acceptable. The problem comes when I need to restart the monetdb server. The only way I was able to do this was to kill the process (mServer5.exe) since I was not able to figure out the service (in the windows services) which starts monetdb. So my simple question is how to restart monetdb server if it gets installed by M5server.bat? Can somebody please help me with the queries? Regards, Anshuman _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Indexing in monetdb
by Anshuman Kumar 26 Jun '13

26 Jun '13

Hello everyone on the mailing list, Please find some time to go through my queries. I created a table with some fields out of which 1 is timestamp. I wanted to have indexing on this table. While going through the available documentation for monetdb, I found out that it creates its own indexing and might ignore user provided indexing. I went ahead and started pushing in data into the table with single inserts (10000 of them accumulated in a transaction). My table is pretty big now and it contains about 250 million records (over 2 months of data). Even simple queries to find records between specified timestamps takes a lot of time(12-13 mins in some cases) We really feel the need of indexing on the table(don't know if that can help). I happened to stumble upon an article on monetdb mailers: http://mail.monetdb.org/pipermail/users-list/2008-March/001726.html According to this article if I insert data into the table using - INSERT INTO table_name (...) ORDER BY X,Y. The order by here will enforce indexing on X, Y. I would like to confirm whether this really enforce indexing? I have a few other questions pertaining to my case: 1) Is there a predefined way of speeding up such queries in monetdb? 2) If the insert with an order by clause is a work around then what if going further, I start inserting with the insert query with the order by clause. a)Will that enforce indexing on the whole table or on only those records which were inserted using such inserts? b) Or should I just move the data from my table with 250 million records into another table using the insert with order by clause? 3)One more question regarding the setup of monetdb - I have initiated monetdb using the M5server.bat on my windows 2008 R2 64 bit server. This makes it run as a service and starts off at the server start-up. This is all nice and acceptable. The problem comes when I need to restart the monetdb server. The only way I was able to do this was to kill the process (mServer5.exe) since I was not able to figure out the service (in the windows services) which starts monetdb. So my simple question is how to restart monetdb server if it gets installed by M5server.bat? Can somebody please help me with the queries? Regards, Anshuman

1 0

Strange behavior
by Victor Almeida 25 Jun '13

25 Jun '13

Hi all, I'm using monetdb on two servers and meet some strange behavior. The two servers are of the same configuration as the following: CPU: Intel(R) Xeon(R) CPU E5-2430L, 6 Cores, 12 Threads, 2 GHz, 64 bits. Memory: 64GB OS: Ubuntu Server 13.04 with 3.8.0-25-generic x86_64 kernel MonetDB: v11.15.7 And they are both bought for less than 4 months. The problem is that the performance of MonetDB is very unstable, even for very simple select * queries. The experiment setting is as the following: Data: Server1: Table1 loaded from a 19GB CSV file (CSV1). Server2: Table1 loaded from another 19GB CSV file (CSV2). CSV1 and CSV2 have the same schema but have different contents. But they both have size of about 19GB and both have 155000000 tuples. The schema is: Field1 VARCHAR(16), Field2 VARCHAR(100), Field3 DATE, Field4 FLOAT, Field5 VARCHAR(64), Field6 CHAR(3), Field7 CHAR(6), Field8 VARCHAR(32), Field9 INT In addition, both the tables have indices on Field1, Field2 and Field3. Experiment Query: echo -e '\\w-1 \n \\> /dev/null \n select * from Table1;' | mclient -d expdatabase --interactive The problem is that the performance varies a lot between the servers and also between different runs on the same server. Here is the result: Server1: 4m 12s Server2: 305m 29s The system conditions during the experiment: Disk speed: I run the command: cat CSV > /dev/null. The speed is normal, about 2m ~ 2m 30s . System load: The machine load during the query executions are both very low. The CPUs are almost completely idle and free memories are abundant. Each monetdb server took about 50G. To eliminate possible system configuration problems. I did two more experiments. 1) I move CSV2 to server1, stop the server1 monetdb and build and start a new monetdb database using CSV2. Then I run the same query again on the new database. The time elapse is 134m 8s. 2) After 1) is done I stop the new monetdb and restart the old monetdb on server1, and run the same query again. This time the time elapse becomes 388m 37s (Comparing 4m 12s in the first run). Further disk observations: During the above two experiments, I monitor the disk read speed of monetdb using iotop. The speed keeps about only 500 ~ 550 k/s. While the read speed of cat Table1.csv > /dev/null keeps around 135m/s. I did not do any configuration about monetdb. Is it possible that I misconfigured something? Thanks. Victor

2 1

broken link
by Anthony Damico 23 Jun '13

23 Jun '13

on http://www.monetdb.org/Home the text below links to a non-existent page :) *The Researcher's Guide to the Data Deluge: Querying a Scientific Database in Just a Few Seconds<http://repository.cwi.nl/search/searchrepository.php?id=18546> .* not sure if it should link to http://oai.cwi.nl/oai/asset/18546/18546B.pdf

2 1

User defined aggregation function (SQL)
by Sebastian Dorok 20 Jun '13

20 Jun '13

Hi all, I am evaluating MonetDB in the context of genomic data. Therefore I'm trying to implement a custom aggregation function. To get a feeling for the system I set up a UDF that just returns identical values. Now I'm trying to implement this simple function as aggregate function for groupings but always run into an error when executing this: select grpident(base) from test.bases group by pos; "TypeException:user.s1_1[18]:'udf.subident' undefined in: _30:bat[:any,:str] := udf.subident(_15:bat[:oid,:str], _27:bat[:oid,:oid], r1_27:bat[:oid,:oid], _1:bit) program contains errors" Here is the MAL plan: +-----------------------------------------------------------------+ | mal | +=================================================================+ | function user.s1_1{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"test","bases"); | | X_6 := sql.bind(X_2,"test","bases","base",0); | | (X_9,r1_9) := sql.bind(X_2,"test","bases","base",2); | | X_12 := sql.bind(X_2,"test","bases","base",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | calc.str("bases"); | | calc.str("base"); | | X_18 := sql.bind(X_2,"test","bases","pos",0); | | (X_20,r1_20) := sql.bind(X_2,"test","bases","pos",2); | | X_22 := sql.bind(X_2,"test","bases","pos",1); | | X_23 := sql.delta(X_18,X_20,r1_20,X_22); | | X_24 := algebra.leftfetchjoin(X_3,X_23); | | calc.str("bases"); | | calc.str("pos"); | | (X_27,r1_27,r2_27) := group.subgroupdone(X_24); | | X_30:bat[:any,:str] := udf.subident(X_15,X_27,r1_27,true); | | calc.str("bases"); | | calc.str("L1"); | | calc.str("bases"); | | calc.str("L1"); | | X_36 := sql.resultSet(1,1,X_30); | | sql.rsColumn(X_36,"test.bases","L1","clob",1,0,X_30); | | X_40 := io.stdout(); | | sql.exportResult(X_40,X_36); | | end s1_1; | +-----------------------------------------------------------------+ I'm using following setup: MonetDB 5 server v11.15.7 "Feb2013-SP2" (64-bit, 64-bit oids) Found 1.9GiB available memory, 2 available cpu cores Libraries: libpcre: 8.31 2012-07-06 (compiled with 8.31) openssl: OpenSSL 1.0.1c 10 May 2012 (compiled with OpenSSL 1.0.1c 10 May 2012) libxml2: 2.9.0 (compiled with 2.9.0) Here's what my related code looks like: udf.c ##### str GRPident(int *ret, int *bid, int *eid){ ... } udf.h ##### udf_export str GRPident(int *ret, int *bid, int *eid); udf.mal ####### module udf ... command ident(b:bat[:oid,:str], e:bat[:oid,:str]) :bat[:oid,:str] address GRPident comment "identity of groups"; udf.sql ####### create aggregate grpident(src string) returns string external name udf.ident; I already renamed the MAL command to "subident" and changed the SQL expression accordingly without success. Furthermore I already had a look at "batxml.c" as suggested here: http://old.nabble.com/users-list-Digest,-Vol-3,-Issue-30-td34736774.html but don't see what I'm doing wrong. What do I have to do to implement a custom aggregate function? Thanks, Sebastian

2 3

Support for simultaneous queries / read locking
by Mark Hansen 13 Jun '13

13 Jun '13

We are looking to use MonetDb as the backend of a multi-user BI system. Before we get too far down the road with development, I'm wondering how MonetDb will perform with 10, 50, 100, 1000 simultaneous users? We are not concerned about writes - just reads, as we plan to do updates as a nightly batch process when we can tolerate a small window of performance downgrades on the queries. Is there any read-locking that limits concurrent queries from accessing the same data? If so, is it at the column level, or finer grained? Does anyone have experience using MonetDb for handling lots of concurrent queries? Thanks in advance for any info. -- Mark

2 1

Import data - MonetDB
by Raquel Ribeiro 13 Jun '13

13 Jun '13

Hi, I am trying do bulk load like I saw here http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/LoadingBulkData , but I am not having success when doing it. I am trying to bulk load more than 60k registers using the following command: > mclient -d adventurework -s "COPY INTO FactInternetSales FROM STDIN USING DELIMITERS ',', '\n', '\"'" - < C:Users\dir\text.txt After running this command I do not receive any error message but I cannot see the data in the database either. What am I missing here? Am I trying to this in the wrong way? PS: I also tried with a smaller file but in the end I have the same result. I attached my partial data file in case of being necessary and the create of this table. And the system is Windows. Thanks for your attention. Best regards, -- Raquel Ribeiro

3 4

question about concurrent read/write
by Roberto Cornacchia 13 Jun '13

13 Jun '13

Hi there, I'm trying to chase the cause of a problem with concurrent reads/writes, that shows up as either a "COMMIT FAILED" error or an assertion (delta_append_bat: Assertion `!c || ((c)->U->count) == bat->ibase' failed), probably depending timing issues. The scenario is roughly: two threads, one reading, one writing. Sometimes, they need to read/write from/to the same table. I'm aware that this may raise issues, but I'd like to get straight what the real cause is (and possibly what the cause of the assertion is). Imagine two JDBC connections, R (read only) and W (write only), in the following timeline: TS = transaction start TC = transaction commit TR = transaction trollback CO = cursor open C++ = increment cursor (read one row) IN = insert R | W _______________ 1| | TS 2| TS | 3| CO | IN 4| C++ | 5| | TC 6| | TS 6| C++ | IN 7| TR | 8| | TC The question is: is this supposed to fail or to succeed? There are no UPDATE in W, only INSERT INTO (actually COPY INTO). I tried a similar scenario with 2 mclient sessions, continuously reading from R (without cursors) and writing and committing from W. R kept nicely showing no updates, until the ROLLBACK. Then, it showed the updates from W. But I'm not sure whether the scenario above with cursors should behave the same. This is using very up to date Feb2013 branch. I guess the assertion is a bug, and I'd like to file it, but at the moment it's quite hard to reproduce consistently, perhaps it would be easier if I could get a better understanding of the scenario above. Thanks, Roberto

2 1

Building MonetDB on Windows (x64)
by Scott Mathieson 12 Jun '13

12 Jun '13

Hi, I am trying to build the latest sources on Windows (x64) and I'm having problems getting the compilation to work, specifically the code in Mx does not seem to be fully compliant with the Windows VC compiler (the use of __attribute__ in the MxFcnDef.h file to be a case in point although there are lots of other examples of gcc specific syntax in this code). I believe I have followed the instructions in windowsbuild.rst but to no avail. Am I missing something - is Mx.exe required for the build and if so, can it be built with VS2010? Thanks, Scott ________________________________

1 0

JDBC driver does not return stored procedure information.
by Thomas Kellerer 11 Jun '13

11 Jun '13

Hello list, while adding support for MonetDB to my tool "SQL Workbench/J" (http://www.sql-workbench.net) I noticed that the calls to DatabaseMetaData.getProcedures() DatabaseMetaData.getProcedureColumns() will always return an empty ResultSet even if there are stored procedures or functions in the database. Are there any plans to fix this bug? If yes, what will be the timeframe for that? Regards Thomas Kellerer

1 0

Inconsistency in select
by Flavio Coelho 10 Jun '13

10 Jun '13

Dear list, We have found this strange inconsistency when selecting from data. Consider the two following select clauses: in the first one with a "where" filter, we notice a number of missing data on the resulting table (hint: they are not really missing in the database). However if we look to any of the specific lines which appear to have missing data, as done in the second select, we confirm that the data is there (not missing). What is going on? Am I doing anything wrong? Is this a bug of the mclient? the version I am running is this: MonetDB Database Server Toolkit v1.1 (Feb2013-SP2) sql>select * from notas_c where instalacao=412307404; +-------------+-----------+------------+------------+------+--------------+------+------------------------------+------------+-----------+--------------------------------------------------+ | id | instalaca | data_de_cr | data_do_en | tipo | descricao_do | codi | descricao_da_codificacao_da_ | data_fim_d | status_do | descricao_do_status_do_usuario | : : o : iacao_da_n : cerramento : _de_ : _tipo_de_not : fica : nota : a_avaria : _usuario : : : : : ota : _da_nota : nota : a : cao_ : : : : : : : : : : _de_ : : da_n : : : : : : : : : : serv : : ota : : : : : : : : : : ico : : : : : : : +=============+===========+============+============+======+==============+======+==============================+============+===========+==================================================+ | 35004675 | 412307404 | 2007-10-22 | 2011-06-14 | AF | | AF01 | | 2007-10-30 | NPRO ARQU | Laudo Válido Improcedente//Enviado via Interface | | 10009362195 | 412307404 | 2009-10-28 | 2009-10-31 | AL | | AL06 | | 2009-10-31 | VIRE EXEC | Vistoria Reprovada//Executada no Campo | | 10009372721 | 412307404 | 2009-10-29 | 2009-11-17 | AG | | AG22 | | 2009-11-17 | PRO | Procedente | | 10009455607 | 412307404 | 2009-11-10 | 2009-11-17 | AD | | AD05 | | 2009-11-16 | EXEC | Executada no Campo | | 10010449356 | 412307404 | 2010-03-30 | 2010-08-11 | AJ | | AJ11 | | 2010-08-11 | EXEC | Executada no Campo | | 10010832683 | 412307404 | 2010-05-21 | 2010-05-24 | AD | | AD05 | | 2010-05-24 | REJE | Rejeitada | | 10011114747 | 412307404 | 2010-06-29 | 2010-06-30 | AG | | AG44 | Serviço fora do prazo | 2010-06-30 | IMP | Improcedente | | 10011308855 | 412307404 | 2010-07-22 | 2010-07-28 | AD | | AD05 | BO - Manutenção na lente | 2010-07-28 | EXEC | Executada no Campo | | 10011475191 | 412307404 | 2010-08-11 | 2010-08-16 | AB | | AB08 | AB08 Auc c/subst c ramal Tri | 2010-08-16 | EXEC | Executada no Campo | | 10012702114 | 412307404 | 2011-01-05 | 2011-01-10 | A3 | | A375 | Corte MCV exec na rede | 2011-01-07 | EXEC | Executada no Campo | | 10014234776 | 412307404 | 2011-06-14 | 2011-06-15 | A3 | | A375 | Corte MCV exec na rede | 2011-06-15 | EXEC | Executada no Campo | | 10014271409 | 412307404 | 2011-06-15 | 2011-06-16 | A5 | Religação BT | A513 | Religa Normal-Exec Rede | 2011-06-16 | EXEC | Executada no Campo | +-------------+-----------+------------+------------+------+--------------+------+------------------------------+------------+-----------+--------------------------------------------------+ 12 tuples (0.9s) sql>select * from notas_c where id=35004675; +----------+-----------+------------+------------+------+--------------------+------+--------------------------------+------------+-----------+---------------------------------------------+ | id | instalaca | data_de_cr | data_do_en | tipo | descricao_do_tipo_ | codi | descricao_da_codificacao_da_no | data_fim_d | status_do | descricao_do_status_do_usuario | : : o : iacao_da_n : cerramento : _de_ : de_nota : fica : ta : a_avaria : _usuario : : : : : ota : _da_nota : nota : : cao_ : : : : : : : : : : _de_ : : da_n : : : : : : : : : : serv : : ota : : : : : : : : : : ico : : : : : : : +==========+===========+============+============+======+====================+======+================================+============+===========+=============================================+ | 35004675 | 412307404 | 2007-10-22 | 2011-06-14 | AF | VerifTensão Fornec | AF01 | Verif Tensão Fornecimento - BT | 2007-10-30 | NPRO ARQU | Laudo Válido Improcedente//Enviado via Inte | : : : : : : : : : : : rface : +----------+-----------+------------+------------+------+--------------------+------+--------------------------------+------------+-----------+---------------------------------------------+ 1 tuple (325.965ms) -- Flávio Codeço Coelho ================ +55(21) 3799-5567 Professor Escola de Matemática Aplicada Fundação Getulio Vargas Praia de Botafogo, 190 sala 312 Rio de Janeiro - RJ 22250-900 Brasil

4 8

disable foreign key checks
by Gijs Molenaar 10 Jun '13

10 Jun '13

Hi monetdb people, I'm doing some work again on Djonet, implementing missing features. One of the features is to flush the content of a set tables e.g. remove all rows. The problem is that the order the tables are processed is important in the case of foreign key relations. They way this is implemented in other database drivers for django is to temporarily disable foreign key relations (mysql) or to TRUNCATE multiple tables in one statement (postgresql). Is there an (undocumented) way of accomplishing something similar? I prefer not to temporarily alter the table. Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 4

gdk_heap
by Hassan, Ahmad 06 Jun '13

06 Jun '13

Hi, I have read the gdk_heap code in detail to understand whether MonetDB reuses the same virtual address range over and over again for multiple allocations. From my analyses, I found out the following: 1) HEAPalloc is used for memory allocation. If requested size is under GDK_mmap_minsize then STORE_MEM (GDKmalloc|malloc) is used for allocation Otherwise STORE_MMAP (anonymous mmap) or STORE_PRIV (DISK mmap) is used 2) HEAPfree use HEAPcacheAdd to return the previously allocated virtual address space to the cache whose size is fixed at 5 elements. So this means only these 5 address ranges will be used over time and their size would be extended using HEAPextend 3) Apart from those 5 HEAPcache entries, MonetDB doesn't use any reallocation of same virtual address range for multiple objects but just relies on operating system to handle the allocation and de-allocation through malloc/mmap. Does this mean that MonetDB just keeps only 5 HEAPcache entries to reduce the overhead of mmap calls and manage those virtual addresses by itself for future allocations? [HEAP_CACHE_SIZE = 5] I understand that this is very simplistic analyses of what MonetDB actually does in gdk_heap but any information on the gdk_heap would be really helpful. Thanks. Kind Regards, Ahmad

1 0

which version of monetdb works with cygwin ?
by Kaniska_Mandal＠DELL.com 06 Jun '13

06 Jun '13

Hi, Which linux flavor of MonetDB (Fedora / FreeBSD / Debian) can I run in Cygwin ? If it's not possible to run a linux version of monetdb in Cygwin, then what are the steps to compile monetdb src into Cygwin ? Thanks, Kaniska

2 1

mserver5 asserts under valgrind (callgrind)
by Stephen P. Morgan 06 Jun '13

06 Jun '13

I ran valgrind –tool=callgrind on mserver5 while executing TPCH Q1 (in SQL) on a scale factor 100 database on a dual-processor, x86_64 linux server. The program failed due to an assertion in DELTAproject, namely assert(tres) on line 3614 in sql.mx. Without valgrind, the query completes correctly when mserver5 is run from a similar command line. What appears below is the valgrind program output. Steve Morgan ==1800== Callgrind, a call-graph generating cache profiler ==1800== Copyright (C) 2002-2011, and GNU GPL'd, by Josef Weidendorfer et al. ==1800== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info ==1800== Command: /usr/local/bin/mserver5 --dbpath=/home/ubuntu/monet/dbfarm/tpch --set merovingian_uri=mapi:monetdb://SM-MoDB3:50000/tpch --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/ubuntu/monet/dbfarm/tpch/.mapi.sock --set monet_vault_key=/home/ubuntu/monet/dbfarm/tpch/.vaultkey --set gdk_nr_threads=32 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes ==1800== ==1800== For interactive control, run 'callgrind_control -h'. # MonetDB 5 server v11.15.3 "Feb2013-SP1" # Serving database 'tpch', using 32 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked # Found 188.887 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for UNIX domain connection requests on mapi:monetdb:///home/ubuntu/monet/dbfarm/tpch/.mapi.sock # MonetDB/GIS module loaded # MonetDB/JAQL module loaded # MonetDB/SQL module loaded #GDKmalloc(116404584) fails, try to free up space [memory in use=4416764392,virtual memory in use=31869532648] #GDKmalloc(116404584) result [mem=3251134360,vm=15052332952] #GDKmallocmax: recovery ok. Continuing.. #GDKmmap(1600192512) fails, try to free up space [memory in use=8024487008,virtual memory in use=26927363168] #GDKmmap(1600192512) result [mem=5046231544,vm=20698587640] #GDKmmap: recovery ok. Continuing.. #GDKmmap(1600192512) fails, try to free up space [memory in use=5046232200,virtual memory in use=20898866312] #GDKmmap(1600192512) fails, try to free up space [memory in use=5046232200,virtual memory in use=22499058824] #GDKmmap(1600192512) result [mem=4897637384,vm=22350464008] mserver5: ../../../../MonetDB/sql/backends/monet5/sql.mx:3614: DELTAproject: Assertion `tres' failed. #GDKmmap(1600192512) result [mem=5097655904,vm=22550482528] #GDKmmap(1600192512) result [mem=5097655904,vm=22550482528] ==1800== ==1800== Events : Ir ==1800== Collected : 142458949886 ==1800== ==1800== I refs: 142,458,949,886 ./out: line 1: 1800 Killed valgrind --tool=callgrind /usr/local/bin/mserver5 --dbpath=/home/ubuntu/monet/dbfarm/tpch --set merovingian_uri=mapi:monetdb://SM-MoDB3:50000/tpch --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/ubuntu/monet/dbfarm/tpch/.mapi.sock --set monet_vault_key=/home/ubuntu/monet/dbfarm/tpch/.vaultkey --set gdk_nr_threads=32 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes

2 1

How to create database in windows
by Kaniska_Mandal＠DELL.com 06 Jun '13

06 Jun '13

Hi, I am a monetdb newbee Once I have installed monetdb on windows, I cannot find monetdbd or monetdb commands. Can you please let me know how do I create a new database and dbfarm from command line in windows ? Thanks, Kaniska

3 4

show processlist
by Christian Braun 05 Jun '13

05 Jun '13

Hello, is there a way to get a list of the running queries / processes from a MonetDB server? Something like "show processlist" command MySQL has. Thanks, Christian Braun.

2 1

Database sampling doesn't work in new release.
by Jun Liu 05 Jun '13

05 Jun '13

MonetDB version: Feb2013-SP2 Using mclient to execute a SQL like: SELECT * FROM TEST SAMPLE 10; It will return "Connection terminated" and quit.

3 3

Question on Quicksort
by Stephen P. Morgan 05 Jun '13

05 Jun '13

We have a question about MonetDB's quicksort routine. It seems that sometimes the head and tail values in a BAT remain paired and sometimes not after passing the BAT through quicksort. For example, a BAT might have the following values before and after quicksor. (This is an artificial example.) index H T ----------------- 0 1 0 1 3 0 3 7 1 4 7 0 5 7 2 6 7 0 7 32 3 8 34 0 9 35 4 10 38 0 After MonetDB qsort index H T ----------------- 0 1 0 1 3 0 3 7 1 4 7 0 5 7 5 <----- Why is this value 5? Why not 2? 6 7 0 7 32 4 <----- Shouldn't it still be 3? 8 34 0 9 35 3 <----- Shouldn't it still be 4? 10 38 0 With some queries, the values seem to move in pairs, but in others, they don't. With some queries, the head-tail pairing seems to be maintained, but with others, it isn't. For example, the first query below apparently preserves the pairing (when run against the TPCH database), but the second query does not (and this happens consistently). select l_suppkey, l_partkey from lineitem where l_partkey > 150000 order by l_orderkey; select o_orderkey, o_custkey, o_orderstatus from orders where o_custkey > 40000 and o_custkey < 10000000 order by o_orderkey; Any light you might throw on this would be very helpful. Thanks, Steve Morgan

2 3

BAT Buffer Pool functionality
by Hassan, Ahmad 04 Jun '13

04 Jun '13

Hi, I am reading Monetdb code to understand the functionality of BAT Buffer Pool (BBP). I understood that it doesn't allocate allocate memory for actual BAT but BATs are first instantiated on the HEAP memory using BATcreatedesc, BATnewstorage where BATcreatedesc also registers the entry of new BAT into BBP. BBP only allocates HEAP memory for maintaining the bookkeeping information for the BATs and keeping the directory structure information. I understood from the code that BBP keeps looking for the HOT/COLD state of BATs and performs unloading if memory is growing or descriptors are increasing using BBPTrim. Does this mean File descriptor here? Questions: 1) I want to know if MonetDB allocates a large heap space internally then reuse that space for allocating memory chunks to BATs on demand or every time MonetDB ask operating system to extend heap size when new BAT is created? 2) I want to understand how BBP manages the HEAP memory. When a BAT is no longer needed, then what BBP does with that memory. Does it return that memory to OS and shrinks the heap size used by MonetDB or it holds that space for future allocations? 3) A typical TPCH query generates 100's of intermediate BATs. Does MonetDB allocates memory for those BATs during query execution and returns it back to operating system when query finishes? 4) When we start MonetDB then how much initial heap memory is allocated by the MonetDB system. Does BBP allocates this memory on the HEAP? If not, then which part of the code knows which BATs to load at the start time and how much HEAP memory needs to be allocated for those BATs. I found the list of all BATs in Monetdb through bbp.get() mal call. I also understand that SQL catalog maintains the mapping of table to column names and column names to BAT_in_BBP mapping. But I couldn't able to find which BAT I should access through mal code to get the sql catalog mappings. I want to use MAL to see which table is composed of which column is linked to which BAT in BBP. Thanks for the help. Kind Regards, Ahmad

2 3

Possible to reset or flush out RAM usage in Windows without closing and re-opening mserver.exe?
by Anthony Damico 23 May '13

23 May '13

Hi, I'm running a two-day-long program on Windows x64 with MonetDB.R that breaks somewhere into the second day due to what I believe is overloaded RAM? I was under the impression that this should not really happen with MonetDB, since it's a database that doesn't load everything into RAM.. Here's what's happening: A big, complicated series of importations, merges, and recodes all work for the first two days on about 250,000,000 records of data.. but all the while my Windows Task Manager will slowly show the physical memory usage meter ticking up in the "Performance" tab. mserver.exe does NOT appear to be using RAM in the "Memory" column of the "Processes" tab, so it looks like there's a ghost using RAM. Then, after two days, the script will break (mserver output shown below) - returning a GDK error on a seemingly-benign line of code, just a simple recode: Error in .local(conn, statement, ..., async = async) : UPDATE ep10 SET rx_cso_cov = CVRD_D_PLAN_PD_AMT WHERE DRUG_CVRG_STUS_CD IN ( 'C','S','O' ) failed! Server says: !GDKerror:!ERROR: GDKmallocmax: failed for 589484032 bytes After that, if I close mserver.exe and open it up again, it takes about twenty minutes to get the ">" mserver prompt. But it does open up eventually.. and at that point, if I re-connect to the server with R and run the exact same line, it works without error. So it appears I just need to flush out that ghost/mystery RAM usage somehow? Does this error make sense? There is no way this is due to lack of hard disk space - this server's C drive has 15GB free and the attached drive actually hosting all of this data has 235GB free. I don't want to rule it out, but I also doubt this problem is related to MonetDB.R, since the scenario I described does not require that I restart my R console, just mserver.exe. I can work around the problem by manually killing and re-starting mserver.exe at a few logical breakpoints in my program, but I didn't think this was supposed to happen in the first place? Thanks!!! Anthony # MonetDB 5 server v11.15.7 "Feb2013-SP2" # Serving database 'medicare_sample', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 20.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded ># SQL catalog created, loading sql scripts once # loading sql script: 09_like.sql # loading sql script: 10_math.sql # loading sql script: 11_times.sql # loading sql script: 12_url.sql # loading sql script: 13_date.sql # loading sql script: 14_inet.sql # loading sql script: 15_history.sql # loading sql script: 16_tracelog.sql # loading sql script: 17_compress.sql # loading sql script: 18_dictionary.sql # loading sql script: 19_cluster.sql # SQL catalog created, loading sql scripts once # loading sql script: 09_like.sql # loading sql script: 20_vacuum.sql # loading sql script: 10_math.sql # loading sql script: 11_times.sql # loading sql script: 12_url.sql # loading sql script: 21_dependency_functions.sql # loading sql script: 13_date.sql # loading sql script: 14_inet.sql # loading sql script: 15_history.sql # loading sql script: 16_tracelog.sql # loading sql script: 17_compress.sql # loading sql script: 18_dictionary.sql # loading sql script: 19_cluster.sql # loading sql script: 20_vacuum.sql # loading sql script: 21_dependency_functions.sql # loading sql script: 22_clients.sql # loading sql script: 23_skyserver.sql # loading sql script: 24_zorder.sql # loading sql script: 25_debug.sql # loading sql script: 22_clients.sql # loading sql script: 23_skyserver.sql # loading sql script: 39_analytics.sql # loading sql script: 24_zorder.sql # loading sql script: 25_debug.sql # loading sql script: 39_analytics.sql # loading sql script: 75_storagemodel.sql # loading sql script: 75_storagemodel.sql # loading sql script: 80_udf.sql # loading sql script: 80_udf.sql # loading sql script: 99_system.sql # loading sql script: 99_system.sql #GDKmalloc(589484032) fails, try to free up space [memory in use=71647756904,vir tual memory in use=72723595880] #GDKmalloc(589484032) result [mem=71615637640,vm=72198121608]

2 2

Fwd: Using the inet and url type functions
by Christopher Nelson 23 May '13

23 May '13

Hi, I noticed in the manual that there are many useful functions for the inet and url types. For example, I would like to convert an inet column to text for use in a Python script (since the python-monetdb module does not support the inet type.) I tried: sql>SELECT wlid, text(src), ts FROM web_log WHERE src IS NOT NULL ORDER BY src, ts LIMIT 5;syntax error, unexpected '(', expecting SCOLON in: "select wlid, text(" sql>SELECT wlid, inet:text(src), ts FROM web_log WHERE src IS NOT NULL ORDER BY src, ts LIMIT 5; unexpected symbol (:) sql>SELECT wlid, inet.text(src), ts FROM web_log WHERE src IS NOT NULL ORDER BY src, ts LIMIT 5; syntax error, unexpected '(', expecting SCOLON in: "select wlid, inet.text(" I have read through the manual sections on types and functions several times now, and I cannot see how to use the specified functions on these types. Any help would be greatly appreciated. Thanks! -={C}=-

2 8

Using something like "trace" within the MAL interpreter
by Masood Mortazavi 20 May '13

20 May '13

Hi, The "trace" commands works well when mclient is in the SQL interpreter mode. How about when mclient is in MAl interpreter mode? Is there a way to use the "trace" command in this mode? Best regards, Masood

3 6

support for non-median quantile functions?
by Anthony Damico 15 May '13

15 May '13

Hi, would it be possible to expand the median() function to be expanded work on any quantile? Martin's original request did say quantile, not just median :D http://osdir.com/ml/db.monetdb.bugs/2008-06/msg00206.html Given the MonetDB.R connection, I think a lot more statisticians are going to be using MonetDB -- and they'll find a lot of value in being able to calculate statistics like the 25th and 75th, percentiles quickly. I can submit a bugs.monetdb.org report if that's appropriate? Thanks for considering this!!

2 2

Re: Help for MonetDB source code (SQL Module)
by Stefan Manegold 14 May '13

14 May '13

Dear Khaled, for questions about MonetDB, I kindly ask you to use the respective mailing list(s); I Cc my answer to the users list. See also http://www.monetdb.org/Developers/Mailinglists On Mon, May 13, 2013 at 06:41:45PM +0200, Khaled Dehdouh wrote: > Dear sir, > > I am a student at university of LYON. Concerning MonetDB, I need your help, > please on some points. > > > - I want to do some optimizations in "GROUP BY" function of SQL module. > For this end, can you show me where is exactly the function code > responsible to perform the "Group By" operation on MonetDB source > codein order to modif > y it. I'm wondering how you know that "optimizations" are necessary/possible without knowing the actual code, but I guess you know. You find the respective code in gdk/gdk_group.c > - I would like to know also how to recompile the code after modification > of the "GROUP BY" function. See http://www.monetdb.org/Developers/SourceCompile http://dev.monetdb.org/hg/MonetDB/file/tip/HowToStart.rst http://dev.monetdb.org/hg/MonetDB/file/1272d67f24be/buildtools/doc/windowsb… > - How creating a new SQL command that performs function with parameters and > combines two existing functions in MONETDB. See http://www.monetdb.org/Documentation/SQLreference/Modules or your favorite SQL:2003 documentation. MonetDB is SQL standard compliant. Best, Stefan > Thanks in advance. > > Best regards, > Khaled DEHDOUH. -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

GROUP BY
by Bouslah Ahmed 14 May '13

14 May '13

Hello, I am a student at a university in France. I work on MonetDB, Windows platform. I have some questions: I want to optimize the "Group by" function of the SQL module in order to create a new function combining the "Group by" function and another function. For this I want to know: 1 - How to locate the class containing the code of the "Group by" function 2 - How to edit the code in question to change it 3 - Which editor should be used to compile the project. 4 - I tried to use "Visual Studio 2012" to open the project "MonetDB" but I have not managed to do. Regards Ahmed Bouslah.

3 2

Question on installing extensions
by Kaniska_Mandal＠DELL.com 12 May '13

12 May '13

Hi All, How to install extensions for Streams, XML and Json support ? Many thanks, Kaniska

2 1

TINYINT display problems in 3rd party query clients
by Diethard Steiner 10 May '13

10 May '13

Hi, I am wondering why TINYINT values are displayed with different values in 3rd party SQL clients like Squirrel (please see attached screenshot): So 1 is shown as 49 in example. I used following JDBC driver version: monetdb-jdbc-2.8.jar Any ideas? Thanks, Diethard

3 5

Feb2013-SP2 mserver loading time much longer than Feb2013 on Windows x86_64
by Anthony Damico 03 May '13

03 May '13

Hi, after upgrading to MonetDB Feb2013-SP2, I've noticed a huge increase in the loading time for the server to get the ">" shown in the last line below (indicating it's loaded and ready for mclient connections). It now takes between three and ten minutes. Has anything changed in the Windows version of the server between Feb2013 and Feb2013-SP2 that might cause this? Once loaded, the queries are still lightning-fast, but I'm debating whether I ought to re-create all of my databases from scratch using Feb2013-SP2, if there's any reason doing that might help? Any clues? :) Thanks!!! # MonetDB 5 server v11.15.7 "Feb2013-SP2" # Serving database 'medicare_sample', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 20.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded >

1 0

question about arrays in SciQL
by Gabor Urbanics 03 May '13

03 May '13

Hi All, I have a question about MonetDB/SciQL, in particular regards to the unbounded arrays and their updates. I've created two arrays (one bounded and an unbounded) essentially with the same schemata in my database as follows: """ sql>\d ARRAY sys.facts ARRAY sys.facts_bounded sql>\d sys.source CREATE TABLE "sys"."source" ( "x" INTEGER, "y" INTEGER, "v" VARCHAR(32) ); sql>\d sys.facts CREATE ARRAY "sys"."facts" ( "x" INTEGER DIMENSION [0:1:1], "y" INTEGER DIMENSION [0:1:*], "v" VARCHAR(32) ); """ My goal at the moment is to use the unbounded array as it would fit very well the domain of my application: several coordinates-like columns and a couple of attributes, but the ranges of the coordinates are not known beforehand. Unfortunately, I am facing some problems when I try to insert values into the unbounded array: """ sql>insert into sys.facts values (0,0,'42'); 0 affected rows (0.938ms) """ On the other hand, the same insert operation works just fine with the bounded array: """ sql>insert into sys.facts_bounded values (0,0,'42'); 1 affected row (20.594ms) sql>select * from sys.facts_bounded; +------+------+------+ | x | y | v | +======+======+======+ | 0 | 0 | 42 | | 0 | 1 | null | ... (5 lines omitted for brevity) | 1 | 3 | null | +------+------+------+ 8 tuples (0.612ms) """ I would like to know if this is the supposed way one can insert values into unbounded arrays or I am doing something wrong. I compiled the source from the branch "sciql" from the mercurial repository 2 days ago, as my understanding was that the built versions do not support SciQL. Is there any other way I can get a MonetDB with SciQL support? If it helps anything, the beginning of the welcome message of the mserver5 is as follows: """ # MonetDB 5 server v11.16.0 # This is an unreleased version # Serving database 'monetdb5', using 4 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked """ Any help or suggestion is appreciated! Best regards, Gabor

2 3

column alias combined with ORDER BY returns wrong data
by Budulinku Dejmihrasku 03 May '13

03 May '13

Hi, please note the test cases below - it should be self-explanatory i am on MonetDB v11.15.7 (Feb2013-SP2), i run the test before the upgrade with the same results please advice thanks a lot milan *test case 1, no ORDER BY - correct** **----------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+ *test case 2, add ORDER BY - wrong result (repeats the 1st value for all columns)* *--------------------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 order by 5, 4, 3, 2 --order by c5,c4,c3,c2 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | a | a | a | +------+------+------+------+------+ Note that the result is the same even if you use the column alias instead of the ord.num. * **test case 3, use completely different alias with order by - correct ** **-------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as a2, D1.c2 as a3, D1.c3 as a4, D1.c4 as a5 from SAWITH0 D1 order by 5, 4, 3, 2 +------+------+------+------+------+ | c1 | a2 | a3 | a4 | a5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+

4 6

Re: should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Stefan Manegold 02 May '13

02 May '13

cast syntax is "cast( <expression> as type )" Anthony Damico <ajdamico(a)gmail.com> wrote: I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

3 3

should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Anthony Damico 02 May '13

02 May '13

I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

1 0

Understanding strides in memory accesses
by Hassan, Ahmad 01 May '13

01 May '13

Hi All, I am analysing the monetdb memory access pattern to identify various strides in LLC accesses. But most of the accesses per object are being observed as non-strided which is not what I expected. As monetdb involves BATs and these are arrays so I was thinking that there would be more strided pattern rather than random accesses unless BAT arrays are being treated as trees and random access is being used to access the items. To understand it further, I have now retrieved the execution plan of TPCH Q1 and the plan has various segments like follows: X_455:bat[:oid,:str] := sql.bind(X_7,"sys","lineitem","l_linestatus",2,5,16); X_1346 := algebra.kdifference(X_400,X_455); X_1379 := algebra.kunion(X_1346,X_455); X_1412 := algebra.leftjoin(X_1180,X_1379); (X_1452,X_1453) := group.multicolumns(X_1293,X_1412); X_1514 := bat.mirror(X_1452); X_1516 := algebra.leftjoin(X_1514,X_1412); I have two questions: 1) As there are many repeated segments like above in whole query plan, so what is the context behind multiple repeated segments please. 2) I want to understand what is the input to most common operators like leftjoin, kdifference, kunion and whether they process those inputs sequentially or randomly. What is the best way to get this understanding please? From the plan it is obvious that the input is the BAT(s), but how do the operators access the BAT is really a question. Thanks for the help. Kind Regards, Ahmad

3 3

Fwd: How to get deleted space back from monetdb
by Gaurav Meena 01 May '13

01 May '13

I have searched entire archive, and this question have been asked many times i.e. to get the space back from monetdb after deleting some rows in monetdb. The answer I found was not consistent one of them was COPY SELECT * FROM <table> INTO <file>; DELETE FROM <table>; COPY INTO <table> FROM <file>; Which suggest that deleting rows (or cleaning??) table will get back the space. One was from this question http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html 1) copy all table contents to another table, drop old table, copy again I have tried both deleting rows and then droping the table but the result was inconsistant. May be monetdb cleans up after some interval Other Related Questions were 1. http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html 2. FEB 23, 2010 Reeves, Matthew J. wrote: >* Hello,*>* *>* I have a MonetDB database that contains a table of rolling events.*>* There are 200 million records with 500,000 bulk loaded each day with the*>* oldest 500,000 deleted at midnight. I'm finding the database gets slower*>* as time goes on. It gets to the point of being almost unusable after a*>* week. Is there something I should be doing different?*Hello Matthew, Thanks for using MonetDB. Interesting application target. This is a known issue. The system keeps the deleted tuples and rebuilds the table. A vacuum-like feature is needed and on the wishlist. For now, taking a copy of the table would 'solve' your problem. regards, Martin >* *>* Thanks,*>* Matt*>* * Gaurav Meena

2 1

Inner join.
by Bouslah Ahmed 30 Apr '13

30 Apr '13

Hello, I'm working on MonetDB under win7, and I encountered some problems that hinder me 1 - I need to make an amendment on the inner join; 2 - To do this, I ask you to explain how the inner join works; 3 - In what part of the program (source code) is implemented the inner join. Cordially.

2 1

sql.clients function removed
by Pete Hollobon 29 Apr '13

29 Apr '13

Hi, It looks like the sql.clients() function was removed (changeset 45a4e2205363). I was wondering why, and whether there is a suitable alternative? Regards, Pete.

1 1

Announcement: New Feb2013-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Apr '13

29 Apr '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-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/>.

1 0

All tested on the same column, some functions work, some don't?
by Anthony Damico 24 Apr '13

24 Apr '13

Hi, I'm using MonetDB on Windows with the MonetDB.R connector. I'm getting some very strange errors on a particular column (called `rx_cso_num`) where *some* of the math functions work and others do not? I tested the median() function using mclient - I get the same error - so this has nothing to do with the fact that I'm connecting through R. I'm running these queries on a confidential data set, so unfortunately I cannot provide a reproducible example with this.. I'm sorry for the hassle, and hoping the error messages below might provide enough of a clue to resolve the issue? If this really isn't enough, I can send some snipped mclient -X output and/or try my best to create some fake data that also creates some of these problems? Thanks!! :) Here's a list of functions that *work* on the column without a problem: "count","distinct","min", "max", "sum","avg","abs","sqrt","floor","ceiling","exp","log","cos","sin","tan","acos","asin","atan","cosh","sinh","tanh" Here's the errors I get for each of the functions that do not: [1] "median" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select median(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. [1] "sign" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select sign(rx_cso_num) from x07'. Server says '!TypeException:user.s5_1[25]:'bat.insert' undefined in: _93:any := bat.insert(_83:bat[:oid,:int], _91:oid, _90:bte)'. [1] "stddev_pop" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev_pop(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev_pop(wrd)''. [1] "stddev" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev(wrd)''. [1] "prod" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select prod(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. Here's my mserver window: # MonetDB 5 server v11.15.3 "Feb2013-SP1" # Serving database 'medicare_sample', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > Thanks!!!

2 9

problem with foreign key constrain violation
by Gijs Molenaar 23 Apr '13

23 Apr '13

Hi MonetDB, I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong? sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") ); sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms) sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms) sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

4 6

Re: Bug grouping on string column, only when results filtered
by Stefan Manegold 17 Apr '13

17 Apr '13

as string-related bug has recently been fixed (see MonetDB bug tracker; I don't recall the bug ID right now) and will be shipped with the upcoming Feb2013-SP2 release, a preview of which is avilable from the testing repository. Stefan Pete Hollobon <hollobon(a)gmail.com> wrote: Hi Hannes, Thanks for looking into this. I'm using a 64-bit build. Here's my uname and the versions from the MonetDB binaries. ~% uname -a Linux lx1 3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux ~% /usr/bin/monetdbd version MonetDB Database Server v1.7 (Feb2013-SP1) ~% /usr/bin/monetdb version MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) ~% /usr/bin/mclient --version mclient, the MonetDB interactive terminal (Feb2013-SP1) support for command-line editing compiled-in character encoding: UTF-8 However, I realised that my local Feb2013 build was using the shared libraries from the Ubuntu debs. I fixed this, and that build now seems to be working properly. I can still reproduce it with the distributed binaries though. Whatever bug it was must have been fixed already - apologies for wasting your time. Regards, Pete. On 17 April 2013 12:56, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: > Hello Pete, > > thank you for your bug report and the effort you have put into making this > reproducible for us. However, having followed your instructions with both > the Feb2013 version as well as the current development branch, I was not > able to also provoke this issue. > > I got the following results (Both Feb2013 and default branch): > > ➜ ~ mclient bug -s "select a, count(*) from test where b = 20227 group by > a" > +------+--------+ > | a | L1 | > +======+========+ > | 2T | 193058 | > | 1T | 202267 | > | 5T | 87038 | > | 4T | 100302 | > | 3T | 142326 | > | 15T | 95 | > | 20T | 95 | > | 30T | 14277 | > | 7T | 767 | > | 6T | 22863 | > > | 10T | 127 | > | 6V | 216594 | > | 9T | 104 | > | 8T | 88 | > +------+--------+ > 14 tuples > > ➜ ~ mclient bug -s "select a, count(*) from test group by a" > > +------+---------+ > | a | L1 | > +======+=========+ > | 2T | 5799418 | > | 1T | 6072825 | > | 5T | 2605374 | > | 4T | 2998157 | > | 3T | 4262607 | > | 15T | 2703 | > | 20T | 2818 | > | 30T | 497657 | > | 7T | 22460 | > | 6T | 686789 | > | 10T | 3496 | > | 6V | 6550540 | > | 9T | 1853 | > | 8T | 1639 | > +------+---------+ > 14 tuples > > > Can you perhaps give more information abuont your environment? For example, > this a 64 or 32 bit build? You could also try the development default branch > and see if the problem also occurs there. > > Best, > > Hannes > > > > > > On 17/04/13 10:33, Pete Hollobon wrote: >> >> Hi, >> >> I think I've found a bug in MonetDB. It only occurs when grouping by a >> text column and filtering the results; with no "where" clause, the >> results are fine. The table is fairly large (~30 million rows). >> >> ~% ./generate_test_csv.py test.csv >> ~% mclient bug -s "create table test (b integer, a varchar(5))" >> operation successful >> ~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS >> ','" >> 29508336 affected rows >> >> There are no nulls or empty strings in column "a", however: >> >> ~% mclient bug -s "select a, count(*) from test where b = 20227 group by >> a" >> +------+--------+ >> | a | L1 | >> +======+========+ >> | | 763088 | >> | 10T | 127 | >> | 6V | 216594 | >> | 9T | 104 | >> | 8T | 88 | >> +------+--------+ >> >> When the "where" clause is removed, no nulls are returned: >> >> ~% mclient bug -s "select a, count(*) from test group by a" >> +------+---------+ >> | a | L1 | >> +======+=========+ >> | 2T | 5799418 | >> | 1T | 6072825 | >> | 5T | 2605374 | >> | 4T | 2998157 | >> | 3T | 4262607 | >> | 15T | 2703 | >> | 20T | 2818 | >> | 30T | 497657 | >> | 7T | 22460 | >> | 6T | 686789 | >> | 10T | 3496 | >> | 6V | 6550540 | >> | 9T | 1853 | >> | 8T | 1639 | >> +------+---------+ >> 14 tuples >> >> I have tested with the latest MonetDB Ubuntu debs and also with a >> local build of the Hg Feb2013 branch tip. Both exhibit the same >> problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb >> RAM. I've tried with minimal_pipe and the results are the same. >> >> Please let me know if any other information would be useful. >> >> Regards, >> Pete. >> >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Bug grouping on string column, only when results filtered
by Pete Hollobon 17 Apr '13

17 Apr '13

Hi, I think I've found a bug in MonetDB. It only occurs when grouping by a text column and filtering the results; with no "where" clause, the results are fine. The table is fairly large (~30 million rows). ~% ./generate_test_csv.py test.csv ~% mclient bug -s "create table test (b integer, a varchar(5))" operation successful ~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS ','" 29508336 affected rows There are no nulls or empty strings in column "a", however: ~% mclient bug -s "select a, count(*) from test where b = 20227 group by a" +------+--------+ | a | L1 | +======+========+ | | 763088 | | 10T | 127 | | 6V | 216594 | | 9T | 104 | | 8T | 88 | +------+--------+ When the "where" clause is removed, no nulls are returned: ~% mclient bug -s "select a, count(*) from test group by a" +------+---------+ | a | L1 | +======+=========+ | 2T | 5799418 | | 1T | 6072825 | | 5T | 2605374 | | 4T | 2998157 | | 3T | 4262607 | | 15T | 2703 | | 20T | 2818 | | 30T | 497657 | | 7T | 22460 | | 6T | 686789 | | 10T | 3496 | | 6V | 6550540 | | 9T | 1853 | | 8T | 1639 | +------+---------+ 14 tuples I have tested with the latest MonetDB Ubuntu debs and also with a local build of the Hg Feb2013 branch tip. Both exhibit the same problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb RAM. I've tried with minimal_pipe and the results are the same. Please let me know if any other information would be useful. Regards, Pete.

2 2

beginning with monetdb on windows
by Satoshi Nagayasu 09 Apr '13

09 Apr '13

Hi, I'm new to the MonetDB, and trying MonetDB on Windows. At first, I have installed MonetDB on Windows with using MonetDB5-SQL-Installer-x86_64-20130227.msi. http://dev.monetdb.org/downloads/Windows/Feb2013-SP1/ Then, I attempted to follow the Getting Started guide. http://www.monetdb.org/Documentation/Tutorial However, I couldn't find monetdb.exe(bat?) and monetdbd.exe(bat?) in my monetdb installed directory. What can I do for the first step? Any comments? Regards, -- Satoshi Nagayasu <snaga(a)uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp

4 6

MonetDB server crashing on Windows for seemingly simple SELECT statements
by Anthony Damico 01 Apr '13

01 Apr '13

Hi, I'm hitting some very strange errors with a few of my data tables. The data users agreement for these datasets does not allow me to share the actual output and I'm having trouble getting a similar error to occur on public data, so hopefully my description of what's going on will help the troubleshooting. I can send more information about the structure of these tables if that would be helpful? This command causes mserver.exe to crash every time: select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr = 64) group by state_cd order by state_cd; This command causes mserver.exe to crash after running it three consecutive times. The first two times, it works. select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr < 65) group by state_cd order by state_cd; ALL of these commands work without any trouble on the same data table: select state_cd from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select state_cd , sum( metro ) as metro from x01 group by state_cd order by state_cd; select state_cd , sum( metro ) as metro from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select distinct bene_age_at_end_ref_yr from x01 order by bene_age_at_end_ref_yr' ) select state_cd , count(sex) from x01 where (bene_age_at_end_ref_yr = 64) group by state_cd order by state_cd; select state_cd , sum(sex) from x01 group by state_cd order by state_cd; select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr >= 64) group by state_cd order by state_cd; I have no idea where to start troubleshooting what's going on here. Can anyone recommend additional tests to diagnose the root of the problem? I really don't understand how flipping the greater than sign on the otherwise same query could cause it to break.. I've pasted a bunch of diagnostics and the mclient in -X mode below, but it really doesn't seem helpful. Thanks!!!!! Here's my mserver.exe version: # MonetDB 5 server v11.15.1 "Feb2013" # Serving database 'medicare_sample', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 20.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > When running the first command that breaks inside mclient -X, here's what happens: user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013) mapi_query:128:SELECT "name", "value" FROM sys.env() AS env WHERE "name" IN ('gd k_dbname', 'monet_version', 'monet_release', 'merovingian_uri') fetch next block: start at:4 got next block: length:200 text:&1 0 3 2 3 % .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] got complete block: text:&1 0 3 2 3 % .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:&1 0 3 2 3 allocating new result set got complete block: text:% .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% .env, .env # table_name got complete block: text:% name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% name, value # name got complete block: text:% varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% varchar, varchar # type got complete block: text:% 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% 13, 15 # length got complete block: text:[ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:[ "gdk_dbname", "medicare_sample" ] got complete block: text:[ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:[ "monet_version", "11.15.1" ] got complete block: text:[ "monet_release", "Feb2013" ] read_line:[ "monet_release", "Feb2013" ] fetch next block: start at:204 got next block: length:0 text: got complete block: text:? read_line:? Database: MonetDB v11.15.1 (Feb2013), 'medicare_sample' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:206 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:209 got next block: length:0 text: got complete block: text:? read_line:? closing result set sql>select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr = 64) grou p by state_cd order by state_cd; mapi_query_part:109:select state_cd , sum(sex) from x01 where (bene_age_at_end_r ef_yr = 64) group by state_cd order by state_cd; fetch next block: start at:211 Connection terminated Press any key to continue . . . When running the second command that breaks inside mclient -X, here's what happens: The first two times, the command runs fine.. Then the third time, it gives: read_line:? +----------+-------+ 67 tuples (241.424ms) closing result set sql>select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr < 65) grou p by state_cd order by state_cd; mapi_query_part:109:select state_cd , sum(sex) from x01 where (bene_age_at_end_r ef_yr < 65) group by state_cd order by state_cd; fetch next block: start at:2403 Connection terminated Press any key to continue . . .

3 2

select + group by expression alias from a view breaks connection
by Budulinku Dejmihrasku 29 Mar '13

29 Mar '13

Hi, i hit a strange problem when using alias for an expression in group by i run into this with a complex view containing multiple UNION ALL queries but after a while everything boiled down to a fairly simple test case that will always produce the error Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP1) Database: MonetDB v11.15.3 (Feb2013-SP1), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE VIEW mmtest_v AS SELECT date'2012-01-01' as period, 1 as quantity; operation successful (213.074ms) sql>SELECT extract(month from period) as m, count(1) as cnt FROM mmtest_v GROUP BY m; *Connection terminated* I am on Ubuntu 12.04.2 LTS As the alias in GROUP BY works in other cases i expect this is a bug - pls confirm and i will be happy to file it. BTW it looks like MonetDB has generally issues with selects from more complex views - i.e. views based on another view(s). In such cases the results do not match expectations or there is even an error. I can reproduce the problem but i only on quite large set of data (typically 20+ mil. records) and after some data manipulation got involved, so i cannot give a simple test case as above. Any idea how to pass such problem (when large data-set is involved) to the development team? Thanks in advance milan

3 3

RAM consumption of MonetDB
by Hassan, Ahmad 27 Mar '13

27 Mar '13

Hi Team, Please may I ask what main memory management strategy has been adopted for MonetDB in terms of memory utilization. For example I loaded the TPCH benchmark dataset in MonetDB (v11.13.5) with scale factor 40. The data on disk is 43Gb in size. But when I run the TPCH queries then the main memory used by mserver stays in the range of 0.5GB to 3GB. I was thinking that the monetDB would load the tables into memory because TPCH queries are doing joins over various tables but it seems that monetdb loads only small subset of the complete dataset. Please can someone explain what is going in the background and why I always see 0.5GB to 3GB memory resident size of mserver process even for dataset of size 43G and if this is the normal expected behaviour of monetdb? The farm size on disk is: farms/f1/db1/bat# du . -hs 48G . Thanks. Kind Regards, Ahmad

3 5

Re: Quickstart Guide
by Stefan Manegold 20 Mar '13

20 Mar '13

Sterling, Thanks for notifing! Could you please file a bug report via http://bugs.monetdb.org/ ? Thanks! Stefan Sterling Paramore <gnilrets(a)gmail.com> wrote: The link at http://www.monetdb.org/Downloads/Tutorial is broken and I don't see any way to contact the creators of the web page. How can I get information about how to install MonetDB? Thanks, Sterling

4 4

Re: Quickstart Guide
by Stefan Manegold 20 Mar '13

20 Mar '13

Sterling, did you start the monetdb daemon (monetdbd), first? See also http://www.monetdb.org/Documentation/monetdbd Stefan Sterling Paramore <gnilrets(a)gmail.com> wrote: Thanks, but I'm not terribly interested in building from source. These installation instructions seem to end after it's compiled. I downloaded the binaries for OS X and put them in /usr/local/monetdb, and added /usr/local/monetdb/bin to my PATH. I found http://www.monetdb.org/Documentation/Tutorial (which is maybe supposed to be the same thing as the broken link?) But when I try the first step, I get the following error: [- opt -] monetdb create /opt/monetdb monetdb: cannot find a control socket, use -h and/or -p Google was not helpful. Any ideas? Thanks, Sterling On Tue, Mar 19, 2013 at 6:52 PM, Hassan, Ahmad <ahmad.hassan(a)sap.com> wrote: > HI Sterling,**** > > ** ** > > Installation from source can be seen at: > http://www.monetdb.org/wiki/MonetDB:Building_from_sources**** > > ** ** > > Cheers, Ahmad**** > > ** ** > > *From:* users-list-bounces+ahmad.hassan=sap.com(a)monetdb.org [mailto: > users-list-bounces+ahmad.hassan=sap.com(a)monetdb.org] *On Behalf Of *Sterling > Paramore > *Sent:* 20 March 2013 01:49 > *To:* users-list(a)monetdb.org > *Subject:* Quickstart Guide**** > > ** ** > > The link at http://www.monetdb.org/Downloads/Tutorial is broken and I > don't see any way to contact the creators of the web page. How can I get > information about how to install MonetDB?**** > > ** ** > > Thanks,**** > > Sterling**** > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > >

1 0

Quickstart Guide
by Sterling Paramore 20 Mar '13

20 Mar '13

The link at http://www.monetdb.org/Downloads/Tutorial is broken and I don't see any way to contact the creators of the web page. How can I get information about how to install MonetDB? Thanks, Sterling

2 2

Performance of PHP client > fetching rows
by Richard Siebeling 18 Mar '13

18 Mar '13

Hi, I querying my database using a script in PHP and returning the result as a JSON (so that it can be retrieved in Javascript). However I notice that querying the database (sending the query to the database) takes about half the time if takes PHP to retrieve the rows (row by row using monetdb_fetch_object). In the documentation I see that the MAPI interface has a fetch_all_rows function, however I can't find this function in the PHP library. In the Python example I see that you are also using a cursor.fetchall() procedure. What is the best way to approach this problem > querying the database and returning all rows as a JSON object Is PHP the way to go (it is the easiest for me, since I know that), if it is, what is the best way or should I consider another language like Python or maybe Java to implement this function? Below I list the PHP code I see that send the query ($q_statement) to the database and formulating the JSON: // Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds error_log($execution_time.'sec - Formulate query'); $time_start = microtime(true); $db = monetdb_connect($lang = "sql", $host = "127.0.0.1", $port = "50000" , $username = "monetdb", $password = "monetdb", $database = "social" ) or die(monetdb_last_error()); $res = monetdb_query($db, monetdb_escape_string($q_statement)); // Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds error_log($execution_time.'sec - Query database'); $time_start = microtime(true); /* Iterate over the result set returning rows as objects */ while ( $row = monetdb_fetch_object($res) ) { $rows[] = $row; } echo json_encode($rows); // Measure time $time_end = microtime(true); $execution_time = ($time_end - $time_start); //dividing with 60 will give the execution time in minutes other wise seconds $total_execution_time = ($time_end - $initial_start); error_log($execution_time.'sec - Return records'); error_log($total_execution_time.'sec - Total time'); $time_start = microtime(true); thanks in advance, Richard

2 1

Single Quotes VS Double Quotes
by Diethard Steiner 17 Mar '13

17 Mar '13

Hi, I ran into a problem when setting up a user with single quotes: sql>CREATE USER 'tpch' WITH PASSWORD 'tpch' NAME 'TPC-H Admin' SCHEMA 'sys'; syntax error, unexpected STRING in: "create user 'tpch'" Then I followed exactly the quatation style of the voc example and it worked: sql>CREATE USER "tpch" WITH PASSWORD 'tpch' NAME 'TPC-H Admin' SCHEMA "sys"; operation successful (3.663ms) So now with user and schema enclosed in double quotes, password and name in single quotes, it worked. This inconsistent usage of single and double quotes seems a bit strange to me. Should I file a bug report for this? Or is there a specific reason for this? As a final test I enclosed all values in double quotes: sql>CREATE USER "test2" WITH PASSWORD "test2" NAME "Test User 2" SCHEMA "sys"; syntax error, unexpected IDENT, expecting STRING in: "create user "test2" with password "test2"" Thanks, Diethard

2 2

Extract quarter from date
by Diethard Steiner 17 Mar '13

17 Mar '13

Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders; I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract(quarter" SQLState: 42000 ErrorCode: 0 Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine. Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code. Thanks, Diethard

4 6

Connecting Tableau to MonetDB
by Pedro Salgueiro 15 Mar '13

15 Mar '13

Hi, I am trying to connect Tableau to a MonetDB instance which is running in a linux machine, using the ODBC MonetDB driver. Tableau manages to connect to MonetDB and is able to list the tables, but then it fails with the following error while performing any Analysis: [MonetDB][ODBC Driver 11.13.9] Invalid identifier 'none:product:nk' > I tried two versions of the ODBC, the latest and Oct2012-SP, but the problem is present in both versions. It's worth pointing that I am able to use the same MonetDB with the JDBC with no problems. <http://dev.monetdb.org/downloads/Windows/Oct2012-SP3/> While googling for this particular problem, I found the following mail in the mailing list: http://mail.monetdb.org/pipermail/users-list/2011-November/005248.html Has there been any developments in this field? Best regards, Pedro Salgueiro

2 1

standard deviation in SQL
by Anshuman Kumar 15 Mar '13

15 Mar '13

Hi, I'm trying to find an aggregate function to find standard deviation. Does monetDB support such a function? For example:- to find the average of the selected values we can use- Select AVG(age) from students where grade = 4; I was taking the help of google and found this link- http://old.nabble.com/users-list-Digest,-Vol-3,-Issue-8-td34660082.html Then I tried the solution given by Percy Wegmann in the above link. After that when I run this query- Select STDEV(age) from students where grade = 4; [Age being a column of type double AND I get similar kind of error for int] I get this error - TypeException:user.s1_1[21]:'aggr.stddev' undefined in: _33:any := aggr.stddev(_ 32:bat[:oid,:dbl]) program contains errors I need to have this function working in order to be able to use MonetDB. Kindly help. Regards, Anshuman

3 4

stddev function disappeared?
by Anthony Damico 14 Mar '13

14 Mar '13

Hello, I'm trying to run some old code with a recent upgrade of MonetDB and the standard deviation function appears to have disappeared. I'm running MonetDB on Windows. This issue was previously discussed and resolved in this thread: http://old.nabble.com/users-list-Digest%2C-Vol-3%2C-Issue-8-tt34660082.html… I could re-open this bug report if appropriate? http://bugs.monetdb.org/show_bug.cgi?id=2454#c18 My server specs are below, and the actual commands below that. Thank you!!! # MonetDB 5 server v11.15.1 "Feb2013" # Serving database 'bsapuf', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50003/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > c:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013) Database: MonetDB v11.15.1 (Feb2013), 'bsapuf' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select sum( hha_pmt_amt ) from hha08; +-----------+ | L1 | +===========+ | 675676300 | +-----------+ 1 tuple (2.311ms) sql>select sum( hha_pmt_amt ), avg( hha_pmt_amt ) from hha08; +-----------+--------------------------+ | L1 | L2 | +===========+==========================+ | 675676300 | 5412.8151310993435 | +-----------+--------------------------+ 1 tuple (63.559ms) sql>select sum( hha_pmt_amt ), median( hha_pmt_amt ) from hha08; +-----------+------+ | L1 | L2 | +===========+======+ | 675676300 | 3500 | +-----------+------+ 1 tuple (9.601ms) sql>select sum( hha_pmt_amt ), stddev( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'stddev(int)' sql>select sum( hha_pmt_amt ), sd( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'sd(int)' sql>select sum( hha_pmt_amt ), sddev( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'sddev(int)' sql>select sum( hha_pmt_amt ), var( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'var(int)' sql>select sum( hha_pmt_amt ), variance( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'variance(int)' sql>select sum( hha_pmt_amt ), stdevp( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'stdevp(int)' sql>

3 4

SQL function for getting the last inserted ID
by Gijs Molenaar 12 Mar '13

12 Mar '13

Hi, A typical question that is probably asked before, but I can't find an answer in the archives or in the doc. Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with: SELECT NEXT VALUE FOR seq_freq INTO freqid; But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type. Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 3

Insertion problem for large number of columns
by Shajon 12 Mar '13

12 Mar '13

Dear Monet Team, Today I was trying to insert data in MonetDB using " insert into ." command through jdbc from my application but the server is crashing & no data is inserting. That table has 25 columns and I am passing all values. Thanks Shajon

5 6

Questions
by Bouslah Ahmed 10 Mar '13

10 Mar '13

Hi, I am a university student and I use MonetDB. I have two questions to ask you regarding loading data using mclient: 1 - Is it possible to load multiple csv files into multiple tables in one query? For exemple i have five csv files and five tables and I want to use a single command line instead of five mclient -u us1 -d demo -s " COPY INTO DWDATE FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\DATE.tbl mclient -u us1 -d demo -s " COPY INTO CUSTOMER FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\CUSTOMER.tbl mclient -u us1 -d demo -s " COPY INTO SUPPLIER FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\SUPPLIER.tbl mclient -u us1 -d demo -s " COPY INTO PART FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\PART.tbl mclient -u us1 -d demo -s " COPY INTO LINEORDER FROM STDIN USING DELIMITERS '|','\n'" -<F:\2Go\LINEORDER.tbl 2 - Is it possible to enter the password in the request as well as user and database name? This will save me introduce each time the password For exemple mclient -u us1 -d demo -s " COPY INTO DWDATE FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\DATE.tbl <PassWord> thank you.

1 0

Parse dates in CSV file, CSV vault
by Aubrey Barnard 10 Mar '13

10 Mar '13

MonetDB Users, Is YYYY-MM-DD the only date format that can be parsed and read from a CSV with the COPY command? Or can the format be specified? On a related note, is the CSV vault feature ready for use (Feb2013 version)? The mailing list implies it is not ready and there is nothing in the release notes. If it is ready, how do I set it up? Thanks much for helping me get my data loaded. Aubrey Barnard

2 1

RE: Installation on Ubuntu 12.10 Quantal
by Duncan Garmonsway 10 Mar '13

10 Mar '13

Thanks, that worked. Didn't realise I was still such a noob... Cheers, Duncan On 10 March 2013 00:00, <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 > http://mail.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. INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state (Percy Wegmann) > 2. Re: INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state (Martin Kersten) > 3. Installation on Ubuntu 12.10 Quantal (Duncan Garmonsway) > 4. Re: Installation on Ubuntu 12.10 Quantal (Brandon Jackson) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 8 Mar 2013 10:22:53 -0600 > From: Percy Wegmann <percy(a)clicksecurity.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state > Message-ID: > < > CADpZG+Scab3V8u_isKziJobUgCYO4BEmkCWMB8iOD-pjggTWQQ(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > Dear Monet Team, > > Today I ran into an issue with copying data between tables. I've tried it > on 11.13.7 and 11.13.9 with the same results. What I find is that when > including an OFFSET clause in my source query, the destination table ends > up in a weird state. > > I've logged a bug for it - http://bugs.monetdb.org/show_bug.cgi?id=3249 > > I'm using the workaround of ordering my data and using a LIMIT instead of > an OFFSET, which seems to work. Just wanted to let you know. > > Thanks, > Percy >

1 0

Installation on Ubuntu 12.10 Quantal
by Duncan Garmonsway 09 Mar '13

09 Mar '13

Hello, After installation I am getting the error: $ monetdbd create ~/monetdb/ monetdbd: error while loading shared libraries: libstream.so.3: cannot open shared object file: No such file or directory But the library (and the symlink to it) are right here: $ ls -l /usr/local/lib | grep libstream -rwxr-xr-x 1 root root 943 Mar 9 14:23 libstream.la lrwxrwxrwx 1 root root 18 Mar 9 14:23 libstream.so -> libstream.so.3.1.5 lrwxrwxrwx 1 root root 18 Mar 9 14:23 libstream.so.3 -> libstream.so.3.1.5 -rwxr-xr-x 1 root root 107879 Mar 9 14:23 libstream.so.3.1.5 And there were no errors reported during any of ./configure sudo make sudo make install My installation script was: sudo apt-get install libxml2-dev bison ant flex unixODBC-dev # download and unpack source wget http://dev.monetdb.org/downloads/sources/Latest/MonetDB-11.15.3.tar.xz tar -xvf MonetDB-11.15.3.tar.xz # install cd MonetDB-11.15.3 ./configure sudo make sudo make install Help much appreciated, Duncan

2 1

INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave destination table in an inconsistent state
by Percy Wegmann 08 Mar '13

08 Mar '13

Dear Monet Team, Today I ran into an issue with copying data between tables. I've tried it on 11.13.7 and 11.13.9 with the same results. What I find is that when including an OFFSET clause in my source query, the destination table ends up in a weird state. I've logged a bug for it - http://bugs.monetdb.org/show_bug.cgi?id=3249 I'm using the workaround of ordering my data and using a LIMIT instead of an OFFSET, which seems to work. Just wanted to let you know. Thanks, Percy

2 1

Announcement: New Feb2013-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 07 Mar '13

07 Mar '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-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/>. Feb 2013-SP1 bugfix release Bug Fixes * 3228: Python client - mapi.Connection.__getbyes should throw exception when 0 bytes received from socket * 3229: GRANT does not work for UPDATE * 3230: GRANT SELECT priveleges not set in SQL FUNCTIONs * 3233: getting a crash of server with segmentation fault on SLES 11 64 SP2 * 3235: MapiPP.pm unquote '0' as 'undef' * 3236: mclient \d produces no output * 3237: Assertion on BATgroup_internal * 3241: SEGFAULT on string processing when using optimizer.reduce()

1 0

good new monetdb question on stackoverflow
by Anthony Damico 28 Feb '13

28 Feb '13

http://stackoverflow.com/questions/15136868/why-is-group-by-giving-only-one… I couldn't figure out the answer..

2 1

Investigating Segfault
by Percy Wegmann 25 Feb '13

25 Feb '13

Dear Monet Team, We're having an issue in which certain data is causing mserver5 to crash. After this condition is hit, mserver5 crashes at every startup and always dumps an identical core. We're running Monet 11.13.7 on Ubuntu Linux 64 bit. The error is happening on line 1142 of gdk_atoms.c: if (GDK_STRCMP(v, (str) (next + 1) + extralen) == 0) { Examining the core dump revealed that (next + 1) + extralen is referring to an out of bounds address. Here's the backtrace: #0 0x00007faf58414829 in strPut (h=0x1e2d180, dst=0x7fff592cf8f8, v=0x314dac0 "SAD014H1") at gdk_atoms.c:1142 #1 0x00007faf582dc935 in BATappend (b=0x1e2cf90, n=0x32dfdb0, force=1 '\001') at gdk_batop.c:578 #2 0x00007faf584c301e in la_bat_updates (lg=0x2d9b030, la=0x2c3ef48) at gdk_logger.c:429 #3 0x00007faf584c3cf9 in la_apply (lg=0x2d9b030, c=0x2c3ef48) at gdk_logger.c:645 #4 0x00007faf584c3f26 in tr_commit (lg=0x2d9b030, tr=0x2e247d0) at gdk_logger.c:705 #5 0x00007faf584c4533 in logger_readlog (lg=0x2d9b030, filename=0x7fff592d1e80 "/opt/clicksecurity/data/_monetdb/click/sql_logs/sql/log.56") at gdk_logger.c:823 #6 0x00007faf584c482a in logger_readlogs (lg=0x2d9b030, fp=0x2d9b160, filename=0x7fff592d3f90 "/opt/clicksecurity/data/_monetdb/click/sql_logs/sql/log") at gdk_logger.c:896 #7 0x00007faf584c6f3e in logger_new (debug=0, fn=0x7faf500adfa8 "sql", logdir=0x7faf50090a08 "sql_logs", dbname=0x1fa3da0 "click", version=52001, prefuncp=0x7faf500746a1 <bl_preversion>, postfuncp=0x7faf500747ed <bl_postversion>) at gdk_logger.c:1420 #8 0x00007faf584c704e in logger_create (debug=0, fn=0x7faf500adfa8 "sql", logdir=0x7faf50090a08 "sql_logs", dbname=0x1fa3da0 "click", version=52001, prefuncp=0x7faf500746a1 <bl_preversion>, postfuncp=0x7faf500747ed <bl_postversion>) at gdk_logger.c:1446 #9 0x00007faf50075b19 in bl_create (logdir=0x7faf50090a08 "sql_logs", dbname=0x1fa3da0 "click", cat_version=52001) at bat_logger.c:249 #10 0x00007faf50060ce4 in store_init (debug=0, store=store_bat, logdir=0x7faf50090a08 "sql_logs", dbname=0x1fa3da0 "click", stk=0) at store.c:1287 #11 0x00007faf4ffe3d3c in mvc_init (dbname=0x1fa3da0 "click", debug=0, store=store_bat, stk=0) at sql_mvc.c:51 #12 0x00007faf4ff66874 in SQLinit () at sql_scenario.c:230 #13 0x00007faf4ff6651f in SQLprelude () at sql_scenario.c:159 #14 0x00007faf58b3085d in malCommandCall (stk=0x2d36e80, pci=0x2ea5520) at mal_interpreter.c:137 #15 0x00007faf58b331b5 in runMALsequence (cntxt=0x7faf5988c020, mb=0x1e04310, startpc=1, stoppc=0, stk=0x2d36e80, env=0x0, pcicaller=0x0) at mal_interpreter.c:710 #16 0x00007faf58b323c1 in runMAL (cntxt=0x7faf5988c020, mb=0x1e04310, startpc=1, mbcaller=0x0, env=0x0, pcicaller=0x0) at mal_interpreter.c:454 #17 0x00007faf58b60a08 in MALengine (c=0x7faf5988c020) at mal_session.c:619 #18 0x00007faf58b5f21f in malBootstrap () at mal_session.c:64 #19 0x00007faf58b1313b in mal_init () at mal.c:244 #20 0x000000000040340e in main (argc=22, av=0x7fff592db568) at mserver5.c:582 I'm digging into it now, but I was hoping that it might ring some bells. Thanks, -- Percy Wegmann +1 512 637 8500 ext 148

2 3

MonetDB Feb2013 Release --dbpath option
by Gennadiy 22 Feb '13

22 Feb '13

Hello, Before MonetDB Feb2013 Release, I used to start MonetDB using the following Windows command: MSQLserver.bat --dbname=MyNewDatabase MSQLserver.bat called M5server.bat, M5server.bat set --dbfarm option. The result was that database with name specified with --dbname option was created in the directory specified with --dbfarm option. MonetDB Feb2013 Release replaced --dbname and --dbfarm options with single --dbpath option. This --dbpath option is set to some_path\dbfarm\demo in M5server.bat. So, now (MonetDB Feb2013 Release) when I start MonetDB with the following Windows command: MSQLserver.bat --dbpath=some_path\MyNewDatabase MonetDB ignores my --dbpath option, because it uses --dbpath option from M5server.bat! So, how can I start MonetDB and create my own database? Thank you very much, Gennadiy

1 1

Windows Concurrency Error when accessing two separate tables in two separate mclient instances connected to the same mserver?
by Anthony Damico 22 Feb '13

22 Feb '13

Hi, I am using a brand new mserver.exe instance of MonetDB5 on Windows. I am getting the error: "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" that I'm not sure I should? Shouldn't I be able to use the COPY command on two separate CSV files into two separate data tables at once? I don't think this behavior is correct, but maybe I'm wrong? :/ If this is incorrect behavior, should I file a bug report for this? The files you can use to re-create this error are: http://downloads.cms.gov/BSAPUF/2008_BSA_PartD_Events_PUF_1.zip http://downloads.cms.gov/BSAPUF/2008_BSA_PartD_Events_PUF_2.zip I create an mserver.exe instance with: # MonetDB 5 server v11.13.9 "Oct2012-SP3" # Serving database 'bsapuf', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50003/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded Then I ran this command in the first mclient: CREATE TABLE bsa1 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECISION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_STR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE PRECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; copy 1000000 offset 2 records into bsa1 from 'c:\\temp\\2008_BSA_PartD_Events_PUF_1.csv' using delimiters ',' NULL AS '' ; And (at the same time) ran this command in the second mclient: CREATE TABLE bsa2 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECISION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_STR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE PRECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; copy 1000000 offset 2 records into bsa2 from 'c:\\temp\\2008_BSA_PartD_Events_PUF_2.csv' using delimiters ',' NULL AS '' ; Here were the results. One worked, one didn't: Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\anthonyd.KFF>cd .. C:\Users>cd .. C:\>cd "Program Files\MonetDB\MonetDB5" C:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012-SP3) Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsapuf' Type \q to quit, \? for a list of available commands auto commit mode: on sql> sql> sql>CREATE TABLE bsa2 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECI SION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_S TR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE P RECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; operation successful (9.511ms) sql> sql>copy 1000000 offset 2 records into bsa2 from 'c:\\temp\\2008_BSA_PartD_Event s_PUF_2.csv' using delimiters ',' NULL AS '' ; 1000000 affected rows (42.4s) sql> XXXXXXXXXXXXXXXXXXXXXXXXXXXXX Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\anthonyd.KFF>cd .. C:\Users>cd .. C:\>cd "Program Files\MonetDB\MonetDB5" C:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012-SP3) Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsapuf' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d sql>CREATE TABLE bsa1 (PDE_EVENT_ID VARCHAR(255), BENE_SEX_IDENT_CD DOUBLE PRECI SION, BENE_AGE_CAT_CD DOUBLE PRECISION, PDE_DRUG_CD DOUBLE PRECISION, PDE_DRUG_S TR_CD DOUBLE PRECISION, PDE_DRUG_STR_UNITS_CD DOUBLE PRECISION, PDE_DRUG_DOSE_CD DOUBLE PRECISION, PDE_DRUG_CLASS_CD DOUBLE PRECISION, PDE_DRUG_QTY_DIS DOUBLE P RECISION, PDE_DRUG_DAY_SPLY_CD DOUBLE PRECISION, PDE_DRUG_COST DOUBLE PRECISION, PDE_DRUG_PAT_PAY_CD DOUBLE PRECISION, PDE_DRUG_TYPE_CD DOUBLE PRECISION) ; operation successful (8.636ms) sql> sql>copy 1000000 offset 2 records into bsa1 from 'c:\\temp\\2008_BSA_PartD_Event s_PUF_1.csv' using delimiters ',' NULL AS '' ; 1000000 affected rows (41.6s) COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK in stead sql> sql> Thanks as always! Anthony

2 2

Announcement: New Feb2013 Feature release of MonetDB suite
by Sjoerd Mullender 22 Feb '13

22 Feb '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013 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/>. Feb 2013 feature release Testing Environment * enabled "top-level" Mtest.py So far, while Mtest.py could be called in any subdirectory of the MonetDB source tree (and could then run all tests in the entire sub-tree), it was not possible to call Mtest.py in the top-level MonetDB source directory to run all tests. Instead, to run all tests, Mtest.py had to be called at least 4 times, once in each of these directories: "clients", "monetdb5", "sql", "geom". Now, it is possible to call Mtest.py once in the top-level MonetDB source directory to run all tests in one go. The behaviour of calling Mtest.py in any subdirectory, including the four mentioned above, did not changed, other than that now obsolete command line options "-p / --package <package>" and "-5 / --monetdb5" have been removed. Java Module * merocontrol was changed to return server URIs, and lastStop time. Connections and dbpath were removed. * Mapi protocol v8 support was removed from MapiSocket. Protocol v8 has not been used by the servers any more since Apr2012 release Client Package * Mapi protocol v8 support was removed from all client drivers. Protocol v8 has not been used by the servers any more since Apr2012 release * The tool mnc was removed from installations * msqldump: Implmented an option (--table/-t) to dump a single table. * Changed msqdump's trace option to be in line with mclient. In both cases, the long option is --Xdebug and the short option is -X. MonetDB5 Server * mserver5: The --dbname and --dbfarm options have been replaced by the single --dbpath option. * The scheduler of mserver5 was changed to use a fixed set of workers to perform the work for all connected clients. Previously, each client connection had its own set of workers, easily causing resource problems upon multiple connections to the server. Merovingian * Upgrade support for dbfarms from Mar2011 and Aug2011 was dropped * monetdb status now uses a more condensed output, to cater for the uris being shown, and prints how long a database is stopped, or how long ago it crashed * monetdb status now prints the connection uri for each database, when available. The connections and database path properties have been dropped. * monetdb status now prints last crash date only if the database has not been started since. Bug Fixes * 2291: small doubles end up as NULL after arithmetic * 3215: Calculation Date function using interval and year * 3033: stethoscope needs better documentation * 3084: Timestamp arithmetic very slow (especially on Windows) * 3125: Python tests fail after recent Python API changes * 3172: assertion fails on table function with subselects as parameters * 3178: one scan is enough to implement ALGstdev_@1 in monetdb5/modules/kernel/algebra.mx * 3179: LIKE: batstr.like+algebra.uselect called instead of pcre.like_filter * 3193: Expressions not supported in the GROUP BY or ORDER BY clause. * 3216: "unknown property" error setting format and width in .monetdb file * 3217: gdk_posix fails to compile under NetBSD * 3221: can no execute large statements * 3227: MT_set_lock() call on an non-initialized lock

5 11

MonetDB documentation
by Brandon Jackson 15 Feb '13

15 Feb '13

Just curious, I follow the mailing lists closely very recently. Is there a way, or does it make sense for someone like me to have access to update the MonetDb documentation based on responses from the maintainers here? I would be happy to collect some of this information and send it in for review before posting as documentation. Brandon Sent from my iPhone

2 2

Windows Networked Drive Issue with COPY INTO
by Anthony Damico 15 Feb '13

15 Feb '13

I am running MonetDB5 Oct 2012 SP3 on my work Windows 7 desktop computer. My office has what I imagine is a pretty typical mix of networked machines, with a few more powerful servers and larger shared drives that everyone can connect to. (1) When I attempt to import a large CSV file into a MonetDB server on the _local_ disk (C:\My Directory\MonetDB\dbfarm), the importation command runs as expected. (2) When I attempt to import the same CSV file to a MonetDB server stored on a networked drive, the import command fails. (3) Finally, when I *remote desktop* into the server that the networked drive is actually attached to, the importation succeeds again! Here's the error seen in scenario (2) -- GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !OS: The process cannot access the file because it is being used by another process. GDKunlink(bat\13\1327.theap) !OS: The process cannot access the file because it is being used by another process. So it seems pretty clear that something about windows networked drives is the culprit. This error does not occur on every file (larger files seem more prone to the error), but if an import command does not work on a file, it _consistently_ does not work on that file. As an example, importing this large file works with methods (1) and (3) but not (2) -- http://downloads.cms.gov/BSAPUF/2008_BSA_Carrier_Line_Items_PUF_1.zip I tried disabling the windows firewall and using different server ports, but I still hit the above error. I spoke with Hannes about it briefly, he said: "The issue is probably related to the Windows mmap() implementation when talking to a SMB filesystem. It is far from certain that there is something we can do about this, but in either case, we'd like to know about it." Should I submit an official bug report to http://bugs.monetdb.org/ ? Perhaps we are overlooking something obvious about our windows network's configuration.. Thanks!!! Here is my MonetDB server info, followed by the COPY INTO commands run in -X mode for each of the three scenarios: # MonetDB 5 server v11.13.9 "Oct2012-SP3" # Serving database 'bsa', using 2 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 15.873 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded ************************** (1) - local disk - works ----- fetch next block: start at:196 got next block: length:0 text: got complete block: text:☺ read_line:☺ Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsa' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:198 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:201 got next block: length:0 text: got complete block: text:☺ read_line:☺ closing result set sql>create table mytable ( mapi_query_part:23:create table mytable ( fetch next block: start at:203 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:206 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (118.294ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Directory\BSAPUF\2008 \2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS ' ' ; mapi_query_part:160:COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Direc tory\BSAPUF\2008\2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n ','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:14 text:&2 9676439 -1 got complete block: text:&2 9676439 -1 read_line:&2 9676439 -1 allocating new result set fetch next block: start at:284 got next block: length:0 text: got complete block: text:☺ read_line:☺ 9676439 affected rows (3m 26s) closing result set sql> ************************** (2) - networked disk - fails ----- read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (289.145ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Directory\BSAPUF\2008 \2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS ' ' ; mapi_query_part:160:COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Direc tory\BSAPUF\2008\2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n ','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:252 text:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !!OS: The process cannot access the file because it is being used by another pro cess. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! got complete block: text:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !!OS: The process cannot access the file because it is being used by another pro cess. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) allocating new result set got complete block: text:!!OS: The process cannot access the file because it is being used by anothe r process. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!!OS: The process cannot access the file because it is being used by a nother process. got complete block: text:!GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!GDKunlink(bat\13\1327.theap) got complete block: text:!!OS: The process cannot access the file because it is being used by anothe r process. ! read_line:!!OS: The process cannot access the file because it is being used by a nother process. got complete block: text:! read_line:! fetch next block: start at:522 got next block: length:0 text: got complete block: text:☺ read_line:☺ GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !OS: The process cannot access the file because it is being used by another proc ess. GDKunlink(bat\13\1327.theap) !OS: The process cannot access the file because it is being used by another proc ess. closing result set sql> ************************** (3) - remote desktop into server so networked disk becomes local disk - works ----- read_line:[ "monet_release", "Oct2012-SP3" ] fetch next block: start at:196 got next block: length:0 text: got complete block: text:☺ read_line:☺ Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsa' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:198 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:201 got next block: length:0 text: got complete block: text:☺ read_line:☺ closing result set sql>create table mytable3 ( mapi_query_part:24:create table mytable3 ( fetch next block: start at:203 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:206 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (276.500ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable3 FROM 'v:\temp\2008_BSA_Carrier_L ine_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS '' ; mapi_query_part:141:COPY 9676440 offset 2 records INTO mytable3 FROM 'v:\temp\20 08_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:14 text:&2 9676439 -1 got complete block: text:&2 9676439 -1 read_line:&2 9676439 -1 allocating new result set fetch next block: start at:284 got next block: length:0 text: got complete block: text:☺ read_line:☺ 9676439 affected rows (2m 14s) closing result set sql>

2 2

TPCH Throughput/latency for MonetDB
by Hassan, Ahmad 12 Feb '13

12 Feb '13

Hi, I intend to run TPCH benchmark on MonetDB. I have executed one iteration of benchmark through run.all script provided under sql/benchmarks/tpch. Please may I know if there is a script or framework available to run tpch benchmark for 'X' duration and get throughput and latency numbers for each query? Second, I want to generated data with big scale factor. I searched monetdb mailing list and found out that DBGEN should be used for that. As there is no specific DBGEN provided by monetdb so I have downloaded from http://www.tpc.org/tpch/ . But please let me know which of the following database shall I select while building DBGEN. Official DBGEN supports the following database options: INFORMIX, DB2, TDAT (Teradata), SQLSERVER, SYBASE, ORACLE, VECTORWISE Thanks for the help Kind Regards, Ahmad

1 0

monetdb status health
by Tapomay Dey 12 Feb '13

12 Feb '13

My questions are simple: what causes crashes? what is health? how do we stop health from degrading? Following is the status of my db- msearch_stats_db: database name: msearch_stats_db state: running locked: no scenarios: mal sql msql start count: 140 stop count: 1 crash count: 138 current uptime: 1m 49s average uptime: 15m 33s maximum uptime: 15m 33s minimum uptime: 15m 33s last start with crash: 2013-02-10 17:32:36 last start: 2013-02-10 17:32:47 last stop: 2013-02-06 11:27:15 average of crashes in the last start attempt: 0 average of crashes in the last 10 start attempts: 0.90 average of crashes in the last 30 start attempts: 0.97 Regards, Tapomay.

6 14

Suggested correction for the Java SQLcopyinto example
by Percy Wegmann 09 Feb '13

09 Feb '13

Dear Monet team, I've been getting quite a bit of use out of your example code here: http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java Recently, though, I ran into a problem. If the "COPY INTO" statement that I issue is wrong, for example the referenced table doesn't exist, Monet comes back with an error. The example isn't checking for this error condition and proceeds to send data anyway. With a really large batch of data, this becomes a problem because the SQL parser ends up trying to parse all that data as if it were SQL and basically chokes on it. The solution is to add something like the following after the out.newLine() on line 86: error = in.waitForPrompt(); if (error != null) { throw new Exception(error); } Cheers, -- Percy Wegmann +1 512 637 8500 ext 148

3 5

Requirement gathering and questions about binary bulk loading.
by Brandon Jackson 08 Feb '13

08 Feb '13

Hello fellow MonetDB'ers, The Pentaho MonetDB Bulk Loader transformation step is now working pretty well in our tests. http://ci.pentaho.com/view/Data%20Integration/job/Kettle contains a continuous integration build of the software if anyone wanted to play with it. New Transformation -> Steps -> Bulk Loading (category)-> MonetDB Bulk Loader Thank you for the previous information about enabling log output from the MonetDB JDBC driver. After killing a stalled "monetdb start ~/sg-farm" thread and restarting it, MonetDB decided to work really well. It was suggested early on that I take a look at the binary bulk loading approach to get the fastest results. Now that I have a streaming ascii version of a bulk loader done for Pentaho Data Integration, I wanted to come back to the binary approach and see what that might look like and what to be aware of. I read http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad It looks like the requirements are: 1. Write a physical file per column to the disk the monetdb farm exists on. 2. Tables with DATES, TIMESTAMPS or UTF-8 characters ie: Simplified Chinese are verboten and must be handled with COPY INTO .... USING DELIMITERS Assuming the above is a correct understanding, given the command below: copy into Tmp from ('path_to_file_i', 'path_to_file_f', 'path_to_file_s'); 1. If run from a computer separate from the machine running the monetdb server, would the 'path_to_file_i' be a pathname relative to the monetdb farm root directory on the remote server machine? 2. This process should work for appending new data on to an existing table, right? 3. Am I out of luck if my table has any of the verboten data types mentioned above mixed in with supported binary bulkloadable types; aka C-array style data types? 4. If not out of luck for a mixed type table, what is the procedure to load the table? In practice I imagine I will not need to fret too much about DATES needing a COPY INTO .... USING DELIMITERS ascii streaming approach because it is likely part of a dimension table with integer keys matching integers on a fact table, which is far more likely to have the supported types MonetDB Binary Bulk-loading likes. Is it true that MonetDB does not handle joins well, but instead prefers single, denormalized tables with many columns? >From what you know of the internals of MonetDb, is the effort to make some kind of streaming binary bulk loader worth it in terms of execution time on the database converting to native binary types? The documentation used the words, 'slightly faster', but I do not have a feel for when 'slightly' makes a big difference. Thanks again for considering these questions. Brandon

2 1

High memory usage while loading data with many columns
by Andrew Duffey 08 Feb '13

08 Feb '13

Hi, I'm loading data into a table with >20,000 columns and several hundred thousand rows using "COPY INTO". It works, but I can't seem to make it take less than about 30GB of memory. I've tried specifying the number of records as well. Is there any way to reduce memory usage when loading? Is this much memory usage expected? It's a shame because our queries take very little memory. We have to size our machine simply to handle the load. It's impressive (and very convenient) that I can actually have a table with so many columns, but are there any other limitations I might come across? I've just been trying to keep the column count in the queries low. A "select *" seems to crash MonetDB. Thanks, Andrew

2 8

Data storage capacity in monetdb
by KHALED DEHDOUH 06 Feb '13

06 Feb '13

I installed the version of windows for monetdb http://www.monetdb.org/Downloads. I installed a database column, but when I went over 30 GB of stokage given. the query response time has become very slow. Please, I have two questions: - Are this version is limited or no storage ? - I have noticed that the name of the database is by default "demo". Can i create another one with this version with 1 TB?

4 3

Create stored procedure to add a list of ID's to a table
by Richard Siebeling 05 Feb '13

05 Feb '13

Hello, is it possible to create a stored procedure in MonetDB that will have one parameter containing a string with several thousands of comma-separated ID's and insert these comma-separated ID's as separate rows into a table? I am receiving thousands of ID's from a separate webservice and want to join these ID's to a table in monetDB, aggregate the results and return them back to the client for further processing in the GUI. I tried inserting the ID's into a temporary table using insert into instructions from PHP, but that didn't gave me the performance I wanted (about 6 seconds for 10.000 ID's). So the next approach would be to create a stored procedure receiving all the ID's in one parameter, splitting them and inserting them one-by-one into the temporary table... Or is there a better approach for this problem? thanks in advance, Richard

2 1

Memory allocations in MonetDB
by Hassan, Ahmad 04 Feb '13

04 Feb '13

Hi Team, I am running monetdb with default settings. After profiling the application instructions, I have noticed that all the memory allocation are currently being performed by the following functions: GDKmalloc, GDKmallocmax, GDKzalloc, HEAPalloc So mmap and malloc are being used with in all GDK* and HEAPalloc. I have three questions please: 1) Which memory allocations of monetdb are performed through mmap and which memory allocation are performed through malloc. I know this is very open ended question but little bit info about this would be helpful. Are the BAT structures allocated through mmap or malloc? 2) One thing I noticed that, when the DB is running and even if I am not running any queries, still the DB is constantly issuing load/store instructions to Main memory. I assume that some thread in monetdb is doing bookkeeping or persistence related stuff. Can you please point out which part of the code is responsible for this? I want to exclude that part of the code from profiling 3) When we start monetdb with multiple threads, whether multiple threads use the same common virtual address memory region to allocate memory? Or each thread has their own distinct memory region? Thanks. Kind Regards, Ahmad

2 1

Dynamic where clause in a stored procedure
by Vassilis Papakonstantinou 31 Jan '13

31 Jan '13

Hi all, Is it possible in monetdb to use a dynamic where clause in a selection query inside a stored procedure? More specifically, i want to pass the clause as parameter. Thank you, Vassilis

3 2

[help] I have a simple question about monetdb query plan.
by 이수호 31 Jan '13

31 Jan '13

p{margin:0;padding:0;} Hello, I'm a student of Korea Advanced Institute of Science and Technology in Korea. I want to compare late materialization with join and invisible join on monetdb. But when i typed SQL query, i couldn't choose which query plan to use. There was no choice to me. I think query optimizer choose some join method. Can I choose join method? or control query plan? I installed Monetdb on my desk-top and followed tutorials. Everything is done. There is no error. In this situation, i tried to control query plan. So I read reference on official homepage. But I couldn't find the information that how to control query plan. Can I control query plan? If I can, I really really want to know how do that. Where can i find some example? Please give me your favor.

2 1

caught SIGTERM, starting shutdown sequence
by Tapomay Dey 29 Jan '13

29 Jan '13

MSG merovingian[32005]: caught SIGTERM, starting shutdown sequence 2013-01-29 11:40:53 MSG control[32005]: control channel closed 2013-01-29 11:41:06 MSG merovingian[32005]: caught SIGTERM, starting shutdown sequence 2013-01-29 11:41:40 MSG merovingian[32005]: caught SIGTERM, starting shutdown sequence 2013-01-29 11:41:51 MSG merovingian[32005]: caught SIGTERM, starting shutdown sequence Every time I do a monetdbd stop {path_to_farm} I keep getting this in merovingian logs. But the monetdbd process never ends. This issue was not there on the same installation of monetdb initially. This problem started occurring annoyingly frequently after I renamed my last farm and created a fresh one.(with monetdbd not running) What leads me to stop the farm is that I am unable to connect to the db using mclient(There's another app accessing the DB using JDBC-c3p0). There's is no complaint in the merovingian log. The mclient command just hangs. I get back access once I force kill the monetdbd using kill -9. But it gets back into this state after some time. Running on Feb13 branch some older revision. Ubuntu 12.10 with lots of resources. Is this a known issue. Is there a workaround/config or something.

1 3

best way to get autogenerated primary key
by Mate Birkas 29 Jan '13

29 Jan '13

Hi, this is my first post to this list, so forgive me if this is not the right place to post this. I tried to do my research (google, monetdb.org, python api help) but didn't find an answer. How can I get the auto-generated primary key (integer) after executing an insert. I want to use it in a second insert to another table that references the first. (in PostgreSQL I used the RETURNING clause for this) thanks in advance Mate

3 5

MonetDB crash / termination issue.
by Brian Hood 29 Jan '13

29 Jan '13

Hi All, I'm not quite sure if i'm doing something wrong here but what is see if i performing a 2 table join on a primary key field is tablea and tableb work fine yet tablec which has less data crashes the daemon. SELECT a.ip_dst as ip_dst, b.tcp_dport as tcp_dport, count(*) as num from "myschema".tablea a LEFT JOIN "myschema".tableb b ON (a.guid = b.guid) WHERE b.tcp_dport IS NOT NULL GROUP BY a.ip_dst, b.tcp_dport ORDER BY num DESC LIMIT 20; This one works fine. However when i perform the join from tablea to tablec I get the following. sql>SELECT a.ip_dst as ip_dst, b.udp_dport as udp_dport, count(*) as num from "myschema".tablea a LEFT JOIN "myschema".tablec b ON (a.guid = b.guid) WHERE b.udp_dport IS NOT NULL GROUP BY a.ip_dst, b.udp_dport ORDER BY num DESC LIMIT 20 more>; Connection terminated I've added alot more detail to the pastebin link. I had the same issue with 11.13.5 after upgrading i have run into the same thing also. http://pastebin.com/m4XseSnH If you want to schema information to reproduce please just email me off list. Regards, Brian Hood

3 6

Strange concurrency issue
by Percy Wegmann 28 Jan '13

28 Jan '13

Dear MonetDB Team, We're starting to exercise our MonetDB installation more heavily now and I've run into a strange issue where I get an occasional "COMMIT: failed" message. It seems that the conditions required to produce this are: - Concurrently inserting into two different tables - At the same time, issuing a SELECT COUNT(*) against either of those tables I've logged a bug with more detailed information, including a Java test case: http://bugs.monetdb.org/show_bug.cgi?id=3210 I'm only just starting my investigation, but I suspect that COUNT(*) may be the culprit, since other selects like SELECT * don't seem to have this problem. Does this ring any bells? Are there any special optimizations for COUNT(*) that bypass the underlying BAT and/or have side effects on some shared table somewhere that could be causing a concurrency violation? Thanks, -- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 11

Bit functions (XOR and inverse)
by Eduardo Oliveira 28 Jan '13

28 Jan '13

Hi, I'm running MonetDB v11.13.9 (Oct2012-SP3), i have some doubts about bit functions, i was trying the bit functions looking at MySQL docs in MonetDB: http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html And when i used ^, the pow was calculated instead of XOR, the ~ don't work as expected too. I google and I found this http://permalink.gmane.org/gmane.comp.db.monetdb.scm/28212 This patch will be applied on MonetDb, any date of release of MonetDb with ^ already as XOR? Right now what operator should i used to make a xor? I tested xor XOR and MonetDb didn't recognize. If i want to insert in binary form how i do? b'binary' works? Thanks in advance, -- *Eduardo Oliveira * *IT* ***Email:* eduardo.oliveira(a)adclick.pt *Web: *www.adclickint.com **<http://www.adclickint.com/> * * <http://www.adclickint.com/> <http://www.adclickint.com/>

3 6

Maximum number of redirects reached, aborting connection attempt. Sorry.
by Brandon Jackson 28 Jan '13

28 Jan '13

I am using a release version of MonetDB 11.13.9 "OCT2012-SP3" on Fedora 18, running as an unprivileged user with no firewall. The server quit allowing connections via JDBC and mclient this morning. mclient hangs indefinitely after asking for a password run from the same machine and account that started mserver5 via monetdbd. "monetdb status", reports a healthy database. JDBC connections initiated using monetdb-jdbc-2.8.jar through Pentaho Data Integration. "Maximum number of redirects reached, aborting connection attempt. Sorry." is the error message. Shutting down MonetDB seemed to work. It also seems to start up okay, but still does not allow any connections. I am happy to install MonetDB with debug symbols and provide some output from gdb if that would be helpful. Please let me know a few things I can try that may be helpful for filing a bug report and getting us back online. Thanks for your help, Brandon The stack trace that came back to java was this: Error connecting to database [monet_testing] : org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database Error connecting to database: (using class nl.cwi.monetdb.jdbc.MonetDriver) Maximum number of redirects reached, aborting connection attempt. Sorry. org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database Error connecting to database: (using class nl.cwi.monetdb.jdbc.MonetDriver) Maximum number of redirects reached, aborting connection attempt. Sorry. at org.pentaho.di.core.database.Database.normalConnect(Database.java:366) at org.pentaho.di.core.database.Database.connect(Database.java:315) at org.pentaho.di.core.database.Database.connect(Database.java:277) at org.pentaho.di.core.database.Database.connect(Database.java:267) at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:86) at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2464) at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:533) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:139) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:123) at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:26) at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:119) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.eclipse.jface.window.Window.runEventLoop(Window.java:820) at org.eclipse.jface.window.Window.open(Window.java:796) at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:378) at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:304) at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:115) at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:62) at org.pentaho.di.ui.trans.steps.monetdbbulkloader.MonetDBBulkLoaderDialog$20.widgetSelected(MonetDBBulkLoaderDialog.java:1350) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.pentaho.di.ui.trans.steps.monetdbbulkloader.MonetDBBulkLoaderDialog.open(MonetDBBulkLoaderDialog.java:905) at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:136) at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:7835) at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:2749) at org.pentaho.di.ui.spoon.trans.TransGraph.mouseDoubleClick(TransGraph.java:705) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1221) at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7044) at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:8304) at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:580) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.pentaho.commons.launcher.Launcher.main(Launcher.java:134) Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Error connecting to database: (using class nl.cwi.monetdb.jdbc.MonetDriver) Maximum number of redirects reached, aborting connection attempt. Sorry. at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:502) at org.pentaho.di.core.database.Database.normalConnect(Database.java:350) ... 51 more Caused by: java.sql.SQLException: Maximum number of redirects reached, aborting connection attempt. Sorry. at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:254) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:171) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:185) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:482) ... 52 more Caused by: nl.cwi.monetdb.mcl.MCLException: Maximum number of redirects reached, aborting connection attempt. Sorry. at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:255) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:395) at nl.cwi.monetdb.mcl.net.MapiSocket.connect(MapiSocket.java:248) at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:232) ... 56 more Hostname : 172.27.0.209 Port : 50000 Database name : testing

3 2

How to handle too much data ?
by mail_server＠lavabit.com 28 Jan '13

28 Jan '13

Hi, i am working on monetdb so as it can be used for handling huge streaming data, in the most efficient way. i am having a requirement of sustained inserts of rate around 50k-100k inserts per sec(maybe more).i have tested up the results on big enough data (upto 100Gb) and it's the fastest one, to be frank.Most data is 8 byte longs,1 column is time in secs and 1 column is string based.FYI,Data writer in single threaded. However,the issue is too much data and it will be going on for months( maybe years) and all queries have a range part.Most queries are on current data, while some will be for ages old data.Horizontal partitioning is the way to go as it seems. so what i thought was for efficiency, dump the arrays as per time in secs and then when a query comes, somehow form a new BAT (maybe using FUSE, since copying is unusable), join the data in temporary way and use it and discard.Few secs delay is okay even for the range query of 1 sec. as i have been digging around, http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad it's possible for full size columns. but since this is going to be a copy,it's inefficient. is there a way to get it done ( already exists) ?? any pointers might help such as BAT Partition Manager and so on. if NOT, i may try to get down and get it done but some pointers might be useful from the experts. Thanks for help. mike

1 0

Unsigned integers
by Eduardo Oliveira 23 Jan '13

23 Jan '13

Hello, I would like to know if MonetDb supports unsigned integer, i couldn't find anything on documentation. I tried to created a table with unsigned int and i get an error. Thank you, -- *Eduardo Oliveira * *IT* ***Email:* eduardo.oliveira(a)adclick.pt *Web: *www.adclickint.com **<http://www.adclickint.com/> * * <http://www.adclickint.com/> <http://www.adclickint.com/>

2 1

Announcement: New Oct2012-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 22 Jan '13

22 Jan '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2012-SP3 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 2012-SP3 bugfix release Bug Fixes * 2291: small doubles end up as NULL after arithmetic * 2579: SQL: incorrect cast from double to int * 2659: tuples INTERSECT vs count (... INTERSECT ...) differs * 2805: row_number doesn't work in complex query * 2886: warning: Catalog parameter '' ignored * 2977: timestamp minus date results in program error * 2978: now() + 1 results in illegal argument * 2999: Up-cast from SQL drops order information? * 3048: algebra.join undefined when using multiple arithmetic operations in SQL where-clause * 3124: insert into table with two foreign keys crashes mserver5 * 3136: Memleak (in querycache?) while querying * 3143: Type resolution error in SQL procedural code * 3163: mserver5 crashes with large number of concurrent clients * 3168: crash when insert varchar/char/clob with default NULL * 3169: php driver uses call-time pass-by-reference * 3173: SQL queries with limit and offset clauses are unable to use bind parameters for either * 3176: Same query and different results * 3181: Join query returns zero results when there is an index on the tables * 3185: inconsistency between documentation and implementation of command line options * 3186: case evaluted to early * 3188: Storage of client history * 3189: Overflow in calculation * 3190: When subtracting two columns of type "timestamp", no results are returned * 3191: rel_optimizer.c:5596: _rel_optimizer: Assertion `0' failed. * 3192: "No such column with index" error if CASE in SELECT clause * 3199: Inconspicuous sequence of prepare, execute, commit and close produces error * 3205: Inet datatype handling inconsistent between int, cast( as inet) and string * 3208: Unable to execute prepared statements with a large number of arguments * 3209: exp_bin: Assertion `0' failed * 3212: Query cache does not work properly

1 0

SQL function as non-privileged user
by Tapomay Dey 21 Jan '13

21 Jan '13

I have created a separate schema and user as follows: echo "CREATE USER \"tapomay\" WITH PASSWORD '~!@#$' NAME 'Ext User' SCHEMA \"sys\";" | mclient -d my_db echo "CREATE SCHEMA \"test_schema\" AUTHORIZATION \"tapomay\";" | mclient -d my_db echo "ALTER USER \"tapomay\" SET SCHEMA \"test_schema\";" | mclient -d my_db When logged in as "tapomay", I am able to execute curdate() but can't execute date_to_str(). I can run date_to_str() when logged in as the super user monetdb. There could be many other such utility functions. How do I enable all of them for the new user "tapomay". Regards, Tapomay.

2 1

BAT Data structures of a Schema
by Hassan, Ahmad 21 Jan '13

21 Jan '13

Hi Martin, I have the following list of tables in database schema: sql>\d TABLE sys.customer TABLE sys.lineitem TABLE sys.nation TABLE sys.orders TABLE sys.part TABLE sys.partsupp TABLE sys.region TABLE sys.supplier Please can you point me to the C code file(s) that hold the BAT objects for each column of the above tables please? I want to debug and analyse specifically that part of the code region that declares, initialises and maintains the list of BAT structures for the tables. The following query is providing the information about the tables and I am assuming that this query also access this information from all the individual BAT structures of a database schema. select * from storage() where "table" = 'lineitem'; Secondly, Is it possible to list of get the list of all the BAT structures (Column BATS and Delta BATs) in the running database please? Thanks. Kind Regards, Ahmad

4 6

Inconsistency in data selection. count*
by Tapomay Dey 21 Jan '13

21 Jan '13

select count(*) from table; Results: 168466 rows select count(*) from table where column=0; Results: 168466 rows select count(*) from table where column<>0; Results: 168466 rows When I do a "select column from table where client_id=0 limit 5;" I see correct non-zero values in the result although the selection condition is that the column value must be 0. I put the data on another instance/machine - I don't see this issue. Plz find a screenshot@ https://picasaweb.google.com/115470548136322675830/January212013?authuser=0… Another case: sql>select count(*) from table2 where column2=0; | 391326 | sql>select count(*) from table2 where column2<>0; | 141108 | sql>select count(*) from table2; | 399326 | sql>select column2 from table2 where column2=0 limit 5; +-----------+ | column2 | +===========+ | 53 | | 53 | | 53 | | 53 | | 53 | +-----------+ 5 tuples (156.631ms) Regards, Tapomay

1 0

Resource limits in MonetDB
by Charles Milutinovic 21 Jan '13

21 Jan '13

I am experimenting with using MonetDB for a project where the most natural implementation would involve ~100K tables (with the huge majority being near empty, a few incredibly massive) with approx. 10 columns each. MonetDB choked on this, with the following error: >!FATAL: BBPextend: trying to extend BAT pool beyond the limit (16384000) I am assuming that I hit this limit because of the extreme amount of tables. I am wondering if there are any other hard limits to be aware of in MonetDB that you could hit before hitting system limits.

3 2

FW: Monetdb on Solaris/Sparc: high system time problem
by Javier Picorel 21 Jan '13

21 Jan '13

Dear Martin, Thanks for your prompt reply. We have verified that the order in which the queries are executed doesn't affect the behavior of the query. The same problem arises at the same queries in any arbitrary order, which implies that it's related to the query itself. In addition, the queries' execution time and utilization are pretty deterministic. We have also verified that there is plenty of memory available and there are no other processes competing for resources (i.e., disk bandwidth, memory). We have used dtrace to profile what functions are being executed by MonetDB while being in the two phases (normal and problematic). In the normal phase, which lasts for a few tens of seconds and the behavior is what we see on Linux, MonetDB mainly access the following functions: - BUNfastins - re_match_no_ignore - strstr When MonetDB enters into that second (problematic) phase, the User/System time is 4% to 21% for a couple of minutes w/o any disk activity or swapping, MonetDB mainly accesses the following functions: - MT_sleep_ms - memset - __pollsys - _pollsys - _save_nv_regs - pselect - select Do these function names help you to understand the problematic behavior? Thanks in advance, Javier ________________________________ From: Martin Kersten <martin(a)monetdb.org<mailto:martin@monetdb.org>> Subject: Re: Monetdb on Solaris/Sparc: high system time problem Date: January 17, 2013 6:56:00 PM GMT+01:00 To: <users-list(a)monetdb.org<mailto:users-list@monetdb.org>> Reply-To: Communication channel for MonetDB users <users-list(a)monetdb.org<mailto:users-list@monetdb.org>> Dear Javier, Thank you for your report. It is hard to assess the consequences/impact of your particular setup. Your report suggests that something else was also running on your platform during this sequence, at that particular time. It could mean the MonetDB was fighting with another process over resources, i.e. memory and disk bandwidth. It could also indicate a thrashing kernel on shared kernel resources. If the system persists during repetitive runs under controlled system settings and it is immune to query order, then we might look further into the issue. regards, Martin On 1/17/13 3:44 PM, Javier Picorel wrote: Dear all, I'm running MonetDB (Dec2011-SP2) on Sparc Solaris 10 u9. I'm testing the TPC-H queries using SF10 and SF100 as the scale factors. My machine has 4 cores and 32GB of RAM and my farm is on a disk array. Unfortunately, there are some queries (e.g., Qry 7, 8, 9) that enters into phase in which the User/System time is 4% to 21% for a couple of minutes. Interestingly, the same issue doesn't show up on a Linux machine with a similar setup and the queries' system time is pretty low (<5%). Interestingly, vmstat output doesn't show any disk activity during the phase where the process is just in system, only there are high values on "re" and "mf" fields. There is no swapping going on ("po" and "pi" values are 0 in vmstat). These are the parameters of the DB: type default database shared default yes nthreads default 4 optpipe default_pipe master default no slave default <unknown> readonly default no nclients default 64 The way that I test the query is by running just one at a time. How can I mitigate this OS time? Thanks in advance, Javier _______________________________________________ users-list mailing list users-list(a)monetdb.org<mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org<mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list

2 1

MonetDB under LLVM
by Hassan, Ahmad 18 Jan '13

18 Jan '13

Hi Team, Has anyone tried compiling/running MonetDB under LLVM/CLANG compiler framework please? Normally the use case is: 1) Produce set of llvm bitcode files *.bc from C code 2) Apply LLVM Pass to instrument *.bc files through llvm 'OPT' compiler tool 3) Link all *.bc files and produce executables of monetdb I am going to try monetdb under llvm. If anyone hasn't done it before then I may ask for the initial pointers to compile monedb under llvm instrumentation. Thanks. Kind Regards, Ahmad _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

5 8

Email archives for the forums
by Tapomay Dey 18 Jan '13

18 Jan '13

I don't see any forum archives since you switched from sourceforge. It's helpful to refer to other email threads. Is it there somewhere? I see the older one here: http://mail.monetdb.org/pipermail/developers-list/ and till 8th Jan '13 on sourceforge. Regards, Tapomay. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Monetdb on Solaris/Sparc: high system time problem
by Javier Picorel 17 Jan '13

17 Jan '13

Dear all, I'm running MonetDB (Dec2011-SP2) on Sparc Solaris 10 u9. I'm testing the TPC-H queries using SF10 and SF100 as the scale factors. My machine has 4 cores and 32GB of RAM and my farm is on a disk array. Unfortunately, there are some queries (e.g., Qry 7, 8, 9) that enters into phase in which the User/System time is 4% to 21% for a couple of minutes. Interestingly, the same issue doesn't show up on a Linux machine with a similar setup and the queries' system time is pretty low (<5%). Interestingly, vmstat output doesn't show any disk activity during the phase where the process is just in system, only there are high values on "re" and "mf" fields. There is no swapping going on ("po" and "pi" values are 0 in vmstat). These are the parameters of the DB: type default database shared default yes nthreads default 4 optpipe default_pipe master default no slave default <unknown> readonly default no nclients default 64 The way that I test the query is by running just one at a time. How can I mitigate this OS time? Thanks in advance, Javier _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1