March 2009 - users-list

Re: [MonetDB-users] too deep recursion
by Nils Grimsmo 09 Mar '09

09 Mar '09

Hi, I just upgraded from the Augst to the Noveber super-ball, and the performance has worsened badly. Example queries on dblp.xml (441 MB): Q0: count(/dblp//author[text()="Michael Stonebraker"]) Q1: count(/dblp/*/author[text()="Michael Stonebraker"]) Query time in milliseconds: August November Q0 1100 4867 Q1 3993 17999 I have compiled with --enable-optimise both times. I query with: mclient --language=xquery --algebra --time < $QUERYFILE Is this performance degradation expected? If so, why? BTW: Is there any way of finding how much disk space a collection uses? Thank you for contributing free software! Klem fra Nils -- http://www.idi.ntnu.no/~nilsgri/ Why is this thus? What is the reason of this thusness? - Artemus Ward

3 4

[MonetDB-users] Kettle and MonetDB
by denvro 09 Mar '09

09 Mar '09

Hello, I have just installed Kettle 3.1 on my Archlinux machine. I wanted to use it with MonetDB but when i try to create a JDBC connection i get the following message: Error connecting to database [MonetDB] : org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database Error connecting to database: (using class nl.cwi.monetdb.jdbc.MonetDriver) Server challenge string unusable! Challenge contains too few tokens: !an internal error has occurred, please try again later I have merovingian running in the back and created a database "pentaho". See status below: bash-3.2# monetdb status -l pentaho pentaho: location: /usr/local/MonetDB/var/MonetDB5/dbfarm/pentaho database name: pentaho state: running locked: no scenarios: mal sql msql connections: mapi:monetdb://myhost:50001/ start count: 3 stop count: 2 crash count: 0 current uptime: 51m 10s average uptime: 13m 12s maximum uptime: 14m 42s minimum uptime: 11m 41s last crash: (unknown) last start: 2009-02-21 13:03:00 average of crashes in the last start attempt: 0 average of crashes in the last 10 start attempts: 0.00 average of crashes in the last 30 start attempts: 0.00 Can anybody help me with this. I already use the lates .jar (monetdb-1.9-jdbc). Kind Regards, Dennis -- View this message in context: http://www.nabble.com/Kettle-and-MonetDB-tp22141036p22141036.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Not working xquery
by Hans van Rijswijk (DT) 06 Mar '09

06 Mar '09

Hi all, Why does this xquery work: let $result := doc("document.xml")//*[properties] let $astypes := distinct-values(for $n in $result/ancestor-or-self::*[properties] return name($n)) return $astypes <> Whereas these xqueries don't: let $result := doc("document.xml")//properties/.. let $astypes := distinct-values(for $n in $result/ancestor-or-self::*[properties] return name($n)) return $astypes <> let $result := doc("document.xml")//properties/parent::* let $astypes := distinct-values(for $n in $result/ancestor-or-self::*[properties] return name($n)) return $astypes <> The latter two xqueries return an error: MAPI = monetdb@host:50000 QUERY = let $result := doc("document.xml")//properties/parent::* ERROR = !ERROR: ancestor-or-self: illegal data in size column or context list. !ERROR: PFll_ancestor_or_self: operation failed. Which MonetDB expert can explain? Regards, Hans v R

2 1

[MonetDB-users] vertically fragment a table manually
by Brightwin 05 Mar '09

05 Mar '09

hi all, Is it possible to vertically fragment a table manually in MonetDB,instead of the Binary Association Table which is automatically created by it. thanks in advance Brightwin. ----------------------------------------- This email was sent using TCEMail Service. Thiagarajar College of Engineering Madurai - 625015 (India)

3 2

[MonetDB-users] implementing high performance time series schemas in monetDB
by Tom H 05 Mar '09

05 Mar '09

Hi, I have recently been using the TSdbi/MySQL library in R, which allows mapping between time series matrices and data stored in a mysql backend - its actually pretty good, I've made loads of progress in a few days... However I now now looking at scaling it up to many parallel sources of time series data, and my limited understanding of implementing this type of schema suggests that column orientated databases like monetDB should perform better than MySQL for this type of data. (?) The schema suggested as the default for storing irregular time series is as follows; create table T ( id VARCHAR(40), date DATETIME, v double DEFAULT ); with an index on the ident and the DATETIME field; CREATE INDEX Tindex_id ON T (id); CREATE INDEX Tindex_date ON T (date); So I am looking to support 500+ writes per second, while entertaining a whole bunch of queries of time periods like; SELECT date, v FROM T WHERE id = 'series1' order by date select date, v from T WHERE date > NOW() - INTERVAL 1 WEEK AND date < NOW(); I was hoping for some advice on whether monetDB is suitable for this type of data, and performance suggestions to get it blisteringly fast!! Thanks, Tom

1 0

[MonetDB-users] too deep recursion
by Lazaros Koromilas 02 Mar '09

02 Mar '09

Hi all, We are trying to annotate some XML data with MonetDB/XQuery. For this I run some big queries, that contain unions of many Xpath expressions (say 100). I get the following error: ERROR = !fatal error: aborted too deep recursion Tried to use the option declare option pf:recursion-depth "<number>"; I see no difference in bahaviour. I also call mclient with -G, though I don't know if it's needed. MonetDB Server is v4.26.2 and default back-end is 'algebra'. Any help with this? Thank you, Lazaros. -- http://www.ics.forth.gr/~koromil

3 4

[MonetDB-users] SUM of NULLs is 0?
by kelas 01 Mar '09

01 Mar '09

Dear MonetDB-users, a complete newbie to MonetDB, I can't really tell whether it is a bug, feature or I'm just getting it all wrong. A script below illustrates some weird NULL handling behaviour found in Nov2008-SP2: -- years under audit CREATE TABLE BUDGET_PERIOD (BP smallint NOT NULL); INSERT INTO BUDGET_PERIOD VALUES (1998); INSERT INTO BUDGET_PERIOD VALUES (1999); INSERT INTO BUDGET_PERIOD VALUES (2000); INSERT INTO BUDGET_PERIOD VALUES (2001); INSERT INTO BUDGET_PERIOD VALUES (2002); INSERT INTO BUDGET_PERIOD VALUES (2003); INSERT INTO BUDGET_PERIOD VALUES (2004); INSERT INTO BUDGET_PERIOD VALUES (2005); -- annual expenditure data CREATE TABLE EXPENDITURE (BP smallint NOT NULL, AMOUNT DECIMAL(15,2)); INSERT INTO EXPENDITURE VALUES (1999, 0.00); INSERT INTO EXPENDITURE VALUES (2000, 1.00); INSERT INTO EXPENDITURE VALUES (2001, 2.00); INSERT INTO EXPENDITURE VALUES (2002, 3.00); INSERT INTO EXPENDITURE VALUES (2003, 4.00); INSERT INTO EXPENDITURE VALUES (2004, NULL); -- calculate past years expenditure as of year: expected to be NULL for 1998, returns 0 SELECT A.BP, SUM( B.AMOUNT ) AS TOTAL_EXPD_ASOF FROM BUDGET_PERIOD A LEFT JOIN EXPENDITURE B ON (A.BP >= B.BP) GROUP BY A.BP ORDER BY A.BP -- same as above using CASE: returns all nulls (?!) SELECT A.BP, SUM( CASE WHEN A.BP >= B.BP THEN B.AMOUNT ELSE NULL END ) FROM BUDGET_PERIOD A, EXPENDITURE B GROUP BY A.BP go Regards, Vladimir

2 1

[MonetDB-users] [Fwd: Re: monetdb, xquery and perl]
by Martin Kersten 01 Mar '09

01 Mar '09

-------- Original Message -------- Subject: Re: monetdb, xquery and perl Date: Fri, 27 Feb 2009 10:35:15 +0100 From: francois guérin <replonk(a)gmail.com> To: Martin.Kersten(a)cwi.nl References: <31b3364e0902230836l140c9d0cs932937c392e8e9d(a)mail.gmail.com> <49A2EBEC.4050306(a)cwi.nl> ok! I'll then try the xrpc requests... is there a way to delete a collection in the database? (deleting documents one by one in a given collection is quite fastidious) and a way to delete a database created by the command Mserver --dbname=<...> ? (the only way i found was to physically delete directories with that same name) Thanks, francois. 2009/2/23 Martin Kersten <Martin.Kersten(a)cwi.nl>: > francois guérin wrote: >> >> Hi, >> I am about to develop an app using monetdb and its xquery part. >> My app will essentially be made of perl code. >> But it seems that perl is only used in monetdb's sql part, isn't? like >> using sql statements to access arrays and rows, but there's no way to >> access xml collections. >> >> Am i right or haven't i read enough of monetdb's documentation? > > You are right. The PHP, Perl, JDBC application interfacing to > the XML/XQuery part is less developed. > > regards, Martin >> >> Thanks a lot, >> >> francois. > >

2 1