January 2010 - users-list

[MonetDB-users] problems with the interpretation of the query history
by Gerrard_56 10 Jan '10

10 Jan '10

Hi. I’m a new MonetDB user and I use the MonetDB server v5.14.2 with the MonetDB/SQL module v2.32.2 on a windows xp platform. I have some problems with the interpretation of the queryhistory and the callhistory. My first question is: What is the meaning of the parse and the optimize attributes in the queryhistory. Is the optimize attribute used to store the time which the front-end needs to parse the sql statement into a mal plan respectively is the optimize attribute used to store the time used for the optimization done for the mal plan. I’m asking because couldn’t find an explanation in the sql manual. My next questions concern the xtime and the rtime attributes of the callhistory. Sometimes for some queries the xtime is 0 usec. Does this indicate that the query was already executed once before and therefore there isn’t a preparation time needed for the result set? Sometimes for some queries only the rtime is 0 usec but the the xtime is > 0 usec. What does this means? There is a preparation time for a result set but there is no time needed to construct and return the result set to the user. For me this makes no sense. Sometimes there is the xtime and the rtime 0 usec. Does this mean that the query never runs in the database engine? Does somebody know the reason for this behavior? Lust but not least in the sql manual you can read that all storage is in K/M/G bytes. But where can I find out if K bytes M bytes or G bytes are used? Thank you for any help Gerrard (I apologize for any grammar and spelling mistakes in this posting but my English is not the best.) -- View this message in context: http://old.nabble.com/problems-with-the-interpretation-of-the-query-history… Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] sql execution time
by Gerrard_56 09 Jan '10

09 Jan '10

Hi. I would like to compare the performance of MonetDB with the performance of an Oracle DB by using the TPC-H benchmark. I use the MonetDB server v5.14.2 with the MonetDB/SQL module v2.32.2 on a windows xp platform. On the MonetDB website (http://monetdb.cwi.nl/SQL/Benchmark/TPCH/) I read that they used the mclient with the –t argument in order to obtain the timings for the queries. I tried the same but the displayed time for queries which return several 100 rows is very high. For example the TPC-H query nr. 3 with returns 11620 row the time was 13796.174 msec. If I execute the same query in the db visualize it just needs 0.864 sec for the result. I think the problem with the mclient is that it needs a lot of time to display the result of the queries. Is there a better alternative to the –t argument of the mclient to get a useful time result? Thank you for any help Gerrard -- View this message in context: http://old.nabble.com/sql-execution-time-tp27088775p27088775.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Ebuilds for MonetDB
by W-Mark Kubacki 07 Jan '10

07 Jan '10

Hello. I'd like to thank you for MonetDB by providing fresh ebuilds for the suite. And yes, I did find Fabian Groffen's, but they were outdated and removed from main tree. So, find new ones in my Gentoo overlay [1] along with binaries for amd64 and arm on my binhosts [2,3]. Unfortunately Monet features a lot unused configure flags and deviates from the Filesystem Hierarchy Standard (FHS) 2.3 [4] (*), therefore I had to put an extra efford to sieve the first out and have files installed corretly. If you happen to find something I missed, don't hesitate to drop me a line or two. -- Beste Grüße W-Mark Kubacki http://mark.ossdl.de/ [1] http://git.ossdl.de/?p=ossdl-overlay.git;a=summary [2] http://binhost.ossdl.de/x86_64-pc-linux-gnu/ , http://binhost.ossdl.de/x86_64-pc-linux-gnu/Packages [3] http://binhost.ossdl.de/armv5tel-softfloat-linux-gnueabi/ , http://binhost.ossdl.de/armv5tel-softfloat-linux-gnueabi/Packages [4] http://www.pathname.com/fhs/

3 4

[MonetDB-users] vaultkey, embedded, and merovingian.
by Guillaume Yziquel 05 Jan '10

05 Jan '10

Hi. I've been creating a database from a call to monetdb_sql() from libembeddedsql5.so, from my OCaml binding. After that, I start up a merovingian process and I do the following: > yziquel@seldon:~/git/ocaml-monetdb5$ monetdb status > name state uptime health last crash > testdb stopped 100%, 49s - > yziquel@seldon:~/git/ocaml-monetdb5$ monetdb start testdb > starting database 'testdb'... FAILED > start: starting 'testdb' failed: cannot start database 'testdb': no .vaultkey found (did you create the database with `monetdb create testdb`?) I'm quite baffled by the error I just got. What is this .vaultkey problem? All the best, -- Guillaume Yziquel http://yziquel.homelinux.org/

3 5

[MonetDB-users] Inserting '\' into varchar(1)
by Alex Bo. 04 Jan '10

04 Jan '10

Hi, I think I found a bug: I can't seem to insert the character '\' into Monet with COPY INTO when I use a single character column: 1. I have a text file that only contains this: '1,"\\"' 2. create table test1 (f1 int, f2 varchar(1)) 3. copy into test1 from 'C:\test1.txt' using delimiters ',','\r\n','"' null as ''; I get: SQLException:sql:value '\\' from line 1 field 2 not inserted, expecting type str If I change the field size from varchar(1) to varchar(2) it works. (Aug09 & Nov09 on Windows) Is this a bug? Thanks & happy new year. -- View this message in context: http://old.nabble.com/Inserting-%27%5C%27-into-varchar%281%29-tp27010393p27… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Nightly builds are still OK?
by Guillaume Yziquel 04 Jan '10

04 Jan '10

Hi. I've been trying to fetch nightly builds, but on the webpage http://monetdb.cwi.nl/testing/projects/monetdb/Current/.DailyBuilds./ it seems that the last nightly build was one week or so ago. Are nightly builds still working fine? Just to know... All the best, -- Guillaume Yziquel http://yziquel.homelinux.org/

2 1

[MonetDB-users] Error on "rank() over (order by sum(.."
by Makoto YUI 04 Jan '10

04 Jan '10

Hi, The following query yields "SELECT: subquery result missing" error on Nov2009-SP1. select s_name, sum(score), rank() over (order by sum(score) desc) from score_table group by s_name -- preparation create table score_table ( s_name varchar(20), subject varchar(10), score int ); insert into score_table values('foo','english',70); insert into score_table values('foo','history',50); insert into score_table values('foo','math',60); insert into score_table values('bar','english',45); insert into score_table values('bar','history',75); It seems allowed to use aggregation functions in "over(..)" in other databases. http://www.google.com/search?q="rank()+over+(order+by+sum(" Thanks, Makoto

1 0

[MonetDB-users] Limitation in "create table .. as subquery"
by Makoto YUI 04 Jan '10

04 Jan '10

Hello, create table t1 (f1 int); insert into t1 values (1); insert into t1 values (2); create table t2 as (select * from t1 limit 1) with data; The above "create table" caused the following syntax error on the Limit clause. syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "create table t2 as (select * from t1 limit" Is this an expected behavior? This "subquery" issue might be related. http://sourceforge.net/tracker/?func=detail&atid=482468&aid=2620437&group_i… I'm using Nov2009-SP1 on Linux. Thanks, Makoto

3 7

[MonetDB-users] Problem with BSS initialisation.
by Guillaume Yziquel 03 Jan '10

03 Jan '10

Hello. I've spent quite some time lately debugging my issue with OCaml binding of MonetDB libraries: http://www.mail-archive.com/caml-list@yquem.inria.fr/msg05449.html It seems that the BSS is not properly initialised to 0 when dynamically loaded. It seems that the code in mal_box.c relies on the fact that BSS is initialised to 0 when loaded. I'm still trying to figure out how exactly is the libmonetdb5.so library loaded. This is still an obscure point. ltrace did not provide useful information. Advice welcome. -- Guillaume Yziquel http://yziquel.homelinux.org/

2 2

[MonetDB-users] Multiple core usage on Windows
by Alex Bo. 03 Jan '10

03 Jan '10

Hi, Does MonetDB split a single query plan over multiple CPU cores when its possible? (maybe the DataFlow optimizer?) For example, the following query could be executed by all cores in parallel: SELECT field1,SUM(field2) FROM Table1 GROUP BY field1 When i tried to see if it does, i got some weird results: (I am running MonetDB on Windows, on a quad core machine) When I set '--set gdk_nr_threads=1', queries never seem to use more than 25%. (seems logical since only one CPU core is being deployed) When I set '--set gdk_nr_threads=2', queries use up to 50% CPU, but the query is ~15% slower. When I set '--set gdk_nr_threads=4', queries use up to 98% CPU, but the query is take twice as long as with 1 thread. So, my initial assumption that more CPU utilization (more cores over the same query) will result faster query results (where applicable) is wrong. Can it be something specific to the windows platform? or maybe single query execution over multiple cores doesn't work without a specific optimizer flag enabled?... or maybe is it just a bug... Thanks. -- View this message in context: http://old.nabble.com/Multiple-core-usage-on-Windows-tp27002955p27002955.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1