June 2013 - users-list

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