- users-list - monetdb.org

[MonetDB-users] After delete records with a field criteria, still shows in group by SQL
by Osmin Castillo 02 Feb '11

02 Feb '11

Hi there. I'm having this extrange problem with MonetDB. After delete some records with a criteria for example: field_id = 201011 The records are deleted, but when I use a select group by, the count(0) shows results for field_id = 201011 Thanks in advance

1 0

[MonetDB-users] collation - sorting order
by Ramkumarlak 28 Jan '11

28 Jan '11

Hi, Can we control the sorting order in MonetDB? Is there any way we can specify collation ...? Regards Ram -- View this message in context: http://old.nabble.com/collation---sorting-order-tp30786899p30786899.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] date functions in monetdb
by Ramkumarlak 27 Jan '11

27 Jan '11

Hi, Let me know whether the following MYSQL functions are available in MonetDB. 1) concat() 2) date() 3) from_unix_time() 4) interval() 5) Weekday() If not, are there any equivalent functions available ? Regards Ram -- View this message in context: http://old.nabble.com/date-functions-in-monetdb-tp30775963p30775963.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] Performance question between schema
by brien colwell 27 Jan '11

27 Jan '11

With Monet DB I've encountered a counter- intuitive performance characteristic. I have a dataset of 33554432 (2^25) triples, (a, b, c). The values a and b are uniformly distributed on [0, 16), and c is unique to each triple, on [0, 2^25). I store them in two ways: Schema 1: One table with three columns (a, b, c) Schema 2: Two tables with two columns each, (a, c) and (b, c) I'm running SQL queries to select the "c" value from the triples based on a=X and b=Y. With schema 1, this is a single … [View More]select. With schema 2, this is an inner join between two selects. What surprised me is that the query against schema 2 is on average >50 ms faster to return than the query against schema 1. To me this was counter- intuitive since schema 1 stores less data, and the data are pre-joined (in a row-oriented sense) for the type of filter applied. However, I'm guessing this has to do with how MonetDB stores data in columns. Is the query against schema 1 actually doing three joins, "a" -> "b" -> "c", on an artificial join key? For example, -- Schema 1) create table abc (a bigint, b bigint, c bigint); -- Schema 2) create table ac (a bigint, c bigint); create table bc (b bigint, c bigint); copy 33554432 records into abc from '/home/hadoop/abc.monetdb'; copy 33554432 records into ac from '/home/hadoop/ac.monetdb'; copy 33554432 records into bc from '/home/hadoop/bc.monetdb'; -- Query Schema 1) select abc.c from abc where abc.a=3 and abc.b=7 order by abc.c asc -- Times to return (JDBC) -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.287, SQL query: 0.287, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.288, SQL query: 0.288, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.35, SQL query: 0.35, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.372, SQL query: 0.372, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.405, SQL query: 0.405, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.285, SQL query: 0.285, Building output: 0 -- Query Schema 2) select ac.c from ac inner join bc on bc.b=7 and bc.c = ac.c where ac.a = 3 order by ac.c asc -- Times to return (JDBC) -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.218, SQL query: 0.218, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.216, SQL query: 0.216, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.318, SQL query: 0.318, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.322, SQL query: 0.322, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.218, SQL query: 0.218, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.219, SQL query: 0.219, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.222, SQL query: 0.222, Building output: 0 [View Less]

2 1

[MonetDB-users] "similar to" text matching syntax
by brien colwell 25 Jan '11

25 Jan '11

I came across a commit for a "similar to" syntax for text columns -- http://www.mail-archive.com/checkin-list@monetdb.org/msg01289.html Very interested. Are there any updates on this commit -- stable, not stable, not tested? Does MonetDB publish nightly builds?

2 2

[MonetDB-users] quer cache
by Ramkumarlak 24 Jan '11

24 Jan '11

Hi, Does monetdb have query cache ? If so, how do we disable it ? I searched in monetdb5.cong but i did not find any option. Regards Ram -- View this message in context: http://old.nabble.com/quer-cache-tp30747505p30747505.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 3

[MonetDB-users] copy select
by Nafees Ur Rehman 19 Jan '11

19 Jan '11

Hi, With executing the statement below: COPY SELECT * FROM adv LIMIT 10 INTO 'D:/exp.txt' I get the following error: !TypeException: user.s0_1[4703]:'aggr.count' undefined in : tuples:wrd :=aggr.count(_4695:str) !SQLException:SQLengine:Program contains errors What am I doing wrong? Thanks Nafees

1 0

[MonetDB-users] ODBC error 'Function Sequence Error' occurredwhen loaded TPC-H data of MonetDB
by JinBinge 16 Jan '11

16 Jan '11

Hi, Benchmark Factory for Database was used to test TPC-H of MonetDB. Errors occurred when creating objects for TPC-H option was selected,such as ODBC error 'Function Sequence Error'. And tables h_nation,h_region,h_supplier' data were loaded successful,the other five tables failed to load data. follows are the test configuration: OS: Linux Hardware: amd64 MonetDB version: Oct2010 Benchmark Factory for Database version: v5.7.1g.504 load data'SF=1G Are there errors in ODBC of MonetDB? … [View More]

2 1

[MonetDB-users] Query Error Question
by Brett Spurrier 14 Jan '11

14 Jan '11

Hi all, I have yet another query that I am having trouble running with Monet, and I am hoping a seasoned expert can help me figure out what is going on here. My query is: SELECT fid, title, D0 AS ed FROM signatures t WHERE fid = ( SELECT fid FROM signatures ti WHERE ti.guid = t.guid ORDER BY D0 ASC LIMIT 1 OFFSET 1) ORDER BY D0 ASC, fid DESC; but I am getting the error: "!syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title, d0 as ed from … [View More]

4 5

[MonetDB-users] An error about MonetDB' TPCH power test
by 金炳娥 14 Jan '11

14 Jan '11

Hi all, Recently I used the Benchmark Factory tool to test TPCH of MonetDB(SF=100). When running power test,there is an error,such as follows: Started test TPC-H Power Test Agent(bejin) Error: ODBC Error (HY000,0) - [MonetDB][ODBC Driver 1.0]SQLException:convert:too many digits (21 > 19) Do you know the reason and how to reslove the problem? Looking forwards to yours answer. Yours sincerely Binge Jin

1 0

[MonetDB-users] Add primary key performance
by paulr135 13 Jan '11

13 Jan '11

I've loaded a 27GB (~165M rows) file into a table in MonetDB and now I am trying to add a primary key which consists of 4 fields - 2 varchars and 2 ints. It has been running for a few days (since Friday of last week). I've followed the instructions laid out on this post: http://old.nabble.com/MonetDB-SQL:-bulk-loading-large-data-sets---adding-ke… which involves setting the optimizer variable to "nov2009_pipe". I am unable to log in via mclient - I am able to put in my credentials but it just … [View More]

2 3

[MonetDB-users] Consultation about TPCH throughput test errors with MonetDB
by 金炳娥 12 Jan '11

12 Jan '11

Hi, Recently I has a problem when running TPCH with MonetDB. Follows are the configuration. Hardware:amd64 OS:Linux MonetDB Version:Oct2010 I loaded 100G' data to the MonetDB.Using TPCH Tester tools,I runned power test.After power test successed,running throughput test. There is an error. ERR TPCH[17170]: *** glibc detected *** /mnt/sdb1/tpch/MonetDB/install/bin/mserver5: free(): invalid pointer: 0x00002ac5381b5008 *** 2011-01-04 14:00:41 ERR TPCH[17170]: ======= Backtrace: =====… [View More]==== 2011-01-04 14:00:41 ERR TPCH[17170]: /lib64/libc.so.6[0x3aa5c71ce2] 2011-01-04 14:00:41 ERR TPCH[17170]: /lib64/libc.so.6(cfree+0x8c)[0x3aa5c7590c] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libbat.so.1[0x2b67e8c062f6] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libbat.so.1(HEAPfree+0xb9)[0x2b67e8b36511] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libbat.so.1(BATdelete+0x196)[0x2b67e8e1769a] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libbat.so.1[0x2b67e8b33697] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libbat.so.1[0x2b67e8b32fa8] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libmonetdb5.so.5[0x2b67e7f1da68] 2011-01-04 14:00:41 ERR TPCH[17170]: /mnt/sdb1/tpch/MonetDB/install/lib/libmonetdb5.so.5[0x2b67e7f1f6f1] 2011-01-04 14:00:41 ERR TPCH[17170]: /lib64/libpthread.so.0[0x3aa6806367] 2011-01-04 14:00:41 ERR TPCH[17170]: /lib64/libc.so.6(clone+0x6d)[0x3aa5cd30ad] 2011-01-04 14:00:41 ERR TPCH[17170]: ======= Memory map: ======== 2011-01-04 14:00:41 ERR TPCH[17170]: 00400000-00404000 r-xp 00000000 08:11 179159655 /mnt/sdb1/tpch/MonetDB/install/bin/mserver5 2011-01-04 14:00:41 ERR TPCH[17170]: 00604000-00605000 rw-p 00004000 08:11 179159655 /mnt/sdb1/tpch/MonetDB/install/bin/mserver5 2011-01-04 14:00:41 ERR TPCH[17170]: 00605000-00612000 rw-p 00605000 00:00 0 2011-01-04 14:00:41 ERR TPCH[17170]: 0a03f000-0e488000 rw-p 0a03f000 00:00 0 [heap] 2011-01-04 14:00:41 ERR TPCH[17170]: 4001c000-4001d000 ---p 4001c000 00:00 0 2011-01-04 14:00:41 ERR TPCH[17170]: 4001d000-4021d000 rw-p 4001d000 00:00 0 2011-01-04 14:00:41 ERR TPCH[17170]: 40287000-40288000 ---p 40287000 00:00 0 … 2011-01-04 14:00:41 ERR TPCH[17170]: 4bc66000-4be66000 rw-p 4bc66000 00:00 0 2011-01-04 14:00:41 ERR TPCH[17170]: 3aa5800000-3aa581c000 r-xp 00000000 fd:00 95158276 /lib64/ld-2.5.so 2011-01-04 14:00:41 ERR TPCH[17170]: 3aa5a1b000-3aa5a1c000 r--p 0001b000 fd:00 95158276 /lib64/ld-2.5.so 2011-01-04 14:00:41 ERR TPCH[17170]: 3aa5a1c000-3aa5a1d000 rw-p 0001c000 fd:00 95158276 /lib64/ld-2.5.so 2011-01-04 14:00:41 ERR TPCH[17170]: 3aa5c00000-3aa5d4c000 r-xp 00000000 fd:00 95158283 /lib64/libc-2.5.so 2011-01-04 14:00:41 ERR TPCH[17170]: 3aa5d4c000-3aa5f4c000 ---p 0014c000 fd:00 95158283 /lib64/libc-2.5.so The test data are correct. Do you know the reason above errors? I hope to have a letter from you before long.With every good wish. Yours sincerely， Binge Jin [View Less]

2 1

[MonetDB-users] Concurrent aggregation crashes MonetDB
by Dávid Karnok 06 Jan '11

06 Jan '11

Hi! I'm running an evaluation test on the latest MonetDB/SQL stable version and always crashes possibly due an out-of-memory problem. My system: Windows 7 x86, 4GB memory, Core i7 920, data files are on an SSD disk MonetDB (default install): # MonetDB server v5.22.1, based on kernel v1.40.1 # Release Oct2010 # Serving database 'demo', using 8 threads # Compiled for i686-pc-win32/32bit with 32bit OIDs dynamically linked # Found 3.186 GiB available main-memory. # Copyright (c) 1993-July 2008 … [View More]CWI. # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see C:\Program Files\MonetDB\MonetDB5\etc\monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.40.1 loaded Using monetdb-1.17-jdbc driver. The test involves a simple, two column table: CREATE TABLE a (text VARCHAR(50), value INT) Which is then filled with 10.000.000 records of random values. The data files take up to 150MB disk space. The test routine creates multiple threads which connect to the local demo database, and simultaneously execute the same aggregation query. The test simulates a concurrent analysis-data mining scenario running on the same data. void parallelTest(int threadCount) { ExecutorService exec = Executors.newFixedThreadPool(threadCount); final CountDownLatch cnt = new CountDownLatch(threadCount); for (int i = 0; i < threadCount; i++) { final int j = i; exec.submit(new Runnable() { @Override public void run() { try { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connection conn = DriverManager.getConnection("jdbc:monetdb://localhost/demo", "monetdb", "monetdb"); try { conn.setAutoCommit(false); long t1 = System.currentTimeMillis(); Statement stmt = conn.createStatement(); try { cnt.countDown(); cnt.await(); ResultSet rs = stmt.executeQuery("SELECT text, AVG(value) FROM a GROUP BY text "); while (rs.next()) { rs.getString(1); rs.getDouble(2); } System.out.printf("[%02d] Select time: %.2fs%n", j, (System.currentTimeMillis() - t1) / 1000.0); } finally { stmt.close(); } } finally { conn.close(); } } catch (Exception ex) { ex.printStackTrace(); } } }); } exec.shutdown(); try { exec.awaitTermination(Long.MAX_VALUE, TimeUnit.MILLISECONDS); } catch (InterruptedException ex) { } } I noticed that if the threadCount >= 4 the MonetDB console starts to print VirtualAlloc failed messages and sooner or later terminates. The memory usage seems to jump by 600MB, e.g. with 3 threads it consumes 1.7 GB memory, but with 4 threads, it can't consume 2.4GB. Replacing the query with a single result "SELECT MAX(VALUE) FROM a" causes the same crash after 5 threads. The same test running with the latest MySQL works perfectly, although slowly. No crashes or memory issues. Is this a memory leak in MonetDB or the result of its architecture, meaning I should use a 64bit system with lots of RAM? Regards, David Karnok [View Less]

1 0

[MonetDB-users] MonetDB LIMIT usage
by Brett Spurrier 05 Jan '11

05 Jan '11

Hi all, I am trying to use the LIMIT clause in my query, and while this should be fairly straight froward, I am having trouble learning the differences between Monet's LIMIT and other T-sql LIMITS. I have the simple query: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1; which works great and as expected. However, I really want the second record, so in other databases I can use: SELECT * FROM signatures ORDER BY scale ASC LIMIT 1, 1; I have also tried: SELECT * FROM signatures ORDER BY … [View More]

3 4

[MonetDB-users] Issues with commandline copy into and opening files
by Lex Slaghuis 03 Jan '11

03 Jan '11

Hi All Eversince I updated my system; The following blockbased import script doesn't work anymore: #!/bin/bash BLOCKSIZE=250000 #RECORDCOUNT L=$(wc -l $1 | awk '{ print $1}'); #MERGE BLOCKS INTO TABLE for I in $(seq 0 $BLOCKSIZE $L); do echo "$BLOCKSIZE $I $1/$L loading... "; mclient --time -d test --statement="COPY $BLOCKSIZE OFFSET $I records into \ wptool.wpstats from '$1' using delimiters ' ','\n','' null as '';"; echo "$BLOCKSIZE $I $1/$L loading...finished "; done Returns the … [View More]

2 1

[MonetDB-users] Monnetdb/Xquery
by Ramachandran, Prakash 30 Dec '10

30 Dec '10

Hi, I am currently evaluating software's for metadata storage that can be used in our product. I came across your product and found it very interesting and we might consider using it for our metadata storage. I just wanted to know if Monnetdb provides commercial support, if so can you please provide the detail on sales contacts. Thank in advance. Regards, Prakash R

1 0

[MonetDB-users] MAPI interface error
by Brett Spurrier 24 Dec '10

24 Dec '10

Hi all, I am having a bit of trouble and I am hoping a seasoned MonetDB expert can lend some advice. I am using the MAPI interface in my C/C++ application to talk to my MonetDB instance. I have a recursive INSERT INTO statement which populates the database, and it initially works pretty well. However, as I populate more (past 200 inserts in my case), I get a MAPI error (exampled by: mapi_explain_result()): MAPI = monetdb@localhost:50000 ACTION= mapi_start_talking ERROR = !Connection … [View More]

3 14

[MonetDB-users] Announcement: New Jun2010-SP1 bugfix release of MonetDB suite
by Sjoerd Mullender 21 Dec '10

21 Dec '10

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2010-SP1 bug fix release of the MonetDB suite of programs. The most important and visible change is a security fix in the SQL COPY INTO command. This is detailed in the release notes. More information (including release notes) on this release is available at <http://monetdb.cwi.nl/Development/Releases/Oct2010/>. The download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[MonetDB-users] How to limit monetdb memory consumption on Windows
by Bianco Prieto-Ilarion 18 Dec '10

18 Dec '10

Hi all! I have a problem. I'm using Oct2010 64-bit MonetDB build. I have a table with 8M records and when I'm trying to execute LEFT JOIN (from JDBC) MonetDB is crashed. I can see that it crashed due to memory (my laptop has 8Gb) consumption is unlimited. What can you suggest (just use Linux is a final step:)) Thanks, Bina Prieto

1 0

[MonetDB-users] MonetDB Installation
by Rob Fynes 16 Dec '10

16 Dec '10

Hello, Regarding the pre-compiled binaries directories for deb distributions "karmic" and "lucid" in the downloads section, there doesn't appear to be anything for x-query. Is this intentional? Regards, -Rob

2 2

[MonetDB-users] mserver5 ignores ulimit?
by Siva Manoharan 14 Dec '10

14 Dec '10

Hi. I am currently running monetdb (release Oct2010) on Debian etch, with 8GB physical RAM and 10GB virutal memory. During unrestricted operation (using the merovingian daemon) monetdb takes up a significant majority of the resources, which is not desirable as there are other processes running on the same server. To limit the memory, I tried using ulimit (setting -d, -v and -m all to 2GB). According to the logs, merovingian notices the limit (a line from log states "Found 1.907 GiB available … [View More]

1 0

[MonetDB-users] crashes with copy into (270MB file)
by Lex Slaghuis 13 Dec '10

13 Dec '10

Hello I Experience crashes with the copy into statement, with large files (270MB utf8) ... sql>COPY 6000000 records into wptool.wpstats from '/var/www/pagecounts' using delimiters ' ','\n','' null as ''; MAPI = testuser@localhost:50000 ACTION= read_line QUERY = COPY 6000000 records into wptool.wpstats from '/var/www/pagecounts' using delimiters ' ','\n','' null as ''; ERROR = !Connection terminated Timer 5846.447 msec...... Number of records: wc -l /var/www/pagecounts 5983201 /var/www/… [View More]pagecounts My memory: free -m(egabytes) total used free shared buffers cached Mem: 2007 431 1576 0 20 304 -/+ buffers/cache: 106 1900 Swap: 16769 0 16769 Error-logs: 2010-12-13 14:17:41 MSG merovingian[902]: proxying client localhost:52737 for database 'test' to mapi:monetdb:///var/MonetDB5/dbf arm/test/.mapi.sock?database=test 2010-12-13 14:17:41 MSG merovingian[902]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2010-12-13 14:18:30 MSG merovingian[902]: database 'test' (1142) was killed by signal SIGSEGV 2010-12-13 14:18:38 ERR control[902]: (local): received stop signal for non running database: test 2010-12-13 14:18:46 MSG merovingian[902]: database 'test' has crashed after start on 2010-12-13 14:17:33, attempting restart, up min/avg/max: 14s/22h/2d, crash average: 1.00 0.70 0.23 (10-3=7) Some specs: uname -a Linux ubuntu 2.6.32-24-server #43-Ubuntu SMP Thu Sep 16 16:05:42 UTC 2010 x86_64 GNU/Linux 2010-12-13 14:17:33 MSG test[1142]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2010-12-13 14:17:33 MSG test[1142]: # Found 1.961 GiB available main-memory. If anyone has some pointers on this , it would really help out. I think it would be really to chop up the files into smaller pieces, just for import. Regards Lex Slaghuis -----Original Message----- From: monetdb-users-request(a)lists.sourceforge.net [mailto:monetdb-users-request@lists.sourceforge.net] Sent: vrijdag 10 december 2010 11:12 To: monetdb-users(a)lists.sourceforge.net Subject: MonetDB-users Digest, Vol 55, Issue 2 Send MonetDB-users mailing list submissions to monetdb-users(a)lists.sourceforge.net To subscribe or unsubscribe via the World Wide Web, visit https://lists.sourceforge.net/lists/listinfo/monetdb-users or, via email, send a message with subject or body 'help' to monetdb-users-request(a)lists.sourceforge.net You can reach the person managing the list at monetdb-users-owner(a)lists.sourceforge.net When replying, please edit your Subject line so it is more specific than "Re: Contents of MonetDB-users digest..." Today's Topics: 1. COPY INTO: wrong length with backslashed chars? (Daniel Boesswetter) 2. Re: COPY INTO: wrong length with backslashed chars? (Stefan de Konink) 3. Some questions regarding parallelism and other stuff... (Mike De-La-Columnar) 4. Re: Some questions regarding parallelism and other stuff... (Martin Kersten) 5. Add primary key performance (paulr135) 6. Re: Add primary key performance (Stefan Manegold) 7. Re: COPY INTO: wrong length with backslashed chars? (Daniel Boesswetter) ---------------------------------------------------------------------- Message: 1 Date: Sun, 05 Dec 2010 19:08:01 +0100 From: Daniel Boesswetter <daniel.boesswetter(a)fu-berlin.de> Subject: [MonetDB-users] COPY INTO: wrong length with backslashed chars? To: monetdb-users(a)lists.sourceforge.net Message-ID: <4CFBD501.9030709(a)fu-berlin.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hi all, I'm using MonetDB for the analysis for ~90GB of data which come from a MySQL-based web-application. The data is dumped into CSV-files on a regular basis and then imported into MonetDB with the COPY command. I have experienced some strange behaviour: obviously, MonetDB counts the number of chars for a VARCHAR-field before removing backslashes which are used e.g. for escaping newlines or other backslashes and rejects strings that should actually fit. A simple reproduction is this: 1. In mclient: CREATE TABLE t (x VARCHAR(1)); 2. Create a textfile foo.txt containing two backslashes: \\ 3. In mclient: sql>copy into t from '/tmp/foo.txt'; SQLException:importTable:value '\\' from line 1 field 1 not inserted, expecting type varchar(1) failed to import table Performing exactly the same steps with a VARCHAR(2) column works without errors and results in t.x containg the de-escaped single backslash. I'm using the Oct-2010 release. My current workaround is to enlarge VARCHARs which caused problems in the past, but this is of course no guarantee that it won't happen again. Although I found a few monetdb-bugs entries concerning the COPY command (e.g. about the misleading error message seen above), none seems to deal with my problem. Is this a bug? Best regards, Daniel ------------------------------ Message: 2 Date: Sun, 05 Dec 2010 19:15:36 +0100 From: Stefan de Konink <stefan(a)konink.de> Subject: Re: [MonetDB-users] COPY INTO: wrong length with backslashed chars? To: Communication channel for MonetDB users <monetdb-users(a)lists.sourceforge.net> Message-ID: <4CFBD6C8.30208(a)konink.de> Content-Type: text/plain; charset=ISO-8859-1 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Op 05-12-10 19:08, Daniel Boesswetter schreef: > Hi all, > > I'm using MonetDB for the analysis for ~90GB of data which come from a > MySQL-based web-application. The data is dumped into CSV-files on a > regular basis and then imported into MonetDB with the COPY command. I > have experienced some strange behaviour: obviously, MonetDB counts the > number of chars for a VARCHAR-field before removing backslashes which > are used e.g. for escaping newlines or other backslashes and rejects > strings that should actually fit. > > A simple reproduction is this: > > 1. In mclient: CREATE TABLE t (x VARCHAR(1)); 2. Create a textfile > foo.txt containing two backslashes: \\ 3. In mclient: > sql>copy into t from '/tmp/foo.txt'; > SQLException:importTable:value '\\' from line 1 field 1 not inserted, > expecting type varchar(1) failed to import table > > Performing exactly the same steps with a VARCHAR(2) column works > without errors and results in t.x containg the de-escaped single backslash. > > I'm using the Oct-2010 release. My current workaround is to enlarge > VARCHARs which caused problems in the past, but this is of course no > guarantee that it won't happen again. > > Although I found a few monetdb-bugs entries concerning the COPY > command (e.g. about the misleading error message seen above), none > seems to deal with my problem. > > Is this a bug? What are you escaping? Paste the line that fails ;) Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.16 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkz71sgACgkQYH1+F2Rqwn3AmgCfayhLg6zjXZgvqhV9OHJbj5Kh z64An2xZJy7H1wv3M6hr/SHQaK2C8IM3 =//V/ -----END PGP SIGNATURE----- ------------------------------ Message: 3 Date: Mon, 6 Dec 2010 05:07:46 -0800 (PST) From: Mike De-La-Columnar <mikevermondusa(a)gmail.com> Subject: [MonetDB-users] Some questions regarding parallelism and other stuff... To: monetdb-users(a)lists.sourceforge.net Message-ID: <30366715.post(a)talk.nabble.com> Content-Type: text/plain; charset=us-ascii Hi fellas, If its not too much of a hassle, i have a few questions regarding parallelism & the internal algorithms 1. What is the difference between Data-Flow, and Mitosis? 2. Is there a special reason why the distinct count aggregation is not parallelized just like the other aggregation functions? Is it just a matter of not making it into the release, or is there a fundamental problem here. Note: even though the parallel distinct can be slower in certain distributions, it could still be useful... 3. Are there any plans on supporting parallel frameworks like OpenMP/Intel TBB directly within the low-level MAL functions? Providing data-parallelism on the loop-code level could even increase performance. Or did you already play with it and decided that the performance gain is just not worth it? 4. Is there a way for me to prevent MonetDB server from cleaning up all intermediate BATs between server restarts. I've read here that some intermediates (like join indices etc.) are lost between restarts... is it still correct? Thank you very much for your time, Keep up the amazing job of building the most powerful columnar database out there! -- View this message in context: http://old.nabble.com/Some-questions-regarding-parallelism-and-other-stuff.… Sent from the monetdb-users mailing list archive at Nabble.com. ------------------------------ Message: 4 Date: Mon, 06 Dec 2010 20:13:55 +0100 From: Martin Kersten <Martin.Kersten(a)cwi.nl> Subject: Re: [MonetDB-users] Some questions regarding parallelism and other stuff... To: monetdb-users(a)lists.sourceforge.net Message-ID: <4CFD35F3.1060001(a)cwi.nl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed On 12/6/10 2:07 PM, Mike De-La-Columnar wrote: > > Hi fellas, Hi Mike > > If its not too much of a hassle, i have a few questions regarding > parallelism& the internal algorithms > > 1. > What is the difference between Data-Flow, and Mitosis? See optimizer descriptions: http://monetdb.cwi.nl/MonetDB/Documentation/Optimizer-Toolkit.html Orthogonal issues. > > 2. > Is there a special reason why the distinct count aggregation is not > parallelized just like the other aggregation functions? > Is it just a matter of not making it into the release, or is there a > fundamental problem here. Distinct is a global property, not a local one. A distributed version may easily lead to throwing the complete underlying table around. > > Note: even though the parallel distinct can be slower in certain > distributions, it could still be useful... > > 3. > Are there any plans on supporting parallel frameworks like OpenMP/Intel TBB > directly within the low-level MAL functions? > Providing data-parallelism on the loop-code level could even increase > performance. It could improve, but there should be a clear driving application. > Or did you already play with it and decided that the performance gain is > just not worth it? Depends on the amount of work per byte unit. > > 4. > Is there a way for me to prevent MonetDB server from cleaning up all > intermediate BATs between server restarts. > I've read here that some intermediates (like join indices etc.) are lost > between restarts... is it still correct? All temporaries are indeed lost. Join indices for foreign key checking are retained on disk. Rebuilding hashes is cheaper (mostly) then write/read them from disk > > > Thank you very much for your time, > Keep up the amazing job of building the most powerful columnar database out > there! Thanks for the support !. Martin > > > ------------------------------ Message: 5 Date: Tue, 7 Dec 2010 07:44:14 -0800 (PST) From: paulr135 <romesh135(a)gmail.com> Subject: [MonetDB-users] Add primary key performance To: monetdb-users(a)lists.sourceforge.net Message-ID: <30397515.post(a)talk.nabble.com> Content-Type: text/plain; charset=us-ascii I've loaded a 27GB (~165M rows) file into a table in MonetDB and now I am trying to add a primary key which consists of 4 fields - 2 varchars and 2 ints. It has been running for a few days (since Friday of last week). I've followed the instructions laid out on this post: http://old.nabble.com/MonetDB-SQL:-bulk-loading-large-data-sets---adding-ke… which involves setting the optimizer variable to "nov2009_pipe". I am unable to log in via mclient - I am able to put in my credentials but it just hangs at the "Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2010-SP2)" line. So I have a few questions: 1. Is there any way I can see the status of the alter table ... add primary key command? 2. Is it possible to stop the alter table gracefully? Or will I have to shut the db down? 3. Is there anything I should do in future to speed up adding of primary keys? Thanks. -- View this message in context: http://old.nabble.com/Add-primary-key-performance-tp30397515p30397515.html Sent from the monetdb-users mailing list archive at Nabble.com. ------------------------------ Message: 6 Date: Wed, 8 Dec 2010 09:38:23 +0100 From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> Subject: Re: [MonetDB-users] Add primary key performance To: Communication channel for MonetDB users <monetdb-users(a)lists.sourceforge.net> Message-ID: <20101208083823.GA24931(a)cwi.nl> Content-Type: text/plain; charset=us-ascii On Tue, Dec 07, 2010 at 07:44:14AM -0800, paulr135 wrote: > > I've loaded a 27GB (~165M rows) file into a table in MonetDB and now I am > trying to add a primary key which consists of 4 fields - 2 varchars and 2 > ints. > It has been running for a few days (since Friday of last week). > > I've followed the instructions laid out on this post: > http://old.nabble.com/MonetDB-SQL:-bulk-loading-large-data-sets---adding-ke… > which involves setting the optimizer variable to "nov2009_pipe". Using the nov2009_pipe optimizer pipeline for adding constraint only holds for the Feb2010 release of MonetDB. It should not be required anymore for later releases as of Jun2010-SP1, incl. the latest Oct2010 release. Which version of MonetDB are you using? Which OS are you running on? What kind of manchine/hardware are you running on, in particular how much memory does it have? Can you check what the machine is doing? Is it running out of physical memory and swapping, i.e., is the virtual size of the mserver5 (much) larger than the machine's physical memory size? > I am unable to log in via mclient - I am able to put in my credentials but > it just hangs at the "Welcome to mclient, the MonetDB/SQL interactive > terminal (Jun2010-SP2)" line. > > So I have a few questions: > 1. Is there any way I can see the status of the alter table ... add primary > key command? You can use MonetDB's stethoscope tool to "listen" what the server is doing; however, while it is busy with a single (MAL-)operation, there will not be any output; hence, you might have to wait for some time to see some output. Unless very familiar with MonetDB internals, the output of stethoscope might not be too informative for you. But you're welcome to send us the output to have a look at it. In addition, you can check what the mserver5 is doing as suggested above. > 2. Is it possible to stop the alter table gracefully? Or will I have to > shut the db down? No, unfortunately there is no way to gracefully stop a running query. You'd have to way until it finishes (successfully or with an error), or shut down the DB. > 3. Is there anything I should do in future to speed up adding of primary > keys? In priciple not. We are aware that with data sizes (i.e., total size of all columns that the primary keys consists of) that significantly exceed main memory, adding the key (i.e., checking whether the data fulfills the key condition) does not work as efficiently as it could/should, in particular with multi-column keys --- we're working on that ... You could, though, run the ALTER TABLE statement in trace mode, i.e., `TRACE ALTER TABLE ...`, collect the output and send it to us for analysis; that could help us to improve the creation of large multi-column primary keys. Regards, Stefan > Thanks. > -- > View this message in context: http://old.nabble.com/Add-primary-key-performance-tp30397515p30397515.html > Sent from the monetdb-users mailing list archive at Nabble.com. > > > ------------------------------------------------------------------------------ > What happens now with your Lotus Notes apps - do you make another costly > upgrade, or settle for being marooned without product support? Time to move > off Lotus Notes and onto the cloud with Force.com, apps are easier to build, > use, and manage than apps on traditional platforms. Sign up for the Lotus > Notes Migration Kit to learn more. http://p.sf.net/sfu/salesforce-d2d > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > > > > > > > -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | ------------------------------ Message: 7 Date: Fri, 10 Dec 2010 11:11:34 +0100 From: Daniel Boesswetter <daniel.boesswetter(a)fu-berlin.de> Subject: Re: [MonetDB-users] COPY INTO: wrong length with backslashed chars? To: monetdb-users(a)lists.sourceforge.net Message-ID: <4D01FCD6.9040201(a)fu-berlin.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hi Stefan, > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA512 > > Op 05-12-10 19:08, Daniel Boesswetter schreef: > > Hi all, > > > > I'm using MonetDB for the analysis for ~90GB of data which come from a > > MySQL-based web-application. The data is dumped into CSV-files on a > > regular basis and then imported into MonetDB with the COPY command. I > > have experienced some strange behaviour: obviously, MonetDB counts the > > number of chars for a VARCHAR-field before removing backslashes which > > are used e.g. for escaping newlines or other backslashes and rejects > > strings that should actually fit. > > > > A simple reproduction is this: > > > > 1. In mclient: CREATE TABLE t (x VARCHAR(1)); > > 2. Create a textfile foo.txt containing two backslashes: \\ > > 3. In mclient: > > sql>copy into t from '/tmp/foo.txt'; > > SQLException:importTable:value '\\' from line 1 field 1 not inserted, > > expecting type varchar(1) > > failed to import table > > > > Performing exactly the same steps with a VARCHAR(2) column works without > > errors and results in t.x containg the de-escaped single backslash. > > > > I'm using the Oct-2010 release. My current workaround is to enlarge > > VARCHARs which caused problems in the past, but this is of course no > > guarantee that it won't happen again. > > > > Although I found a few monetdb-bugs entries concerning the COPY command > > (e.g. about the misleading error message seen above), none seems to deal > > with my problem. > > > > Is this a bug? > > What are you escaping? Paste the line that fails ;) > > As I said: try to import a single "\" into a VARCHAR(1) with COPY. Writing "\" does not work because it escapes the newline. Writing "\\" does not work because it is longer than 1 char. Regards, Daniel > Stefan > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v2.0.16 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEAREKAAYFAkz71sgACgkQYH1+F2Rqwn3AmgCfayhLg6zjXZgvqhV9OHJbj5Kh > z64An2xZJy7H1wv3M6hr/SHQaK2C8IM3 > =//V/ > -----END PGP SIGNATURE----- > ------------------------------ ------------------------------------------------------------------------------ ------------------------------ _______________________________________________ MonetDB-users mailing list MonetDB-users(a)lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users End of MonetDB-users Digest, Vol 55, Issue 2 ******************************************** [View Less]

1 0

[MonetDB-users] COPY INTO: wrong length with backslashed chars?
by Daniel Boesswetter 10 Dec '10

10 Dec '10

Hi all, I'm using MonetDB for the analysis for ~90GB of data which come from a MySQL-based web-application. The data is dumped into CSV-files on a regular basis and then imported into MonetDB with the COPY command. I have experienced some strange behaviour: obviously, MonetDB counts the number of chars for a VARCHAR-field before removing backslashes which are used e.g. for escaping newlines or other backslashes and rejects strings that should actually fit. A simple reproduction is this: 1. … [View More]

3 4

[MonetDB-users] Some questions regarding parallelism and other stuff...
by Mike De-La-Columnar 06 Dec '10

06 Dec '10

Hi fellas, If its not too much of a hassle, i have a few questions regarding parallelism & the internal algorithms 1. What is the difference between Data-Flow, and Mitosis? 2. Is there a special reason why the distinct count aggregation is not parallelized just like the other aggregation functions? Is it just a matter of not making it into the release, or is there a fundamental problem here. Note: even though the parallel distinct can be slower in certain distributions, it could still … [View More]

2 1

[MonetDB-users] MonetDB/XQuery Installation
by Rob Fynes 01 Dec '10

01 Dec '10

Hello, Apologies if this is the improper place to be asking this. I'm a new user to MonetDB, and I'm having trouble installing MonetDB/XQuery on Ubuntu 10.04 32-bit. Actually, the installation did seem to succeed (running the auto install script from the super source tarball) and I can start MServer, but with the error: # MonetDB Server v4.40.2 # based on GDK v1.40.2 # not released # Copyright (c) 1993-July 2008, CWI. All rights reserved. # Copyright (c) August 2008-2010, MonetDB B.V.. All … [View More]

3 5