February 2012 - users-list

[MonetDB-users] failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details
by Tony 15 Feb '12

15 Feb '12

Hi folks, How can I recover from a crashed database? I got this error when I restart a running database 'dw', Do you have any ideas on this? 2012-02-15 13:59:28 MSG control[27007]: (local): served status list 2012-02-15 13:59:28 MSG merovingian[27007]: startup of database under maintenance 'dw' forced 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' has crashed after start on 2012-02-15 13:58:47, attempting restart, up min/avg/max: 1d/1d/1d, crash average: 1.00 1.00 0.97 (45-1=44) 2012-02-15 13:59:28 MSG dw[27831]: arguments: /usr/lib/monetdb/bin/mserver5 --set gdk_dbfarm=/data/warehouse --dbname=dw --set merovingian_uri=mapi:monetdb://monet00:50000/dw --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/warehouse/dw/.mapi.sock --set monet_vault_key=/data/warehouse/dw/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB 5 server v11.7.5 "Dec2011" 2012-02-15 13:59:28 MSG dw[27831]: # Serving database 'dw', using 24 threads 2012-02-15 13:59:28 MSG dw[27831]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-15 13:59:28 MSG dw[27831]: # Found 23.533 GiB available main-memory. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) 1993-July 2008 CWI. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-15 13:59:28 MSG dw[27831]: # Visit http://www.monetdb.org/ for further information 2012-02-15 13:59:28 MSG dw[27831]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/warehouse/dw/.mapi.sock 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB/SQL module loaded 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' (27831) was killed by signal SIGSEGV 2012-02-15 13:59:28 ERR control[27007]: (local): failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details >>The following is my platform information: MonetDB 5 server v11.7.5 "Dec2011" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 23.5GiB available memory, 24 available cpu cores Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: root@monet00 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 Thanks in advance, Tony

2 1

[MonetDB-users] Odd Single Thread Only Processing in Dec 2011 Release
by Henry Addington 14 Feb '12

14 Feb '12

Hi: We are testing Dec 2011 Release to upgrade from Oct 2010 SP1 release on CentOS 5.2 64-bit with 100+ GB memory. We have a BI tool that users use to generate various reports. We have access to the SQL statements that get generated by the BI tool and sent to the MonetDB. So, we know exactly the SQL statements that are being sent to MonetDB. One very odd thing that we are seeing the December release is that the reports from the BI tool are very, very slow (x10 - x15 from Oct releasee). It is obvious why looking at the server resource use. It looks like only 1 cpu is being used for all the queries! However, if I take the SQL statements generated by the BI tool and run them in DBVisualizer, all 20 cpu cores are used and the results return quickly. I am at a loss as to why we are seeing such different performance from identical SQL statements... Is there a configuration setting or something in the December release that we overlooked? Can you please help us troubleshoot? Thank you. HS

2 3

[MonetDB-users] select distinct ... where algorithm
by zack_pcd 13 Feb '12

13 Feb '12

I am seeing the following: select count(*) from f_sorted; +-----------+ | L7 | +===========+ | 102612097 | +-----------+ 1 tuple (0.988ms) select count(*) from f_sorted where yyyymm='201110'; +----------+ | L10 | +==========+ | 23505343 | +----------+ 1 tuple (1.0s) select count(distinct uuid) from f_sorted where yyyymm='201110'; +---------+ | L6 | +=========+ | 5941581 | +---------+ 1 tuple (23.8s) Very fast thus far! However: copy (select distinct(uuid) from f_sorted where yyyymm='201110') into '/home/user/filename' USING DELIMITERS ',','\n','"' NULL as 'null'; This has been running for hours and has yet to start writing to the file. I get this when I explain the query: +----------------------------------------------------------------------------------------+ | mal | +========================================================================================+ | function user.s5_3{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_22:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",0); | | X_23 := algebra.uselect(X_22,"201110":str); | | X_20:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",2); | | X_24 := algebra.kdifference(X_23,X_20); | | X_21 := algebra.uselect(X_20,"201110":str); | | X_25 := algebra.kunion(X_24,X_21); | | X_18:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",1); | | X_19 := algebra.uselect(X_18,"201110":str); | | X_26 := algebra.kunion(X_25,X_19); | | X_16:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","f_sorted",1); | | X_17 := bat.reverse(X_16); | | X_27 := algebra.kdifference(X_26,X_17); | | X_28 := algebra.markT(X_27,0@0:oid); | | X_29 := bat.reverse(X_28); | | X_10:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",0); | | X_8:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",2); | | X_12 := algebra.kdifference(X_10,X_8); | | X_13 := algebra.kunion(X_12,X_8); | | X_5:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",1); | | X_14 := algebra.kunion(X_13,X_5); | | X_30 := algebra.leftjoin(X_29,X_14); | | (ext44,grp42) := group.done(X_30); | | X_33 := bat.mirror(ext44); | | X_34 := algebra.leftjoin(X_33,X_30); | | X_35 := sql.resultSet(1,1,X_34); | | sql.rsColumn(X_35,"sys.f_sorted","uuid":str,"varchar",2048,0,X_34); | | X_43 := io.stdout(); | | sql.exportResult(X_43,X_35); | | end s5_3; | +----------------------------------------------------------------------------------------+ 31 tuples (1.350ms) Is it doing something wrong? I don't expect the query to take so long (hours) to produce the output, especially since the distinct count is also very fast. The copy-into *seems* to be irrelevant to the problem, but I decided to include it just in case, as that's how I am using the query. -- View this message in context: http://old.nabble.com/select-distinct-...-where-algorithm-tp33297578p332975… Sent from the monetdb-users mailing list archive at Nabble.com.

4 6

[MonetDB-users] False dependency
by Stathis Gkotsis 10 Feb '12

10 Feb '12

Hi all, trying to install: libmonetdb-client-dev_11.7.5-20120120_amd64.deb on Ubuntu 10.04, I see that there is a wrong dependency on libmonetdb-client1. Is there a way around this? Thank you.

1 0

[MonetDB-users] re cycler_pipe and cracker_pipe
by zack_pcd 08 Feb '12

08 Feb '12

Is it possible to have a mix of these pipe's optimizers so you get the benefit of both? Or are these two pipes incompatible? (recycler_pipe and cracker_pipe) -- View this message in context: http://old.nabble.com/recycler_pipe-and-cracker_pipe-tp33282420p33282420.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] LIMIT clause not recognized in nested query in FROM clause
by Viktor Rosenfeld 07 Feb '12

07 Feb '12

Hi, it appears that clauses like LIMIT or OFFSET are not allowed if the query is used inside a FROM clause. The following query throws an error: SELECT * FROM ( SELECT id FROM corpus LIMIT 10 ) AS inner_table; The error message is: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * Cheers, Viktor

1 0

[MonetDB-users] How can I execute SQL via mclient directly?
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I'm pretty new for Monetdb, As per the introduce of the online document, I have to type the password on the command line window when I want to execute SQL via the mclient program. It there a way like this which can be used in Mysql: "mysql --user --password databasename -e SQL" in monetdb? I didn't found the argument that can used for the "password" in mclient. Thanks, Tony

2 4

[MonetDB-users] Can't drop "global temporary table" in SCHEMA tmp
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I got a problem when I trying to drop a global temporary table in my database, I got following errors: sql>drop table tmp.test; 42S02!DROP TABLE: no such table 'test' I got the same error as above when I trying to execute a "select" query on that table, It's so weird, cause the table "test" was been there: sql>\d TABLE tmp.test The even more weird things happened when I execute following command: sql>create global temporary table tmp.test(id int); operation successful (1.888ms) sql>\d TABLE tmp.test sql>\d test table tmp.test is not unique, corrupt catalog? sql>\d TABLE tmp.test sql>drop table tmp.test more>; operation successful (1.470ms) sql>\d TABLE tmp.test Please pay attention on this line "table tmp.test is not unique, corrupt catalog?". Do anybody can help me out of here? Thanks, Tony

1 0

[MonetDB-users] Batch sql inserts in monetdb
by Federico Dayan 01 Feb '12

01 Feb '12

Hello all, I'm working in a project and we are planning to use monetdb. Our Data: We have around 15 millon users and 6,000 columns. Our Problem We need to be able to query specific log data. We use Hadoop to massage the data and create a sql file with sql inserts (around 15 millon insert with 3000 columns) Out Question Is it efficent to run a command like. By efficent I refe:. betweent 1 to 6 hours efficent, after that, a big problem. monetdb -u ....... < my_sql_insert.sql with 15 millon inserts and between 1000 to 3000 columns? (I'm not always going to create insert for the 6000 columns) could monetdb handle multiple of those command if a split the file. monetdb -u ....... < my_sql_insert_1.sql monetdb -u ....... < my_sql_insert_2.sql monetdb -u ....... < my_sql_insert_3.sql ... Is SQL COPY more efficient than a sql scrip file with inserts? BAT files is it significant faster ? I'm trying to avoid this approach since it seems a little bit obscue. any other suggestion? I'm not able to run all this test and get the info myself becuase I will not have a monetdb ready soon. Thank you very much and I would be glad to send back an email with the results I got and contribute in that way. Federico

4 3