- 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