January 2011 - users-list

[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 signatures t where fid = ( select fid from signatures ti where ti.guid = t.guid order" So it appears to not like the ORDER BY term, and I can't figure out why. I am trying to grab the second element (after ordering by D0) based on distinct GUIDs. The query iteself is based on the answer for a question from stackoverflow that I posted: http://stackoverflow.com/questions/4634068/sql-query-with-order-by-part-2 Can anyone help me figure this out? The syntax looks fine to me. By the way, I have "D0 AS ed" because D0 will eventually be replaced with a simple math operator (i.e., D0/D1). Many thanks, Brett

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 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.

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: ========= 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

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 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

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 scale ASC LIMIT 1 1; However, these both fail in MonetDB. According to: http://monetdb.cwi.nl/SQL/Documentation/Queries.html#Queries it looks like it should be supported. Does anyone have any insight as to how to grab the second record in MonetDB (I.e., limiting the record results to one with an offset of one)??? I am at a loss here. Cheers, Brett

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 following error (it used to work): IOException:streams.open:Could not open file ERROR: mnstr_open: could not open file '/var/www/loadstats/pagecounts-20110102-160000' The script is started through: sh importintodb.sh /var/www/loadstats/pagecounts-20110102-160000 Does anyone has some thoughts on this, or should I report a bug? I would really like to use commandline, as the hourly imports are 6M records big... The .monetdb file is placed locally and automatically authenticates with monetdb (admin). Lang=SQL, history=yes Regards Lex Slaghuis

2 1