February 2009 - users-list

[MonetDB-users] Re naming a column via MAL/SQL
by Alfred Nordman 03 Feb '09

03 Feb '09

Hi guys, Is there a way to rename an existing column within a table via SQL? (the RENAME COLUMN syntax is not supported as it seems) and if not, can i do it via MAL? I tried to use MAL as follows, but after i do, the DB seems to get corrupt when i try to access the specific table. include sql; sql.init(); bat1 := sql.bind("sys","t1","Field1",0); bat.setName(bat1,"Field2"); bat.setPersistent(bat1,true); bot.commit; Thanks & Best Regards... -- View this message in context: http://www.nabble.com/Renaming-a-column-via-MAL-SQL-tp21807788p21807788.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Logs & Corrupt databases
by David Svenson 02 Feb '09

02 Feb '09

Hi, Every once in a while I get this message in the server: !ERROR: Please move away <log folder> and its corresponding dbfarm. !mvc_init: unable to create system tables When I delete the logs from the log folder, the server starts normally and the database is ok. Any ideas on what the reason for that might be? Is it possible to disable logging altogether? Thanks, David. -- View this message in context: http://www.nabble.com/Logs---Corrupt-databases-tp21792046p21792046.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Searching for possibilities to reduce the fluctuation in insertion times.
by John van Schie (DT) 02 Feb '09

02 Feb '09

Hi, We use MonetDB v4.26.4 (Nov 2008-sp2) for our application with the XQuery frontend. Often, queries are executed very fast, but sometimes, execution of the same query takes significantly more time. For example: We insert a XML document of 1.4Mb. with a maximum depth of 4. The document is structured as follows: <rootelement> <item> <child1> <sha1>sha1-value</sha1> <md5>md5-value</md5> </child1> </item> ... </rootelement> We have 8k 'item' elements. Often the insertion of this document is performed in ~0.5s, which is fine. But when the system is performing IO (e.g. copying a large file), the insertion can take up to 6 minutes. So I have two questions: 1) What is the cause of this large difference in execution times; 2) Is there a way to configure MonetDB (or the server) in such a way that the difference between the 'fast' and 'slow' is smaller. Regards, John van Schie

3 7

[MonetDB-users] sql performance - in vs between
by dariuszs 01 Feb '09

01 Feb '09

Hi, I know it's probably a trivial question but after looking at the following trace - is there a way to make sql 'in' behave like sql 'between', make *algebra.semijoin behave like **algebra.uselect*? Thanks for you help. Dariusz. :0[0]:W Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25; :13172[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _10 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_20266>:bat[:oid,:str][0]); ] [ 0 usec _3 := nil; ] [ 15000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _13 := algebra.kdifference(<tmp_20616>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_20617>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _13 := nil; ] [ 0 usec _11 := nil; ] [ 15000 usec _15 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _16 := bat.reverse(<tmp_20253>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 0 usec _17 := algebra.kdifference(<tmp_20616>[96240405],<~tmp_20253>[0]); ] [ 15000 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_20617>[96240405]); ] [ 0 usec _17 := nil; ] [ 15000 usec _19 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_20506>:bat[:oid,:str][1],"F",true); ] [ 0 usec bat.append(<tmp_20506>:bat[:oid,:str][2],"M",true); ] [ 16000 usec _25 := bat.reverse(<tmp_20506>:bat[:oid,:str][2]); ] [ 15000 usec _19 := nil; ] [ 10782000 usec _26 := *algebra.semijoin(<~tmp_20617>[96240405],<~tmp_20506>[2]); *] [ 15000 usec _18 := nil; ] [ 16000 usec _25 := nil; ] [ 15000 usec _27 := bat.reverse(<tmp_20620>[93838869]); ] [ 0 usec _26 := nil; ] [ 16000 usec _29 := algebra.markT(<~tmp_20620>[93838869],0@0); ] [ 16000 usec _27 := nil; ] [ 0 usec _30 := bat.reverse(<tmp_20617>[93838869]); ] [ 15000 usec _29 := nil; ] [ 453000 usec _31 := algebra.join(<~tmp_20617>[93838869],<tmp_20616>[96240405]); ] [ 63000 usec _30 := nil; ] [ 0 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_20621>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_20542>[2]); ] [ 15000 usec ext37 := nil; ] [ 0 usec _35 := algebra.join(<tmp_20620>[2],<tmp_20621>[93838869]); ] [ 47000 usec _31 := nil; ] [ 344000 usec _36 := aggr.count(<tmp_20616>[93838869],<tmp_20616>[93838869],<tmp_20620>[2]); ] [ 31000 usec grp35 := nil; ] [ 16000 usec _34 := nil; ] [ 15000 usec _37 := sql.resultSet(2,1,<tmp_20544>[2]); ] [ 0 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0,<tmp_20544>[2]); ] [ 16000 usec _35 := nil; ] [ 16000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0,<tmp_20545>:bat[:oid,:wrd][2]); ] [ 15000 usec _36 := nil; ] [ 0 usec _45 := io.stdout(); ] &1 0 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 16000 usec sql.exportResult(137554336,0,""); ] [ 13172000 usec user.s0_2("F","M"); ] :13203[0]:R :39062[0]:W Strace select f25,count(*) from trw100 where f25 between 'F' and 'M' group by f25; :43156[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 1750000 usec _8 := *algebra.uselect(<tmp_5213>:bat[:oid,:str][96240405],"F","M",true,true);* ] [ 15000 usec _10 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _12 := algebra.uselect(<tmp_20266>:bat[:oid,:str][0],"F","M",true,true); ] [ 16000 usec _13 := algebra.kunion(<tmp_20620>[93838869],<tmp_20621>[0]); ] [ 16000 usec _8 := nil; ] [ 15000 usec _12 := nil; ] [ 0 usec _14 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _16 := algebra.kdifference(<tmp_20616>[93838869],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _13 := nil; ] [ 0 usec _17 := algebra.uselect(<tmp_61>:bat[:oid,:str][0],"F","M",true,true); ] [ 15000 usec _18 := algebra.kunion(<tmp_20621>[93838869],<tmp_20617>[0]); ] [ 16000 usec _16 := nil; ] [ 15000 usec _17 := nil; ] [ 0 usec _19 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _20 := bat.reverse(<tmp_20253>:bat[:oid,:oid][0]); ] [ 16000 usec _19 := nil; ] [ 0 usec _21 := algebra.kdifference(<tmp_20616>[93838869],<~tmp_20253>[0]); ] [ 15000 usec _18 := nil; ] [ 16000 usec _20 := nil; ] [ 16000 usec _23 := algebra.markT(<tmp_20617>[93838869],0@0); ] [ 0 usec _21 := nil; ] [ 15000 usec _24 := bat.reverse(<tmp_20616>[93838869]); ] [ 16000 usec _23 := nil; ] [ 0 usec _25 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_20266>:bat[:oid,:str][0]); ] [ 15000 usec _3 := nil; ] [ 16000 usec _10 := nil; ] [ 16000 usec _26 := algebra.kdifference(<tmp_20617>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _25 := nil; ] [ 15000 usec _27 := algebra.kunion(<tmp_20621>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _26 := nil; ] [ 0 usec _14 := nil; ] [ 453000 usec _28 := algebra.join(<~tmp_20616>[93838869],<tmp_20617>[96240405]); ] [ 31000 usec _24 := nil; ] [ 16000 usec _27 := nil; ] [ 922000 usec (ext34,grp32) := group.new(<tmp_20621>[93838869]); ] [ 15000 usec _31 := bat.mirror(<tmp_20542>[2]); ] [ 16000 usec ext34 := nil; ] [ 0 usec _32 := algebra.join(<tmp_20620>[2],<tmp_20621>[93838869]); ] [ 47000 usec _28 := nil; ] [ 344000 usec _33 := aggr.count(<tmp_20617>[93838869],<tmp_20617>[93838869],<tmp_20620>[2]); ] [ 31000 usec grp32 := nil; ] [ 16000 usec _31 := nil; ] [ 15000 usec _34 := sql.resultSet(2,1,<tmp_20544>[2]); ] [ 16000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0,<tmp_20544>[2]); ] [ 0 usec _32 := nil; ] [ 15000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0,<tmp_20545>:bat[:oid,:wrd][2]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _42 := io.stdout(); ] &1 1 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 15000 usec sql.exportResult(137554336,1,""); ] [ 4094000 usec user.s1_2("F","M"); ] :43187[0]:R :253953[0]:W Connection closed

3 3

[MonetDB-users] Building 64 bits
by David Svenson 01 Feb '09

01 Feb '09

Hi, I followed the instructions for building MonetDB in 64-bit, however, after finishing the build process and trying to run the server, I get this error message: !FATAL: BBPinit: database created with incompatible server: expected pointer size 8, got 4, expected OID size 4, got 4. I added "bits=64" to all my nmakes, but I guess something is still missing. Are there any other flags I need to raise in the build process that I might have missed? Thanks, David. -- View this message in context: http://www.nabble.com/Building-64-bits-tp21772757p21772757.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1