- users-list - monetdb.org

error documentation ALTER USER
by Pierre-Adrien Coustillas 03 Oct '14

03 Oct '14

Hello, There is an error on this page : https://www.monetdb.org/Documentation/SQLreference/Authorisation It's not UNENCRYPYED but UNENCRYPTED sql>ALTER USER SET UNENCRYPYED PASSWORD 'azerty' USING OLD PASSWORD 'monetdb'; syntax error, unexpected IDENT, expecting PASSWORD in: "alter user set unencrypyed" sql>ALTER USER SET UNENCRYPTED PASSWORD 'azerty' USING OLD PASSWORD 'monetdb'; operation successful (19.015ms) Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

2 1

MonetDB bulk load error
by Frik Lubbe 02 Oct '14

02 Oct '14

Hi all, I'm new to MonetDB and ran into a bulk load error. I have a delimited file to be loaded. Column Delimiter : '~' Quote Char : ' " ' (double quote) Row Delimiter: '\n' My load script is this: sql>COPY 1000 RECORDS INTO voc.dim_anonymous_wallets FROM '/tmp/monetdb/201409_BA_ANON_WALLETS.SML' USING DELIMITERS '\~','\\n','\"' NULL AS ''; Error: Incomplete record at end of file. failed to import table This file loads OK in other db engines. Any ideas will help please. Regards, Frik

2 1

Bulk Load error
by Frik Lubbe 02 Oct '14

02 Oct '14

Hi all, I'm new to MonetDB and ran into a bulk load error. I have a delimited file to be loaded. Column Delimiter : '~' Quote Char : ' " ' (double quote) Row Delimiter: '\n' My load script is this: sql>COPY 1000 RECORDS INTO voc.dim_anonymous_wallets FROM '/tmp/monetdb/201409_BA_ANON_WALLETS.SML' USING DELIMITERS '\~','\\n','\"' NULL AS ''; Error: Incomplete record at end of file. failed to import table This file loads OK in other db engines. Any ideas will help please. Regards, Frik

2 1

why is my SAMPLE percentage limiter throwing an error?
by Anthony Damico 30 Sep '14

30 Sep '14

sorry if this is a silly question.. i'm trying to sample a percentage of a table. from https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Sampling it looks like these should work, but i must be making a mistake in my syntax somewhere? thanks!! create table test ( a int , b double precision ); insert into test values( 1 , 3.3 ); insert into test values( 13 , 2.3 ); insert into test values( 3 , 0.3 ); select * from test sample 0.1 ; TypeException:user.s12_1[9]:'sample.subuniform' undefined in: _18:any := sample. subuniform(_15:bat[:oid,:int], _16:dbl) program contains errors select * from test uniform sample 0.1 ; TypeException:user.s13_1[9]:'sample.subuniform' undefined in: _18:any := sample. subuniform(_15:bat[:oid,:int], _16:dbl) program contains errors # MonetDB 5 server v11.17.21 "Jan2014-SP3" # Serving database 'demo', 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-2014 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/JAQL module loaded # MonetDB/SQL module loaded >

4 10

MEDIAN ???
by Budulinku Dejmihrasku 26 Sep '14

26 Sep '14

Hi, just a quick question regarding to MEDIAN: with x as (select 1.0 as a union all select 2.0 as a) select sys.median(a) as result from x returns result=1 i would expect result=1.5 though any suggestion? thanks milan

4 7

Show create table equivalent
by Guillaume Bassot 23 Sep '14

23 Sep '14

Hello everyone, I'm looking for the mysql show create table equivalent. I need this as a query to get the result in a php script. (I already found how to do this with mclient and msqldumq) I found this query that need to be parsed to recreate the "CREATE TABLE". SELECT * FROM SYS.tables tbl INNER JOIN SYS.columns col ON (tbl.id=col.table_id) WHERE tbl.name='exemple'; But i still miss the table's constraints (primary key, unique key) Could you tell me where to find this information ? Thank you for helping. Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

4 3

table size
by Christian Braun 22 Sep '14

22 Sep '14

Hello, i want to know how much disk space each table uses. How can i find out to which table each of the files in the database directory belong? Thanks in advance, Christian

2 1

Crashing when querying on date column
by Nick Chadwick 18 Sep '14

18 Sep '14

Hi. I'm currently evaluating MonetDB for an upcoming project, and am having some problems when using date criteria in the where clause. I have a very simple table: Create table negs (id varchar(50) not null, xdate date not null, market int, product int, notional double) Id and xdate form a composite primary key, and I have indices on xdate, market and product. If I run the following query: Select * from negs where xdate = current_date the client crashes. Similarly if I run the same query over a JDBC connection from java, the connection is lost. Looking in the log file, it says that the database was killed due to a SIGSEGV. Am I doing something wrong? I am running on Ubuntu 14.04, and built MonetDB from the latest source tarball, 11.17.21. Thanks, Nick www.digiterre.com _________________ Privileged or confidential information may be contained in this message. If you are not the addressee of this message please notify the sender by return and thereafter delete the message, and you may not use, copy, disclose or rely on the information contained in it. Internet e-mail may be susceptible to data corruption, interception and unauthorised amendment for which Digiterre does not accept liability. Whilst we have taken reasonable precautions to ensure that this e-mail and any attachments have been swept for viruses, Digiterre does not accept liability for any damage sustained as a result of viruses. Statements in this message that do not relate to the business of Digiterre are neither given nor endorsed by the company or its directors.

4 12

MonetDB Optimizer
by Steffen B 15 Sep '14

15 Sep '14

Hello, I'm writing a back end for Database Supported Haskell (DSH) to target MonetDB. Can I please get some help with running my generated MAL-code though an optimizer? I do not really understand the concept behind this: https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers Do I have to generate optimizer code at the appropriate places in my code? Are the optimizer instructions woven into the MAL-Program? Isn't there a optimizer function `a la: optimize :: MalCode -> MalCode? Please be so kind and give me a little bit of futher explaination.

2 1

index on Monetdb
by Bing Zhang 11 Sep '14

11 Sep '14

Hello: I wonder whether the index will give monetdb query more performance or not. For example, like tpc-h queries, if I put index on the particular fields, then the query on over those fields will faster? Thanks & Regards.

2 2

monetdb performance Time measurement
by Bing Zhang 11 Sep '14

11 Sep '14

hi: I am trying to measure query performance of monetdb, but not sure about the time output by using option -ims includes the query results printing time, or it is just the query time. Is there some way to silence the output of queries? Thanks.

2 2

Fwd: COLD RUNS
by Prashant Patel 11 Sep '14

11 Sep '14

Dear all, as a part of my project, i have to check the performance of monetdb column-store database. I am very new to this technology, so i dont know where the monetdb caches its previous query results.. I want to know how can i take results for cold runs on monetdb ( i have tried closing monetdb client and server every time i fire a query , but doesnt seem to clear the cache and i always get hot runs result) so please can anyone help me regarding these.... suggestions are appreciated.. thanks in advance.. Prashant Rajkotiya M.tech. (ICT) DA-IICT ,Gandhinagar

5 5

MonetDB Cluster Management
by Ali Salem 05 Sep '14

05 Sep '14

Hello all, I followed the tutorial on https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/ClusterManagement but I still can't get it to work. I followed all instructions, but *monetdb discover *only returns instance(s) running on the same machine, but not others on the network. I am using three machines running debian, with monetdb installed and configured according to the link above. What am I missing? Your assistance is highly appreciated.

2 4

Messed up MonetDB installation? SQL module not initialized
by Steffen B 05 Sep '14

05 Sep '14

Hello, because of some library stuff I had to reinstall MonetDB on my Linux Mint/Ubuntu maschine. I removed all Packages that had anything to do with monetdb and did a fresh sudo apt-get install monetdb5-sql monetdb-client But now when I start mclient -l mal the SQL-module seems to be broken. I can do a sql.init(); without any errors but when I do t := sql.mvc(); I get: MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = t := sql.mvc(); ERROR = !SQLException:mvc:SQL module not initialized Please tell me which additional information you need to help me. Thank you very much Steffen

1 0

JDBC driver problem - rounding decimal numbers during binding
by Radovan Bičiště 04 Sep '14

04 Sep '14

Hello, Today I discovered problem in JDBC driver that caused a very ugly problem with rounding decimal numbers. It rounds decimal numbers during binding. I encountered the bug with the latest driver from Jan2014-SP3 distribution. I filed bug 3561 in MonetDB bug system. Regards, Radovan

1 0

Available binary functions
by Dennis Pallett 02 Sep '14

02 Sep '14

Hi all, I'm just wondering what kind of binary functions MonetDB comes with standard? I'm specifically looking for a function to convert a smallint/int/bigint into a byte array (either big or little endian), concat several byte arrays into a single byte array and encode a byte array as a hex string. Do any such functions exist or do I need to write them myself in C? Best regards, Dennis Pallett

2 2

Costly bat.new/bat.append not optimized out
by Frédéric Jolliton 28 Aug '14

28 Aug '14

Hi, Unless I'm missing something, I do not understand why this apparent pattern is not easily optimized out: X_3:bat[:oid,:lng] =<tmp_321264>[0] := bat.new(nil:oid,nil:lng); .. # X_3 is never used before this line. X_19=<tmp_331015>[23068647] := <some computation> X_20=<tmp_321264>[23068647] := bat.append(X_3=<tmp_321264>:bat[:oid,:lng][23068647], X_19=<tmp_331015>[23068647],true); # X_3 is never used after this line. An empty BAT is created for the sole purpose of appending a BAT with millions of rows, discarding the original one.. instead of just using it! This account for up to 30% of the time spent for some SQL queries! Like this one: SELECT name, avg(value) FROM some_view GROUP BY name LIMIT 10; I tried to manually remove these extra operation, and I got the exact same result, in less time. (I took the output of EXPLAIN, removed the lines in question, and added a bit of type information for the function to compile, before playing it in mclient -l mal.) Regarding the setup, for our tests, we have 3 tables (say, a, b and c) of 11M rows each, then a view declared as: CREATE VIEW some_view AS (SELECT * FROM a UNION ALL SELECT * FROM b UNION ALL SELECT * FROM c); I've tested it with v11.17.21 and v11.19.0, with the "default" optimizer. My question: is this extra operation required, or could it be optimized in MonetDB? -- Frédéric Jolliton SecurActive

2 7

ODBC driver and config on the Mac?
by Robert Kosara 19 Aug '14

19 Aug '14

Hello, I’ve been trying to connect to MonetDB via ODBC on Mac OS X, without luck. I’m seeing the ODBC drivers in /usr/local/monetdb/lib/, but I’m confused that they are .so files and not .dylib. It looks like all the others are .dylib, and as far as I know that’s required so they can be loaded dynamically. I’ve tried using the .so files in the ODBC manager, but that has only made it crash. Has anybody used MonetDB with ODBC on the Mac? Can you perhaps show me your configuration? Thanks, Robert

2 1

Table is not unique, corrupt catalog?
by Ali Salem 11 Aug '14

11 Aug '14

Hello all, Last night I imported 500M records into my fact table, it took around 19 hours, but it worked fine. I created my indexes afterwards with no issues as well. However, when I tried to access the rest of the tables in my database, I received the following error: table table_name is not unique, corrupt catalog? Any idea why this happened and how to fix it? Thank you.

1 1

MonetDB partition by day
by Ali Salem 08 Aug '14

08 Aug '14

Hello all, I know MonetDB has window functions, but can I partition a table by day? If you can direct me to a link with examples, I'd appreciate it. Thanks

1 0

MEDIAN(double) not found
by Radovan Bičiště 08 Aug '14

08 Aug '14

Hello, We use MonetDB Jan2014-SP2. When I try MEDIAN function it simply says: MEDIAN: no such operator 'median(double)' [SQL State=22000] Am I missing something? I tried to execute section of 39_analytics.sql containing the MEDIAN function. It says MEDIAN function is already there. Thank you for any tips. Radovan Test Case: I took the sql from bug 2454: select median(id) from sys.tables; result: An error occurred when executing the SQL command: select median(id) from sys.tables MEDIAN: no such operator 'median(int)' [SQL State=22000] Execution time: 0.03s 1 statement failed.

2 2

Connection Reset(mserver still alive?)
by Ali Salem 06 Aug '14

06 Aug '14

Hello, I have recently started using MonetDB for a BI project, and it's doing pretty well. However, I receive a connection reset error each time I run an ETL process (I use Pentaho PDI) to populate my dimensional schema. This error is reported by the MonetDB JDBC driver *Connection reset(mserver still alive?)* This usually happens after a random amount of time, 30 min, 45 min, 1 hour, etc. Any pointers related to this would be very helpful. Specs ------- Ubuntu 12.04 8 GB Ram 500 GB Space Pentaho PDI (community edition) Monetdb5 (installed using apt-get) Thank you.

4 6

SIGSEGV during log flush
by Richard Hughes 01 Aug '14

01 Aug '14

Hello, Can I persuade a developer to help me out with bug 3422 [https://www.monetdb.org/bugzilla/show_bug.cgi?id=3422#c4]? I'm amassing an unhealthy collection of core dumps now (about one a day), and the latest is from Jan2014SP3 which I upgraded to last night. FWIW I'm no longer sure that I am looking at the same SIGSEGV as the one that the original bug reporter was talking about - of course I can create a new Bugzilla entry if it helps. This is our staging platform so I have the ability to run all sorts of custom builds or anything else that will help debug it, I'm just not sure where to start looking; I'd really appreciate some hints. Thanks for your help, Richard.

1 0

Adding conditions slows query 1000x
by Dennis Pallett 31 Jul '14

31 Jul '14

Hi all, When I run the following query the results are computed extremely fast (within 5 ms): SELECT id_str, len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 LIMIT 100; However if I add additional conditions to the query so that it becomes the following: SELECT id_str,len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 AND coordinates_y >= 0.0 AND coordinates_y <= 61.60639636617352 LIMIT 100; The time it takes to compute the results is approximately 1000x bigger (i.e. 5 seconds). Clearly the additional conditions on the coordinates_y column is forcing MonetDB to take a different query strategy but I don't know how I can solve this. In Postgres I would make sure there is an index on the (coordinates_x, coordinates_y) column but this doesn't seem to have any effect with MonetDB. I've attached traces of both queries. There are approximately 11 million rows in the table. Can anyone tell me why there is such a huge difference in query execution time and how I can prevent it? Best regards, Dennis Pallett

5 15

Announcement: New Jan2014-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 Jul '14

31 Jul '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP3 bugfix release Build Environment * Fix configure to continue without Python if the python binary is too old. This instead of always aborting configure if python happens to be too old. Bug Fixes * 3487: dead link to "Professional services" * 3500: MonetDB driver wants an empty string for SQLTables and SQLColumns API calls, where other drivers expect NULL * 3514: mserver5 crash due (assertion failure in gdk_select.c) * 3515: mserver5 crash due (assertion failure in gdk_bat.c) * 3519: Uppercase TRUE/FALSE strings cannot be converted to boolean values

1 0

High amount of VIRT and high amount of writing
by amihay gonen 29 Jul '14

29 Jul '14

Hi all , we are trying to compare oracle vs monetdb. the query we are try to run is : select count( client_code_new ) as s from ( SELECT t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident, MAX (t.system_code) FROM transactions t left outer join ( SELECT c.linked_open_transaction_new as linked_open_transaction_new FROM calculated_taxs c GROUP BY c.linked_open_transaction_new) c_temp on t.transaction_no = c_temp.linked_open_transaction_new join clients_list cl on cl.client_code = t.client_code_new AND cl.branch = t.branch_new WHERE t.opg_cls = 'O' AND (t.close_trans_disregard_balance_new) =0 and new_system_code = 1 GROUP BY t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident) a ; tables sizes are: transactions = 103M rows calculated_taxs= 79M rows The query is taking 1min to run. we've notice that during the query run the VIRT column (from top utility) is become very high 10G , and during the we see extensive write to disk . why does monet is do so much writing ? thanks, amihay bellow is output of minimal_pipe optimizer +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================+ | function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void; | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","clients_list"); | | X_9 := sql.bind(X_5,"sys","clients_list","client_code",0); | | (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2); | | X_15 := sql.bind(X_5,"sys","clients_list","client_code",1); | | X_17 := sql.delta(X_9,X_12,r1_12,X_15); | | X_18 := algebra.leftfetchjoin(X_6,X_17); | | X_19 := batcalc.int(X_18); | | X_20:bat[:oid,:wrd] := batcalc.hash(X_19); | | X_23 := calc.int(22); | | X_25 := sql.bind(X_5,"sys","clients_list","branch",0); | | (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2); | | X_29 := sql.bind(X_5,"sys","clients_list","branch",1); | | X_30 := sql.delta(X_25,X_27,r1_27,X_29); | | X_31 := algebra.leftfetchjoin(X_6,X_30); | | X_32:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31); | | X_33:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","transactions"); | | X_38 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0); | | (X_40,r1_40) := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2); | | X_43 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1); | | X_44 := sql.delta(X_38,X_40,r1_40,X_43); | | X_45 := algebra.leftfetchjoin(X_36,X_44); | | X_46 := A1; | | X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0); | | (X_50,r1_50) := sql.bind(X_5,"sys","transactions","new_system_code",2); | | X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1); | | X_55 := sql.delta(X_47,X_50,r1_50,X_53); | | X_56 := algebra.leftfetchjoin(X_36,X_55); | | X_57 := A2; | | X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0); | | (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2); | | X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1); | | X_63 := sql.delta(X_58,X_60,r1_60,X_62); | | X_64 := algebra.leftfetchjoin(X_36,X_63); | | X_65 := A0; | | X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false); | | X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false); | | X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false); | | X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0); | | (X_74,r1_74) := sql.bind(X_5,"sys","transactions","transaction_no",2); | | X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1); | | X_78 := sql.delta(X_71,X_74,r1_74,X_77); | | X_79 := algebra.leftfetchjoin(X_36,X_78); | | X_80 := algebra.leftfetchjoin(X_70,X_79); | | X_81:bat[:oid,:oid] := sql.tid(X_5,"sys","calculated_taxs"); | | X_83 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0); | | (X_86,r1_86) := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2); | | X_89 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1); | | X_91 := sql.delta(X_83,X_86,r1_86,X_89); | | X_92 := algebra.leftfetchjoin(X_81,X_91); | | (X_93,r1_93,r2_93) := group.subgroupdone(X_92); | | X_96 := algebra.leftfetchjoin(r1_93,X_92); | | X_97 := batcalc.lng(X_96); | | (X_98,r1_98) := algebra.join(X_80,X_97); | | X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0); | | (X_102,r1_102) := sql.bind(X_5,"sys","transactions","client_code_new",2); | | X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1); | | X_106 := sql.delta(X_100,X_102,r1_102,X_104); | | X_107 := algebra.leftfetchjoin(X_36,X_106); | | X_108 := algebra.leftfetchjoin(X_70,X_107); | | X_109 := algebra.leftfetchjoin(X_98,X_108); | | X_110 := bat.append(X_33,X_109,true); | | X_112 := bat.mirror(X_80); | | X_113 := algebra.tdiff(X_112,X_98); | | X_114 := algebra.leftfetchjoin(X_113,X_108); | | X_115 := bat.append(X_110,X_114,true); | | X_116:bat[:oid,:wrd] := batcalc.hash(X_115); | | X_119 := calc.int(22); | | X_121:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_124 := sql.bind(X_5,"sys","transactions","branch_new",0); | | (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2); | | X_131 := sql.bind(X_5,"sys","transactions","branch_new",1); | | X_133 := sql.delta(X_124,X_128,r1_128,X_131); | | X_134 := algebra.leftfetchjoin(X_36,X_133); | | X_135 := algebra.leftfetchjoin(X_70,X_134); | | X_136 := algebra.leftfetchjoin(X_98,X_135); | | X_137 := bat.append(X_121,X_136,true); | | X_138 := algebra.leftfetchjoin(X_113,X_135); | | X_139 := bat.append(X_137,X_138,true); | | X_140:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_116,X_119,X_139); | | (X_141,r1_141) := algebra.join(X_32,X_140); | | X_143 := algebra.leftfetchjoin(X_141,X_19); | | X_144 := algebra.leftfetchjoin(r1_141,X_115); | | X_145:bat[:oid,:bit] := batcalc.==(X_143,X_144); | | X_147 := calc.bit(true); | | X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false); | | X_151 := algebra.leftfetchjoin(X_149,X_141); | | X_152 := algebra.leftfetchjoin(X_151,X_31); | | X_153 := algebra.leftfetchjoin(X_149,r1_141); | | X_154 := algebra.leftfetchjoin(X_153,X_139); | | X_155:bat[:oid,:bit] := batcalc.==(X_152,X_154); | | X_157 := calc.bit(true); | | X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false); | | X_159 := algebra.leftfetchjoin(X_158,X_153); | | X_160:bat[:oid,:dbl] := bat.new(nil:oid,nil:dbl); | | X_163 := sql.bind(X_5,"sys","transactions","bank",0); | | (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2); | | X_171 := sql.bind(X_5,"sys","transactions","bank",1); | | X_173 := sql.delta(X_163,X_168,r1_168,X_171); | | X_174 := algebra.leftfetchjoin(X_36,X_173); | | X_175 := algebra.leftfetchjoin(X_70,X_174); | | X_176 := algebra.leftfetchjoin(X_98,X_175); | | X_177 := bat.append(X_160,X_176,true); | | X_178 := algebra.leftfetchjoin(X_113,X_175); | | X_179 := bat.append(X_177,X_178,true); | | X_180 := algebra.leftfetchjoin(X_159,X_179); | | X_181:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_183 := sql.bind(X_5,"sys","transactions","account",0); | | (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2); | | X_187 := sql.bind(X_5,"sys","transactions","account",1); | | X_188 := sql.delta(X_183,X_185,r1_185,X_187); | | X_189 := algebra.leftfetchjoin(X_36,X_188); | | X_190 := algebra.leftfetchjoin(X_70,X_189); | | X_191 := algebra.leftfetchjoin(X_98,X_190); | | X_192 := bat.append(X_181,X_191,true); | | X_194 := algebra.leftfetchjoin(X_113,X_190); | | X_195 := bat.append(X_192,X_194,true); | | X_196 := algebra.leftfetchjoin(X_159,X_195); | | X_197:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0); | | (X_204,r1_204) := sql.bind(X_5,"sys","transactions","instrument_ident",2); | | X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1); | | X_209 := sql.delta(X_199,X_204,r1_204,X_207); | | X_210 := algebra.leftfetchjoin(X_36,X_209); | | X_211 := algebra.leftfetchjoin(X_70,X_210); | | X_212 := algebra.leftfetchjoin(X_98,X_211); | | X_213 := bat.append(X_197,X_212,true); | | X_214 := algebra.leftfetchjoin(X_113,X_211); | | X_215 := bat.append(X_213,X_214,true); | | X_216 := algebra.leftfetchjoin(X_159,X_215); | | X_217 := algebra.leftfetchjoin(X_159,X_115); | | X_218 := algebra.leftfetchjoin(X_159,X_139); | | (X_219,r1_219,r2_219) := group.subgroup(X_218); | | (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219); | | (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222); | | (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225); | | (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228); | | X_234 := algebra.leftfetchjoin(r1_231,X_217); | | X_235 := algebra.selectNotNil(X_234); | | X_236 := aggr.count(X_235); | | sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,""); | | end s4_3; | | # querylog.define("explain \nselect count( client_code_new ) as s from \n(\n select t.client_code_new, t.bank, t.branch_new, t.account, t. | : instrument_ident, max (t.system_code)\n from transactions t left outer join \n ( select c.linked_open_transaction_new as linked_open_transa : : ction_new from calculated_taxs c group by c.linked_open_transaction_new) c_temp\n on t.transaction_no = c_temp.linked_open_transaction_new \njoin cli : : ents_list cl on cl.client_code = t.client_code_new and cl.branch = t.branch_new\n where t.opg_cls = \\'O\\' and (t.close_trans_disregard_balance_new) = : : 0 and new_system_code = 1 \n group by t.client_code_new, t.bank, t.branch_new, t.account, t.instrument_ident) a : : ;","minimal_pipe") : +------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 1

monetdb is slow to backup
by Pierre-Adrien Coustillas 29 Jul '14

29 Jul '14

Hello, I'm used to save monetdb using msqldump : msqldump --database=base --table=table > backup.sql This command takes 30 minutes to backup 6 GB , but 4 minutes to restore. How can I increase backup speed ? Thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 5

GDKerror when using COPY INTO sql commands
by Mahmut Uludag 25 Jul '14

25 Jul '14

Hi, We update our MonetDB databases using COPY INTO commands, we recently have started to get errors similar to the following error. GDKerror:!ERROR: GDKload: cannot read: name=13/52/135274, ext=theap, 56932 bytes missing. !OS: No such file or directory GDKload: cannot read: name=13/56/135610, ext=theap, 906 bytes missing. !OS: No such file or directory Deleting and recreating the tables and the schema haven't helped. If we create a new table with a table name not used in the past then we are able to use COPY INTO commands to update the new table. It is also possible to update the broken tables using 'INSERT INTO' commands. We see the problem only when we use COPY INTO commands. We use MonetDB Jan2014-SP1 release. We have 3 database nodes that 2 of them doesn't show this error. Any help much appreciated. We also don't know yet why we have started getting this error, any notes on how one can start getting this error would also be helpful. Mahmut Uludag

2 3

Developing a C-based UDF
by Dennis Pallett 18 Jul '14

18 Jul '14

Hi all, I'm developing a C-based user defined function at the moment (actually porting an existing Postgres extension) and I was wondering what kinds of tools I should use for this and how I should actually do the development. At the moment I'm using a simple text editor to edit the relevant files (i.e. the c and mal files) and then completely re-compile MonetDB to test my changes. I'm not entirely happy about this workflow though, so is there an easier way? Perhaps with Visual Studio (I'm on Windows7) ? How do the core developers work on MonetDB? Also, if I run into any problems or have any questions, is this the right mailing list or should I use the developers mailing list? Best regards, Dennis

3 3

python3-monetdb cursor.scroll questions
by Chris Richards 17 Jul '14

17 Jul '14

Howdy. I've recently started using monetdb and was testing out the python3-monetdb module with the 'quick start' voc dataset. I'm not grokking the purpose of cursor.scroll() if it's working as intended. I am getting weird (to me) results, which I'm including below. Running 11.17.17-20140514 of the server, client, and python module under Ubuntu 14.04 LTS. My example starts at this point in the quick start guide (but in python): sql>SELECT type, COUNT(*) AS total I loaded this snippet into the python3 interpreter: from monetdb import sql def p(c): print('.rowcnt={}, .__offset={}, .rownumber={}'.format ( c.rowcount, c._Cursor__offset, c.rownumber)) print ('.__rows={}'.format (c._Cursor__rows)) db = sql.connect(database='voc', user='voc', password='voc') cursor = db.cursor() # Set the arraysize small so it traverses result sets, # though it doesn't matter for the problem I'm seeing. cursor.arraysize = 2 cursor.execute("SELECT type, COUNT(*) AS total FROM onboard_people GROUP BY type ORDER BY type;") Then proceeded with this interactive session. Consecutive fetchone()'s seem to work properly; it traverses to the second result set. Where I get confused is when I .scroll(): >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=0 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('craftsmen', 2349) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=1 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('impotenten', 938) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=2 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('passengers', 2813) >>> p(cursor) .rowcnt=6, .__offset=2, .rownumber=3 .__rows=[('passengers', 2813), ('seafarers', 4468)] # Here's where I scroll back to the beginning. The # offset changes, but the rownumber doesn't. >>> cursor.scroll (0, mode='absolute') >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=3 .__rows=[('craftsmen', 2349), ('impotenten', 938), ('passengers', 2813), ('seafarers', 4468), ('soldiers', 4177)] # My expectation was that this would be ('craftsmen', 2349), # but it continues as if I didn't scroll. >>> cursor.fetchone() ('seafarers', 4468) # I try to back to the last item with no luck, it instead # marches forward to the next item as if I didn't scroll. >>> cursor.scroll (-1, mode='relative') >>> p(cursor) .rowcnt=6, .__offset=3, .rownumber=4 .__rows=[('seafarers', 4468), ('soldiers', 4177), ('total', 2467)] >>> cursor.fetchone() ('soldiers', 4177) Am I using the .scroll() wrong? Is this a bug? Thanks, Chris

1 0

Large query taking a long time
by Dennis Pallett 16 Jul '14

16 Jul '14

Hi all, I'm executing a very large query with MonetDB and it takes very long before the result is calculated (e.g. several minutes) whereas PostgreSQL does it in less than 100 ms. The query itself is a very simple select + sum query but the WHERE condition has more than a thousand equality tests on the primary key (ckey) of the table. It basically looks like this: SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey=16947611436 OR ckey=16947611437 OR ckey=16947611438 OR ckey=16947611439 OR ckey=16947611440 OR ckey=16947617908 OR ckey=16947617909 OR ckey=16947617910 OR ckey=16947617911 OR ckey=16947617912 [.. several hundreds of lines removed ..] OR ckey=289411309830 OR ckey=289411309831 OR ckey=289411309832 OR ckey=289411309833 OR ckey=289411309834 OR ckey=289411309835 OR ckey=289411309836 OR ckey=289411309837; Does anyone know why it's taking so long with MonetDB and if there is a way to significantly improve performance? Best regards, Dennis

4 7

TMP space deallocation
by Radovan Bičiště 08 Jul '14

08 Jul '14

Hi, My database started consuming disk space. Here is how: 1. run procedure. 2. procedure fails with server crash due to insufficient disk space. It eats up all the space and crash. 3. restart the database but the disk space remain allocated. All changes by the procedure are rolled back. There are inserts but not that many to eat up 10 GB of space. When I check sys.storage, it does not show any large table. My guess is that it allocates temporary space and it does not deallocate it after database start. It deallocates a bit but not everything. Is there any way how to shrink it manually? It already ate around 40 GB space. The whole database is around 130 GB so the quick dump and restore is not possible. Thank you for any tips, Radovan Biciste

1 0

MonetDB and cpu resources management
by Dimitris Karampinas 03 Jul '14

03 Jul '14

Hi, I'm trying to set the maximum number of cores using gdk_nr_threads parameter but it doesn't seem to work for me. I've tried "variants" of this command: monetdbd set gdk_nr_threads=4 start /my/dbfarm but it seems like gdk_nr_threads is not a supported property. Additionally to this, I would like to ask how does MonetDB handle concurrent user sessions and the available cpu resources ? The manual says there is support for "inter- and intra- parallelism". Can someone roughly explain me what to expect or give me a pointer to the code that is responsible for resource management ? Cheers! Dimitris

6 12

Index usage
by Danila B. Savchuk 27 Jun '14

27 Jun '14

I started using Monetdb two weeks ago. Since this is not RDBMS , what is the general strategy of using and building indexes on tables ?

2 1

Problem at start dbfarm
by Dimitris Karampinas 18 Jun '14

18 Jun '14

Hi, I've built from source MonetDB and I'm following the steps at the tutorial: https://www.monetdb.org/Documentation/UserGuide/Tutorial I've successfully created the dbfarm but I can't proceed beyond the second step. Monetdb returns after ~30 seconds with the following message: $ dbms/bin/monetdbd start data/ another monetdbd is already running I 'm sure no other monetdb instance is running in the system. For the record, get all looks as follows: $ dbms/bin/monetdbd get all data/ property value hostname sgs-r815-02 dbfarm data status a monetdbd is serving this dbfarm, but a pidfile was not found/is corrupt mserver unknown (failed to connect to monetdbd) logfile data/merovingian.log pidfile data/merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 I had no problems with the whole process when I installed the system on my desktop a few days ago. Any hints ? Dimitris

4 6

Unresponsive mserver
by Pete Hollobon 18 Jun '14

18 Jun '14

Hi, We have recently seen MonetDB become unresponsive several times. Upon restarting, after mserver has been sent the TERM signal, merovingian.log contains lots of "unknown state: 4" entries, repeated many times without linefeeds. We're running Jan2014-SP2 (official binaries) on Ubuntu server 14.04. We had the same database running on Jan2014-SP1 on Ubuntu 12.10 for several months without this issue. I have submitted as a bug ( https://www.monetdb.org/bugzilla/show_bug.cgi?id=3499) Thanks, Pete.

1 0

odbc driver compliance: NULL strings or "" in SQLTables and SQLColumns API calls
by László Sándor 18 Jun '14

18 Jun '14

Stata fixed how they will talk to the MonetDB driver in the future, but they say the MonetDB driver is less standard, so perhaps we should be aware of this: We were passing a NULL string to the ODBC > api call SQLTables and SQLColumns where the MonetDB driver wants an empty > string > (""). Some other drivers need a NULL string passed in to work (Oracle > being > one) so I believe this is a driver bug, but I have patched the problem in > Stata. > I have added code in -odbc- to check for the MonetDB driver and pass an > empty > string in that case.

2 1

How do I monitor or speed up insert from aggregation query?
by Joe Wass 17 Jun '14

17 Jun '14

I'm running the following query over a database of 1,000,000,000 items. insert into date_aggregate (count, a, date) select count(date) as count, a, date, from input group by date, a order by date; I expect it to take a certain length of time, but I have no way of knowing how long. The system queue table has a 'null' estimate. All I know from that table is that the query is running. Is there a way to get any more information about its progress? I would be happy to just wait, but I don't know if I should be waiting for hours or days or weeks. The monetdbd process is only using 14% CPU (on a 6 core machine) and 14% RAM (the machine has 7GB and lots of SSD swap). My machine is basically idling when I want it to run as fast as possible. Running Jan2014-SP2, 64-bit on Ubuntu 14.04. Any hints about measuring progress and improving resource utilisation? Joe

2 1

MonetDB just reads and reads when restoring a dump
by Bryan Senseman 16 Jun '14

16 Jun '14

All, I know I have asked this before, but I am still plagued by this error. I am restoring a 50GB database from Linux to Windows, the dump file created via mclient is ~53GB. I am reading that file from an SSD and loading the database onto another SSD. This has been running for 2 DAYS! Using almost all available memory 16GB. Using Windows Resoure Monitor, the mserver5.exe process is just reading tail files for hours. The pattern is that the disk usage will grow by some amount say 5gb (I’m assuming the size of the table actually being loaded) and then it will spend 6-12 hours reading what it just wrote. Why? Can this be eliminated? I removed all indexes on the fact tables so it isn’t indexing (although that runs almost instantly anyway). Only about 10% cpu utilization. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 1

"LIMIT + ORDER BY" TypeException
by George Ramonov 15 Jun '14

15 Jun '14

Hi, I am still learning about monetdb internals, so I hope someone can enlighten me here. I was playing around with monetdb, and run into the following problem: Suppose we have a table CREATE TABLE test.simple_table("id" INTEGER); INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5); Let's say I want to run a simple query: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC; +----+ | v | +==+ | 5 | +----+ Runs fine. However, if add a *LIMIT* clause: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC LIMIT 5; I get the following TypeException:user.s37_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid) program contains errors Now things let's add a primitive *HAVING* clause SELECT COUNT(*) AS val FROM test.simple_table HAVING 1 > 0 ORDER BY val DESC LIMIT 5; +----+ | v | +==+ | 5 | +----+ And things are magically fine again. Can someone explain what's going on? Thanks, George Ramonov UC Berkeley B.S. Electrical Engineering & Computer Science '13 <http://www.linkedin.com/in/georgeramonov>

2 2

odbc driver 11.17.17 (64-bit) memory allocation error on Windows Server 2012
by László Sándor 10 Jun '14

10 Jun '14

Upon some further reflection, the Stata error message still suggest the error happened within MonetDB's ODBC driver. Anybody else using MonetDB with ODBC on Windows? What shall I check? Thanks! Laszlo

1 2

Re: Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

Update to my previous message. Looks like something went wrong with an mmap. 2014-06-09 17:10:58 ERR my_database[32949]: = gdk_posix.c:560: MT_mremap(bat/11/1150.theap,c0000000,538312704,1076625408): p == MAP_FAILED Looks like the server ran out of memory but just kept going with a corrupted table? Should this have been allowed to happen or should the process have crashed? It looks like this bug is related, although it has a different cause: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3450 Joe

1 0

Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

My schema is: create table my_table (col_a date, col_b varchar(1024), col_c varchar(128), col_d varchar(128), col_e varchar(128)); I have approx 5,000,000 entries. The following queries not work: select count(col_b) from my_table; select distinct(col_b) from my_table; select 1 from my_table group by col_b; Giving the error: GDK reported error. BATproject: does not match always They work fine on all other fields. I don't know precisely what triggers this. I've tried to create some small sample values but I can only reproduce this on my large data set. I'm actually trying to do more interesting aggregation, but this is a minimal way to trigger the error. I'm using Jan2014-SP2 on Mac OS X 10.9.2 . Thanks Joe

1 0

Re: MonetDB problem with mserver.exe
by Hannes Mühleisen 10 Jun '14

10 Jun '14

On 06/10/2014 09:04 AM, Kaspar Burger wrote: > You are right, Anthony, when I run the file independent of R, > mserver.exe shuts down immediately. Could you please share your test.bat file? > > *De :*users-list > [mailto:users-list-bounces+kaspar.burger=iukb.ch@monetdb.org] *De la > part de* Anthony Damico > *Envoyé :* mardi 10 juin 2014 08:51 > *À :* Communication channel for MonetDB users > *Objet :* Re: MonetDB problem with mserver.exe > > kaspar has something changed? when you and i screenshared to try to > resolve this issue, mserver.exe was shutting down immediately before R > ever came into the picture? > > what happens when you run this file independent of R? > > > C:\Users\Kaspar Burger\MonetDB\test.bat > > > > On Tue, Jun 10, 2014 at 2:42 AM, Hannes Mühleisen > <Hannes.Muehleisen(a)cwi.nl <mailto:Hannes.Muehleisen@cwi.nl>> wrote: > > This seems to fall into my backyard... > > > On 10.06.2014, at 08:32, Kaspar Burger <kaspar.burger(a)iukb.ch > <mailto:kaspar.burger@iukb.ch>> wrote: > > I am working in R, configuring a MonetDB test database and trying to > > access MonetDB with the following code: > > > > batfile <- "C:/Users/Kaspar Burger/MonetDB/test.bat" > > pid <- monetdb.server.start( batfile ) > > dbname <- "test" > > dbport <- 50000 > > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) > > > > When I run the “pid <- monetdb.server.start( batfile )” line, the > > mserver5.exe opens briefly but shuts down immediately afterwards (for a > > more extensive description of the R code, see: > > > https://raw.githubusercontent.com/ajdamico/usgsd/master/MonetDB/monetdb%20d…) > > By contrast, running the batch file from C:\Program > > Files\MonetDB\MonetDB5\M5server.bat or from the Start menu works fine. I > > tried when both drivers were installed as well as when only one > driver was > > installed. Could that problem be related to R or to my code? > > Would not be surprised if the space in the batfile path causes the > problem. Try setting up the DB into another directory without a space in > it and try again. > > Best, > > Hannes > > > > > > > > >> > >> > >> On 2014-06-10 00:06, Stefan Manegold wrote: > >>>> On 09/06/14 16:28, Kaspar Burger wrote: > >>>>> > >>>>> Hello, > >>>>> > >>>>> I seem to have difficulties with mserver.exe. I would like to use > >>>>> MonetDB > >>>>> on a Windows 64 bit computer, so... > >>>>> > >>>>> 1) ...I went to > >>>>> https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ > >>>>> > >>>> Most likely, you need only 1 ODBC driver. > >>>> Otherwise causing a clash. > >>> > >>> For the records, as by our own documentation, on a 64-bit Windows > >>> system, > >>> both the 32-bit and the 64-bit ODBC driver *must* be installed. > >>> (As far as I understand, this is required to support both 32-bit and > >>> 64-bit > >>> applications, or simply because Windows requires it.) > >> > >> You need both in order to support both 32 and 64 bit applications. > >> That's the only reason to install both. > >> > >>> Stefan > >>> > >>>>> 2) ...and first installed the MonetDB ODBC driver, 32 bit (to > >>>>> C:\Program > >>>>> Files (x86)\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program > >>>>> Files\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 4) ...and finally I installed the MonetDB SQL server and client, 64 > >>>>> bit > >>>>> (to C:\Program Files\MonetDB\MonetDB5\). > >>>>> > >>>>> When I open up mserver.exe, it shuts down immediately. Does anyone > >>>>> know > >>>>> what could have gone wrong? > >> > >> Did you start the server using the start menu? That actually runs a cmd > >> script to set a bunch of environment variables before starting the > >> actual server mserver5.exe. > >> > >>>>> I would appreciate any hint. Thanks in advance. > >>>>> > >>>>> Kaspar Burger > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> users-list mailing list > >>>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>>> > >>>> > >>>> _______________________________________________ > >>>> users-list mailing list > >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>> > >>> > >> > >> -- > >> Sjoerd Mullender > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >> https://www.monetdb.org/mailman/listinfo/users-list > >> > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > https://www.monetdb.org/mailman/listinfo/users-list > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list >

3 5

MonetDB problem with mserver.exe
by Kaspar Burger 10 Jun '14

10 Jun '14

Hello, I seem to have difficulties with mserver.exe. I would like to use MonetDB on a Windows 64 bit computer, so... 1) ...I went to https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ 2) ...and first installed the MonetDB ODBC driver, 32 bit (to C:\Program Files (x86)\MonetDB\MonetDB ODBC Driver\) 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program Files\MonetDB\MonetDB ODBC Driver\) 4) ...and finally I installed the MonetDB SQL server and client, 64 bit (to C:\Program Files\MonetDB\MonetDB5\). When I open up mserver.exe, it shuts down immediately. Does anyone know what could have gone wrong? I would appreciate any hint. Thanks in advance. Kaspar Burger

7 8

Re: Could monetdb have a capacity to surpport our case
by Hannes Mühleisen 09 Jun '14

09 Jun '14

Hello, On 06.06.2014, at 12:06, liuguodong <liuguodong(a)jd.com> wrote: > Our case as following: > > 1. There are several big tables(more than billion rows per table, more than 1 TB) ; > 2. Many dimentions in every table( more than 150 dimentions per table), they random do join operation; > 3. Low latency reponse, it is expected to reponse time being less than 1 second. > > Could monetdb have a capacity to surpport our case or any suggestion? This depends first on your queries and second on your hardware. In general, the columnar architecture of MonetDB would be a good match for what you describe (lots of dimensions, random join combinations). I would suggest to simply try your queries with a subset of data on a scaled-down machine (e.g. 100 GB of your data on a 16GB RAM box) and see how it goes. Best, Hannes

1 0

Re: Re: Re: tomograph does not work
by Cheng Xuntao 07 Jun '14

07 Jun '14

Thanks Martin. Xuntao

1 0

MAL scheduling and stethoscope metrics
by Cheng Xuntao 07 Jun '14

07 Jun '14

Hi all, I am new to monetdb. I want to know how Monetdb schedule its MAL statements to threads, or to which CPU socket in a multi-cpu environment. I didn't find any document talking bout this directly. Shall I start from reading the source code? Which part of the source code shall I start with? Any suggestion will do me a great favour! I also have some confusions about the metrics of stethoscope. So I asked these 2 question in stackoverflow. http://stackoverflow.com/questions/24077439/what-does-the-metrics-c-m-r-w-a… http://stackoverflow.com/questions/24076688/how-does-monetdb-schedule-its-m… Any suggestion? Thanks very much! Xuntao

1 0

odbc (64-bit) problems in Windows?
by László Sándor 06 Jun '14

06 Jun '14

Hi, I reinstalled MonetDB as well as the ODBC driver after last week's update to SP2. How could I troubleshoot my system if I get a Memory allocation error SQLSTATE=HY001 if Stata would query the database? Otherwise I do see the data source listed, both in Stata (with -odbc query-) and Microsoft ODBC Data Source Administrator. (The latter lists the database under System DSN with the right name, platform and driver.) Thanks, Laszlo

1 1

Text type vs JDBC driver
by Joe Wass 06 Jun '14

06 Jun '14

I just started looking at MonetDB today, so please excuse me if I've missed something. I'm using the JDBC driver in Clojure. I have a table which has one column of type TEXT. The docs mention TEXT [0] and I have no problem with it from the database CLI. When I'm inserting with JDBC, I get an error message : "Conversion not allowed", pointing to MonetPreparedStatement.java line 1775. Looking at the switch statement it doesn't look like the driver has a mapping of String to TEXT (and possibly others). I'm aware that using Clojure might introduce issues, but I've checked and the type of objects I'm passing in are definitely java.lang.Strings. I don't want to raise a ticket because I'm not confident that is is a bug. But it does look like a mismatch between the MonetDB types and the JDBC driver. A quick look at the bug tracker doesn't mention anything about 'text'. (FWIW I've got my code working by using VARCHAR.) Joe [0] https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes [1] http://dev.monetdb.org/hg/MonetDB/file/tip/java/src/nl/cwi/monetdb/jdbc/Mon…

1 0

Re: Re: tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi Martin, My Monetdb version is MonetDB Database Server v1.7 (Jan2014-SP2). I'm using Ubuntu 12.04 LTS Precise. My Stethoscope works perfectly displaying valuable information. However, I have some confusion about the metrics. Why some MAL statement has non-zero 'blk.reads' and 'blk.writes' but their rbytes and wbytes are zero? What exactly does these memory related metrics mean? Thanks! Xuntao On 06/06/2014 06:00 PM, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. tomograph does not work (Cheng Xuntao) > 2. Re: tomograph does not work (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 06 Jun 2014 11:18:29 +0800 > From: Cheng Xuntao <chengxuntao(a)gmail.com> > To: users-list(a)monetdb.org > Subject: tomograph does not work > Message-ID: <53913305.5090803(a)gmail.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi all, > > I need to have a visual view of the execution detail of each thread in > monetdb so I wish to use Tomograph. I followed this > https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph > and have installed all required pacakges. But Tomograph does not respond > when I finished my queries. All its outputs are empty. However, my > Stethoscope works perfectly. > > Is there anything not listed in the Tomograph documentation that needs > to be done to get it work? > > Thanks! > > Xuntao > > > ------------------------------ > > Message: 2 > Date: Fri, 06 Jun 2014 08:26:00 +0200 > From: Martin Kersten <Martin.Kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: tomograph does not work > Message-ID: <53915EF8.5030202(a)cwi.nl> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi > > Could you indicate which platform and MonetDB version you are using. > > regards, Martin > > > On 06/06/14 05:18, Cheng Xuntao wrote: >> Hi all, >> >> I need to have a visual view of the execution detail of each thread in >> monetdb so I wish to use Tomograph. I followed this >> https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph >> and have installed all required pacakges. But Tomograph does not respond >> when I finished my queries. All its outputs are empty. However, my >> Stethoscope works perfectly. >> >> Is there anything not listed in the Tomograph documentation that needs >> to be done to get it work? >> >> Thanks! >> >> Xuntao >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > > ------------------------------ > > End of users-list Digest, Vol 22, Issue 5 > *****************************************

2 1

tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi all, I need to have a visual view of the execution detail of each thread in monetdb so I wish to use Tomograph. I followed this https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph and have installed all required pacakges. But Tomograph does not respond when I finished my queries. All its outputs are empty. However, my Stethoscope works perfectly. Is there anything not listed in the Tomograph documentation that needs to be done to get it work? Thanks! Xuntao

2 1

Announcement: New Jan2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 02 Jun '14

02 Jun '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP2 bugfix release Build Environment * Lots of minor fixes were made for potential defects found by Coverity Scan. * On Windows we now build the geom module against version 3.4.2 of the geos library. Client Package * ODBC: Implemented {call procedure-name(...)} escape. The version {?=call ...} is not implemented. Bug Fixes * 3424: numeric values at the front of strings determines whether CAST works successfully * 3439: Python driver drops milliseconds from timestamps * 3446: SET READ ONLY forgets previous changes * 3455: String columns unusable from 64-bit .NET via ODBC * 3456: Insert fails * 3457: When kernel of remote client crashes, the connection remains established on server side * 3458: mserver5 crash on SQL: SELECT COUNT(*) FROM SYS.TABLES HAVING COUNT(*) > 0 * 3461: mserver5 crash on SQL: SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS) OR FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS) * 3462: Invalid SQL (IN with subquery which returns multiple columns) is accepted * 3463: Crash on SELECT with SERIAL aggregation and GROUP BY column alias's * 3468: Local temporary table persists across sessions * 3469: Absolute network paths considered invalid for COPY INTO ... FROM statement. * 3473: Various memory leaks in SQL compilation * 3477: ODBC driver raises "unexpected end of input" for prepared string parameter from .NET application * 3481: Cannot run multiple COPY INTO statements in one 's'-command * 3482: Crossproduct error

3 9

how to count the number of query results in monetdb
by Bing Zhang 29 May '14

29 May '14

Hi: Is there a fast way to get the number of results, instead of looping on the ResultSet.next()? Thanks.

1 0

Processing web logs in monetdb
by Christopher Nelson 27 May '14

27 May '14

Hello, I've been looking at monetdb for a while, and I've been very impressed with the project. I would like to use monetdb to analyze web log data from a very active site. I have no doubt that it can handle the quantity of data, but I'm not sure how to perform some of the queries that I have used in other systems. For example, I would like in particular to be able to perform click-stream analysis of the data. On other systems I've used proprietary extensions which allowed me to create a window by pattern matching a certain column. Do you have any suggestions about where I should look to perform similar kinds of queries? Thanks! -={C}=-

2 1

question: efficient use of partitioned tables
by László Sándor 26 May '14

26 May '14

Hi, This might be less of MonetDB-specific question about features than about RDBMS (or just SQL) in general, but I would still appreciate your advice in these matters. I have data with a few "cross-sectional" tables (e.g. the parents of an individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately). I wonder if it is a good idea to merge/union the annual tables into long panels. The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary steps. However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. The merged tables could be too big anyway — if it matters in MonetDB (think of tables ~70 GB in some cases, while my server has 128 GB RAM), while many use cases would focus on a subset of years anyway. Do you have any thoughts about this? Maybe more concrete questions would be: — How cumbersome would it be (for a novice!) to code joining data from the annual tables separately, for multiple series of tables? (E.g. union all tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then employment records year-by-year etc.) — How much slower would this *run* than simply selecting years from the longitudinal tables? — Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Thanks, Laszlo

1 0

Strange query performance
by N.H. Schuiling 24 May '14

24 May '14

Hi all, Can someone explain me why this simple query takes so long to execute on MonetDB 5 server v11.17.13 "Jan2014-SP1"? sql>select * from fp_tree limit 1; +-----------------------------+ | path | +=============================+ | ;8;60;379;519;1241;174;692; | +-----------------------------+ 1 tuple (1.2s) Table fp_tree has one column path with data type string. Some useful statistics of table fp_tree: sql>select count(*) from fp_tree; +--------+ | L1 | +========+ | 416499 | +--------+ 1 tuple (2.801ms) sql>select max(length(path)) from fp_tree; +------+ | L1 | +======+ | 1275 | +------+ 1 tuple (254.742ms) sql>select min(length(path)) from fp_tree; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (272.186ms) sql>select avg(length(path)) from fp_tree; +--------------------------+ | L1 | +==========================+ | 202.73579288305606 | +--------------------------+ 1 tuple (331.306ms) Query trace: +---------+-------------------------------------------------------------------+ | ticks | stmt | +=========+===================================================================+ | 3 | X_2 := sql.mvc(); | | 29 | X_35:bat[:oid,:oid] =<tmp_440>[104124] := sql.tid(X_2=0,"sys","fp | : : _tree",0,4); : | 6 | X_8=<tmp_3501>[0] := sql.bind(X_2=0,"sys","fp_tree","path",1); | | 40 | (X_52:bat[:oid,:oid] =<tmp_2110>[0],X_53:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4); : | 8 | X_45:bat[:oid,:str] =<tmp_1357>[104127] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,3,4); : | 3 | X_57=<tmp_1357>[104127] := sql.delta(X_45=<tmp_1357>:bat[:oid,:st | : : r][104127],X_52=<tmp_2110>:bat[:oid,:oid][0],X_53=<tmp_3501>:bat[ : : : :oid,:str][0],X_8=<tmp_3501>[0]); : | 4 | X_41:bat[:oid,:oid] =<tmp_1256>[104127] := sql.tid(X_2=0,"sys","f | : : p_tree",3,4); : | 8 | X_61=<tmp_302>[104127] := algebra.leftfetchjoin(X_41=<tmp_1256>:b | : : at[:oid,:oid][104127],X_57=<tmp_1357>[104127]); : | 10 | X_65=<tmpr_1256>[1] := algebra.subslice(X_61=<tmp_302>[104127],0: | : : wrd,0:wrd); : | 6 | X_71=<tmp_1357>[1] := algebra.leftfetchjoin(X_65=<tmpr_1256>[1],X | : : _61=<tmp_302>[104127]); : | 1 | language.pass(X_61=<tmp_302>[104127]); | | 24 | (X_50:bat[:oid,:oid] =<tmp_2110>[0],X_51:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4); : | 5 | X_44:bat[:oid,:str] =<tmp_1560>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,2,4); : | 3 | X_56=<tmp_1560>[104124] := sql.delta(X_44=<tmp_1560>:bat[:oid,:st | : : r][104124],X_50=<tmp_2110>:bat[:oid,:oid][0],X_51=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_39:bat[:oid,:oid] =<tmp_1760>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",2,4); : | 5 | X_60=<tmp_302>[104124] := algebra.leftfetchjoin(X_39=<tmp_1760>:b | : : at[:oid,:oid][104124],X_56=<tmp_1560>[104124]); : | 8 | X_64=<tmpr_1760>[1] := algebra.subslice(X_60=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 4 | X_70=<tmp_1560>[1] := algebra.leftfetchjoin(X_64=<tmpr_1760>[1],X | : : _60=<tmp_302>[104124]); : | 1 | language.pass(X_60=<tmp_302>[104124]); | | 21 | (X_48:bat[:oid,:oid] =<tmp_2110>[0],X_49:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4); : | 5 | X_43:bat[:oid,:str] =<tmp_1660>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,1,4); : | 1 | X_55=<tmp_1660>[104124] := sql.delta(X_43=<tmp_1660>:bat[:oid,:st | : : r][104124],X_48=<tmp_2110>:bat[:oid,:oid][0],X_49=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_37:bat[:oid,:oid] =<tmp_1054>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",1,4); : | 5 | X_59=<tmp_302>[104124] := algebra.leftfetchjoin(X_37=<tmp_1054>:b | : : at[:oid,:oid][104124],X_55=<tmp_1660>[104124]); : | 8 | X_63=<tmpr_1054>[1] := algebra.subslice(X_59=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 5 | X_69=<tmp_1660>[1] := algebra.leftfetchjoin(X_63=<tmpr_1054>[1],X | : : _59=<tmp_302>[104124]); : | 1 | language.pass(X_59=<tmp_302>[104124]); | | 23 | (X_46:bat[:oid,:oid] =<tmp_2110>[0],X_47:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4); : | 9 | X_42:bat[:oid,:str] =<tmp_462>[104124] := sql.bind(X_2=0,"sys","f | : : p_tree","path",0,0,4); : | 2 | X_54=<tmp_462>[104124] := sql.delta(X_42=<tmp_462>:bat[:oid,:str] | : : [104124],X_46=<tmp_2110>:bat[:oid,:oid][0],X_47=<tmp_3501>:bat[:o : : : id,:str][0]); : | 6 | X_58=<tmp_764>[104124] := algebra.leftfetchjoin(X_35=<tmp_440>:ba | : : t[:oid,:oid][104124],X_54=<tmp_462>[104124]); : | 8 | X_62=<tmpr_440>[1] := algebra.subslice(X_58=<tmp_764>[104124],0:w | : : rd,0:wrd); : | 10 | X_72=<tmp_462>[1] := mat.packIncrement(X_62=<tmpr_440>[1],4); | | 3 | X_74=<tmp_462>[2] := mat.packIncrement(X_72=<tmp_462>[2],X_63=<tm | : : pr_1054>[1]); : | 1 | language.pass(X_63=<tmpr_1054>[1]); | | 2 | X_75=<tmp_462>[3] := mat.packIncrement(X_74=<tmp_462>[3],X_64=<tm | : : pr_1760>[1]); : | 1 | language.pass(X_64=<tmpr_1760>[1]); | | 3 | X_66=<tmp_462>[4] := mat.packIncrement(X_75=<tmp_462>[4],X_65=<tm | : : pr_1256>[1]); : | 1 | language.pass(X_65=<tmpr_1256>[1]); | | 9 | X_11=<tmpr_1206>[1] := algebra.subslice(X_66=<tmp_462>[4],0:wrd,0 | : : :wrd); : | 5 | X_68=<tmp_1760>[1] := algebra.leftfetchjoin(X_62=<tmpr_440>[1],X_ | : : 58=<tmp_764>[104124]); : | 1 | language.pass(X_62=<tmpr_440>[1]); | | 6 | language.pass(X_58=<tmp_764>[104124]); | | 1019824 | X_77=<tmp_1256>[1] := mat.packIncrement(X_68=<tmp_1760>[1],4); | | 8 | X_78=<tmp_1256>[2] := mat.packIncrement(X_77=<tmp_1256>[2],X_69=< | : : tmp_1660>[1]); : | 7 | X_79=<tmp_1256>[3] := mat.packIncrement(X_78=<tmp_1256>[3],X_70=< | : : tmp_1560>[1]); : | 18 | X_67=<tmp_1256>[4] := mat.packIncrement(X_79=<tmp_1256>[4],X_71=< | : : tmp_1357>[1]); : | 18 | X_12=<tmp_1357>[1] := algebra.leftfetchjoin(X_11=<tmpr_1206>[1],X | : : _67=<tmp_1256>[4]); : | 1095479 | barrier X_89 := language.dataflow(); | | 13 | X_13 := sql.resultSet(1,1,X_12=<tmp_1357>[1]); | | 7 | sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=<tmp_135 | : : 7>[1]); : | 2 | X_17 := io.stdout(); | | 33 | sql.exportResult(X_17=="104d2":streams,X_13=7); | | 0 | end s2_2; | | 1095725 | function user.s2_2(); | | 1095772 | X_5:void := user.s2_2(); | +---------+-------------------------------------------------------------------+ 56 tuples (1.1s) Why does mat.packIncrement takes so much time? Thanks in advance, Nik

1 0

How to use SciQL on MonetDB
by Yingwei Yu 23 May '14

23 May '14

Hello, I am new to MonetDB, and I am very interested in the SciQL functionality. I downloaded and installed the windows build of MonetDB. When I tried some SciQL on the client, it seems that it does not work. I followed the commands in paper Kersten et. al (2011) as follows: sql>start transaction; auto commit mode: off sql>CREATE ARRAY A1 ( more> x INTEGER DIMENSION[4], more> v FLOAT DEFAULT 0.0); syntax error, unexpected IDENT, expecting INDEX in: "create array" Do I miss something important? Please advise. Thanks, yingwei

2 1

ODBC driver cannot be removed (or repaired, upgraded), Jan2014-SP2 .msi files
by László Sándor 22 May '14

22 May '14

Hi, With today's Jan2014-SP2 update, I wanted to uninstall and reinstall using the .msi files in Windows Server 2012. (I also wanted to change the install locations, as I have no access to the default location on C to change the bat files and edit where my dbfarm should be.) Maybe because I ran MonetDB5-SQL-Installer-x86_64-20140514.msi before MonetDB-ODBC-Installer-x86_64-20140514.msi, but now the ODBC remove fails, repairing also. A desperate deletion of the entire C:/Program Files/MonetDB, did not make things better, maybe it made worse. Could you let me know how I could proceed with uninstalling the ODBC driver? I do want to have a clean, stable reinstall (under the new location) which I can trust. Thanks! Laszlo

1 2

ascii encoding error in block input (COPY INTO)
by László Sándor 21 May '14

21 May '14

Hi, I found previous notes<https://www.monetdb.org/pipermail/users-list/2010-November/004529.html>here that the server would expect UTF-8 input from the client or COPY INTO, so I need to work on my my Western ISO Latin 1 files. However, the error message is confusing about, COPY INTO complains about "'ascii' codec can't decode byte 0xc4 in position 8: ordinal not in range(128)." Shall I convert the data to UTF-8 then, or is that futile? What has the ascii codec to do with UTF-8? Thanks, Laszlo

1 0

COPY INTO on Windows
by László Sándor 21 May '14

21 May '14

Hi, I investigated further, and actually I cannot get any CSV file working with monetDB's COPY INTO on Windows. Hannes filed a bug report today: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3484 However, I tried other things since his/our attempts, e.g. removing the final end-of-line (EOL, still CRLF on Windows) or switching EOL to LF only for the temporary file (and changing the COPY INTO call's delimiters too, of course). Neither helped. Still the same error: Incomplete record at end of file. (Which I am not sure whether it literally means the end of the source textfile or simply the last line before breaking.) Basically, the question is, is there a way to get this working on Windows in Jan14-SP1? Or could someone please verify it's worth downgrading to a previous because it definitely can do COPY INTO? Thanks! Laszlo

2 3

vacuum safe to use
by Bryan Senseman 20 May '14

20 May '14

All, Is the sys.vacuum call safe, anybody using it? I notice it is marked experimental. We have times when an entire data set is reprocessed and reloaded forcing a delete to occur, we have added this call after the delete and it seems to be working, however we still get a frequent number of database crashes. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

COPY INTO robust to empty row at end of CSV
by László Sándor 19 May '14

19 May '14

Hi all, Hannes Mühleisen (cc'd) generously shared his homepages.cwi.nl/~hannes/importer.py Python script with me last week, which automates the creation of tables from text files: Python's csv-reader helps recognize the dialect of the text file as well as grab the header for column names and identify the types for the columns, and then a single COPY %i OFFSET %i RECORDS INTO %s FROM \'%s\' USING DELIMITERS … is issued to MonetDB (see line 404 of the script). I had some issues with this over the last three days, but now I see the source of the error I get: There is nothing wrong with Hannes's Python code but when COPY INTO turns back to the text file, MonetDB still complains about the last row containing an "incomplete record" (it is empty) even if Python was smart enough not to count that row into the number of records beforehand. Is there a way to call COPY INTO that is robust to the text file ending in an extra empty row? It is common with some generated CSV or TSV files. By the way, I also have a follow-up about how to merge some of the tables together. I am happy to work on this, of course, I don't expect anyone to script this for me, but I am confused about how much I can use SQL variables for this (and how) versus how much I need to use another language to script the repeated calls to MonetDB about each year for each series of tables (I have dozens of dozens). I hope this is a reasonably well-posed question: http://dba.stackexchange.com/questions/65329/dynamic-sql-merge-partitioned-… Thanks for any help, Laszlo

2 7

type recognition, *creating* tables from CSVs
by László Sándor 15 May '14

15 May '14

Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399 I am confused whether you can copy records into a *new* table in MonetDB. The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows. A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing. Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation. Other answers[4] also suggest that this is impossible in MySQL without external scripts. The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf) though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious. [1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399

3 5

SELECT failure: "insufficient space for HEAP ..."
by Clarkson, Edward C. 14 May '14

14 May '14

Hi, We have an application (http://tmt.gtri.gatech.edu/lytic.html) based on a relatively modest, single (~1-10M row, ~20 column) MonetDB table (Win7, 64 bit, Jan14-SP1 server release, quad-core, 8GB RAM) and have been encountering sporadic SELECT query failures with the error: "Insufficient space for HEAP of 1017856 bytes". Since I've been trying in earnest to diagnose this, that byte value has been consistent, FWIW. The failures are frustratingly intermittent; if I rerun a failing query (by hand, using mclient) it sometimes fails again, and sometimes works correctly. I've been unable to figure out any sort of pattern. Unfortunately, I can't post the exact table and query, but the general form of the queries (which have ~5-10 case clauses) are: SELECT CASE WHEN Foo = 'ValueA' and Bar LIKE 'ValueB%' THEN 100*(Baz-1234)/1234 WHEN Foo = 'ValueC' and Bar LIKE 'ValueD%' THEN 100*(Baz-3456)/3456 ... END FROM table WHERE <same case statement as above> IS NOT NULL LIMIT 1 Any ideas what I might do to narrow this down or other information I might provide? I captured an explain of a failing query--it's many hundreds (1000s?) of lines, so I didn't want to include it here in full. All suggestions appreciated. Thanks, Ed P.S. MonetDB has been a fantastic complement to our application, thanks to everyone involved!

2 3

thank you for monetdb
by Anthony Damico 02 May '14

02 May '14

i love it this much: http://2.bp.blogspot.com/-ogFP6EHJ6h8/U1E225vANvI/AAAAAAAAww8/9wOEp95iekk/s…

2 1

why the same procedure (in shell or in sql) cost so different time?
by integrity 30 Apr '14

30 Apr '14

Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng

4 8

procedure to follow when changing the sql-optimizer settings
by Mahmut Uludag 25 Apr '14

25 Apr '14

Hi, We were getting database crashes when we were using our database with read/write mode and using the default sql_optimizer settings. We decided to run our database in read-only mode and also has changed the sql_optimizer setting to sequential_pipe. With new settings we don't see any crashes but have started to see error messages in the log files similar to the following lines. 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_schema schema1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_table schema1.table1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_column table1.col1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback done 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit done My question is there a procedure to follow when we change sql-optimizer settings? I assume MonetDB has some history of the past and because of the change of the sql_optimizer settings the bits in the history causing these error messages but i would not be surprised if this is completely wrong. Mahmut Uludag

3 4

concurrency and use of temporary tables
by Niall Rooney 23 Apr '14

23 Apr '14

Hello, I am running a java program which connects to a MonetDB database and runs a thread where each thread creates a number of local temporary tables using the and populates these tables in certain data processing steps. On termination the temporary tables are deleted. These temporary tables are utilised in a number of select statements in combination with the tables in the database. Tables are created using a table name where a UUID is generated for each thread so the use of local tables is unique to the thread e.g. create local temporary table tempquerywords0e5a0e08c1d24cf6b9529426463b6191(word_word varchar(255)) ON COMMIT PRESERVE ROWS. All needed temporary tables are created by the thread as an initial process. The connection to the database is set with autoCommit set to true. If each thread is run sequentially , there is no problem. However if I run the process concurrently with a maximum of 2 concurrent threads, some threads terminate early with an Exception message e.g. nested exception is java.sql.SQLException: SELECT: no such table 'tempweightedclustersa7f7f47e7c184175ac8ab5ee2dc76a03'. This message is confusing as the table is populated ok before the select. Although other participants have posted messages concerning problems based on concurrent updates and temporary tables , is there an issue concerning concurrent reads and temporary tables? Best Regards Niall

1 0

TPC-H in MALs
by Qing Guo 23 Apr '14

23 Apr '14

Hi, I was trying to run “mserver” (standalone) in a simulator to do some performance study. As mserver only reads MALs, are there any ready-to-use MAL scripts for TPC-H? I tried to use “explain” in “mclient”, but the translated MALs seem to be too detailed and cannot simply copy and run. Any idea on this? Thank you! -Kevin

2 2

Build for Ubuntu 14.04 "Trusty Tahr"?
by Robert Kosara 19 Apr '14

19 Apr '14

Hi, I see Ubuntu 14.04 (which was just officially released a few days ago) listed on the Ubuntu/Debian packages list: http://dev.monetdb.org/downloads/deb/ However, the “trusty” directory doesn’t exist, and I can’t install MonetDB through apt-get. Will that build be enabled in the near future? Thanks, Robert

2 1

Overflow calculation in tpch Q1
by Tsai Li Ming 17 Apr '14

17 Apr '14

Hi, I’m getting an overflow calculation when SF=100 for Q1. The rest of the queries are fine. There’s an old thread on this from Sep 2013: https://www.monetdb.org/pipermail/users-list/2013-September/006817.html I’m using 11.17.13. Will this be fixed soon? Thanks!

1 0

Blob and Clob not implement in JDBC v 2.10?
by Phat Loc 11 Apr '14

11 Apr '14

Hello, I am not able to get Blob and Clob to work in JDBC v 2.10? I get a SQLFeatureNotSupportedException? Is this the correct behavior? Thanks, Phat

2 5

fastest way of having a number of rows of a table in monetdb ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Helo everybody, To have the exact number of rows of a table t I actually run the following query SELECT COUNT(*) AS n FROM t; Is there a faster way to do this ? Best regards, Fopa

1 0

How to have the list of all tables names of a monetdb database ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Hi everyone In postgresql one can have all tables names by running the following query SELECT table_name FROM information_schema.tables WHERE table_schema='public'; Is there something similar in monetdb to have the list of tables ? Best regards, Fopa

3 3

UPDATE fails with BATproject: does not match always
by Radovan Bičiště 11 Apr '14

11 Apr '14

Hello, I have a problem with following update and suspect data corruption. UPDATE crmkarta SET rodne_cislo = (SELECT MAX(rodne_cislo) FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL) WHERE cislo_karty IS NOT NULL --AND aktivita > 0 AND EXISTS (SELECT verka_klient.cislo_karty FROM verka_klient WHERE crmkarta.cislo_karty = verka_klient.cislo_karty AND rodne_cislo IS NOT NULL); Can not create object [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Next: BATproject: does not match always [SQL State=22000] Would dump and restore of tables help ? We are on version Jan2014-SP1. Thank you, Radovan

1 0

Update with subquery
by George Kollias 09 Apr '14

09 Apr '14

Hello, Let's assume the script below (version Jan-2014-SP1): 1> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | 2> select * from saving where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 2599.01 | 3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk'); | ifthenelse_isnull_<_L7 | +==============+ | -992420.33 | -- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk'); 5> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | So, it seems that the step 4's update has no impact. Am i missing something? Thank you, George

2 2

MonetDB usage of openssl and recently reported Security Vulnerability Issue in Openssl ( refer http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-opens-two-thirds-of-the-web-to-eavesdropping/ )
by Ashish Kumar Singh 09 Apr '14

09 Apr '14

Hi All, Can any one give some guideline on how MonetDB uses openssl library which is a dependency expressed during compile time and what kind of measures should taken to avoid security vulnerability reported recently with openssl. Please refer to links below : http://arstechnica.com/security/2014/04/critical-crypto-bug-in-openssl-open… http://blog.cloudflare.com/staying-ahead-of-openssl-vulnerabilities Regards, Ashish

2 1

decreasing table size
by Radovan Bičiště 09 Apr '14

09 Apr '14

Hello, Thanks to the great new feature sys.storage() I'm able to see table size. As per documentation I would expect that "DELETE FROM table" without any condition will reclaim the disk space. According the sys.storage() it does not. I tried to delete and than select from sys.storage() and the table size was still the same. Am I doing something wrong? Thank you, Radovan

2 2

Properly handling of concurrency conflicts
by Alastair McKinley 09 Apr '14

09 Apr '14

Dear all, I am currently testing an application running on MonetDB that experiences intermittent errors when under high concurrency. I believe these are aborted transactions (the mapi error code is -4). The applications loads a small input table using the mapi interface, runs two queries internally (including writing to an intermediate table) and then reads the result set. I have tried to produce a similar minimal example using the Python API which does produce concurrency errors and I am wondering what the correct thing to do on the client side is. Should I simply resubmit the failed query? The Python script used is shown below: import monetdb.sql import sys from multiprocessing import Process import uuid def f(): dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) for i in range (0,20): tablename = "input_"+str(uuid.uuid1()).replace("-","_") cursor = dbh.cursor() #cursor.execute('create local temporary table %s (term int, p float) on commit preserve rows;' % tablename) cursor.execute('declare table %s (term int, p float);' % tablename) cursor.execute('insert into %s values (1,0.1);' % tablename) cursor.execute('select * from %s;' % tablename) print(cursor.fetchall()) cursor.execute('drop table %s;' % tablename) dbh.close() procs=[] for t in range(0,int(sys.argv[4])): p = Process(target=f) procs.append(p) p.start() for proc in procs: proc.join()? Running this locally with "python2.7 concurrency.py 50000 test_db 127.0.0.1 10" results in a number of error messages like: ?ProgrammingError: 40000!COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead What is the right thing to do? It seems that anything that writes to the db even when they are separate tables causes these messages. Best regards and thanks, Alastair

2 3

Flushing the cache
by Masood Mortazavi 08 Apr '14

08 Apr '14

Is there a method (available from mclient) to cause MonetDB to evict all cached columns without having to restart? Thanks, - Masood

2 1

Hash ('#') in column aliases
by Clarkson, Edward C. 07 Apr '14

07 Apr '14

Hi, I'm using the 64 bit windows Jan'14 release (v11.17.9 "Jan2014"). I wanted to confirm the following behavior is a bug before I reported it (this is from the mclient terminal): sql>CREATE TABLE test (a int); operation successful (5.230ms) sql>SELECT a AS "#" FROM test; +--+ | | +==+ +--+ 0 tuples (3.320ms) In contrast: sql>SELECT a AS "@" FROM test; +---+ | @ | +===+ +---+ 0 tuples (1.672ms) When there's data in the table, all the *results* are fine, it's just the field names which are wonky. Loosely, it looks like the first alias (I have not tried making columns with '#' in the name, so maybe there too?) to use '#' basically blanks out its and all following column names in the result set--so even if I'm missing something w/r/t escaping "#", it doesn't' seem right that all following columns should also be blank... Thanks, Ed

1 0

Scope of local temporary tables
by Alastair McKinley 05 Apr '14

05 Apr '14

Hi all, I was wondering if I could clarify something about the scope of local temporary tables in MonetDB. I imagined that these would be local to a single client session but it seems that I am wrong about this. If I execute the following Python script multiple times in succession, it fails as the table name is already in use. import monetdb.sql import sys dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) cursor = dbh.cursor() cursor.execute('create local temporary table input (term int, p float) on commit preserve rows;'); cursor.execute('insert into input values ( 1,0.1);'); cursor.execute('select * from input;'); print(cursor.fetchall()) dbh.close() How is the local temporary table different from a normal table in this case? Best regards, Alastair

2 3

Fastest ('standard") way to load data into MonetDB
by Masood Mortazavi 05 Apr '14

05 Apr '14

What is the fastest way to load data into MonetDB, using its "standard" user APIs? -m.

2 3

How to update one table from another table or from aggregation in subqueries?
by George Kollias 04 Apr '14

04 Apr '14

Hello, On a related note, would it be possible to have a "with" statement inside the subquery, .i.e. "update t1 set data = *(with tmp_table as (select ...) select ... from ... where ...)* where exists (select * from t2 where t1.id= t2.id);"? According to my tests it's not possible. What would be the best way to simulate it in MonetDB? Using a View/Temporary Table? Thank you, George > Thanks for your reply and clarification! > With Jan2014 release, the two functions can be expressed in the following > working forms: > 1) update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) > where exists (select * from t2 where t1.id=t2.id); > 2) update t1 set data = (select sum(t2.data) from t2 where t2.id=t1.idgroup > by t2.id) where exists (select * from t2 where t1.id=t2.id);

1 0

MonetDB Performance and Data Schema
by Dino Veritas 02 Apr '14

02 Apr '14

Hi, My firm is investigating the use of MonetDB as a potential market data store. I am interested in the opinion of those on this list as to how best to structure the data for optimum performance. Basically, our data consists of ~50,000 instruments. One use we are looking at is for the storage of bar data, one for each minute.The resulting data for each bar is like <Timestamp,Open,High,Low,Close,Volume,...>, with one observation per minute. While not all symbols will have an entry for each minute, many observations will share the same Timestamp. Is this an appropriate use for MonetDB? One schema I tried was to put all symbols into the same table, and to separate the tables by month. So for 5 years that would be 5*12=60 tables. A query along the lines of "SELECT * FROM "200801" WHERE symbol='IBM' UNION ALL SELECT * FROM "200802" WHERE symbol='IBM' UNION ALL ...)" The reads for a single table were quite good, however, when reading across multiple tables I encountered a lot of swap space and disk IO which drastically slowed down the query. I also tried one table per symbol but found this to be simply untenable as INSERTS became extremely slow (on the order of minutes) and once again, massive swap space usage. How would you partition this data? We will be inserting once per day in bulk, and I am not super concerned with the insert performance. However, query performance is paramount, with some users querying data very frequently for long time periods for single or groups of symbols. The machine I am testing on has 16GB of RAM and is running Windows. However, the machine MonetDB would ultimately be deployed on would have much more RAM. Even so, the size of the results of a single query are less than 16GB and should fit into memory without issue. So please help me, MonetDB gurus, with any suggestions as to how you would partition and store this data. I would appreciate any input. Thank you!

5 8

Cycling through cursor in a procedure
by Radovan Bičiště 01 Apr '14

01 Apr '14

Hello, Please is there a flow control syntax for cycling through rows in a cursor in a procedure? I can not find any other solution to how to select highest numbers from a table which total falls under certain threshold. :( I have a function that returns sorted rows and can be used as a subquery and I did not get further ... Thank you, Radovan

4 5

Help, took 18 hours to COPY 113 million rows
by Bryan Senseman 28 Mar '14

28 Mar '14

Anybody have any insight into why the Windows performance is so poor? I am trying to restore a database (55GB total) and it took 18 hours to insert into my first big table. The CPU and Disk was less than 25% the entire time; with complete periods of zero activity. Attached is a screen show of the moderate cpu 28% and minor disk and the disk is **only** reading the dump file by mclient. -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

Restore/Load just hangs for hours (Windows Jan2014)
by Bryan Senseman 27 Mar '14

27 Mar '14

For about 3 hours I saw cyclical activity on memory and disk when loading the 55GB dump file (created on Linux via \D), however for the last 12 hours it has just sat there doing nothing. No CPU activity, no disk; this happened last time and I believe after 17 hours of "nothing" it popped up with a series of "operation completed" messages, what is it doing? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 3

MonetDB on Windows keeps crashing
by Bryan Senseman 26 Mar '14

26 Mar '14

MonetDB (Feb2013SP5 & Jan2014) keep crashing on Windows Server 2008. Multiple times per week, we find the MonetDB process has crashed and needs to be restarted. Is there a log file somewhere that we can see that might give us insight into why it fails? -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

3 3

Extending physical memory via memory cache
by Calum Miller 26 Mar '14

26 Mar '14

Good Morning, Is it possible to extend the amount of physical memory available to monetdb via Memcached/Hazelcast? Thanks Calum Miller Millersoft

2 1

JDBC - how to set autocommit in url as on option
by Radovan Bičiště 21 Mar '14

21 Mar '14

Hello, We are using the JDBC driver from KETTLE (http://kettle.pentaho.com) tool. I could not find an option how to tell the driver that my connections are autocommit. I tried to add option autocommit=true. The tool added the opton at the end of the JDBC URL as follows jdbc:monetdb://172.26.123.14:50000/zkdev1;autocommit=true . But it failed with error that it could not find database "zkdev1;autocommit=true". Thank you for your help. Radovan

3 2

How to execute a MAL function in a monetdb database ?
by Leon-Constantin Fopa 17 Mar '14

17 Mar '14

I'm reading the MonetDB Internal Description because I'm interested to implement a special procedure direct in the MonetDB Algebra Language (MAL) instead of SQL-frontend (I hope to gain more performance by doing so). Here is a sample MAL code provide in the MonetDB documentation function sample(nme:str, val:any_1):bit; c := 2 * 3; b := bbp.bind(nme); #find a BAT h := algebra.select(b,val,val); t := aggr.count(h); x := io.print(t); y := io.print(val); end sample; My question is how to execute such a MAL code upon one of my existing database ? Thans for any replay

2 1

Copy table between monet instances using COPY BINARY ...
by Radovan Bičiště 17 Mar '14

17 Mar '14

Hello, I read the following recipe https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad . Please is there a command or a binary that does the dump part "Each attachment file is produced by a program that writes the binary image of the BAT directly, ..." ? (mclient --format=raw?) We are on "MonetDB Database Server v1.7 (Feb2013-SP6)". Thank you for your help. Radovan

2 1

COPY INTO table specify columns
by Wei Zhuo 17 Mar '14

17 Mar '14

Hi, Couldn't find any info/doc if a list of column names, and order of columns, can be specified when using COPY INTO command. If not possible, is this something relatively easy to implement? Thanks!

2 1

possible to improve paging performance on the windows version of monetdb?
by Anthony Damico 17 Mar '14

17 Mar '14

hi monetdb team, could disk paging performance be improved on windows? a query run without versus with any disk paging is kinda like the hulk versus plain old bruce banner. ;) ==background== i'm working on a longer tutorial for http://asdfree.com/ about how to work with census microdata from 74 different countries (yes, 74!) with R and monetdb. i'm planning to release a blog post specifically for the brazilian microdata and then expand that code to work on the other 73 countries <https://international.ipums.org/international-action/countries>. both brazilian census bureau employees and outside users have taken a serious interest in this work, with official links from one<http://www.ibge.gov.br/home/estatistica/populacao/trabalhoerendimento/pnad2…>, two<http://www.ibge.gov.br/home/estatistica/indicadores/trabalhoerendimento/pme…>, three <http://www.ibge.gov.br/home/xml/pof_2008_2009.shtm> of their microdata pages. r and monetdb are obviously great choices for analysts who don't have the resources for expensive computers and/or software. a former employee at insee (the french stat. bureau) who currently consults on international demography projects outlined this problem for me pretty clearly: lower-resource statistical agencies in the developing world need a good, free option like (R + monetdb) for analysis of larger data sets on the (generally cheaper) hardware that they already have. international census bureau employees and other demographers are much more likely to have statistical but not computer programming skills, so as i work on these instructional documents, i'll aim to make the computing side of things as easy as possible. and, most central to this e-mail, much of this new potential audience uses windows. (on march 10th, 2014<http://cran-logs.rstudio.com/2014/2014-03-10.csv.gz>, 67% of MonetDB.R package downloads were windows users) ==problem== certain sql commands - it's hard for me to pin down which ones - tend to hang. i will run a command, look at my "process manager" and see RAM start getting gobbled up (not by mserver.exe but by some invisible process--i think disk paging?), and then wait. mserver.exe will use between 0% and 1% of the total CPU for a few minutes, then something will "wake up" and suddenly the CPU starts lighting up.. with 70%-90% usage across multiple cores. this 1% CPU usage to 75% CPU usage can jump back and forth a few times until the full command finally finishes. all of the time spent with 0% or 1% CPU usage - waiting for commands to execute - seems like an opportunity for improvement of the windows build? here's an example that took 11 minutes for a query run on 20,000,000 records.. > system.time( z <- svymean( ~v0601 , pes.design , byvar = ~v0001 , se = TRUE ) ) user system elapsed 18.47 1.13 *710.86 * ..totally awesome! but can it be improved? ==comparison between two machines== the attached .sql code has been run on two windows machines: on the windows server with 64 gb of ram-- # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. on my personal windows laptop-- # Serving database 'test', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. ==processing time comparison== processing time on the 64gb server: 27 tuples *(5.2s)* processing time on my 8gb laptop: 27 tuples *(7m 46s)* *so on a windows machine, if disk paging can be avoided, the processing time is 90x faster for this attached sql code?* ==how to load this reproducible example== ** data file with four columns ** https://drive.google.com/file/d/0B3LuuP081KDYNERWQWx5MEpZU2M/edit?usp=shari… ** commands to load the data ** CREATE TABLE "sys"."d10" ( "idkey" INTEGER, "v0601" VARCHAR(255), "pes_wgt" DOUBLE, "v0001" VARCHAR(255) ); COPY 20635472 RECORDS INTO d10 FROM 's:/temp/example.txt'; [[see .sql attachment for post-import commands]] ==general diagnostics== # MonetDB 5 server v11.17.11 "Jan2014-SP1" # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > ==conclusion question== the windows version of monetdb is intensely slow at rather unexpected times. can and will anything be done about this in the near future? thanks for monetdb!

1 1

MonetDB Slow Query with Index
by Summer Xia 16 Mar '14

16 Mar '14

Hi all, I created a table with 10GB (60M records), added unique index (hidden_id) manually after data insertion. I had the simplest query but it took one minutes to complete. select hidden_id from netflow where hidden_id = 350000; And also the query took tens of minutes "select * from netflow order by hidden_id limit 12500 offset 212500;". It really confuses me. I post the analysis of the first below. Any clue why it's so slow? trace select hidden_id from netflow where hidden_id = 350000; +----------+------------------------------------------------------------------+ | ticks | stmt | +==========+==================================================================+ | 3 | X_3 := sql.mvc(); | | 15 | X_7=<tmp_2510>[69396995] := sql.bind(X_3=0,"sys","netflow","hidd | : : en_id",0); : | 227 | X_4:bat[:oid,:oid] =<tmp_13332>[69396995] := sql.tid(X_3=0,"sys" | : : ,"netflow"); : | 72978741 | X_36=<tmp_4053>[1] := algebra.subselect(X_7=<tmp_2510>[69396995] | : : ,X_4=<tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 : : : 0:lng,true,true,false); : | 17 | (X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0]) := sql.bind(X_3=0,"sys" | : : ,"netflow","hidden_id",2); : | 14 | X_37=<tmp_13337>[0] := algebra.subselect(r1_10=<tmp_3416>[0],A0= | : : 350000:lng,A0=350000:lng,true,true,false); : | 6 | X_13=<tmp_3416>[0] := sql.bind(X_3=0,"sys","netflow","hidden_id" | : : ,1); : | 15 | X_38=<tmp_11053>[0] := algebra.subselect(X_13=<tmp_3416>[0],X_4= | : : <tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng : : : ,true,true,false); : | 4 | X_15=<tmp_4053>[1] := sql.subdelta(X_36=<tmp_4053>[1],X_4=<tmp_1 | : : 3332>:bat[:oid,:oid][69396995],X_10=<tmp_2175>[0],X_37=<tmp_1333 : : : 7>[0],X_38=<tmp_11053>[0]); : | 20 | X_17=<tmp_11053>[1] := sql.projectdelta(X_15=<tmp_4053>[1],X_7=< | : : tmp_2510>[69396995],X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0],X_13= : : : <tmp_3416>[0]); : | 6 | X_18 := sql.resultSet(1,1,X_17=<tmp_11053>[1]); | | 7 | sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 | : : =<tmp_11053>[1]); : | 2 | X_23 := io.stdout(); | | 25 | sql.exportResult(X_23=="104d2":streams,X_18=1); | | 1 | end s1_3; | | 73011629 | X_5:void := user.s1_3(350000:lng); | +----------+------------------------------------------------------------------+ This is the table being created. CREATE TABLE "netflow" ( "time_seconds" double DEFAULT NULL, "parsed_date" timestamp DEFAULT NULL, "date_time_str" varchar(45) DEFAULT NULL, "ip_layer_protocol" bigint DEFAULT NULL, "ip_layer_protocol_code" varchar(45) DEFAULT NULL, "first_seen_src_ip" varchar(45) DEFAULT NULL, "first_seen_dest_ip" varchar(45) DEFAULT NULL, "first_seen_src_port" bigint DEFAULT NULL, "first_seen_dest_port" bigint DEFAULT NULL, "more_fragments" varchar(45) DEFAULT NULL, "cont_fragments" varchar(45) DEFAULT NULL, "duration_seconds" bigint DEFAULT NULL, "first_seen_src_payload_bytes" bigint DEFAULT NULL, "first_seen_dest_payload_bytes" bigint DEFAULT NULL, "first_seen_src_total_bytes" bigint DEFAULT NULL, "first_seen_dest_total_bytes" bigint DEFAULT NULL, "first_seen_src_packet_count" bigint DEFAULT NULL, "first_seen_dest_packet_count" bigint DEFAULT NULL, "record_force_out" varchar(45) DEFAULT NULL ); Best regards, Summer

2 1

python 3.2 support
by Simon 13 Mar '14

13 Mar '14

Hi thanks for the great project. I just wonder when there would be python 3.2 support. Is there any way that we could use monetdb in python 3.2 before the official package? Thanks again. Simon

4 5

Announcement: New Jan2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 10 Mar '14

10 Mar '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP1 bugfix release Build Environment * Configure now enables the SQL front end by default. Merovingian * monetdb destroy -f now also works on running databases Bug Fixes * 3442: COPY INTO ... LOCKED reports incorrect count * 3443: DROP INDEX crashes server with BATsubselect: invalid argument: b must have a dense head * 3444: AND after ON () of LEFT OUTER JOIN with certain expressions will cause crash * 3452: ODBC driver build fails on Mac OS X due to a conflicting types for the SQLColAttribute with the unixODBC library

1 0

Fwd: [asdfree by anthony damico] New comment on analyze the program for international student asse....
by Anthony Damico 04 Mar '14

04 Mar '14

if there's any chance you might release SP1 soon, i'd appreciate it :) this user is hitting (resolved) bug 3443<http://bugs.monetdb.org/show_bug.cgi?id=3443>. thanks! ---------- Forwarded message ---------- From: lightest <noreply-comment(a)blogger.com> Date: Sun, Feb 23, 2014 at 7:53 PM Subject: [asdfree by anthony damico] New comment on analyze the program for international student asse.... To: ajdamico(a)gmail.com lightest <http://www.blogger.com/profile/12197082513968468249> has left a new comment on your post "analyze the program for international student asse...<http://www.asdfree.com/2013/12/analyze-program-for-international.html>": Hi Anthony, I've tried to run to codes, and when I run lines such as those of MICombine() functions, sometimes there is an error like the following: In R: Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) > ======================================== In the console: # MonetDB 5 server v11.17.9 "Jan2014" # Serving database 'pisa', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.913 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50007/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >!FATAL: 40000!COMMIT: transation commit failed (perhaps your disk is full?) exi ting (kernel error: !ERROR: BATsubselect: invalid argument: b must have a dense head. ) Press any key to continue . . . ======================================= Publish<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Delete<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Mark as spam<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Moderate<http://www.blogger.com/blogger.g?blogID=6983061292048347330#pendingcomments>comments for this blog. Posted by lightest to asdfree by anthony damico <http://www.asdfree.com/>at February 23, 2014 at 7:53 PM

2 3

How to update one table from another table or from aggregation in subqueries?
by Ning Yan 04 Mar '14

04 Mar '14

Hi, Firstly, I'm using MonetDB branch from MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) I have two tables, and want to complete the following two functions without creating a temporary table: 1) update one table from another table; and 2) update one table from aggregation of another table. I figured out how to do them in MySQL and Postgresql but similar queries failed in MonetDB. For 1), Suppose I have the following two tables: drop table t1; create table t1(id int, data int); drop table t2; create table t2(id int, data int); delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select*from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4); select*from t2; We need to set t1.data to t1.data+t2.data when their ids match, t1.id=t2.id . In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=t1.data+t2.data; In Postgresql, we can use: with tmp as (select t1.id, t1.data+t2.data as data from t1,t2 where t1.id= t2.id) update t1 set data = tmp.data from tmp where t1.id=tmp.id;select*from t1; Or for both, we can use: update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); But none of them work in MonetDB. This question is similar to question asked before ( https://www.monetdb.org/pipermail/users-list/2011-August/005072.html) but the answered suggested sees to be incorrect. There the two tables having the same value for tuple id=2. update t1 set data=(select data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); Thus in this query, the data field in 1st subquery of select actually comes from t2 not t1. However, if we changed to t1.data, the parser will not recognize name t1. It is somehow wired that it can recognize t1.id in condition clause. Not that the following query is also not correct: update t1 set data=(select t1.data + t2.data from t1, t2 where t2.id = t1.id) where exists (select t2.id from t2 where t2.id = t1.id); As the subquery becomes independent from external query, and might result in multiple values which violates cardinality of assignment. Also changing field names in t1 and t2 will not be helpful either. For 2), Suppose we have the following two tables: delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select * from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4), (2,5); select * from t2; We want to aggregate values in t2 and assign results to t1. In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=(select sum(t2.data) from t2 where t2.id=t1.id group by t2.id); Or in Postgresql, we can use: with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) update t1 set data=tmp.data from tmp where t1.id=tmp.id; In MonetDB, how to correctly write such subquery in update statement? It looks like that MonetDB supports with clause in select, but not in update. with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) select * from tmp; I'm also interested to know the performance difference between using subqueries and creating temporary tables. Thanks! Regards, -- Ning Yan

2 2

Re: what is monetdb doing?
by Luis Neves 25 Feb '14

25 Feb '14

On 02/25/2014 05:56 PM, Mrunal Gawade wrote: > Also try to tune your virtual memory settings by playing around with the following parameters. > This changes the flushing behavior of memory pages. > > Do not use the parameters below directly as it is. > > Look at what are your existing parameters and tune to delay flushing. The parameters below, will > delay flushing for an extreme time, which might not be good in your case as file system might get > out of sync. But as a test you can try these parameters to see if they solve the problem, and then > use correct parameters after playing around with them. > > sysctl -w vm.swappiness=0 > sysctl -w vm.dirty_expire_centisecs=6000000 > sysctl -w vm.dirty_background_ratio=90 > sysctl -w vm.dirty_writeback_centisecs=6000000 > sysctl -w vm.dirty_ratio=90 The current value for these parameters are: vm.swappiness = 60 vm.dirty_expire_centisecs = 3000 vm.dirty_background_ratio = 10 vm.dirty_writeback_centisecs = 500 vm.dirty_ratio = 20 I will read more about the effects of these settings and try to tune the system. Also... about the hugepages, these are Debian 6.0.4 servers and the only hugepages parameters I can find are these: /proc/sys/vm/nr_hugepages /proc/sys/vm/hugetlb_shm_group /proc/sys/vm/hugepages_treat_as_movable /proc/sys/vm/nr_overcommit_hugepages and all of them are set to "0". Thanks for your help. -- /** * Luis Neves * @e-mail: luis.neves(a)co.sapo.pt * @xmpp: lfs_neves(a)sapo.pt * @web: <http://about.me/luisneves> * @tlm: +351 962 057 656 * -- * As opiniões expressas por mim não são * necessariamente opiniões em que eu acredito * -- */

1 0

what is monetdb doing?
by Luis Neves 25 Feb '14

25 Feb '14

Hi all, I've run into a behaviour that I would like to understand and possibly disable. Two times per day the mserver daeomon starts trashing the disk furiously, I see that the size on disk of data directory goes up to 115 GB from 85 GB and then comes back again to the same initial size, during this process disk utilization goes trough the roof. Can anyone tell me what is monetdb doing? There are no import/export process running at this times, it seems that monetdb is doing some kind of data compaction... is there anyway to disable this? $ mserver5 --version MonetDB 5 server v11.15.7 "Feb2013-SP2" (64-bit, 64-bit oids) Thanks! -- Luis Neves

2 3

Re: slow performance with 10 joins and above
by Ying Zhang 24 Feb '14

24 Feb '14

Hai Imad, On Feb 20, 2014, at 10:16 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > Hi Ying, > > Thank you for your update, i will try to have an instance of monetdb > on linux machine and check the difference. Can you please specify the > optimal machine configuration for deploy (since we are using key value > store model, target number of row per table > 1 billion): CPU,RAM,SSD. This largely depends on the size of your hot data set, the type of queries, and the query load. It's important to have sufficient RAM to hold the how hot dataset. SSDs are always nice to have, but probably doesn't qualify the costs if you can have sufficient RAM. > Also do running monetdb in cluster will help to optimize queries > response or its just for fail-over purpose? does monetdb support > physical table data-partitioning? any reference on how monetdb > operates in cluster mode, like data partitioning, query plans, merging > results,... For this, you probably want to have a look at the possibilities to query "REMOTE DATABASES" and creating "MULTIPLEX-FUNNELS" here:http://www.monetdb.org/Documentation/monetdbd-man-page With kind regards, Jennie > > Thank you > >>>> >>>> On 17/02/14 11:44, imad hajj chahine wrote: >>>> >>>> Hi, >>>> >>>> I have the following query that do a pivot for the data stored in different tables, the time of the query is acceptable as long as the number of table to join is up to 10, 10 to 15 the query take more than 90 seconds to return, above 15 it >>>> does not >>>> return. >>>> Is there something i am missing, or i have to slice my query in smaller joins and the join back the results? >>>> >>>> PS: the max number of records in each table is < 200k >>>> >>>> select tval1.value as countall,tval2.value as ana1,tval3.value as ana2,tval4.value as count5,tval5.value as min5,tval6.value as max5,tval7.value as sum5,tval8.value as avg5,tval9.value as count6,tval10.value as min6,tval11.value as >>>> max6,tval12.value as >>>> sum6,tval13.value as avg6,tval14.value as count7,tval15.value as min7,tval16.value as max7,tval17.value as sum7,tval18.value as avg7 >>>> from "RPME".t_entity_cache tec join >>>> "RPME".t_value_int_cache tval1 on tec.id_schema=tval1.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval1.id_____entity and tval1.id_attribute=1 join >>>> "RPME".t_value_date_cache tval2 on tec.id_schema=tval2.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval2.id_____entity and tval2.id_attribute=2 join >>>> "RPME".t_value_string_cache tval3 on tec.id_schema=tval3.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval3.id_____entity and tval3.id_attribute=3 join >>>> "RPME".t_value_int_cache tval4 on tec.id_schema=tval4.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval4.id_____entity and tval4.id_attribute=4 join >>>> "RPME".t_value_numeric_cache tval5 on tec.id_schema=tval5.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval5.id_____entity and tval5.id_attribute=5 join >>>> "RPME".t_value_numeric_cache tval6 on tec.id_schema=tval6.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval6.id_____entity and tval6.id_attribute=6 join >>>> "RPME".t_value_numeric_cache tval7 on tec.id_schema=tval7.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval7.id_____entity and tval7.id_attribute=7 join >>>> "RPME".t_value_numeric_cache tval8 on tec.id_schema=tval8.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval8.id_____entity and tval8.id_attribute=8 join >>>> "RPME".t_value_int_cache tval9 on tec.id_schema=tval9.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval9.id_____entity and tval9.id_attribute=9 join >>>> "RPME".t_value_numeric_cache tval10 on tec.id_schema=tval10.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval10.id_____entity and tval10.id_attribute=10 join >>>> "RPME".t_value_numeric_cache tval11 on tec.id_schema=tval11.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval11.id_____entity and tval11.id_attribute=11 join >>>> "RPME".t_value_numeric_cache tval12 on tec.id_schema=tval12.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval12.id_____entity and tval12.id_attribute=12 join >>>> "RPME".t_value_numeric_cache tval13 on tec.id_schema=tval13.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval13.id_____entity and tval13.id_attribute=13 join >>>> "RPME".t_value_int_cache tval14 on tec.id_schema=tval14.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval14.id_____entity and tval14.id_attribute=14 join >>>> "RPME".t_value_numeric_cache tval15 on tec.id_schema=tval15.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval15.id_____entity and tval15.id_attribute=15 join >>>> "RPME".t_value_numeric_cache tval16 on tec.id_schema=tval16.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval16.id_____entity and tval16.id_attribute=16 join >>>> "RPME".t_value_numeric_cache tval17 on tec.id_schema=tval17.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval17.id_____entity and tval17.id_attribute=17 join >>>> "RPME".t_value_numeric_cache tval18 on tec.id_schema=tval18.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval18.id_____entity and tval18.id_attribute=18 >>>> where tec.id_schema=3 >>>> >>>> >>>> ___________________________________________________ >>>> users-list mailing list >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> <mailto:users-list@monetdb.org <mailto:users-list@monetdb.org>__> >>>> https://www.monetdb.org/____mailman/listinfo/users-list <https://www.monetdb.org/__mailman/listinfo/users-list> <https://www.monetdb.org/__mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>> >>>> >>>> >>>> >>>> >>>> >>> >> >>

1 0

Load Bulk Data With COPY INTO
by Summer Xia 21 Feb '14

21 Feb '14

Hi, I followed the description in load bulk data to load a 200MB csv file into monetdb. It worked on my desktop (64-bit windows 7 system, 32GB memory), but not on my laptop (64-bit windows 8 system, 2GB memory). I also tried with a windows 7 8GB memory computer, it didn't work either. I followed the same procedure on all computers. There is no other error except "failed to import data". I'm wondering what's the cause of loading error. BTW, data fields in this data include timestamp, char, text, char... Thanks ahead. Best regards, Summer

1 0

Indices on monetdb
by kanumula raviteja 21 Feb '14

21 Feb '14

Hello, I’m doing a comparision on a couple of databases including monetdb . But to maintain uniformity , I wanted to either turn off the indexing feature in monetdb (or) to know what all indices are being maintained. Thanks in advance .

1 0

Announcement: New Jan2014 Feature release of MonetDB suite
by Sjoerd Mullender 20 Feb '14

20 Feb '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014 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/>. Jan 2014 feature release Build Environment * Created development packages for RPM based systems and Debian/Ubuntu containing the files needed to create extensions to the SQL front end. * Removed Mx, the literate programming tool. All code for the server is now pure C. * Created packages for RPM based systems and Debian/Ubunty containing the MonetDB interface to the GNU Scientific Library (gsl). * We no longer install the .la files in our Fedora/Debian/Ubuntu packages. Client Package * ODBC: Implemented {fn scalar()} and {interval ...} escapes. python2 * Changed defaults for connecting (defaults to unix socket now). * Unix sockets partially working for control protocol. * Add support for unix socket. python3 * Changed defaults for connecting (defaults to unix socket now). * Unix sockets partially working for control protocol. * Add support for unix socket. R * The R connector is now distributed in the source code packages. MonetDB Common * The join code has been completely rewritten. It now optionally uses candidate lists, like the select code that was introduced in the previous release. * A new indexing structure for range selections on unsorted data has been added. * The vmtrim thread is no longer started by default on 64 bit architectures. The vmtrim thread monitors memory usage and drops BATs from memory when memory gets tight. However, in the age of large address spaces and virtual memory, the kernel does a good enough job. And in addition to dropping BATs, the thread also destroyed indexing structures which would have to be recreated the next time they were needed. * Cleaned up some of the parameters dealing with memory usage. * If available on the system, we now use atomic instructions for latching. * Removed some unused fields in the atomDesc structure. This change requires a complete recompilation of the whole suite. * Replaced the mutex implementation for both GNU C and Visual Studio with a home-grown implementation that uses atomic instructions (__sync_*() in gcc, _Interlocked*() in VS). SQL * Added support for quantiles (generalization of median). Usage: SELECT quantile(column_name,0.25) FROM table_name; The value should be in the range 0..1. Bug Fixes * 3040: Wrong NULL behavior in EXCEPT and INTERSECT * 3092: ODBC client doesn't support scalar function escape * 3198: SIGSEGV insert_string_bat (b=0x7fffe419d0a0, n=0x7fffc4006010, append=0) at gdk_batop.c:196 * 3210: Unexpected concurrency conflict when inserting to 2 tables simultaneously and querying one of them * 3273: Add support to Python DBAPI package for timetz, inet and url types * 3285: no such table 'queryHistory' * 3298: GDKmmap messages and monetdb start db takes very long * 3354: Introduce query time-out * 3371: (i)like generates batloop instead of algebra.likesubselect * 3372: Large group by queries never complete - server at 100% cpu(all cores) until MonetDB stopped * 3383: Bad performance with DISTINCT GROUP BY * 3391: Bad performance with GROUP BY and FK with out aggregate function * 3393: "COPY .. INTO ..." - escape of string quotes * 3399: server crashed on simple (malformed) query * 3401: inconsistent/strange handling of invalid dates (e.g. 2013-02-29) in where clause * 3403: NOT NULL constraint can't be applied after deleting rows with null values * 3404: Assertion `h->storage == STORE_MMAP' failed. * 3408: nested concat query crashed server * 3411: (disguised) BETWEEN clause not recognised. Hence no rangejoin. * 3412: Boolean expressions in WHERE clause, result in incorrect resulsts * 3417: Nested Common Table Expressions Crash * 3418: Segmentation fault on a query from table expression * 3419: Database does not start after upgrade * 3420: Database does not start after upgrade * 3423: Group by alias with distinct count doesn't work * 3425: Temporal extraction glitches * 3427: Consistent use of current_timestamp and now() * 3428: Aggregation over two columns is broken * 3429: SAMPLE on JOIN result crashes server * 3430: Wrong temporary handling * 3431: SQLGetInfo returns incorrect value for SQL_FN_NUM_TRUNCATE * 3432: MonetDB SQL syntax incompatible with SQL-92 <delimited identifier> syntax * 3435: INDEX prevents JOIN from discovering matches * 3436: COPY INTO from file containing leading Byte Order Mark (BOM) causes corruption

1 0

slow performance with 10 joins and above
by imad hajj chahine 20 Feb '14

20 Feb '14

Hi, I have the following query that do a pivot for the data stored in different tables, the time of the query is acceptable as long as the number of table to join is up to 10, 10 to 15 the query take more than 90 seconds to return, above 15 it does not return. Is there something i am missing, or i have to slice my query in smaller joins and the join back the results? PS: the max number of records in each table is < 200k select tval1.value as countall,tval2.value as ana1,tval3.value as ana2,tval4.value as count5,tval5.value as min5,tval6.value as max5,tval7.value as sum5,tval8.value as avg5,tval9.value as count6,tval10.value as min6,tval11.value as max6,tval12.value as sum6,tval13.value as avg6,tval14.value as count7,tval15.value as min7,tval16.value as max7,tval17.value as sum7,tval18.value as avg7 from "RPME".t_entity_cache tec join "RPME".t_value_int_cache tval1 on tec.id_schema=tval1.id_schema and tec.id=tval1.id_entity and tval1.id_attribute=1 join "RPME".t_value_date_cache tval2 on tec.id_schema=tval2.id_schema and tec.id=tval2.id_entity and tval2.id_attribute=2 join "RPME".t_value_string_cache tval3 on tec.id_schema=tval3.id_schema and tec.id=tval3.id_entity and tval3.id_attribute=3 join "RPME".t_value_int_cache tval4 on tec.id_schema=tval4.id_schema and tec.id=tval4.id_entity and tval4.id_attribute=4 join "RPME".t_value_numeric_cache tval5 on tec.id_schema=tval5.id_schema and tec.id=tval5.id_entity and tval5.id_attribute=5 join "RPME".t_value_numeric_cache tval6 on tec.id_schema=tval6.id_schema and tec.id=tval6.id_entity and tval6.id_attribute=6 join "RPME".t_value_numeric_cache tval7 on tec.id_schema=tval7.id_schema and tec.id=tval7.id_entity and tval7.id_attribute=7 join "RPME".t_value_numeric_cache tval8 on tec.id_schema=tval8.id_schema and tec.id=tval8.id_entity and tval8.id_attribute=8 join "RPME".t_value_int_cache tval9 on tec.id_schema=tval9.id_schema and tec.id=tval9.id_entity and tval9.id_attribute=9 join "RPME".t_value_numeric_cache tval10 on tec.id_schema=tval10.id_schema and tec.id=tval10.id_entity and tval10.id_attribute=10 join "RPME".t_value_numeric_cache tval11 on tec.id_schema=tval11.id_schema and tec.id=tval11.id_entity and tval11.id_attribute=11 join "RPME".t_value_numeric_cache tval12 on tec.id_schema=tval12.id_schema and tec.id=tval12.id_entity and tval12.id_attribute=12 join "RPME".t_value_numeric_cache tval13 on tec.id_schema=tval13.id_schema and tec.id=tval13.id_entity and tval13.id_attribute=13 join "RPME".t_value_int_cache tval14 on tec.id_schema=tval14.id_schema and tec.id=tval14.id_entity and tval14.id_attribute=14 join "RPME".t_value_numeric_cache tval15 on tec.id_schema=tval15.id_schema and tec.id=tval15.id_entity and tval15.id_attribute=15 join "RPME".t_value_numeric_cache tval16 on tec.id_schema=tval16.id_schema and tec.id=tval16.id_entity and tval16.id_attribute=16 join "RPME".t_value_numeric_cache tval17 on tec.id_schema=tval17.id_schema and tec.id=tval17.id_entity and tval17.id_attribute=17 join "RPME".t_value_numeric_cache tval18 on tec.id_schema=tval18.id_schema and tec.id=tval18.id_entity and tval18.id_attribute=18 where tec.id_schema=3

3 3

what is the character limit for monetdb queries?
by Anthony Damico 17 Feb '14

17 Feb '14

i'm definitely hitting one around 1.7 million characters, but i'm wondering how much lower the actual limit is? thanks!

3 4

mapi_get_last_id in JDBC
by imad hajj chahine 16 Feb '14

16 Feb '14

Hi, How to get the last inserted id with JDBC, i see its supported in mapi library but i am unable to get it with JDBC interface using st.getGeneratedKeys(). Thanks

2 1

Choices of operating system
by Radovan Bičiště 12 Feb '14

12 Feb '14

Hello, We have monetdb deployed for our customers and due to decent experience we would like to solidify and standardize our enviroment. So far we've been using UBUNTU 12.04. I would like to ask for recommendation over operating system with good mix performance/support period. Internally we use Oracle Linux, but lack of prebuild packages pushed us to consider different distribution. We are looking at these options ordered by our priority: 1. Fedora, I have doubts about long term support. Is this what is used for Monet development? 2. FreeBSD, AFAIK it has different memory management and should scale better. Thank you, Radovan

2 1

database crash
by Radovan Bičiště 11 Feb '14

11 Feb '14

Hello, I hope anyone can help with database crash. In .gdk_lock is following: USR=1001 PID=6751 TIME=Mon Feb 10 16:04:17 2014 @ LOGON USR=1001 PID=6751 TIME=Mon Feb 10 16:04:18 2014 @ !FATAL: logger_new: there is a logger catalog, but no log file. Are you sure you are using the correct combination of database (--dbpath) and log directory (--set sql_logdir)? If you have done a recent update of the server, it may be that your logs are in an old location. You should then either use --set sql_logdir=<path to old log directory> or move the old log directory to the new location (/u01/ceosdata/database/monetdb/drmax/crm/zkdev1/sql_logs/sql/). USR=1001 PID=6751 TIME=Mon Feb 10 16:04:18 2014 @ LOGOFF Anyway to start the database without logs ? Thanks, Radovan

3 6

Announcement: (Hopefully) Final Release Candidate for Jan2014 Release
by Sjoerd Mullender 10 Feb '14

10 Feb '14

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The MonetDB team at CWI/MonetDB BV is pleased to announce a new release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) A number of bugs in the previous release candidate have been fixed. We hope that this release candidate is good enough to be the new release, but we will give it a few more days. The release candidate can be found at http://dev.monetdb.org/downloads/testing. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQCVAwUBUviUtz7g04AjvIQpAQJy5wQArulRMgV7YWX1NoOJMoSSKINjEQLeneZC vj4NqMJ0igxYMRxqBLd7EcSfLKvgDR3ElmP7HhDkXbmCDk6POD4PjxPRmh66AEwY uYBhT3iIEe01PFzqjMhWm2HnDnfDOTzvn8Ai4n1/qCIVmW0p14kIYOkTRXWiNX5o tcGfy384cx8= =2un1 -----END PGP SIGNATURE-----

1 0

Some questions regarding MonetDB
by Alejandro Villamarin 07 Feb '14

07 Feb '14

Hi everyone, I've got some questions regarding MonetDB and scientifc data...more precisely, I'd like to know if MonetDB is suitable for ETL on datasets coming from NOAA in the following formats: GRIB2, NetCDF and HDF5, which all are multdimensional value grid files (to put it simple). I've already installed MonetDB and played with the tutorial, but so far I looks like a traditional RDBMS to me (yet)...any insights in this direction? Should I deep dive into the reference Manual? Another question would be...could I directly insert the data or do I have to do any parsing before? Does MonetDB provide analytical tools to do, for instance, correlation on that type of datasets? Thanks in advance, Alejandro

2 2

Fwd: Error when running Monet as cron jobs
by Nitin Gautam 07 Feb '14

07 Feb '14

Hi I am parsing my logs hourly as part of cron jobs and then uploading the data to Monet. However sometimes I do see some error messages in the logs *unsupported hash algorithms: gorithms: gorit* *monetdbd: internal error while starting mserver, please refer to the logs* Not sure what these error messages are, are they because of a bug or limitation of the Monet DB. Regards Nitin

4 9

mserver5 parameters from monetdbd
by Javier Picorel 06 Feb '14

06 Feb '14

Hello, I want to profile the data that is being read or created (e.g., tables, intermediate results) during the execution of a query. I would like to have more info than the tomograph or stethoscope show. I'm thinking about using the gdk_debug parameter (ALLOCMASK and HEAPMASK). However, I cannot specify the parameters of mserver5 when I run monetdbd. Running mserver5 manually doesn't seem to work. How can I specify the mserver5 parameter when starting the farm through monetdbd? Thanks! Regards, Javier

3 2

MonetDB Next Release Feature Wishlist
by Martin Kersten 04 Feb '14

04 Feb '14

LS, A major MonetDB feature release is pending, called Jan2014. To plan our (always scarce) resources, we would like to learn from the community in what areas the functionality or performance should be further improved. Please drop us an email on info(a)monetdb.org to share your thoughts. And, please, enlighten us on how you use the product, or modify/deploy/expand it to better suite your environment. regards, Martin Kersten

1 0

Re: problem with Jan2014 build
by Ying Zhang 29 Jan '14

29 Jan '14

Hai Bas, We hebben vandaag een probleem met substring gefixed die waarschijnlijk met jou probleem te maken heeft. Zou je misschien willen kijken of die fix iets voor je doet? We kunnen dat helaas zelf niet testen. Mvg, Jennie On Jan 27, 2014, at 15:02, Ying Zhang <ying.zhang(a)monetdb.com> wrote: > Hai Bas, > > Thanks again for the information. I was hoping that with some luck your problems might be gone in RC2 due to various fixes, unfortunately they are not. I'd like to look more deeply into the problems you have reported. Problems like not effective updates are rather show stoppers. > > Would you please provide me the ASC files with which the problems can be reproduced? You can send the files directly to me if necessary. Thanks! > > Regards, > > Jennie > > On Jan 27, 2014, at 13:05, Bas Kaptijn <b.kaptijn(a)chs.nl> wrote: > >> Tested with Jan 2014 RC2. All mentioned problems still occur with this release. >> >> Also, with the no_mitosis_pipe my program has problems with DELETE’s and possibly other queries >> not taking place effectively somehow. For me the investigation of the Jan2014 build stops here >> until some of the problems mentioned here are fixed >> >> Regards, Bas >> >> Van: Bas Kaptijn >> Verzonden: dinsdag 21 januari 2014 12:55 >> Aan: 'users-list(a)monetdb.org' >> Onderwerp: RE: problem with Jan2014 build >> >> Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. >> >> sql>set optimizer='no_mitosis_pipe'; >> operation successful (0.139ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> | file | line | source | >> +=============================================================================+========+====================================================================================================================================================+ >> | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 >> : >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> 1 tuple (1.1s) >> sql>set optimizer='default_pipe'; >> operation successful (0.305ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +------+------+--------+ >> | file | line | source | >> +======+======+========+ >> +------+------+--------+ >> 0 tuples (798.870ms) >> >> Regards, Bas >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >

1 0

Dynamic queries
by imad hajj chahine 28 Jan '14

28 Jan '14

Hi, What is the recommended way to work with dynamic queries, any optimization techniques, is there a way to avoid them using functions written in MAL.

1 0

Bug when using BAT UDFs with grouped aggregates and mitosis (related to 3396)
by Alastair McKinley 27 Jan '14

27 Jan '14

Dear all, I have been trying to integrate a BAT UDF to test the performance of MonetDB for a particular query. I am currently testing on Ubuntu 13.04 with MonetDB 11.15.19. My BAT UDF works in some cases and in others fails with the error message "b and g must be aligned" in gdk_aggr.c:89. Using gdb I can see that the BATcount() function returns different values for the length of b and g. For a minimal test case, I created a very simple BAT UDF that just multiples two doubles together in the input bats. I believe this is related to bug 3396 as passing --forcemito to mserver triggers the bug. If only the scalar version of the UDF is defined the query works correctly in all cases. Below I have included my test sql and the query plans of the working and non-working usages. I hope this enough to help track this down. Best regards, Alastair // SQL create table tab1 (group_by_col int, index_col int, f float); create table tab2 (index_col int, f float); insert into tab1 values (1,1,0.1),(1,2,0.2),(2,1,0.3),(2,2,0.4),(3,1,0.5),(3,2,0.6); insert into tab2 values (1,0.1),(2,0.2); select tab1.group_by_col,SUM(udf_multiply_dbl(tab1.f,tab2.f)) from tab2 inner join tab1 on tab1.index_col = tab2.index_col group by tab1.group_by_col; drop table tab1; drop table tab2; // Working MAL +-------------------------------------------------------------------------+ | mal | +=========================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_36 := nil:bat[:oid,:int]; | | X_57:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_86 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1"); | | X_6 := sql.bind(X_2,"sys","tab1","index_col",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","tab1","index_col",2); | | X_12 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_18 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_19,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_21 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_22 := sql.delta(X_18,X_19,r1_19,X_21); | | X_23 := algebra.leftfetchjoin(X_16,X_22); | | (X_24,r1_24) := algebra.join(X_15,X_23); | | X_85 := algebra.leftfetchjoin(X_24,X_3); | | X_26 := sql.bind(X_2,"sys","tab1","group_by_col",0); | | (X_28,r1_28) := sql.bind(X_2,"sys","tab1","group_by_col",2); | | X_30 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_31 := sql.delta(X_26,X_28,r1_28,X_30); | | X_32:bat[:oid,:int] := algebra.leftfetchjoin(X_85,X_31); | | (X_33,r1_34,r2_34) := group.subgroupdone(X_32); | | X_36 := algebra.leftfetchjoin(r1_34,X_32); | | X_37 := sql.bind(X_2,"sys","tab1","f",0); | | (X_42,r1_43) := sql.bind(X_2,"sys","tab1","f",2); | | X_45 := sql.bind(X_2,"sys","tab1","f",1); | | X_47 := sql.delta(X_37,X_42,r1_43,X_45); | | X_48:bat[:oid,:dbl] := algebra.leftfetchjoin(X_85,X_47); | | X_49 := sql.bind(X_2,"sys","tab2","f",0); | | (X_51,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_53 := sql.bind(X_2,"sys","tab2","f",1); | | X_54 := sql.delta(X_49,X_51,r1_53,X_53); | | X_55:bat[:oid,:dbl] := algebra.leftfetchjoinPath(r1_24,X_16,X_54); | | X_56:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_48,X_55); | | X_57:bat[:oid,:dbl] := aggr.subsum(X_56,X_33,r1_34,true,true); | | language.pass(X_3); | | language.pass(X_32); | | language.pass(X_85); | | language.pass(X_16); | | language.pass(r1_34); | | exit X_86; | | X_59 := sql.resultSet(2,1,X_36); | | sql.rsColumn(X_59,"sys.tab1","group_by_col","int",32,0,X_36); | | sql.rsColumn(X_59,"sys.L1","L1","double",53,0,X_57); | | X_71 := io.stdout(); | | sql.exportResult(X_71,X_59); | | end s2_1; | +-------------------------------------------------------------------------+ // Non working MAL with --forcemito +-------------------------------------------------------------------------------------------------------+ | mal | +=======================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_23 := nil:bat[:oid,:int]; | | X_41:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_196 := language.dataflow(); | | X_2 := sql.mvc(); | | X_70:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",0,4); | | X_78:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,0,4); | | (X_82:bat[:oid,:oid] ,X_83:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,0,4); | | X_119 := sql.delta(X_78,X_82,X_83); | | X_123 := algebra.leftfetchjoin(X_70,X_119); | | X_10:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_12 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_13,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_15 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_16 := sql.delta(X_12,X_13,r1_19,X_15); | | X_17 := algebra.leftfetchjoin(X_10,X_16); | | (X_127,X_128) := algebra.join(X_123,X_17); | | X_180 := algebra.leftfetchjoin(X_127,X_70); | | X_90:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,0,4); | | (X_94:bat[:oid,:oid] ,X_95:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,0,4); | | X_136 := sql.delta(X_90,X_94,X_95); | | X_143:bat[:oid,:int] := algebra.leftfetchjoin(X_180,X_136); | | (X_147,X_148,X_149) := group.subgroupdone(X_143); | | X_150 := algebra.leftfetchjoin(X_148,X_143); | | X_72:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",1,4); | | X_79:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,1,4); | | (X_84:bat[:oid,:oid] ,X_85:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,1,4); | | X_120 := sql.delta(X_79,X_84,X_85); | | X_124 := algebra.leftfetchjoin(X_72,X_120); | | (X_129,X_130) := algebra.join(X_124,X_17); | | X_181 := algebra.leftfetchjoin(X_129,X_72); | | X_91:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,1,4); | | (X_96:bat[:oid,:oid] ,X_97:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,1,4); | | X_138 := sql.delta(X_91,X_96,X_97); | | X_144:bat[:oid,:int] := algebra.leftfetchjoin(X_181,X_138); | | (X_151,X_152,X_153) := group.subgroupdone(X_144); | | X_154 := algebra.leftfetchjoin(X_152,X_144); | | X_74:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",2,4); | | X_80:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,2,4); | | (X_86:bat[:oid,:oid] ,X_87:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,2,4); | | X_121 := sql.delta(X_80,X_86,X_87); | | X_125 := algebra.leftfetchjoin(X_74,X_121); | | (X_131,X_132) := algebra.join(X_125,X_17); | | X_182 := algebra.leftfetchjoin(X_131,X_74); | | X_92:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,2,4); | | (X_98:bat[:oid,:oid] ,X_99:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,2,4); | | X_140 := sql.delta(X_92,X_98,X_99); | | X_145:bat[:oid,:int] := algebra.leftfetchjoin(X_182,X_140); | | (X_155,X_156,X_157) := group.subgroupdone(X_145); | | X_158 := algebra.leftfetchjoin(X_156,X_145); | | X_76:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",3,4); | | X_81:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,3,4); | | (X_88:bat[:oid,:oid] ,X_89:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,3,4); | | X_8 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_122 := sql.delta(X_81,X_88,X_89,X_8); | | X_126 := algebra.leftfetchjoin(X_76,X_122); | | (X_133,X_134) := algebra.join(X_126,X_17); | | X_183 := algebra.leftfetchjoin(X_133,X_76); | | X_93:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,3,4); | | (X_100:bat[:oid,:oid] ,X_101:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,3,4); | | X_19 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_142 := sql.delta(X_93,X_100,X_101,X_19); | | X_146:bat[:oid,:int] := algebra.leftfetchjoin(X_183,X_142); | | (X_159,X_160,X_161) := group.subgroupdone(X_146); | | X_162 := algebra.leftfetchjoin(X_160,X_146); | | X_184 := mat.packIncrement(X_150,4); | | X_185 := mat.packIncrement(X_184,X_154); | | X_186 := mat.packIncrement(X_185,X_158); | | X_20 := mat.packIncrement(X_186,X_162); | | (X_21,r1_34,X_163) := group.subgroupdone(X_20); | | X_23 := algebra.leftfetchjoin(r1_34,X_20); | | X_103:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,0,4); | | (X_111:bat[:oid,:oid] ,X_112:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,0,4); | | X_168 := sql.delta(X_103,X_111,X_112); | | X_172:bat[:oid,:dbl] := algebra.leftfetchjoin(X_180,X_168); | | X_105:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,1,4); | | (X_113:bat[:oid,:oid] ,X_114:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,1,4); | | X_169 := sql.delta(X_105,X_113,X_114); | | X_173:bat[:oid,:dbl] := algebra.leftfetchjoin(X_181,X_169); | | X_107:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,2,4); | | (X_115:bat[:oid,:oid] ,X_116:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,2,4); | | X_170 := sql.delta(X_107,X_115,X_116); | | X_174:bat[:oid,:dbl] := algebra.leftfetchjoin(X_182,X_170); | | X_109:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,3,4); | | (X_117:bat[:oid,:oid] ,X_118:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,3,4); | | X_29 := sql.bind(X_2,"sys","tab1","f",1); | | X_171 := sql.delta(X_109,X_117,X_118,X_29); | | X_175:bat[:oid,:dbl] := algebra.leftfetchjoin(X_183,X_171); | | X_188 := mat.packIncrement(X_172,4); | | X_189 := mat.packIncrement(X_188,X_173); | | X_190 := mat.packIncrement(X_189,X_174); | | X_31 := mat.packIncrement(X_190,X_175); | | X_32 := sql.bind(X_2,"sys","tab2","f",0); | | (X_34,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_36 := sql.bind(X_2,"sys","tab2","f",1); | | X_37 := sql.delta(X_32,X_34,r1_53,X_36); | | X_38 := algebra.leftfetchjoin(X_10,X_37); | | X_176 := algebra.leftfetchjoin(X_128,X_38); | | X_177 := algebra.leftfetchjoin(X_130,X_38); | | X_178 := algebra.leftfetchjoin(X_132,X_38); | | X_179 := algebra.leftfetchjoin(X_134,X_38); | | X_192 := mat.packIncrement(X_176,4); | | X_193 := mat.packIncrement(X_192,X_177); | | X_194 := mat.packIncrement(X_193,X_178); | | X_39 := mat.packIncrement(X_194,X_179); | | X_40:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_31,X_39); | | X_41:bat[:oid,:dbl] := aggr.subsum(X_40,X_21,r1_34,true,true); | | language.pass(X_70); | | language.pass(X_143); | | language.pass(X_72); | | language.pass(X_144); | | language.pass(X_74); | | language.pass(X_145); | | language.pass(X_17); | | language.pass(X_76); | | language.pass(X_146); | | language.pass(X_20); | | language.pass(X_180); | | language.pass(X_181); | | language.pass(X_182); | | language.pass(X_183); | | language.pass(X_10); | | language.pass(X_38); | | language.pass(r1_34); | | exit X_196; | | X_187:bat[:oid,:int] := nil:bat[:oid,:int]; | | X_191:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_195:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_43 := sql.resultSet(2,1,X_23); | | sql.rsColumn(X_43,"sys.tab1","group_by_col","int",32,0,X_23); | | sql.rsColumn(X_43,"sys.L1","L1","double",53,0,X_41); | | X_55 := io.stdout(); | | sql.exportResult(X_55,X_43); | | end s2_1; | +-------------------------------------------------------------------------------------------------------+

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 27 Jan '14

27 Jan '14

Tested with Jan 2014 RC2. All mentioned problems still occur with this release. Also, with the no_mitosis_pipe my program has problems with DELETE's and possibly other queries not taking place effectively somehow. For me the investigation of the Jan2014 build stops here until some of the problems mentioned here are fixed Regards, Bas Van: Bas Kaptijn Verzonden: dinsdag 21 januari 2014 12:55 Aan: 'users-list(a)monetdb.org' Onderwerp: RE: problem with Jan2014 build Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

2 1

Announcement: Second Release Candidate for Jan2014 Release
by Sjoerd Mullender 25 Jan '14

25 Jan '14

The MonetDB team at CWI/MonetDB BV is pleased to announce a second release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) A number of bugs in the previous release candidate have been fixed. Among others, packaging errors on Debian and Ubuntu have been fixed, and a problem with slow startup has been fixed. The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

1 0

Re: Contents of users-list digest What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 24 Jan '14

24 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M

1 0

Query speed on MonetDB with 4-5 users
by Charles Bajomo 23 Jan '14

23 Jan '14

Hello, We have just started using MonetDB in production and can I start by saying its a pretty impressive database. We are having a problem with query when we have 4 -5 users running reports and I wanted to know if anyone could recommend some optimisation tips. On particular one is multiplex-funnels. I can't find any documentation on how to create it. I read some where that it can be used to replicate data between two servers. It would be great to get some guidance on how to do this. Kind Regards

1 0

Possible mapi bug in Windows version 20131120?
by Alastair McKinley 23 Jan '14

23 Jan '14

Dear all, I am using MonetDB for 64 bit Windows from the package MonetDB5-SQL-Installer-x86_64-20131120.msi. If I compile the example mapi program from here http://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI and cause an error in a query (e.g. incorrect port number in mapi_connect params) then the application will crash with the following message: 0xC0000005: Access violation writing location 0x0000000000000024 This appears to happen if any of the following functions are called: mapi_explain(dbh, stderr); mapi_explain_query(hdl, stderr); mapi_explain_result(hdl, stderr); The same code works correctly on Ubuntu 13.04 with MonetDB-11.15.17?. Can anyone reproduce this error on Windows? Best regards and thanks, Alastair

1 0

mapi connect latency
by Matthew Russell 22 Jan '14

22 Jan '14

Hi all, I am writing a simple C++ dll which uses the mapi interface to connect to MonetDB. The query itself runs extremely fast which is fantastic. I did notice that the mapi_connect() takes approximately 1 second to connect on my machine on a local socket. Is there anyway to improve this connection latency? I can't unfortunately use a connection pool or keep the connection open due to the design of the calling application. Best regards, Matthew Russell

3 5

Re: users-list Digest, Vol 17, Issue 8
by Edgar Mejia 22 Jan '14

22 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M 2014/1/22 <users-list-request(a)monetdb.org> > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. RE: problem with Jan2014 build (Bas Kaptijn) > 2. Re: What differences between monetdb and mongodb for > analytical data? (Ying Zhang) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 21 Jan 2014 11:55:38 +0000 > From: Bas Kaptijn <b.kaptijn(a)chs.nl> > To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> > Subject: RE: problem with Jan2014 build > Message-ID: > < > a553ef932478438e82367f954b701a0f(a)AMSPR05MB211.eurprd05.prod.outlook.com> > > Content-Type: text/plain; charset="us-ascii" > > Some last info: the problem disappears when using the no_mitosis_pipe > optimizer pipeline. > > sql>set optimizer='no_mitosis_pipe'; > operation successful (0.139ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > | file > | line | source > > | > > +=============================================================================+========+====================================================================================================================================================+ > | > /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC > | 430664 | 04999999999999999999999999999999999999999 > > : > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 tuple (1.1s) > sql>set optimizer='default_pipe'; > operation successful (0.305ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > +------+------+--------+ > | file | line | source | > +======+======+========+ > +------+------+--------+ > 0 tuples (798.870ms) > > Regards, Bas >

1 0

What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 21 Jan '14

21 Jan '14

I had 6 months works as monetdb and this is great for analytical data, datawarehouses and cubes. Which is faster of these two databases in percentage for reading? I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? Thanks, Edgar M

2 1

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Ok got the Jan2014 package working now with the comments in #3419 as suggested. Thanks. I omitted the LOCKED statement and now the count of rows per value in the column "file" matches the amount of rows in the actual files that were loaded, so that seems to work. But it dows not solve the whole problem. I still get the initial reported problem with querying only the rows of which the first two characters in the value of the field 'source' matches the string '04'. When I select a count of rows it reports about 2.8M matching rows which it should. When selecting the actual rows however I get a lot less. sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 540299 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1655861 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 476629 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 886713 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1404888 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 1509934 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (94.898ms) sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (901.377ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+----------------------------------------------+ | file | line | source +=============================================================================+========+==============================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_7084_20130501_20130531_Schade_516.ASC | 268991 | 0499999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+----------------------------------------------+ 1 tuple (770.827ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (892.431ms) sql>CREATE TABLE test04 AS SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' WITH DATA; operation successful (21.4s) sql>SELECT COUNT(*) FROM test04; +--------+ | L1 | +========+ | 326372 | +--------+ Note: I didn't have this problem with the Feb2013-SP5 release. I never tested the Feb2013-SP6 release. Regards Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Additional info: A build of the latest sources of the Feb2013 branch shows the same problem. However when I omit the optional LOCKED statement in the COPY INTO queries the problem does not occur. It seems the LOCKED statement also does not improve the performance of loading data much anymore (in my case at least); I can omit it but the data loading is as fast as before, so I guess this works for me now, but still there seems to be a problem with this LOCKED data loading. Regards, Bas

1 0

problem with Jan2014 build
by Bas Kaptijn 20 Jan '14

20 Jan '14

Hi, With a build of the latest version in the Jan2014 branch I encounter a problem which worked fine in the Feb2013-SP5 release. I load multiple files into a single table with a COPY INTO ... LOCKED statement for each file. This seems to work correctly but When quering this table I get the following results (note I masked sensitive data with 9's but that does not matter): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1; +---------+ | L1 | +=========+ | 6474324 | +---------+ 1 tuple (0.662ms) sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 809294 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (13.204ms) sql>\q bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/*.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 476629 /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC 886713 /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC 1404888 /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC 1509934 /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC 6474324 total bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (1.1s) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (752.096ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+===========================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 268991 | 04999999999999999999999999999999999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ 1 tuple (832.778ms) This can't be right. I formerly compiled, build and used the Feb2013 from the mercurial site without problems. bkaptijn@DevRef1:~/DDDB$ mserver5 --version MonetDB 5 server v11.17.2 (64-bit, 64-bit oids) This is an unreleased version Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 15.6GiB available memory, 8 available cpu cores Libraries: libpcre: 8.12 2011-01-15 (compiled with 8.12) openssl: OpenSSL 1.0.1 14 Mar 2012 (compiled with OpenSSL 1.0.1 14 Mar 2012) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: bkaptijn@DevRef1 (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Is this a bug? Regards, Bas Kaptijn PS I also tried to install the Jan2014 RC1 but I can't get it working on my Ubuntu (precise) system. First it misses libstream6.so while a libstream3.so was installed... when creating a symlink referring to the libstream3 lib it fails to startup mentioning that it can not setup communication channel. The Merovingian log mentions a MALException that says that it can not write to the "querylog" (which I also can not find in the dbfarm where I though it should be) and a MALException something about sysmon.

2 2

Concurrent connections to MonetDB and temporary tables
by Alastair McKinley 15 Jan '14

15 Jan '14

Dear all, I want to connect to a single MonetDB server from an application using the mapi interface. The intention is to load a small input dataset using insert statements and mapi_query(). Then I will execute a SELECT statement from mapi against another dataset resident in the MonetDB server and return the results to the application. My question is about possible concurrent connections from this application. Is it sufficient to use unique table names for my input data sets so each input data set ends up in a separate table? Is there a better approach for this use case built in to MonetDB that I have missed? Best regards, Alastair

1 0

Announcement: Release Candidate for Jan2014 Release
by Sjoerd Mullender 15 Jan '14

15 Jan '14

The MonetDB team at CWI/MonetDB BV is pleased to announce a release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

1 0

MonetDB licensing
by Basil Peace 15 Jan '14

15 Jan '14

Currently MonetDB is licensed under `fork` of MPL 1.1. This is free software license, but it is not compatible with GPL ( http://www.gnu.org/licenses/license-list.html#MPL ). Are there some plans to change license, e.g. to upgrade to MPL 2.0? I'm asking since I'm planning big project (under GPLv3) just now, and I need to choose backend database. I like MonetDB, but GPL incompatibility stopped me. Best regards, Basil Peace

2 3

Stay in touch with me through LinkedIn
by Edgar Mejia silva 14 Jan '14

14 Jan '14

LinkedIn ------------ Communication: Me gustaría añadirte a mi red profesional en LinkedIn. -Edgar Edgar Mejia silva Ingeniero consultor de sistemas de gestion y BI (Business intelligent) at ITFusion Colombia Confirm that you know Edgar Mejia silva: https://www.linkedin.com/e/-id7iby-hqfkq7w7-3s/isd/18454372725/QZ9x6cIx/?hs… -- You are receiving Invitation to Connect emails. Click to unsubscribe: http://www.linkedin.com/e/-id7iby-hqfkq7w7-3s/d3mz0XtXuHhwR56ylf8k7AOIRLhmN… (c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.

1 0

Building a debug version of mserver5?
by Alastair McKinley 14 Jan '14

14 Jan '14

Dear all, I need to build a debug version of mserver5 in order to attach gdb to it to debug my bulk version of a UDF. How exactly do I go about this? It seems there is no debug target in the standard Makefile. Best regards, Alastair

2 1

Using msqldump while data is being inserted.
by Pedro Salgueiro 13 Jan '14

13 Jan '14

Hi, What is the expected behavior of msqldump if I try to a backup a database or table, and at the middle of the backup process new data is inserted? Would the dump process ignore the new data, or it would rollback and restart all over again? Thank you, Pedro Salgueiro

2 3

couldn't locate mserver5
by Hanumanth Maduri 10 Jan '14

10 Jan '14

Hello All, I have compiled the source of monetdb ~/Downloads/MonetDB-11.15.17$ monetdb -v MonetDB Database Server Toolkit v1.1 (Feb2013-SP5) All the following things were installed in /usr/local/bin malsample.pl mclient Mfilter.py MkillUsers monetdb Mtest.py sample0 sample2 sample4 smack01 sqlsample.pl tomograph Mapprove.py Mdiff Mfilter.pyc Mlog msqldump Mtimeout sample1 sample3 smack00 sqlsample.php stethoscope There is nothing like mserver5 nor monetdbd. Please let me know if there is any problem with my build. Thanks, -Hanu

3 3

Fwd: Bulk-loading in monetdb with primary key constraint
by Vasilis Efthymiou 23 Dec '13

23 Dec '13

I am trying to bulk-load a list of objects into a one-column (primary key) db. The only reason is to remove duplicates. I can't load the list in memory, because the file size is way greater than my memory size. I use monetdb's COPY-INTO<http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto>command, but I don't want it to fail when there is a duplicate. I want it to add everything that is not a duplicate and skip the duplicates. Is there any way to do that with monetdb? Any other way? Thank you in advance!

3 3

varchar split function
by imad hajj chahine 17 Dec '13

17 Dec '13

Hi, I am trying to implement a function that split a varchar and returns a table with the splitted values. this is what i have so far but i dont know how to complete the implementation. CREATE FUNCTION EV_SPLIT(stringtosplit varchar(255), chartospliton char) RETURNS TABLE (id int, val varchar(255), hashval BIGINT) BEGIN --if(EV_TABLEEXISTS('tmp_evsplitres') = 0) --then -- create local temporary table tmp_evsplitres (id int, val varchar(255), hashval BIGINT); --end if; declare idx int, tmpVal varchar(255); set idx = 0; --start transaction; while (position(chartospliton in str)>0) do set tmpVal = substring(str, 0, position(',' in str)-1); insert into tmp_evsplitres values(idx, tmpVal, hash(tmpVal)); set str = substring(str, position(',' in str), length(str)); set idx = idx + 1; end while; insert into tmp_evsplitres values(idx, str, hash(str)); RETURN TABLE ( select * from tmp_evsplitres ); --commit; END;

2 1

Inline function much faster than UDF in C?
by Alastair McKinley 13 Dec '13

13 Dec '13

Hi everyone, I have only been using MonetDB for a few weeks now and I have come across a very unexpected result when using User Defined Functions in C. I have a query which does essentially this: TABLE small_table ( int col1; float p; ); TABLE big_table ( int col1; int col2; float p; ); SELECT col1,1+SUM(MY_FUNCTION(big.p,small.p)) FROM small_table small inner join big_table big on big.col2 == small.col1 group by big.col1; My function is f(x,y) = 0.7213*(x*ln(x/x+y) + y*ln(y/x+y)) I wrote an optimised version of this function using sse intrinsics in C and compiled it into MonetDB. To my surprise, I discovered that writing this function inline in the query like so: 1+SUM(0.7213475*(big.p*LOG(big.p/(big.p+small.p))+small.p*LOG(small.p/(big.p+small.p)))) is a lot faster! The query takes approx 2 seconds with the C version and less than 0.5 seconds with the inline version in SQL! Can anyone explain this strange result? Best regards, Alastair

4 12

BUG 3259 should be re-opened
by Roberto Cornacchia 12 Dec '13

12 Dec '13

Hello, I cannot re-open bug 3259, but I believe t should be. After the bug had been fixed by Niels, it seems to have been re-introduced. The changeset below (after the fix) approves a MAL plan that in fact is the wrong one the original report refers to: the join has again been replaced by a CROSS-PRODUCT followed by inequality selections. Changeset d0b65a3e8eb6<http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6>, made by Stefan Manegold <Stefan.Manegold(a)cwi.nl> in the MonetDB repo, refers to this bug. For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6 Changeset description: between.Bug-3259: approved new MAL plan after changeset 79e66f677113 <http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=79e66f677113>

1 1

create a database
by Gaetano Mele 12 Dec '13

12 Dec '13

Hello can you tell me how do i create a new database with monetdb? Thanks

4 7

Dump database with user != monetdb
by Klaus Kruse 10 Dec '13

10 Dec '13

Hello, I posted my issues with dumping a database from MonetDB a while ago. The issue is still ongoing. It reduces to the fact, that no other user than the default monetdb-user has enough rights to dump a database. I played around with the various tables regarding users and schemas (sys.auths , sys.user_role ) but it's still failing on 'sys.db_user_info' : msqldump -u dump test password: -- msqldump dump database Tue Dec 10 17:19:43 2013 -- MonetDB v11.15.7 (Feb2013-SP2), 'mapi:monetdb://dev01.picalike.corpex-kunden.de:50000/test' START TRANSACTION; MAPI = (dump) /tmp/.s.monetdb.50000 QUERY = SELECT "name" FROM "sys"."auths" WHERE "name" NOT IN (SELECT "name" FROM "sys"."db_user_info") AND "grantor" <> 0 ORDER BY "name" ERROR = !SELECT: access denied for dump to table 'sys.db_user_info' So, my question to all subscribers: Does anybody managed to create another user with full rights to dump the database and when so, how? Any advice is greatly apprecitated. Best regards Klaus Kruse -- Corpex Internet GmbH * Schauenburgerstrasse 6 * D-20095 Hamburg Tel: +49 40 822268-0 * Fax: +49 40 822268-100 * http://www.corpex.de/ HRB 78752, Amtsgericht Hamburg, Sitz: Hamburg, USt.-Id: DE 213001412 Geschäftsführer: Daniel Hanelt

1 0

Moving monetdb data to another directory
by Rémi Charpentier 10 Dec '13

10 Dec '13

I am trying to change the default data directory of MonetDB. I installed monetdb using the ubuntu package, by default the data is stored in: /var/lib/monetdb I am running out of space, and I like to migrate the data to another folder. Does anyone know how to do that? I would welcome a solution that doesn't involve compiling monetdb from sources... Thanks in advance Remi

2 2

Listing database objects in schema
by Lee Hachadoorian 06 Dec '13

06 Dec '13

Is there a way to list tables in a particular schema or even in all schemas? \d and \dt seem to only return user tables in the sys schema. Also, what does \ds return? I have \dS system tables \dt user tables \dv views \ds ????? \df functions \dn schemas Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu/<http://freecity.commons.gc.cuny.edu/>

2 2

Bulk loading file with non-UTF8 characters
by Lee Hachadoorian 06 Dec '13

06 Dec '13

I need to bulk load some files with characters like ñ and é. I would do this using the LATIN1 encoding. I read that bulk loading from file assumes UTF8, but bulk loading from STDIN will use encoding of mclient. I would like to know: (a) How do I determine and change the client encoding? (b) What is an example for piping STDIN to mclient? Regarding the latter, I tried: cat /my/file.csv | mclient -d mydb -u monetdb -s "COPY INTO mytable FROM (STDIN) USING DELIMITERS ',', '\n' , '"' NULL AS '';" I wasn't sure how the embedded " would be handled, but the terminal basically became unresponsive until I ctl-C'ed out of it. Note the the file was similar in size to several others (without non-UTF8 characters) that loaded in seconds within mclient. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

3 4

Load file MonetDB Client
by Gaetano Mele 06 Dec '13

06 Dec '13

Hello i'm an italian student. I am loading a file .sql with MonetDB Client. But, during the file's loading, happens that the Client suddenly stops working. Why does this happen? Thanks

3 6

Strange behavior: dumping results from SELECT query (or inserting it into a table) does not contain the proper data until table is dumped/reloaded
by Bas Kaptijn 06 Dec '13

06 Dec '13

Hi, I can reproduce a MonetDB 2013 - SP5 database which gets in such a state the following first query does not return a result with a record containing '20130510199500000099' as value for the field "ida" whereas the second query (with only a little extra at the end) does while clearly this should never be the case. Moreover: the problem disappears when I dump, drop and reload the table analysis_04_1 ! testdpe99.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ); testdpe99-2.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ) AND A.identificatierecord = '20130510199500000099'; The table analysis_04_1 is the result of a SELECT query after which it is altered and updated quite some times adding extra fields etc. No errors seem to be reported in that process. Before reloading the table analysis_04_1 I get: bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.sql | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.sql | grep 20130510199500000099 bkaptijn@DevRef1:~/DDDB$ But after reloading the table (using in mclient: \> dumpfile, \D analysis_04_1, DROP TABLE analysis_04_1;, \< dumpfile): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ Is there a known issue which does play a role here or what can I do to investigate this further / give more information? Sincerely, Bas Kaptijn

1 0

Pathological behaviour when building large hash index
by Will Muldrew 06 Dec '13

06 Dec '13

Hi I've been investigating very slow queries on my 350M row dataset when selecting for equality on a non-unique string id. e.g. select count(*) from t_order where orderId = 'FOO'; This query takes ~4.5 hours to build a thash index. Subsequent repeats hit the cache and are sub milli. select count(*) from t_order where time > '1970-01-01' and orderId = 'FOO' This second one is consistently finished in a few seconds or less, and doesn't create any indexes. The first query is a bit of a show-stopper. It spends a huge about of time in BAThash (gdk_search.c), and incurs a lot of page faults while reading and writing at random across a multi GB mmaped thash file. Additionally these page faults are hampered by the OS trying to write dirty pages out in the background. I've got plenty of RAM overall (130GB), but the 'active' page proportion for the thash file seems to be stuck at about 25% - giving me a 0.75 probability of a miss. * Is there some way to make my OS less keen to evict pages? (swappiness = 0 already) I should have plenty of room to have the whole thash file resident. Additionally, allowing it to sit on dirty pages longer would reduce the total write IO. * Is there some way to simply prevent large thash creation? For many applications I'd rather have slower consistent queries, then incur a massively slow query following a restart (not great in a prod environment!) * Is there some way to generate the hash index with a more sympathetic algo that doesn't degrade so steeply. e.g some hash + sort. (I have literally no experience with this!) -Will Muldrew

2 12

Re: Pathological behaviour when building large hash index
by Roberto Cornacchia 06 Dec '13

06 Dec '13

Sorry for the quick intrusion, I just urged to say this is one of the most interesting threads of the year ;-) On Fri, Dec 6, 2013 at 10:06 AM, Roberto Cornacchia <roberto(a)spinque.com>wrote: > Sorry for the quick intrusion, I just urged to say this is one of the most > interesting threads of the year ;-) > > > > > > On Thu, Dec 5, 2013 at 8:57 PM, Will Muldrew <will.muldrew(a)gmail.com>wrote: > >> No index! >> On 5 Dec 2013 17:05, "Stefan Manegold" <Stefan.Manegold(a)cwi.nl> wrote: >> >>> hm, that's with the index in place? >>> >>> If so: >>> good for our optimizer, >>> but unfortunately not so good for you ... >>> >>> If not: >>> - good for you! >>> - impressive for our "execution engine" (scan) >>> (- "embarrassing" for our optimizer ... well, it's a minor detail ...) >>> >>> ;-) >>> >>> Stefan >>> >>> ----- Original Message ----- >>> > Sorry! >>> > >>> > sql>select count(*) from t_order2 where orderId between 'AAAAAA' and >>> > 'AAAAAA'; >>> > +------+ >>> > | L1 | >>> > +======+ >>> > | 0 | >>> > +------+ >>> > 1 tuple (596.202ms) >>> > >>> > >>> > >>> > On 5 December 2013 16:49, Stefan Manegold < Stefan.Manegold(a)cwi.nl > >>> wrote: >>> > >>> > >>> > Hi Will, >>> > >>> > thank for reminding me: the optimizer still sees your query as point >>> query >>> > and thus prevents parallelization in order to build/use the hash index. >>> > >>> > Maybe you can "fool" the optimizer (and I hope noone else is reading >>> what I'm >>> > writing now ... ;-)) by "disguising" the point predicate as range >>> predicate, >>> > say, "where orderId between 'FOO' and 'FOO'" --- in fact, I rather >>> hope (for >>> > us) >>> > that is will not work ... >>> > >>> > Stefan >>> > >>> > ----- Original Message ----- >>> > > Hi Stefan >>> > > >>> > > I've added that hash = FALSE patch, and now my simple query returns >>> > > reliably >>> > > in 6 seconds. It's still a lot slower than the indexed one >>> (obviously), and >>> > > 12x slower than the one with the time > '1970-01-01' optimiser nudge >>> (which >>> > > causes parallelism), but it'll do for now. >>> > > >>> > > Thanks >>> > > >>> > > -Will >>> > > >>> > > >>> > > On 5 December 2013 16:32, Will Muldrew < will.muldrew(a)gmail.com > >>> wrote: >>> > > >>> > > >>> > > >>> > > Hi Stefan >>> > > >>> > > Here are my version details: >>> > > >>> > > MonetDB-11.15.17 >>> > > CentOS release 6.3 >>> > > kernel 3.6.2 >>> > > >>> > > I think I might proceed with a patched version with the hash >>> disabled. >>> > > That's >>> > > totally fine for me right now, though obviously it'd be nice to be >>> on the >>> > > trunk! >>> > > >>> > > W.r.t. a less random access hash generation. This is all very >>> hand-wavey >>> > > but >>> > > how about: 1) generate a sequence of (hash,oid) pairs to some file or >>> > > mmapped area. 2). use some well-known "external sort" algo to sort >>> these >>> > > pairs by hash (e.g. split, qsort, merge). 3). build your final hash >>> by >>> > > iterating through this sorted datastructure. >>> > > >>> > > Your memory writing would then be sequential and you'd get good page >>> hit >>> > > characteristics. You just need to choose a decent sort algo - >>> ideally which >>> > > works transparently over memmapped data. >>> > > >>> > > What do you think? I might even have a crack at it myself :) >>> > > >>> > > -Will >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > On 5 December 2013 15:54, Stefan Manegold < Stefan.Manegold(a)cwi.nl> wrote: >>> > > >>> > > >>> > > Hi Will, >>> > > >>> > > could you please also share with us which version of MonetDB you are >>> using, >>> > > and OS you are running? >>> > > >>> > > Pleass find more comments & questions inlined below. >>> > > >>> > > ----- Original Message ----- >>> > > > Hi >>> > > > >>> > > > I've been investigating very slow queries on my 350M row dataset >>> when >>> > > > selecting for equality on a non-unique string id. e.g. >>> > > > >>> > > > select count(*) from t_order where orderId = 'FOO'; >>> > > >>> > > Is your orderId column unique, or are there duplicate values? >>> > > >>> > > > This query takes ~4.5 hours to build a thash index. Subsequent >>> repeats >>> > > > hit >>> > > > the cache and are sub milli. >>> > > > >>> > > > select count(*) from t_order where time > '1970-01-01' and orderId >>> = >>> > > > 'FOO' >>> > > > >>> > > > This second one is consistently finished in a few seconds or less, >>> and >>> > > > doesn't create any indexes. >>> > > > >>> > > > The first query is a bit of a show-stopper. It spends a huge about >>> of >>> > > > time >>> > > > in >>> > > > BAThash (gdk_search.c), and incurs a lot of page faults while >>> reading and >>> > > > writing at random across a multi GB mmaped thash file. >>> Additionally these >>> > > > page faults are hampered by the OS trying to write dirty pages out >>> in the >>> > > > background. I've got plenty of RAM overall (130GB), but the >>> 'active' page >>> > > > proportion for the thash file seems to be stuck at about 25% - >>> giving me >>> > > > a >>> > > > 0.75 probability of a miss. >>> > > > >>> > > > * Is there some way to make my OS less keen to evict pages? >>> (swappiness = >>> > > > 0 >>> > > > already) I should have plenty of room to have the whole thash file >>> > > > resident. >>> > > > Additionally, allowing it to sit on dirty pages longer would >>> reduce the >>> > > > total write IO. >>> > > >>> > > In fact, if there is sufficient memory in total, there would be no >>> need to >>> > > flush >>> > > dirty (non-persistent) pages at all, but we have not yet found a >>> good way >>> > > to >>> > > hit >>> > > this to the OS --- madvice() did not have much/any useful efficet >>> for us on >>> > > Linux. >>> > > >>> > > Maybe some of the issues dicsussed here play a role: >>> > > >>> http://engineering.linkedin.com/performance/optimizing-linux-memory-managem… >>> > > ? >>> > > >>> > > While we have not yet encountered the NUMA problem reported there, >>> > > we have encountered the transparent huge page problems, and usually >>> > > disable them as also suggested in the above posting. >>> > > >>> > > > * Is there some way to simply prevent large thash creation? For >>> many >>> > > > applications I'd rather have slower consistent queries, then incur >>> a >>> > > > massively slow query following a restart (not great in a prod >>> > > > environment!) >>> > > >>> > > Not easily right now. >>> > > However, you can either add the "fake" time predicate as you did >>> above, >>> > > which (in this case) triggers MonetDB's optimizer to push the time >>> > > predicate >>> > > (either because it's in timestamp (int) rather then varchar >>> (string), or >>> > > because the time column happens to be ordered) below the orderId >>> predicate >>> > > and this avoid the hash build, >>> > > or change the following line(s) in gdk/gdk_select.c (in case you >>> built >>> > > MonetDB yourself from source): >>> > > >>> > > hash = b->batPersistence == PERSISTENT && >>> > > (size_t) ATOMsize(b->ttype) > sizeof(BUN) / 4 && >>> > > BATcount(b) * (ATOMsize(b->ttype) + 2 * sizeof(BUN)) < >>> GDK_mem_maxsize / 2; >>> > > -> >>> > > hash = FALSE; >>> > > >>> > > We'll consider whether we can provide a better solution in a future >>> > > release. >>> > > >>> > > > * Is there some way to generate the hash index with a more >>> sympathetic >>> > > > algo >>> > > > that doesn't degrade so steeply. e.g some hash + sort. (I have >>> literally >>> > > > no >>> > > > experience with this!) >>> > > >>> > > We'll also look into this. However, building a hash index inherently >>> incurs >>> > > random access, so chances are slim ... >>> > > >>> > > Best, >>> > > Stefan >>> > > >>> > > > -Will Muldrew >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > _______________________________________________ >>> > > > users-list mailing list >>> > > > users-list(a)monetdb.org >>> > > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > > >>> > > >>> > > -- >>> > > | 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) | >>> > > >>> > > _______________________________________________ >>> > > users-list mailing list >>> > > users-list(a)monetdb.org >>> > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > >>> > > >>> > > >>> > > _______________________________________________ >>> > > users-list mailing list >>> > > users-list(a)monetdb.org >>> > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > >>> > >>> > -- >>> > | 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) | >>> > >>> > _______________________________________________ >>> > users-list mailing list >>> > users-list(a)monetdb.org >>> > https://www.monetdb.org/mailman/listinfo/users-list >>> > >>> > >>> > _______________________________________________ >>> > users-list mailing list >>> > users-list(a)monetdb.org >>> > https://www.monetdb.org/mailman/listinfo/users-list >>> > >>> >>> -- >>> | 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) | >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >

1 0

Mantente en contacto conmigo a través de LinkedIn
by Edgar Mejia silva 03 Dec '13

03 Dec '13

LinkedIn ------------ Communication: Me gustaría añadirte a mi red profesional en LinkedIn. -Edgar Edgar Mejia silva Ingeniero consultor de sistemas de gestion y BI (Business intelligent) en ITFusion Colombia Confirma que conoces a Edgar Mejia silva: https://www.linkedin.com/e/-id7iby-hor6db4w-59/isd/18454372725/QZ9x6cIx/?hs… -- Estás recibiendo invitaciones a conectar. Haz clic para darte de baja: http://www.linkedin.com/e/-id7iby-hor6db4w-59/d3mz0XtXuHhwR56ylf8k7AOIRLhmN… (c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, EE.UU.

1 0

Announcement: New Feb2013-SP6 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Dec '13

03 Dec '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP6 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-SP6 bugfix release MonetDB5 Server * Fixed a possible buffer overflow in the COPY INTO code. * Fixed a problem that when the server is using all available threads for a query, it was not possible to attach another client and have it execute even the smallest query. This is fixed by creating extra threads for each client entering the fray at the cost of having more threads that execute queries. But at least there is guaranteed progress for all clients (modulo the operating system scheduler). See bug #3258. Merovingian * monetdbd(1) now refuses to startup if it cannot read the properties from the dbfarm, bug #3398 Client Package * mclient: Fixed a bug where the -H option only worked if the readline history file already existed. Now we properly create and use the history file. * ODBC: Fixed interpretation SQL_C_SLONG/SQL_C_ULONG/SQL_C_LONG to refer to a 32 bit integer always (i.e. "int" on 64 bit architectures despite the name and the Microsoft documentation). This seems to be the consensus. * ODBC: Fixed transaction level: MonetDB only supports the highest level (SQL_TXN_SERIALIZABLE), so setting the transaction level can be accepted and ignored. MonetDB Common * Stopped using the deprecated sbrk() system call. * Fixed a problem when reverse sorting a sorted column. * Fixed bugs that deal with problems that could possibly occur when transactions are aborted and the server is restarted. See bug #3243. * A bug was fixed in the handling of grouped aggregates when all values in a group are null. See bug #3388. SQL * Fixed a bug where the server at some point stopped saving compiled queries in the SQL query cache. Bug Fixes * 3243: Segmentation fault (possible data corruption) after clean shutdown * 3258: Scheduling issues * 3368: BAT sortedness info ignored on ORDER BY and TOPN * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3382: Response to PREPARE emtpy if query contains a LIMIT * 3385: Simple query fails with 'identifier not found' * 3387: mclient does not properly double quote schema names when using autofill tab * 3388: case statement in "order by" clause doesn't work when used together with "group by" * 3389: median function with "group by" - SIGSEGV * 3392: ODBC datatype conversion for INTEGER not working properly * 3394: "Cannot find column type" error in temporary tables in functions * 3395: error occurred during a query: "'CASE WHEN" sentence * 3396: Improper UDF expansion * 3397: Error in ODBC-Driver when using Prepared Statements * 3398: Cannot stop monetdbd after erroneously starting an uninitialized dbfarm

1 0

I can integrate monetdb as hadoop?
by Edgar Mejia 02 Dec '13

02 Dec '13

if for example i have 300 millions of rows, i need to use something as hadoop? is posible integrate monetdb as hadoop? if i use only monetdb, and i have 300 millions of rows, how much memory and processor is recommended as minimum? Thanks, EM

2 2

how i do one date diff of on month?
by Edgar Mejia 02 Dec '13

02 Dec '13

i need to know how to subtract one month the current date. For example in postgres i can to do it so: extract('year' from current_date - interval '1 month') . How i can to do this as monetdb? Thanks

2 2

Very different query speeds for two similar queries
by Will Muldrew 02 Dec '13

02 Dec '13

Hi I've been trying out monetdb on a 350M row table (modern linux workstation with 24GB RAM + SSD). This table has a sorted TIMESTAMP column and a second unsorted VARCHAR(6) column which is non-unique (perhaps 50M distinct values fairly randomly distributed). This query is very slow, spends huge amount of time writing, and has very low CPU (intermediate results?): select count(*) from t_order where orderId = 'XXXXXX' This takes hours to complete. However, this query is a lot faster, even though the timestamp clause is redundant (all my data is > '2012-01-01'): select count(*) from t_order where orderId = 'XXXXXX' and time > '1970-01-01'; The first run is slowish (10s of seconds - I guess because we're reading the orderId column fully), however subsequent runs are sub-second. Obviously I could write an app which puts the spurious timestamp clause into the where, but it's still confusing and not ideal. Is there something I can do? The EXPLAINs are: sql>explain select count(*) from t_order where orderId = 'XXXXXX' and time > '1970-01-01'; +-------------------------------------------------------------------------------------------------------------------------+ | mal | +=========================================================================================================================+ | function user.s9_3{autoCommit=true}(A0:str,A1:str):void; | | X_14 := nil:wrd; | | barrier X_263 := language.dataflow(); | | X_4 := sql.mvc(); | | X_68:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,0,16); | | X_132 := X_68; | | X_101:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,0,16); | | X_164 := X_101; | | X_35:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",0,16); | | X_10 := calc.timestamp(A1,7); | | X_180 := algebra.thetasubselect(X_164,X_35,X_10,">"); | | X_197 := algebra.subselect(X_132,X_180,A0,A0,true,true,false); | | X_228 := algebra.leftfetchjoin(X_197,X_164); | | X_246 := aggr.count(X_228); | | X_70:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,1,16); | | X_133 := X_70; | | X_103:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,1,16); | | X_165 := X_103; | | X_37:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",1,16); | | X_181 := algebra.thetasubselect(X_165,X_37,X_10,">"); | | X_199 := algebra.subselect(X_133,X_181,A0,A0,true,true,false); | | X_229 := algebra.leftfetchjoin(X_199,X_165); | | X_247 := aggr.count(X_229); | | X_72:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,2,16); | | X_134 := X_72; | | X_105:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,2,16); | | X_166 := X_105; | | X_39:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",2,16); | | X_182 := algebra.thetasubselect(X_166,X_39,X_10,">"); | | X_201 := algebra.subselect(X_134,X_182,A0,A0,true,true,false); | | X_230 := algebra.leftfetchjoin(X_201,X_166); | | X_248 := aggr.count(X_230); | | X_74:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,3,16); | | X_135 := X_74; | | X_107:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,3,16); | | X_167 := X_107; | | X_41:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",3,16); | | X_183 := algebra.thetasubselect(X_167,X_41,X_10,">"); | | X_203 := algebra.subselect(X_135,X_183,A0,A0,true,true,false); | | X_231 := algebra.leftfetchjoin(X_203,X_167); | | X_249 := aggr.count(X_231); | | X_76:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,4,16); | | X_136 := X_76; | | X_109:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,4,16); | | X_168 := X_109; | | X_43:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",4,16); | | X_184 := algebra.thetasubselect(X_168,X_43,X_10,">"); | | X_205 := algebra.subselect(X_136,X_184,A0,A0,true,true,false); | | X_232 := algebra.leftfetchjoin(X_205,X_168); | | X_250 := aggr.count(X_232); | | X_78:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,5,16); | | X_137 := X_78; | | X_111:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,5,16); | | X_169 := X_111; | | X_45:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",5,16); | | X_185 := algebra.thetasubselect(X_169,X_45,X_10,">"); | | X_207 := algebra.subselect(X_137,X_185,A0,A0,true,true,false); | | X_233 := algebra.leftfetchjoin(X_207,X_169); | | X_251 := aggr.count(X_233); | | X_80:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,6,16); | | X_138 := X_80; | | X_113:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,6,16); | | X_170 := X_113; | | X_47:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",6,16); | | X_186 := algebra.thetasubselect(X_170,X_47,X_10,">"); | | X_209 := algebra.subselect(X_138,X_186,A0,A0,true,true,false); | | X_234 := algebra.leftfetchjoin(X_209,X_170); | | X_252 := aggr.count(X_234); | | X_82:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,7,16); | | X_139 := X_82; | | X_115:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,7,16); | | X_171 := X_115; | | X_49:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",7,16); | | X_187 := algebra.thetasubselect(X_171,X_49,X_10,">"); | | X_211 := algebra.subselect(X_139,X_187,A0,A0,true,true,false); | | X_235 := algebra.leftfetchjoin(X_211,X_171); | | X_253 := aggr.count(X_235); | | X_84:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,8,16); | | X_140 := X_84; | | X_117:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,8,16); | | X_172 := X_117; | | X_51:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",8,16); | | X_188 := algebra.thetasubselect(X_172,X_51,X_10,">"); | | X_213 := algebra.subselect(X_140,X_188,A0,A0,true,true,false); | | X_236 := algebra.leftfetchjoin(X_213,X_172); | | X_254 := aggr.count(X_236); | | X_86:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,9,16); | | X_141 := X_86; | | X_119:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,9,16); | | X_173 := X_119; | | X_53:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",9,16); | | X_189 := algebra.thetasubselect(X_173,X_53,X_10,">"); | | X_215 := algebra.subselect(X_141,X_189,A0,A0,true,true,false); | | X_237 := algebra.leftfetchjoin(X_215,X_173); | | X_255 := aggr.count(X_237); | | X_88:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,10,16); | | X_142 := X_88; | | X_121:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,10,16); | | X_174 := X_121; | | X_55:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",10,16); | | X_190 := algebra.thetasubselect(X_174,X_55,X_10,">"); | | X_217 := algebra.subselect(X_142,X_190,A0,A0,true,true,false); | | X_238 := algebra.leftfetchjoin(X_217,X_174); | | X_256 := aggr.count(X_238); | | X_90:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,11,16); | | X_143 := X_90; | | X_123:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,11,16); | | X_175 := X_123; | | X_57:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",11,16); | | X_191 := algebra.thetasubselect(X_175,X_57,X_10,">"); | | X_219 := algebra.subselect(X_143,X_191,A0,A0,true,true,false); | | X_239 := algebra.leftfetchjoin(X_219,X_175); | | X_257 := aggr.count(X_239); | | X_92:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,12,16); | | X_144 := X_92; | | X_125:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,12,16); | | X_176 := X_125; | | X_59:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",12,16); | | X_192 := algebra.thetasubselect(X_176,X_59,X_10,">"); | | X_221 := algebra.subselect(X_144,X_192,A0,A0,true,true,false); | | X_240 := algebra.leftfetchjoin(X_221,X_176); | | X_258 := aggr.count(X_240); | | X_94:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,13,16); | | X_145 := X_94; | | X_127:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,13,16); | | X_177 := X_127; | | X_61:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",13,16); | | X_193 := algebra.thetasubselect(X_177,X_61,X_10,">"); | | X_223 := algebra.subselect(X_145,X_193,A0,A0,true,true,false); | | X_241 := algebra.leftfetchjoin(X_223,X_177); | | X_259 := aggr.count(X_241); | | X_96:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,14,16); | | X_146 := X_96; | | X_129:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,14,16); | | X_178 := X_129; | | X_63:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",14,16); | | X_194 := algebra.thetasubselect(X_178,X_63,X_10,">"); | | X_225 := algebra.subselect(X_146,X_194,A0,A0,true,true,false); | | X_242 := algebra.leftfetchjoin(X_225,X_178); | | X_260 := aggr.count(X_242); | | X_98:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,15,16); | | X_147 := X_98; | | X_131:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,15,16); | | X_179 := X_131; | | X_65:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",15,16); | | X_195 := algebra.thetasubselect(X_179,X_65,X_10,">"); | | X_227 := algebra.subselect(X_147,X_195,A0,A0,true,true,false); | | X_243 := algebra.leftfetchjoin(X_227,X_179); | | X_261 := aggr.count(X_243); | | X_245 := mat.pack(X_246,X_247,X_248,X_249,X_250,X_251,X_252,X_253,X_254,X_255,X_256,X_257,X_258,X_259,X_260,X_261); | | X_262 := algebra.selectNotNil(X_245); | | X_14 := aggr.sum(X_262); | | language.pass(X_164); | | language.pass(X_165); | | language.pass(X_166); | | language.pass(X_167); | | language.pass(X_168); | | language.pass(X_169); | | language.pass(X_170); | | language.pass(X_171); | | language.pass(X_172); | | language.pass(X_173); | | language.pass(X_174); | | language.pass(X_175); | | language.pass(X_176); | | language.pass(X_177); | | language.pass(X_178); | | language.pass(X_179); | | exit X_263; | | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,""); | | end s9_3; | +-------------------------------------------------------------------------------------------------------------------------+ 171 tuples (63.183ms) sql>explain select count(*) from t_order where orderId = 'XXXXXX'; +----------------------------------------------------------------+ | mal | +================================================================+ | function user.s10_3{autoCommit=true}(A0:str):void; | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","t_order","orderid",0); | | X_10 := X_7; | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","t_order"); | | X_11 := algebra.subselect(X_10,X_4,A0,A0,true,true,false); | | X_13 := algebra.leftfetchjoin(X_11,X_10); | | X_14 := aggr.count(X_13); | | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,""); | | end s10_3; | | # optimizer.mitosis() | | # optimizer.dataflow() | +----------------------------------------------------------------+ 12 tuples (0.838ms) sql>select * from storage() where "table" = 't_order'; +--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+=========+===================+===========+==========+===========+===========+============+============+=========+========+ | sys | t_order | time | timestamp | 11/1146 | 349989136 | 8 | 2799913088 | 0 | 0 | true | ... | sys | t_order | orderid | varchar | 10/1043 | 349989136 | 6 | 2799913088 | 8240889856 | 0 | false | ... | sys | t_order | idx_orderid | oid | 04/456 | 349989136 | 8 | 2799913088 | 0 | 0 | true | +--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+ (some fields edited out...) So the fast one has a much more complicated MAL plan. (Other than this, and a date parsing prob which I've reported, I'm really excited about this software!) -Will Muldrew

3 6

Re: copy into failed to import table
by Colin Foss 30 Nov '13

30 Nov '13

I am using the latest version. MonetDB 5 server v11.15.17 "Feb2013-SP5". The server and client are running under my account. The files and folders were also created with my account. Here is a simpler version which also encounters the "failed to import table" message. CREATE TABLE staging_simple_results( lookup varchar(20) NULL, extra_symbol varchar(20) NULL, measure1 tinyint NULL, base_temp float NULL ) ; Here are the contents of the same input, in a file called D:\junk\simple_import.csv. The file is also attached. one,two,3,4.023456787 green,brown,2,8.3456837 This is the statement I issued for the bulk load. copy into staging_simple_results from ('D:\junk\simple_import.csv') delimiters ',', '\\r\\n' ; On 2013-11-27 09:03, Colin Foss wrote: > > I created a table with 43 columns. The table contains char, > varchar, and tinyint datatypes. I am running on Windows Server > 2008 R2 64bit. The file contains two rows and is comma separated. > The file is BCP output from MSSQL. When I tried to bulk load into > the table from a file with a COPY INTO command I got an unhelpful > message. > > "failed to import table" > > How can I find out why the import failed? Which version of MonetDB are you using? I seem to remember fixing a problem where the actual failure message wasn't propagated to the level where the error was reported.

3 5

"temp" table renamed to "temporary"
by Lee Hachadoorian 28 Nov '13

28 Nov '13

I created a nontemporary table, but named it "temp" (because, after messing around with it, I was going to drop it). The table name on create was changed to "temporary". But the name "temp" still works in some contexts (apparently all SQL) but not others (\d). See session history below. Is this by design? --Lee sql>CREATE TABLE geo_boundaries.temp (id int PRIMARY KEY, name text); operation successful (55.041ms) sql>SELECT * FROM geo_boundaries.temp; +----+------+ | id | name | +====+======+ +----+------+ 0 tuples (1.818ms) sql>INSERT INTO geo_boundaries.temp VALUES (1, 'name'); 1 affected row (16.073ms) sql>SELECT * FROM geo_boundaries.temp; +------+------+ | id | name | +======+======+ | 1 | name | +------+------+ 1 tuple (0.977ms) sql>\d geo_boundaries.temp table geo_boundaries.temp does not exist sql>\d TABLE geo_boundaries.states TABLE geo_boundaries.temporary sql>\d geo_boundaries.temporary CREATE TABLE "geo_boundaries"."temporary" ( "id" INTEGER NOT NULL, "name" CHARACTER LARGE OBJECT, CONSTRAINT "temporary_id_pkey" PRIMARY KEY ("id") ); sql>DROP TABLE geo_boundaries.temp; operation successful (21.538ms) sql>\d TABLE geo_boundaries.states sql> -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

1 0

Table very slow
by Edgar Mejia 27 Nov '13

27 Nov '13

HI, i have one table as 15 millions of rows of all 2013. I all days delete the las 2 months and return to reload them again. Today is table is very slow, one select count takes more that 2 minutes, when it would normally to a few seconds. I migrate this information to a new table and this table if works fine and is very fast. What i need to do for to fix this and how i to fix this table slow? Thansk Edgar M

3 2

copy into failed to import table
by Colin Foss 27 Nov '13

27 Nov '13

I created a table with 43 columns. The table contains char, varchar, and tinyint datatypes. I am running on Windows Server 2008 R2 64bit. The file contains two rows and is comma separated. The file is BCP output from MSSQL. When I tried to bulk load into the table from a file with a COPY INTO command I got an unhelpful message. "failed to import table" How can I find out why the import failed?

2 1

Sum of nulls is 0
by Pedro Salgueiro 26 Nov '13

26 Nov '13

Hi, A few weeks ago we encountered an issue with monetdb when summing nulls, grouped by some column. We managed to create a simple test case which reproduces this scenario: create table test (number int, name varchar(50)); > > insert into test values(1,'jonh'); > insert into test values(2,'mike'); > insert into test values(3,'mary'); > insert into test values(null,'austin'); > insert into test values(null,'jonh'); > insert into test values(null,'mary'); > > select name, sum(number) > from test > group by name; The result of this query is: > austin | 0 > jonh | 1 > mary | 3 > mike | 2 But the expected result is: > austin | null > jonh | 1 > mary | 3 > mike | 2 The version that we are using is Feb2013-SP5. We found a bug report that could be related to this one, but we are not sure: http://bugs.monetdb.org/show_bug.cgi?id=3388 Should we open a new issue for this one? Thank you, Pedro Salgueiro

3 7

MonetDB with OpenCL externsion (Ocelot) query fails trying to cache an empty BAT
by Edward Ching 25 Nov '13

25 Nov '13

I'm running into a problme with Ocelot, the MonetDB with OpenCL extension. I apologize if this is not the right forum to post. I'm seeing a query failure which was caused by MoneDB-OpenCL trying to cache an empty BAT and ended up calling clCreateBuffer with a 0 size buffer and failed. Any select with a where clause would trigger it. Has anyone seen this too? It fails whether I use 32 bit or 64 bit oid, Nvidia or AMD. I'm trying to figure if it's a real issue or if I'm missing anything. Any input greatly appreciated. The database was generated by TPC-H dbgen (details below). The query I used was "select c_custkey, c_name from customer where c_custkey < 2", and here's a trace of the sequence of calls leading to the failure starting from the MAL statement: 1) X_52 := ocelot.thetasubselect(r1_10,A0,"<",0);(see log below) 2) ocl_select.c::ocl_subselect() line 142: ocl_cacheBAT 3) ocl_memmanager.c::ocl_cacheBAT() line 771: allocateBuffer 4) ocl_memmanager.c::allocateBuffer() line 432: clCreateBuffer ===== Source version and environment ===== 1) Ocelot src installed from zip downloaded from monetdb-opencl: msaecker-monetdb-opencl-b513b35eb84a.zip 2) Ocelot configure options: ./configure --prefix=/usr/local --enable-oid32 --enable-optimize --disable-test ing --disable-developer --disable-debug --disable-assert --enable-sql --enable-m onetdb5 --with-opencl=/usr 3) Operating System: Fedora 17 kernel 3.9.10-100 4) Hardware: NVIDIA GTX570 on Intel i7-3820 w/ 16GB mem 5) OpenCL: Nvidia OpenCL that came with CUDA 5.5 SDK 6) Database: generated w/ TPC-H dbgen using size factor s=0.01 ===== operation log follows ===== [root@gen3-2 ocelot]# mclient -ddb001 -lmal mal>ocelot.init(); mal>ocelot.listDevices(); # Device 0: NVIDIA Corporation - GeForce GTX 570 (default device) mal>\q [root@gen3-2 ocelot]# mclient -ddb001 -lsql 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 sql>\d customer CREATE TABLE "sys"."customer" ( "c_custkey" INTEGER NOT NULL, "c_name" VARCHAR(25) NOT NULL, "c_address" VARCHAR(40) NOT NULL, "c_nationkey" INTEGER NOT NULL, "c_phone" VARCHAR(15) NOT NULL, "c_acctbal" DECIMAL(15,2) NOT NULL, "c_mktsegment" VARCHAR(10) NOT NULL, "c_comment" VARCHAR(117) NOT NULL, CONSTRAINT "customer_c_custkey_pkey" PRIMARY KEY ("c_custkey"), CONSTRAINT "customer_c_nationkey_fkey" FOREIGN KEY ("c_nationkey") REFERENCES "sys"."nation" ("n_nationkey") ); sql>select count(*) from customer; +------+ | L1 | +======+ | 1500 | +------+ 1 tuple (1.311ms) sql>select c_custkey, c_name from customer where c_custkey < 2; +-----------+--------------------+ | c_custkey | c_name | +===========+====================+ | 1 | Customer#000000001 | +-----------+--------------------+ 1 tuple (1.776ms) sql>set optimizer='cl_pipe'; operation successful (0.981ms) sql>select c_custkey, c_name from customer where c_custkey < 2; invalid/unknown response from server, ignoring output ? #Could not allocate OpenCL buffer of size: 0. Error caching input BAT. sql> sql>explain select c_custkey, c_name from customer where c_custkey < 2; +------------------------------------------------------------------------------+ | mal +==============================================================================+ | function user.s9_1{autoCommit=true}(A0:int):void; | | ocelot.beginQuery(); | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","customer","c_custkey",0); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","customer"); | | X_51 := ocelot.thetasubselect(X_7,X_4,A0,"<",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","customer","c_custkey",2); | | X_52 := ocelot.thetasubselect(r1_10,A0,"<",0); | | X_13 := sql.bind(X_3,"sys","customer","c_custkey",1); | | X_53 := ocelot.thetasubselect(X_13,X_4,A0,"<",0); | | ocelot.sync(X_51); | | ocelot.sync(X_52); | | ocelot.sync(X_53); | | X_15 := sql.subdelta(X_51,X_4,X_10,X_52,X_53); | | X_17 := sql.projectdelta(X_15,X_7,X_10,r1_10,X_13); | | X_18 := sql.bind(X_3,"sys","customer","c_name",0); | | (X_20,r1_23) := sql.bind(X_3,"sys","customer","c_name",2); | | X_22 := sql.bind(X_3,"sys","customer","c_name",1); | | X_23 := sql.projectdelta(X_15,X_18,X_20,r1_23,X_22); | | X_24 := sql.resultSet(2,1,X_17); | | sql.rsColumn(X_24,"sys.customer","c_custkey","int",32,0,X_17); | | sql.rsColumn(X_24,"sys.customer","c_name","varchar",25,0,X_23); | | X_32 := io.stdout(); | | sql.exportResult(X_32,X_24); | | ocelot.endQuery(); | | end s9_1; | | # querylog.define("explain select c_custkey, c_name from customer where c_cu | : stkey < 2;","cl_pipe") : +------------------------------------------------------------------------------+ 27 tuples (1.408ms) sql> =======================================

2 3

monetdbd hangs from Python subprocess
by Christopher Nelson 23 Nov '13

23 Nov '13

Hi, I am writing some automation to control monetdbd programmatically from Python. While the rest of the monetdbd commands work fine, the command: monetdbd start <farm> never returns control to Python. I have tried this on both CPython 2.7 and PyPy 2.0.4. cnelson@hpvm:~/workspace/cql-db-py/cql $ python store.py DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set passphrase=letmein /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set control=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set discovery=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set port=60000 /tmp/test_warehouse' INFO:root:Starting warehouse at '/tmp/test_warehouse' cnelson@hpvm:~/workspace/cql-db-py/cql $ ps a PID TTY STAT TIME COMMAND 31608 pts/3 S 0:00 python store.py 31613 pts/3 Z 0:00 [monetdbd] <defunct> You can see that the process appears to have finished, but never returns control to Python. If I run the 'stop' command from the prompt, everything clears up and finishes: cnelson@hpvm:~/workspace/cql-db-py/cql $ /home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse cnelson@hpvm:~/workspace/cql-db-py/cql $ DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd start /tmp/test_warehouse' INFO:root:Stopping warehouse at '/tmp/test_warehouse' ERROR:root:Unable to run command: '/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse' DEBUG:root:unable to open merovingian.pid: No such file or directory ERROR:root:None Traceback (most recent call last): File "store.py", line 198, in <module> w.stop() File "store.py", line 192, in stop self._control_warehouse("stop") File "store.py", line 98, in _control_warehouse raise subprocess.CalledProcessError(p.returncode, cmd, out) subprocess.CalledProcessError: Command '['/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd', 'stop', '/tmp/test_warehouse']' returned non-zero exit status 1 The error here occurs because the next thing that the script will try to do is 'stop' the monetdbd instance. Which of course I already did by hand. The point is that once I stop the monetdbd instance OOB, the original monetdbd where 'start' was called finally does return control to Python. Which indicates that it is probably not a Python problem. Any ideas?

2 1

LIMIT clause in sub-query?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, I'm trying to use a LIMIT clause in a sub-query but MonetDB doesn't seem to be able to handle it. A very simple example query would be: SELECT COUNT(*) AS gt_result FROM ( SELECT * FROM nyc_buildings LIMIT 10 ) AS temp; The result of this query should be 10 and works fine in PostgreSQL. However MonetDB gives the following error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "<original query>" Is this a bug in MonetDB? Any way to work around it? Best regards, Dennis Pallett

2 1

Enum data type?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, Does MonetDB support some kind of enum type, similar to MySQL's enum type (http://dev.mysql.com/doc/refman/5.0/en/enum.html) i.e. a column that only allows a limited set of values? I couldn't find it in the documentation and I'm unable to simulate it with triggers or constraints. Best regards, Dennis Pallett

2 2

CREATE CLUSTERED INDEX fails
by Michael Wittig 20 Nov '13

20 Nov '13

Hi, I am just getting started with monetdb. I have a table like this: CREATE TABLE values ( "date" DATE, "datetime" TIMESTAMP, "origin" VARCHAR(30), "value" REAL ); Normally I want to do queries like SELECT date, AVG(value) FROM values WHERE date>='2013-01-01' AND origin='sensor1' GROUP BY date; date and datetime are sorted so I would assume that an index on origin will be useful. The possible values of origin are limited so I tried: (Version Feb2013-SP5) CREATE CLUSTERED INDEX index_origin ON values (origin); But the result is syntax error, unexpected IDENT, expecting INDEX in "create clustered" What am I missing? Or is there even a better solution for data that looks like this where origin is sorted within a date. date | datetime | origin | value 2013-01-01 | 2013-01-01 10:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 12:00:00.000 | sensor1 | 12 2013-01-01 | 2013-01-01 14:00:00.000 | sensor1 | 15 2013-01-01 | 2013-01-01 16:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 10:00:00.000 | sensor2 |111 2013-01-01 | 2013-01-01 12:00:00.000 | sensor2 | 222 2013-01-01 | 2013-01-01 14:00:00.000 | sensor2 | 333 2013-01-01 | 2013-01-01 16:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 10:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 12:00:00.000 | sensor1 | 12 2013-01-02 | 2013-01-02 14:00:00.000 | sensor1 | 15 2013-01-02 | 2013-01-02 16:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 10:00:00.000 | sensor2 | 111 2013-01-02 | 2013-01-02 12:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 14:00:00.000 | sensor2 | 333 2013-01-02 | 2013-01-02 16:00:00.000 | sensor2 | 222 Regards, Michael

2 2

COPY INTO with foreign key
by Treffer, Arian 20 Nov '13

20 Nov '13

Hello, I want to import data into a table which has a foreign key to itself: CREATE TABLE "Type" ( "id" INT NOT NULL , "name" VARCHAR(255) NOT NULL , "file" VARCHAR(255) NULL , "componentTypeId" INT NULL , PRIMARY KEY ("id") , CONSTRAINT "fk_Type_componentType" FOREIGN KEY ("componentTypeId" ) REFERENCES "Type" ("id" ) ON DELETE NO ACTION ON UPDATE NO ACTION) ; Now, if I try to import some data, eg: 0;a;a;NULL 1;b;b;0 the second line causes an error: sql>COPY INTO "Type" FROM 'C:/data.csv' USING DELIMITERS ';','\n'; INSERT INTO: FOREIGN KEY constraint 'Type.fk_Type_componentType' violated What is the correct way to import the data? Since I have a lot of tables, I would rather not have to drop and re-create all constraints. Best regards, Arian

2 1

User Defined Aggregations in MonetDB?
by Alastair McKinley 20 Nov '13

20 Nov '13

Dear all, I was wondering if it is possible to build a User Defined Aggregation in MonetDB. For example, rather than do SUM(myfunction(a,b)) I would like to do MYAGG(a,b) where MYAGG internally holds a small buffer of a and b values so that they can be calculated in a batch fashion with a high performance batch implementation of the function. Could MYAGG be implemented in C? Best regards, Alastair

3 6

User Defined Functions returning null
by Alastair McKinley 13 Nov '13

13 Nov '13

Hi all, I am trying to evaluate the performance of MonetDB for an analytical workload. I am using MonetDB-11.15.17 built from source on Ubuntu 13.04. I have encountered a problem with user using defined functions in SQL which I have reduced to this test case. I appears that whenever I have over 200,000 rows in the table, the UDF returns null. First create a test data set with the following python: import random with open("test_data.csv","w") as f: for i in range(0,1000000): f.write("%s,%s\n" % (random.random(),random.random())) Then I use the following to load the data, and try using a simple UDF in SQL. CREATE TABLE TEST ( x float, y float ); COPY 200000 RECORDS INTO TEST from '/home/alastair/test_data.csv' USING DELIMITERS ',','\n','"' NULL AS ''; CREATE FUNCTION MY_UDF (x float, y float) RETURNS float BEGIN DECLARE ret float; set ret = LOG(x/y); RETURN ret; END; SELECT 'udf alone',MY_UDF(0.1,0.2); SELECT 'udf from table',MY_UDF(t.x,t.y) FROM TEST as t limit 1; -- this returns NULL with 200000 records DROP FUNCTION MY_UDF; DROP TABLE TEST; The value returned my MY_UDF is null with 200,000 records, changing to 199,999 records in COPY INTO produces the correct result. If I use a UDF built in C, the problem does not exist at all. I feel like I might have missed something silly but I can't think of anything. Best regards, Alastair

3 5

Spatial reference ID not stored/returned correctly?
by Dennis Pallett 12 Nov '13

12 Nov '13

Hello all, I'm working with the GeoSpatial extension of MonetDB and I might have run into a bug with regards to the spatial reference ID of objects. When adding a new object I specify the spatial reference ID but when retrieving the same object the correct spatial reference ID is not returned. The following queries demonstrate the bug: # Create a new spatial table CREATE TABLE trees (id INT, shape POINT); # insert a new Point object in spatial reference system 4326 (WGS 84) INSERT INTO trees VALUES ( 1, PointFromText('POINT (30 10)', 4326) ); # Retrieve the Spatial Reference ID from the just inserted Point object SELECT SRID(shape) FROM trees; I expect the result to be 4326 but instead it is always 0. Is this a bug in MonetDB / GeoSpatial or am I misunderstanding something? Best regards, Dennis Pallett

2 2

How to reclaim disk space used by intermediates
by Ashish Kumar Singh 10 Nov '13

10 Nov '13

Hi , While going through below section @ http://www.monetdb.org/book/export/html/242 : I understand that monetdb can create a lot of intermediates over the period if time, and when memory is limited can keep on consuming hard disk space forever. Is there a way to reclaim this space if I want to? Running out of diskspace One of the features of MonetDB's execution model is that all intermediates are materialized as memory mapped files. A consequence of this approach is that when memory is too small to keep them around, they will be swapped to disk by the operating system. This can be seen as a decaying free space and ultimately a full disk. In turn this (should) lead to a single transaction abort and removing its disk claim. Evidently, filling your disk depends on the number of users and the complexity of their queries. It aids to the cost of running COPY INTO and queries concurrently. If you have limited resources for concurrent access then the monetdb funnel may be an option to serialize the user requests. A single server can have multipled funnels. Bets Regards, Ashish

2 1

TypeException:user.main[2]:'A0' may not be used before being initialized
by Ruben Silva 08 Nov '13

08 Nov '13

Dear MonetDB users, Have you experienced anything like this before? TypeException:user.main[2]:'A0' may not be used before being initialized. Stacktrace follows: java.sql.SQLException: TypeException:user.main[2]:'A0' may not be used before being initialized at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:118) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:901) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:825) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) (...) What do you recommend? Should I open a bug report? Ruben Silva

2 3

Monetdb ODBC datatype conversion problem
by baraa Mohamad 06 Nov '13

06 Nov '13

Dear MonetDB users, I have created a database link from oracle to monetdb using Monetdb ODBC driver, the link works fine but i have a strange results 😓 I have the following configurations: host1: Oracle (ubuntu 64bit)host2: MonetDB (ubntu 64bit)ODBC driver: MonetDB odbc (64bit) using unixODBC driver manager on monetdb i have the following table: emp (id integer , name varchar); test query: select id from emp where id<20; when I execute this query directly on montdb I get the following results (IDs from 1 to 19)ID123..19 BUT when I execute this query from oracle using dblink select "id" from "emp"@monetdb where "id"<20 ; I GET THE FOLLOWING RESULTS: ID-------- 18589934592 21.2885E+10 31.7180E+10 42.1475E+10 52.5770E+10 63.0065E+10 73.4360E+10 83.8655E+10 94.2950E+10 10 I cannot figure out the cause of the problem and how I can solve it (it seems a problem related to datatype conversion using ODBC) Could you please tell me what I should do.... Best regards,Baraa

3 6

Modify column datataype
by baraa Mohamad 06 Nov '13

06 Nov '13

Hello, I would like to know if it is possible to change a column type. I have a table with millions of records and I wand to change the type of the column (id) but I couldn't find how to do that. Thanks in advance for your help, Regards,Baraa

3 3

Database corruption
by Pedro Salgueiro 04 Nov '13

04 Nov '13

Hi, We are using MonetDB combined with Mondrian for a while ago, and ever since we started put more load on it, with several users using the same instance, we are experiencing some serious data corruption with no reason, leading lost data of some tables and some times loosing the entire database. This weekend we had such an issue, for no apparent reason, the data got corrupted and apparently all data of a table got lost. We have no way of seeing what was being done before the data got corrupted, but since we were not loading data into it, the only queries that could be running were queries generated from Mondrian. In the logs, the only thing that we can find related to this issue is the following: 2013-11-04 11:17:56 MSG merovingian[22802]: database 'normal' (16384) was > killed by signal SIGSEGV which, results in a "connection terminated" After alot of debugging, we found out that issuing a simple query as "select count(*) from table" would cause this issue: sql>select count(*) FROM table; > Connection terminated Under the sql_logs in the database directory, there is a file named "log" with the following contents: 052001 9516 and a file named "log.9516" which is empty. When I do "\d table", I get the following result. It seems the table lost all its columns: sql>\d "table" > CREATE TABLE "sys"."table" ( > ); It's also important to note that this only happened only some tables. The rest of tables are ok. Don't know if it's important, the entire database uses about 390MB, so it's quite small. This seems to be quite a big issue leading to data loss, and worst, corrupted databases preventing its usage, unless the entire database is rebuilt. I have some questions regarding this issue which I never saw them answered, and might be very useful to any users of MonetDB: In a situation like this, is there any way to understand what happened to MonetDB in order to avoid such situation in the future? What are the "sql_logs" for? I see there are some files there, but can't find any meaning of the contents. Could these files be used to recover lost data? In previous mails to the mailing list Martin stated that the system logs all entries added to each table. Where are these logs located? Also, could they be used to recover lost data? If you want, we can provide the entire database so you can debug and try to understand what happened in order to prevent such situations in the future. Best regards, Pedro Salgueiro

1 0

Login Help
by Nitin Gautam 04 Nov '13

04 Nov '13

Hi I need to exectue some batch jobs on Monet which are supposed to run as cron jobs. Since the process runs in background I was hoping to use mclient to execute the queries. But when I use mclient I am prompted to enter a password. I could not see any option to specify the password too as an argument to mclient. Is there something I am missing or is there another way to do what I am trying to do i.e. not use mclient. Thanks Nitin

2 2

Char limitation for ruby-monetdb-sql
by Christian Kleinke 01 Nov '13

01 Nov '13

Hi, I have problem regarding executing a script with ruby-monetdb-sql. The connection works and simple statements are executed as expected. However, when I try to send a large script, nearly 20000 characters, nothing happens. Neither an exception is thrown nor a SQL error is produced by MonetDB. However, I use "\<" with the same 20000 character script, it works as expected. Two questions: 1. Is there a char limitation for ruby-monetdb-sql? 2. Is there a MonetDB-SQL command to load scripts, something like sql>load("my_script.sql")? Thank you very much in advance. Christian

2 1

MonetDB using all availaible disk space
by Ruben Silva 01 Nov '13

01 Nov '13

Hello, I'm performing some bulk inserts into some tables (COPY INTO). Meanwhile some JOINs between those tables are executed and also some DELETEs. During this JOIN's, the disk begins to fill up and only stops after there is no more space available (60GB in my case). Same for RAM. After a (long) while the space gets freed up. The COPY INTO's are done without any significant delay. The files that use that space are some .tail and .head in the database bat folder. If the queries are not concurrent there aren't any problems. The JOIN it's not using any PK or FK. Have some one noticed this before? Should I open a bug report? Ruben Silva * *

2 2

Using GeoSpatial functionality
by Dennis Pallett 30 Oct '13

30 Oct '13

Hello, I want to start using the GeoSpatial features of MonetDB and according to http://www.monetdb.org/Documentation/Extensions/GIS I need to rebuild MonetDB with the --enable-geom argument to do so. Does this mean that all the existing binaries published do not include the GeoSpatial functionality? If so, I need to follow the guide at http://www.monetdb.org/Developers/SourceCompile to build my own version of MonetDB, correct? Best regards, Dennis Pallett

2 2

Re: Are joins really this expensive?
by Bryan Senseman 30 Oct '13

30 Oct '13

Forwarding to user-list as this seems to have gotten lost Actually, I'm running a few different versions of MonetDB on different environments. July2012SP2, Feb2013SP4 & SP5. July2012SP2 on Windows and Linux (Ubuntu); Feb2013SP5 on Windows and Linux, and Feb2013SP4 on Linux. All were installed using pre-built packages/installers. The trace files themselves are from a Linux Feb2013SP4 installation. The Feb2013SP5 install was an inplace install on top of July2012SP2 and wasn't sure if different "data" versions would cloud the trace. To Martins questions (which I never got in my inbox...or junk) yes, date_hour_id is a unique key for the dim_date_hour table, but obviously duplicated within the fact_utilization_hourly table. My question was more generalized, but if a specific solution can be found, fantastic. In general I am seeing joins drastically reduce performance overall. All are running on EC2; m1.xlarge with High I/O Thanx, Bryan On Mon, Oct 28, 2013 at 9:10 AM, Bryan Senseman <monetdb(a)openbi.com> wrote: > Forwarding to user-list as this seems to have gotten lost > > Actually, I'm running a few different versions of MonetDB on different > environments. July2012SP2, Feb2013SP4 & SP5. July2012SP2 on Windows and > Linux (Ubuntu); Feb2013SP5 on Windows and Linux, and Feb2013SP4 on Linux. > All were installed using pre-built packages/installers. The trace files > themselves are from a Linux Feb2013SP4 installation. The Feb2013SP5 > install was an inplace install on top of July2012SP2 and wasn't sure if > different "data" versions would cloud the trace. > > To Martins questions (which I never got in my inbox...or junk) yes, > date_hour_id is a unique key for the dim_date_hour table, but obviously > duplicated within the fact_utilization_hourly table. > > My question was more generalized, but if a specific solution can be found, > fantastic. In general I am seeing joins drastically reduce performance > overall. > > All are running on EC2; m1.xlarge with High I/O > > Thanx, > Bryan > > ---------- Forwarded message ---------- > From: Bryan Senseman <monetdb(a)openbi.com> > Date: Sat, Oct 19, 2013 at 8:51 PM > Subject: Re: Are joins really this expensive? > To: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > > > Ask, and ye shall receive, let me know if you need anything else; and I > can't share the data, nor would you want a many GB data bomb. > > Bryan > > > On Sat, Oct 19, 2013 at 3:17 PM, Stefan Manegold <Stefan.Manegold(a)cwi.nl>wrote: > >> Dear Bryan, >> >> unless you could share your data such that we could reproduce the problem, >> could you please profile both queries using TRACE, stethoscope or >> tomograph; >> cf., >> http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming >> and share the respective output? >> >> Then we might be able analyze what exactly causes the performance >> difference between both queries. >> >> Thanks, >> Stefan >> >> >> Bryan Senseman <monetdb(a)openbi.com> wrote: >>> >>> I have created a traditional star-schema in MonetDB, using both Linux >>> and Windows. We are using Mondrian OLAP on top so it generates the >>> standard OLAP-y type of query using group-bys of dimensional values. What >>> I’m finding is that doing joins in MonetDB is extremely expensive, for >>> example: >>> >>> >>> >>> The following query against a fact table with 261 million rows returns >>> in 9-11seconds consistently >>> >>> select date_hour_id/10000 as date_month_id, avg(seconds) from >>> fact_utilization_hourly group by date_month_id; >>> >>> returning 41 rows, the query includes a “Trick” to get to a month_year >>> value because the time_ids are smart keys… yyyyMMddhh. >>> >>> >>> >>> With the following query, using standard OLAP-y syntax, the query takes >>> over 5 mins consistently. >>> >>> select year_month_value, avg(seconds) from fact_utilization_hourly f >>> join dim_date_hour d on f.date_hour_id = d.date_hour_id group by >>> year_month_value; >>> >>> returning the same 41 rows. >>> >>> >>> >>> I’m at a loss to figure out why this join causes a 3200% percent >>> increase in response time. And this seems to have gotten worse in SP5 from >>> anecdotal evidence. >>> >>> >>> >>> Bryan >>> >>> ------------------------------ >>> >>> This transmission is confidential and intended solely for the use of the >>> recipient named above. It may contain confidential, proprietary, or legally >>> privileged information. If you are not the intended recipient, you are >>> hereby notified that any unauthorized review, use, disclosure or >>> distribution is strictly prohibited. If you have received this transmission >>> in error, please contact the sender by reply e-mail and delete the original >>> transmission and all copies from your system. >>> >>> ------------------------------ >>> >>> users-list mailing list >>> users-list(a)monetdb.org >>> >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> -- >> | Stefan.Manegold(a)CWI.nl | Database Architectures (DA) | >> | www.CWI.nl/~manegold | Science Park 123 (L321) | >> | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >> > > > -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

Dump database with other than admin-user - access denied
by Klaus Kruse 30 Oct '13

30 Oct '13

Hello, we will run MonetDB on two servers and while installing and so on went fine, I struggle with the backup solution. For this, we normally add a separate user with full rights. The dump seems to fail on the sys.db_user_info : mclient --format=sql --user=backup > /dump-file MAPI = backup@localhost:50000 QUERY = SELECT "name" FROM "sys"."auths" WHERE "name" NOT IN (SELECT "name" FROM "sys"."db_user_info") AND "grantor" <> 0 ORDER BY "name" ERROR = !SELECT: access denied for backup to table 'sys.db_user_info' Ok, let's give it a try: GRANT ALL ON sys.db_user_info TO "backup" ; operation successful (1.874ms) MAPI = backup@localhost:50000 QUERY = SELECT "ui"."name", "ui"."fullname", "password_hash"("ui"."name"), "s"."name" FROM "sys"."db_user_info" "ui", "sys"."schemas" "s" WHERE "ui"."default_schema" = "s"."id" AND "ui"."name" <> 'monetdb' ORDER BY "ui"."name" ERROR = !access denied for user 'backup' So, my question is: How to give another user than 'monetdb' full rights to dump the whole db? Thanks in advance und best regards Klaus Kruse -- Corpex Internet GmbH * Schauenburgerstrasse 6 * D-20095 Hamburg Tel: +49 40 822268-0 * Fax: +49 40 822268-100 * http://www.corpex.de/ HRB 78752, Amtsgericht Hamburg, Sitz: Hamburg, USt.-Id: DE 213001412 Geschäftsführer: Daniel Hanelt

1 0

Accessing MonetDB form Oracle DB
by baraa Mohamad 26 Oct '13

26 Oct '13

Dear MonetDB users, I have a question about accessing MonetDB via Oracle using ODBC. I have oracle on machine1 (ubuntu 64bit) and MonetDB on machine2(ubuntu 64bit)I installed Monetdb odbc driver using apt-get install libmonetdb-client-odbc I have done all the needed configurations for the listner, tnsnames, sqlnet, initservice.ora and I used UnixODBC. After creating the database link, I get this error when I try to connect MonetDB: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[unixODBC][MonetDB][ODBC Driver 11.11.11]Client unable to establish connection{08001}ORA-02063: preceding 2 lines from MONETDB I don't know if the use of (64bit) needs some special configurations?? or its du to something else. So I need your help to figure out what is the problem.... Best regards,Baraa

3 4

Primary and Foreign keys
by Hilario Fernandes 22 Oct '13

22 Oct '13

Hi, I've been trying to understand if there is any performance benefit in using primary and foreign key constraints when performing join queries. I've tested joining a table with 1 million rows with one with 60k. Tryed the same queries with and without constraints and couldn't see a significant change in execution time. The tables are not that big but they are a sample of what we'll be working with in average. Should i be seeing any difference? Hows do PK and FK constraints affect query performance in Monetdb? Will it only be noticeable with larger datasets? Thank you Hilario Fernandes

2 1

Are joins really this expensive?
by Bryan Senseman 20 Oct '13

20 Oct '13

I have created a traditional star-schema in MonetDB, using both Linux and Windows. We are using Mondrian OLAP on top so it generates the standard OLAP-y type of query using group-bys of dimensional values. What I’m finding is that doing joins in MonetDB is extremely expensive, for example: The following query against a fact table with 261 million rows returns in 9-11seconds consistently select date_hour_id/10000 as date_month_id, avg(seconds) from fact_utilization_hourly group by date_month_id; returning 41 rows, the query includes a “Trick” to get to a month_year value because the time_ids are smart keys… yyyyMMddhh. With the following query, using standard OLAP-y syntax, the query takes over 5 mins consistently. select year_month_value, avg(seconds) from fact_utilization_hourly f join dim_date_hour d on f.date_hour_id = d.date_hour_id group by year_month_value; returning the same 41 rows. I’m at a loss to figure out why this join causes a 3200% percent increase in response time. And this seems to have gotten worse in SP5 from anecdotal evidence. Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

3 3

Monetdb python error in connection
by Rose Beck 19 Oct '13

19 Oct '13

I am using python-monetdb 11.16.0.7. I created my database farm and database according to instructions given below (source: http://www.monetdb.org/Documentation/monetdbd) % monetdbd start /home/my-dbfarm % monetdb create my-first-db Then I tried to connect to the database using the below mentioned command in python(https://pypi.python.org/pypi/python-monetdb/). Upon doing so I am getting the below mentioned error: >import monetdb.sql >connection=monetdb.sql.connect(username="monetdb",password="monetdb",hostname="localhost",database="my-first-db"); File "/usr/local/lib/python2.7/dist-packages/monetdb/sql/__init__.py", line 28, in connect return Connection(*args, **kwargs) File "/usr/local/lib/python2.7/dist-packages/monetdb/sql/connections.py", line 58, in __init__ unix_socket=unix_socket) File "/usr/local/lib/python2.7/dist-packages/monetdb/mapi.py", line 93, in connect self.socket.connect((hostname, port)) File "/usr/lib/python2.7/socket.py", line 224, in meth return getattr(self._sock,name)(*args) socket.error: [Errno 111] Connection refused Can someone please help me with this? Also my my-first-db resides inside my db-farm? I am not getting as to what should I do?

1 0

Re: nulls last
by Klaudiusz 18 Oct '13

18 Oct '13

On 18.10.2013 12:05, Hilario Fernandes wrote: > O made the same test in SP4 and the results were the same, sum(null) kept > returning 0 and no results for the case. My tests in SP5 were done with a > clean database with no data imported, so according to your tests i should > at least have the correct value for the sum... > > These are the queries i've done and what i believe would be the expected > results: > > create table test (number int, name varchar(50)); > > insert into test values(1,'jonh'); > insert into test values(2,'mike'); > insert into test values(3,'mary'); > insert into test values(null,'austin'); > insert into test values(null,'jonh'); > insert into test values(null,'mary'); > > select name, sum(number) > from test > group by name; > > Result: > austin | 0 > jonh | 1 > mary | 3 > mike | 2 > > Expected: > austin | null > jonh | 1 > mary | 3 > mike | 2 > Hi Hilario, I've tested your query using default branch (11.16.0) and I can confirm that this bug also exist. So probably it depends on type of value by which you group. Probably if you try my example from http://bugs.monetdb.org/show_bug.cgi?id=3388 on fresh database, results should be as expected. So group by int works, but group by varchar doesn't :-(. I'm not sure if this is another bug than 3388 or it is the same. It's rather another one. Klaudiusz

1 0

nulls last
by Hilario Fernandes 18 Oct '13

18 Oct '13

Hello, I'm trying to build a query with order by that puts nulls last always. I tryed to use the "NULLS LAST" expression but apparently it's not supported by monetdb. So i came up with something like this: select name, sum(number) from test group by name order by case when sum(number) is null then 1 else 0 end, sum(number) ASC Thing is, this works when the ordering column (number) is not aggretated, as soon as i throw the sum in there it returns no results. The sum of null values seems to be returning 0, is it possible to change that behaviour trough configuration? Is there a better way to achieve this? thanks

2 1

Announcement: New Feb2013-SP5 Bugfix release of MonetDB suite
by Hannes Mühleisen 16 Oct '13

16 Oct '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP5 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-SP5 bugfix release Client Package * ODBC: Implemented retrieving variable-length data in parts with SQLGetData. See bug 3381. MonetDB Common * Removed the heap cache. Since the fix for bug 3323 which made that the cache was actually getting used, bug 3376 made clear that it didn't work very well. In addition, on Linux at least, the heap cache slows things down. Java Module * Rearranged order of returned columns of certain metadata functions to comply with the JDBC documentation. See bug 3356. Bug Fixes * 3323: Heapcache bugs/performance issues * 3331: SAMPLE will return same result every time. * 3356: DatabaseMetaData.getColumns() doesn't work correctly when using index-based getters * 3367: Fully qualified order by column gives "relational query without result" * 3368: BAT sortedness info ignored on ORDER BY and TOPN * 3370: SQL environment settings are updated even when the set statement fails * 3373: Setting table to read-only (Bug 3362) fails on big-endian * 3375: LIKE join: BATfetchjoin does not hit always * 3376: COPY INTO fails with HEAPextend: failed to extend: MT_mremap() failed * 3377: Query interfering with next query in same transaction, after SP4 * 3380: Python DBAPI driver throws exception when fetching EXPLAIN results * 3381: Windows ODBC driver hangs or crashes on simple queries

1 0

Fwd: Query execution time
by baraa Mohamad 13 Oct '13

13 Oct '13

>> Hello dear MontDB users, >> >> I have a question please, I just need to get the query execution time >> without showing the results of the query (for performance comparaison) . >> Something which corresponds to the "traceonly" in Oracle. that will be >> greate if I can see the query plan but for the moment the most important >> for me is to get the query execution time. >> >> for example: I want to know the query exection time of this query >> (select * from source where a>10) knowing that I have more than one >> hundred million records which correspond to this predicate. >> >> Thank in advace for your appreciated help. >> >> Baraa

4 16

Re: How to transfer monetdb database to another machine
by Rose Beck 13 Oct '13

13 Oct '13

Thanks a lot for helping. I am unable to figure as to where does * <dbfarm>/<dbname>* directory reside within ubuntu 12.04. Can you please help me with this. On Sun, Oct 13, 2013 at 6:06 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: Query execution time (baraa Mohamad) > 2. Re: Query execution time (Martin Kersten) > 3. Re: How to transfer monetdb database to another machine > (Martin Kersten) > 4. Re: Query execution time (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 13 Oct 2013 12:31:35 +0200 > From: baraa Mohamad <bem85(a)hotmail.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Cc: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Query execution time > Message-ID: <BAY406-EAS1557A9804AABD08B94A7905A8190(a)phx.gbl> > Content-Type: text/plain; charset="utf-8" > > That's great! Thank you very much for your appreciated help. > > I just could't really understand the output of this command. > > This is my output: > Real: 1m23 > User: 0m24s > Sys: 0m2.3s > > I'm a little bit confused because when I execute the same query directly > without the time option i get 733,28ms. > > So i couldn't know what is the real execution time. > > > > > Le 13 oct. 2013 ? 11:29, "Martin Kersten" <Martin.Kersten(a)cwi.nl> a > ?crit : > > > > How about (time mclient -d db <inputfile >/dev/null) > > > >> On 10/13/13 11:24 AM, baraa Mohamad wrote: > >> Thank you for your answer. > >> But in general I would like to know how to get the query exexution time > >> without printing the results. It's for performance comparaison. I want > >> to evaluate MonetDB for my research and i'm working with large tables. > >> > >> Please any idea?? > >> > >> Greetings, > >> Baraa > >> > >> > >> > >> > >> Le 13 oct. 2013 ? 11:02, "Martin Kersten" <Martin.Kersten(a)cwi.nl > >> <mailto:Martin.Kersten@cwi.nl>> a ?crit : > >> > >>> Hi > >>> > >>> > >>>> On 10/13/13 10:55 AM, baraa Mohamad wrote: > >>>> > >>>>>> Hello dear MontDB users, > >>>>>> > >>>>>> I have a question please, I just need to get the query execution > time > >>>>>> without showing the results of the query (for performance > >>>>>> comparaison) . > >>>>>> Something which corresponds to the "traceonly" in Oracle. that will > be > >>>>>> greate if I can see the query plan but for the moment the most > >>>>>> important > >>>>>> for me is to get the query execution time. > >>>>>> > >>>>>> for example: I want to know the query exection time of this query > >>>>>> (select * from source where a>10) knowing that I have more than one > >>> This is not the ind of query you fire when you expect a large outcome. > >>> Better use SELECT COUNT(*).... > >>> > >>>>>> hundred million records which correspond to this predicate. > >>>>>> > >>>>>> Thank in advace for your appreciated help. > >>>>>> > >>>>>> Baraa > >>>> _______________________________________________ > >>>> users-list mailing list > >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>> https://www.monetdb.org/mailman/listinfo/users-list > >>> _______________________________________________ > >>> users-list mailing list > >>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>> https://www.monetdb.org/mailman/listinfo/users-list > >> > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org > >> https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list >

2 1

How to transfer monetdb database to another machine
by Rose Beck 13 Oct '13

13 Oct '13

I created monetdb database using the python library https://pypi.python.org/pypi/python-monetdb/11.16.0.7 on my ubuntu 12.04 lts server. Now I want to transfer this database to another ubuntu 12.04 lts server. However, I am unable to understand as to where(directory name) does monetdb create its database (within ubuntu 12.04) and what is the procedure which I must follow to transfer this database to another machine (so that i may query the database on another machine)?

2 1

Crashed DB - BBPextend: trying to extend BAT pool beyond the limit
by Pedro Salgueiro 09 Oct '13

09 Oct '13

Hi, We have a MonetDB instance which was working without problems and suddenly stopped working. The following error shows up in "merovingian.log" when the client(mclient) tries to connect to the database: 2013-10-07 10:35:27 ERR testbd[12111]: !FATAL: BBPextend: trying to extend > BAT pool beyond the limit (16384000) > 2013-10-07 10:35:27 ERR testbd[12111]: > 2013-10-07 10:35:27 MSG merovingian[12015]: database 'testbd' (12111) has > exited with exit status 1 > 2013-10-07 10:35:27 ERR merovingian[12015]: client error: database > 'testbd' has crashed after starting, manual intervention needed, check > monetdbd's logfile for details > 2013-10-07 10:35:27 MSG merovingian[12015]: database 'testbd' has crashed > after start on 2013-10-02 16:36:06, attempting restart, up min/avg/max: > 0s/0s/0s, crash average: 1.00 1.00 0.83 (25-0=25) The DB size is about 1.1 GB. I really don't know how many tables, columns or records this specific database has, as it was being used for a while and the data is created/uploaded daily by its users. I tried with Feb2013-SP2, Feb2013-SP3 and Feb2013-SP4 I have a few question about this issue: 1 - Is there any way to see a more detailed log about the error? 2 - Is it possible to recover the DB from this error? I saw somewhere in the mailing list that removing the sql_logs could help starting the DB, at the cost of loosing some data. Already tried that with no success. 3 - What is this "BAT pool limit" thing? Is this related in some way with the number of tables, number of columns per table, or total amount of columns that a DB can have? Thank you, Pedro Salgueiro

3 8

ORDER BY in sub-query
by Hadley Wickham 08 Oct '13

08 Oct '13

Currently, monetdb fails on this query: SELECT * FROM (SELECT * FROM "Batting" ORDER BY "playerID") with syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from (select * (obviously that query isn't very useful, but it makes it harder to automatically generate sql) In case it matters, that query (with minor escaping variations) works in sqlite, postgres and mysql. Hadley -- Chief Scientist, RStudio http://had.co.nz/

5 17

Create database other then "demo" in windows 8
by Prashant Patel 07 Oct '13

07 Oct '13

Hi all, I am using MonetDB on my windows 8 platform every time open monetdb client and server , it automatically connects to "demo" database by default. I am very new to these technology I have searched but couldn't find how to create our own database in monetdb (other then "demo"). Can anyone tell me how to create database and connect that database in monetdb server and client. I know this is very dump question but help will be really appreciated. ( If i can get some documents that would also work) Thanks in advance, Prashant Rajkotiya Research Aspirant

2 2

How to take COLD RUNs Execution time
by Prashant Patel 07 Oct '13

07 Oct '13

as a part of my project, i have to check the performance of monetdb column-store database. I am very new to this technology, so i dont know where the monetdb caches its previous query results.. I want to know how can i take results for cold runs on monetdb ( i have tried closing monetdb client and server every time i fire a query , but doesnt seem to clear the cache and i always get hot runs result). I am using window 8 core i5 2.5GHz machine. so please can anyone help me regarding these.... suggestions are appreciated.. thanks in advance.. Prashant Rajkotiya M.tech. (ICT) DA-IICT ,Gandhinagar

2 2

Window functions
by Hadley Wickham 05 Oct '13

05 Oct '13

Hi all, Does monetdb support window functions? I found https://www.monetdb.org/pipermail/users-list/2011-June/004992.html, which claims that OVER is supported, but: * I can't see where support is listed on http://www.monetdb.org/print/book/export/html/63 * I get errors when I try and use window functions: sql> SELECT *, MAX("AB") OVER (PARTITION BY "playerID") AS "_W4", MAX("G") OVER (PARTITION BY "playerID") FROM "Batting"; SELECT: function 'max' not found But that might be because I don't have the syntax right (but that query works in Postgres) Are there are any plans to add standard window functions like rank, ntile, lead, lag etc to monetdb? They are pretty useful for analysis. Hadley -- Chief Scientist, RStudio http://had.co.nz/

2 1

new "too many digits" bug in SP-4
by Anthony Damico 03 Oct '13

03 Oct '13

this issue is new for SP-4 ..i have been using this syntax for a long time and never encountered this problem until the upgrade :( > dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) ) ) / ( count( * ) ) as pct_male from x11" ) pct_male 1 0 > dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) )*1.000 ) / ( count( * )*1.000 ) as pct_male from x11" ) Error in .local(conn, statement, ...) : Unable to execute statement 'select ( sum( ( bene_sex_ident_cd = 1 ) )*1.000 ) / ( count( * )*1.000 ) as pct_male from x11'. Server says 'too many digits (19 > 18)' [#22003]. > what diagnostics can i run for you to troubleshoot this? thanks!! :)

2 7

Bug with fetching EXPLAIN results in Python DBAPI driver.
by Pete Hollobon 03 Oct '13

03 Oct '13

Hi, I spotted a bug in the Python DBAPI driver when fetching results from an EXPLAIN. >>> import monetdb.sql >>> db = monetdb.sql.connect(database="demo") >>> cursor = db.cursor() >>> cursor.execute("explain select count(*) from sys.tables") 31 >>> cursor.fetchone() Traceback (most recent call last): File "<stdin>", line 1, in <module> File "monetdb/sql/cursors.py", line 203, in fetchone result = self.__rows[self.rownumber - self.__offset] IndexError: list index out of range >>> I've logged a bug with patches for Python 2 and 3 here: http://bugs.monetdb.org/show_bug.cgi?id=3380 thanks, Pete.

1 0

Static binary compilation
by Hassan, Ahmad 01 Oct '13

01 Oct '13

Hi, May be someone has tried compiling monetdb as a static binary please? I tried compiling the binary using -static flag but the resulting binary is still 'Dynamically linked' binary. I think it is due to glibc and dl libraries. If I pass libglibc.a and libdl.a statically then I get errors like: ============= /tmp/mserver5-rkIbiC.o: In function `mdlopen': /data/ahmad/test/MonetDB/gdk/gdk_posix.c:454: warning: Using 'dlopen' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking /usr/lib/x86_64-linux-gnu/libreadline.a(complete.o): In function `rl_username_completion_function': (.text+0x2bc9): warning: Using 'getpwent' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking ... /data/ahmad/test/MonetDB/common/stream/stream.c:1765: warning: Using 'gethostbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking ============= Does anyone know the workaround of creating static binary of monetdb executable please? Cheers, Ahmad

2 1