- users-list - monetdb.org

[MonetDB-users] Get average value for intervals
by Gustav Koller 18 Jun '10

18 Jun '10

Hello, I've some time series data (time + a few value columns), now I want to generate average values for each hour. Currently I'm using the following query: SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup; I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time? Greetings Gustav

2 2

[MonetDB-users] Limit memory
by Miguel Ángel Águila Lorente 17 Jun '10

17 Jun '10

Hello, I'm trying to limit the memory that monetdb can use during its executions. I'm using mal language and I've found this operation in order to do it. command mem_maxsize(v:lng) :void address set_mem_maxsize comment "Set the maximum usable amount of physical swapspace in KB."; When I use this operation the connection is terminated. I let here some examples: (I'm working with a machine with 32 GB) mal>status.mem_maxsize(4294967296:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(4294967296:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1073741824:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1073741824:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1048576:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1048576:lng); ERROR = !Connection terminated Looking forward to hearing from you. Thank you. Mike

4 6

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender

4 4

Re: [MonetDB-users] MonetDB XML - slow execution of query, with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Jan, thank you for your response. I thought that all documents within a collection are stored within one (or several) relational tables? I am not sure, if merging all documents into one big document is an option. The documents are supposed to be updateable ... does that give a good performance for one large document? Patrick > Message: 7 > Date: Tue, 15 Jun 2010 21:58:44 +0200 > From: Jan Rittinger <jan.rittinger(a)uni-tuebingen.de> > Subject: Re: [MonetDB-users] MonetDB XML - slow execution of query > with test on attribute > To: Communication channel for MonetDB users > <monetdb-users(a)lists.sourceforge.net> > Message-ID: <702CE627-774E-47C5-B3B7-34A5431F20CF(a)uni-tuebingen.de> > Content-Type: text/plain; charset="windows-1252" > > Hi Patrick, > > the evaluation plan of your query looks quite bad -- with respect to what could be possible... > > If you could use 'doc("tu")' (with a single big document containing all small documents), you would get the result in *no* time, as then the attribute index is used. Unfortunately I'm not aware of the difference in the attribute index with respect to collections... > > Regards, > Jan >

2 1

Re: [MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Peter, thank you for your answer. using pf:collection gives me a "Speicherzugriffsfehler" (/Segmentation fault)/ when executing the query you proposed. Is that a bug in MonetDB XML? replacing "pf:collection" by "fn:collection" leads to Trans 55.436 msec Shred 19.083 msec Query 29241.029 msec Print 0.207 msec Timer 29582.455 msec So it is basically the same query time :( Patrick > Hi, > > How about: > > smiles> { > for $i in pf:collection("tu") > where $i//xq:molecule/@id = "1" > return > <smile>{$i}</smile> > } </smiles> > > is that any faster? > > Peter > > >

1 0

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang > > Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender >

1 0

[MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Alex Bo. 17 Jun '10

17 Jun '10

Hi, Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' from the server when I try to start it in scenarios that usually work without any problems. I can't reproduce it consistently, but sometimes I get it. What does this exception mean? Thanks. -- View this message in context: http://www.nabble.com/Meaning-of%3A-Scenario-not-initialized-%27sql%27-tp26… Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 15 Jun '10

15 Jun '10

Hi, I am trying to write a xquery-expression that does a test on an attribute (id="1"). Unfortunately the query execution time for this query is really bad (>30 seconds). Is there any way to find out why this query takes so long? 30 seconds for a test on one single attribute is just way to high for our use-case. I read that MonetDB automatically creates indices on all attribute - is there a way to test, if there was an index created for that attribute and if that index was used for query execution? The query (in File q3.xp) is supposed to return the molecule with id "1" declare namespace xq="http://www.xml-cml.org/schema"; <smiles> { for $i in collection("tu") where $i/xq:molecule/@id = "1" return <smile>{$i}</smile> } </smiles> The query is executed like this: # mclient -t -lx -G q3.xp ... Trans 55.684 msec Shred 18.818 msec Query 29335.766 msec Print 0.258 msec The database contains 1.000.000 XML-documents in a single collection, is updatable and is about 2601 MiB. A document looks similar to this one: <?xml version="1.0" encoding="UTF-8"?> <molecule id="1" xmlns="http://www.xml-cml.org/schema"> <bondArray atomRef1="..." order="..."/> <atomArray atomID="..." elementType="..." x3="..."/> <identifier convention="iupac:smile" value="..."/> <identifier convention="iupac:inchi" value="..."/> </molecule> We are using: "MonetDB/XQuery 0.36.5, based on the pathfinder <http://www.pathfinder-xquery.org> compiler and MonetDB 4.36.1 <http://monetdb.cwi.nl>" Thank you very much for any help! Patrick

3 2

[MonetDB-users] Decimal(p,s)
by Nozhup 15 Jun '10

15 Jun '10

What is the reason that MonetDB only supports a maximum p value of 18 while for example SQL Server (see link) supports up to 38? http://www.w3schools.com/SQL/sql_datatypes.asp -- View this message in context: http://old.nabble.com/Decimal%28p%2Cs%29-tp28892721p28892721.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] MIL and GROUP BY
by Hannes Rauhe 14 Jun '10

14 Jun '10

Hello together, I installed MonetDB with the help of the latest monetdb-install.sh and played around with MIL. Now I'm stuck with translating a simple SQL-Query containing a GROUPBY. After starting the server with # Mserver --dbname test --dbinit 'module(mapi); mil_start();' and the client with # ./mclient -lmil -utest -dname=test I tried the following: var a:= bat(oid,int); a.insert(1@0, 1995); a.insert(2@0, 1992); a.insert(3@0, 1993); a.insert(4@0, 1994); a.insert(5@0, 1995); a.insert(6@0, 1996); CTgroup(a).print(); the answer is: ERROR: const operation on a BAT ERROR: CMDconstCopy: operation failed. ERROR: interpret_params: print(param 1): evaluation error. Another try after loading the xtables-Module: mil>module("xtables"); mil>CTgroup(a).print(); +-------------------------------------------------------+ | � | +-------------------------------------------------------+ 1 tuple I used a few papers and http://monetdb.cwi.nl/MonetDB/Version4/Documentation/mil/index.html (Chapter 3.7 is what I'm looking for) as tutorial but didn't find anything helpful. Where's my mistake? Best Regards, Hannes

2 1