September 2015 - users-list

Issue importing certain character combinations
by andy.barlow＠datum360.com 25 Sep '15

25 Sep '15

I filed a bug earlier, the gist of which is below but perhaps there is some setting or flag I am missing when importing? If your import file contains a number of character combinations, such as \560, \561, \700 and probably lots of others, the copy into fails with a message like: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Reproducible: Always Steps to Reproduce: 1. create table test (test varchar(20)); 2. create a load file test.txt with the following: ONE TWO THREE FOUR FIVE\700 3. copy into test from 'test.txt' using delimiters '\t','\n'; 4. fails with message: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Actual Results: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Expected Results: should import OK, the character combinations are valid Andy Barlow Datum360

1 1

Re: Disk space running out
by scArcer 24 Sep '15

24 Sep '15

"create table" and "insert data" are separate. And the disk is consumed by queries. because when I finished data loading, it was, only 3~4G. Thank you for the link. I will try restart server to see if monetdb can unlink these files too. I execute queries in auto commit mode, should I control transactions myself? ------------------ Original ------------------ From: "Guillaume Savary";<guillaume.savary(a)securactive.net>; Date: Thu, Sep 24, 2015 02:30 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "scArcer"<53308768(a)qq.com>; Subject: Re: Disk space running out Hi, Are you repeating this "create table + insert data" multiple times on this disk? Because there is a bug on the BAT files which are never freed : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3802 Work around : try to do the "create table" in an other transaction than the "insert data". Le 24/09/2015 08:17, scArcer a écrit : Here is my situation. I create a table A, load about 3~4G data into it. Then a lot of queries are executed, making it using disk up to 1T. [hadoop@falco001-bi-30-141 data3]$ du -sh monetdb/ 1.1T monetdb/ and when I try to execute new query, I got this error: sql>select * from ods.attr_user_c_profile limit 10; could not allocate space for GDKsave: error -1 on: name=06/62/66261, ext=theap, mode=0 !OS: No space left on device HEAPextend: failed to extend to 983040 for 06/62/66261.theap: h->storage == STORE_MEM && can_map && fd >= 0 && HEAPsave_intern() < 0 ------------------ Original ------------------ From: "Martin Kersten";<martin.kersten(a)cwi.nl>; Date: Thu, Sep 24, 2015 02:03 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Disk space running out On 24/09/15 06:04, scArcer wrote: > Hi, I'm facing the problem of disk space running out. > Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? > And if it doesn't, how can I do it manually? > > Hi It will release disk space as soon as possible. > _______________________________________________ > 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 _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list -- Guillaume Savary Securactive R&D

1 0

Disk space running out
by scArcer 24 Sep '15

24 Sep '15

Hi, I'm facing the problem of disk space running out.Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? And if it doesn't, how can I do it manually?

3 4

monetdb cluster on EC2
by imad hajj chahine 23 Sep '15

23 Sep '15

Hi, As per this link https://www.monetdb.org/pipermail/users-list/2013-August/006744.html, to be able to have monetdb cluster to work in ec2, a VPN must be used to connect the nodes. Following this link: https://www.buckhill.co.uk/blog/how-to-enable-broadcast-and-multicast-on-am…, i configured the VPN between the cluster nodes. Now for each node I have the eth0 and edge0 network interface, when i start monetdbd it directly bind to the eth0 interface, thats why i suppose the discover is not working. Any idea how to start monetdbd and bind to the interface used for VPN to be able to use TCP broadcast hence discover the other nodes. Any help will be appreciated. Thank You.

1 0

if i run these commands in R, mserver dies.. any ideas how to fix? thanks
by Anthony Damico 22 Sep '15

22 Sep '15

# MonetDB 5 server v11.21.3 "Jul2015" # Serving database 'marketscan', 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-2015 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:49700/ # Start processing logs sql/sql_logs version 52200 # Start reading the write-ahead log 'sql_logs\sql\log.211' # Finished reading the write-ahead log 'sql_logs\sql\log.211' !ERROR: log_sequence_: write failed # Finished processing logs sql/sql_logs # MonetDB/SQL module loaded > ========================= > pid <- monetdb.server.start( batfile ) > dbname <- "marketscan" > dbport <- 49700 > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) Server not ready(cannot open the connection), retrying (ESC or CTRL+C to abort) > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: BBPdir_subcommit: Syncing BBP.dir file failed' [#GDKerror:]. > dbListTables(db) Error in .local(conn, statement, ...) : Unable to execute statement 'select schemas.name as sn, tables.name as tn from sys.tables join sys.schemas on tables.schema_id=sc...'. Server says 'ERROR: GDKsave: error on: name=06\651, ext=timprints, mode=0' [#GDKerror:]. > dbListTables(db) [1] "ccaea" "ccaed" "ccaef" "ccaei" "ccaeo" [6] "ccaep" "ccaes" "ccaet" "redbook" "state" [11] "wgt" "ccaea_st_wt" "ccaet_st_wt" "ccaes_monthly" "ccaes_annual" [16] "ccaeo_monthly" "ccaeo_annual" "ccaed_monthly" "ccaed_annual" "ccaes_admmon_xwalk" [21] "ccaes_monthly_wxw" "ccaed_svcmon_xwalk" "ccaed_monthly_wxw" "ccaeo_svcmon_xwalk" "ccaeo_monthly_wxw" [26] "acons_pre" "mcons_pre" "ccs" "acons_diag" "mcons_diag" [31] "readm" "acons_readm" "mcons_readm" "mcons" "acons" > > > # create a temporary person-year table > dbSendQuery( + db , + 'CREATE TABLE temp AS + SELECT + datatyp , + yr , + enrolid , + SUM( wgt ) AS sum_wgt , + SUM( CAST( ( rx_pay ) > 250 AS INTEGER ) ) AS rx_pay_above_250 , + SUM( CAST( ( rx_copay + rx_coins + rx_deduct ) > 250 AS INTEGER ) ) AS rx_copay_coins_deduct_above_250 + FROM + mcons + WHERE + datatyp = 1 + GROUP BY + datatyp , yr , enrolid + ORDER BY + datatyp , yr , enrolid + WITH DATA' + ) Error in readBin(con, "integer", n = 1, size = 2, signed = FALSE, endian = "little") : error reading from the connection > # # # # mserver is dead

2 2

Monetdb clusters VS Distributed Query Processing
by imad hajj chahine 22 Sep '15

22 Sep '15

Hi, I am trying to understand the main difference between clusters and the remote table introduced in JUL2015 . Correct me if I am wrong, in clusters the complete table is copied over all nodes, and monetdb will do the query parallel execution on the nodes and result concat. In the remote table I have more control on executing the query on a part of a table on a selected node depending on resources availables. If this is true, are the following point valid: 1. its better to have identical nodes for clusters 2. the execution time is inversely proportional to the number of nodes in the clusters 3. the executing time is dictated by the slowest node in cluster 4. fail-over is by default supported in cluster configuration One last question about the supported queries in these 2 modes(groupby, order, limit). if any, a reference to a documentation about the clustering is appreciated. Thank you.

2 1

Two questions about R integration
by Roberto Cornacchia 22 Sep '15

22 Sep '15

Hi, I have 2 questions about R integration. 1) I see that missing packages are automatically installed when invoked by library() calls. That is really cool. The question: if I need a library in a function, will this actually be loaded at every function call? 2) How is a SQL type date translated to R? Apparently not "Date", because I tried to use it as such in the declared procedure and I got a complaint. I tried to figure the type myself with this: sql> create function dt(d date) returns string language R { class(d) }; sql> select dt( cast('2015-09-21' as date) ); but unfortunately this triggered a server assertion :/ mserver5: .../src/monetdb5/mal/mal_interpreter.c:648: runMALsequence: Assertion `t == stk->stk[a].vtype' failed. Thanks, Roberto

2 2

sys.analyze not working
by Robert Koch 19 Sep '15

19 Sep '15

Following the manual https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/statistics I am trying to reproduce this, but it fails with a type conversion error: sql>call sys.analyze('sys', 'types'); conversion of string 'sys' to type int failed. sql>SELECT * FROM sys.statistics; +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ | column_id | type | width | stamp | sample | count | unique | nils | minval | maxval |> +===========+======+=======+=======+========+=======+========+======+========+========+ +-----------+------+-------+-------+--------+-------+--------+------+--------+--------+ 0 tuples (3.013ms) !1 column dropped! Tried it with different schema/table/column names. Both with system and user-generated tables. Always the same error. Running 11.21.5 here. Thanks.

3 2

couple of unanswered qs
by Anthony Damico 18 Sep '15

18 Sep '15

https://stackoverflow.com/questions/tagged/monetdb

1 0

Why doesn't MonetDB exploit SIMD feature?
by Xu,Wenjian 18 Sep '15

18 Sep '15

Hi, We know that MonetDB excels in application where the database hot-set can be largely held in main-memory, since the physical operators (e.g., hashjoin, sort) are highly optimized for main-memory. But it is strange to me that these operators do not utilize SIMD which a performance-critical feature of modern CPUs. For example, MonetDB use *timsort* as its underlying stable sort algorithm (do_ssort() in gdk/gdk_ssort_impl.h), but I cannot find any SIMD instructions there. Why doesn't MonetDB exploit SIMD feature? or did I miss something? In paper 'Vectorwise: Beyond Column Stores', the authors claim that X100/Vectorwise use SIMD instructions. So my follow-up question is that whether any of X100/Vectorwise's technologies/features has been integrated into MonetDB's code base. Thank you. Best regards, Xu, Wenjian

2 1