- users-list - monetdb.org

[MonetDB-users] ask for help
by Xianmin Liu 21 Mar '11

21 Mar '11

Hi, I got some errors when using MonetDB/XQuery, could you pls give me some help? I was running experiments over large XML data using MonetDB/XQuery. My hardware: CPU:Intel Core2 Duo CPU 2.93GHz, RAM:2.00GB MonetDB: Server v4.32.0 based on GDK v1.32.0, Compiled for i686-pc-win32/32bit, MonetDB/XQuery module v0.28.3 loaded (default back-end is 'algebra') When I try to load xmark1G.xml (generated by xmark generator with factor 10) by command 'pf:add-doc("H:\lxm\1G\xmark1000M.xml","xmark1G.xml")<>' Return errors: ERROR = !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: HEAPextend: failed to extend to 278712278 for 03\373theap !ERROR: shredBAT_append_str: APPEND-STR[_prop_text]( !ERROR: CMDshred_url: operation failed. -- Xianmin Liu

1 0

[MonetDB-users] EXTRACT(SECOND FROM timestamp) gives 0 seconds
by spamme.gs＠googlemail.com 18 Mar '11

18 Mar '11

Hello, I can't understand the following behaviour : Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2010-SP1) Database: MonetDB v5.22.3, 'tradedb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select extract(second from timestamp '2011-3-10 9:30:42.246'); +---------------------+ | second_single_value | +=====================+ | 0.246 | +---------------------+ Whoops. Well maybe monetdb can EXPLAIN what's goin on sql>explain select extract(second from timestamp '2011-3-10 9:30:42.246'); | function user.s2_1{autoCommit=true}():void; | | sql.mvc(); | | _4 := mtime.milliseconds(2011-03-10 09:30:42.246:timestamp); | | sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,""); | | end s2_1; | Ok ... Looks like this function is declared here: (modules/atoms/mtime.mal) 413 command milliseconds(d:daytime) :int 414 address MTIMEdaytime_extract_milliseconds And digging into the source of _that_ function shows us that it's just returning the millisecond part. So, any chance of a quick fix here? It looks like I'm not the first to ask this question: http://sourceforge.net/mailarchive/message.php?msg_id=23238837 but that poor guy was completely ignored.

2 1

[MonetDB-users] LC_ALL=C in monetdb-install.sh breaks RDoc
by Lazaros Koromilas 18 Mar '11

18 Mar '11

Hello, all! I did the following on an Arch Linux yystem: MonetDB-Oct2010-SuperBall-SP1> ./monetdb-install.sh --enable-xquery ... make[8]: Entering directory `/var/tmp/MonetDB-1299754542/MonetDB-client/clients/ src/ruby/adapter' mkdir -p /home/myuser/MonetDB/lib/ruby/gems/1.9.1 gem install --local --install-dir /home/myuser/MonetDB/lib/ruby/gems/1.9.1 --forc e --rdoc activerecord-monetdb-adapter-0.1.gem ERROR: While executing gem ... (Encoding::UndefinedConversionError) U+2019 from UTF-8 to US-ASCII Successfully installed activerecord-monetdb-adapter-0.1 1 gem installed Installing ri documentation for activerecord-monetdb-adapter-0.1... Installing RDoc documentation for activerecord-monetdb-adapter-0.1... make[8]: *** [install-exec-local-activerecord-monetdb-adapter-0_1_gemspec] Error 1 ... Setting LC_ALL to UTF-8 fixes my problem. --- monetdb-install.sh.orig 2011-03-10 13:34:58.000000000 +0200 +++ monetdb-install.sh 2011-03-10 13:35:05.000000000 +0200 @@ -108,7 +108,7 @@ : ${FETCHCOMMAND:="wget --progress=dot:mega -t 5 --passive-ftp -O \"\${FILE}\" \"\${URI}\""} # make sure we get English error messages and so on -export LC_ALL=C +export LC_ALL="en_US.UTF-8" # parse command line options ORGARG=$*

2 3

[MonetDB-users] MonetDB/XQuery order by problem
by Rob Fynes 13 Mar '11

13 Mar '11

Hello, I'm trying to produce a sorted list of results with MonetDB/XQuery using 'order by', with a query that goes something like this: for $cname in pf:collections()/text() return if (local:is-date-between($cname, **start_date_here**, **end_date_here**)) then for $s in pf:collection($cname)//Dublin/stations[time/hour=13 and time/minute>=22 and time/minute<=25] order by $s/timeOfDay return $s else () ...the 'is-date-between' is just some custom function to check if the $cname (collection names are dates) is between the start and end dates supplied to the function. However, the problem is with the nested for loop. I want each $s that satisfies the XPath expression to be returned, but ordered by $s/timeOfDay. When I submit this query, I get the following error: ERROR = !type error: [err:XPTY0004] Orderspec requires at most one atomic item (given: string*). I've tried a number of variations on this, but the error is always the same. If I remove the order by clause, the results will return correctly, but in the unsorted order that they appear in the database. I'm still a beginner with XQuery, so I'm unsure where I'm going wrong, and documentation and tutorials have been unhelpful so far. Any help is appreciated! Regards, -Rob

2 3

[MonetDB-users] Copy records into table failed
by Tibor Barna 11 Mar '11

11 Mar '11

Hello, I am trying to import data from MySQL into MonetDB usig the bulk loader, the csv has ~11GB, 163Mil Rows. When running: sql>copy 163715240 records into sys.clicks from '/data/csv_monet_dump/clicks.tbl' using delimiters '|',' more>', '"' null as ''; I always get this error message: SQLException:importTable:failed to import table ERROR: HEAPextend: failed to extend to 654860960 for 32/3231tail ERROR: TABLETcreate_bats: Failed to create bat of size 163715240 merovingian.log: 2011-03-10 14:12:31 MSG performance[6281]: #GDKmmap(2383544320) fails, try to free up space [memory in use=41411528,virtual memory in use=12310675456] 2011-03-10 14:12:31 MSG performance[6281]: #GDKmmap(2383544320) result [mem=32087208,vm=12310675456] I am using 64bit openSUSE 11.3, 12GB RAM, 4GB Swap. Does anybody know how can i overcome this error? Any help would be most welcome :) Cherers, Tibor. -- View this message in context: http://old.nabble.com/Copy-records-into-table-failed-tp31115783p31115783.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

3 7

[MonetDB-users] Get MAL from SQL
by Kusber, Michael 10 Mar '11

10 Mar '11

Hi, is there a possibility to see the MAL code generated from a specific SQL statement? Kind regards, Michael

3 2

[MonetDB-users] Are these bugs？
by tangray 09 Mar '11

09 Mar '11

hi all when i use c++ pragrm to insert some data into a table,the program crashes,the message is below: so i use gdb to debug the bug, i find that these places may be bugs： 1. out of bounds access file mapi.c function: mapi_param_store 2356 strncpy(hdl->query + k, p, q - p); 2357 k += q - p; 2358 hdl->query[k] = 0; q-p is equal or bigger than lim - k ,the strncpy will hava a out of bounds access. this may be the reason to crash. 2. out of bounds access file:mapi.c function: mapi_param_store 2411 case MAPI_DOUBLE: 2412 checkSpace(20); 2413 sprintf(hdl->query + k, "%.17g", *(double *) src); 2414 break; sprintf(hdl->query + k, "%.17g", *(double *) src) , sprintf function with "%.17g" format may produces a string longer than 20,eg： double d = 0.000200000000000001; double d = 0.000200000000000001; printf("%.17g",d); the result is : 0.00020000000000000101 so i think, in some paticular condition ,this will hava a out of bounds access too. I'm working with MonetDB Server v5.22.3, based on kernel 1.40.3 over fedora 12.

2 1

[MonetDB-users] lose some datas of a table's one field, how can this happen?
by tangray 08 Mar '11

08 Mar '11

Hi when execute sql : select * from td_ind_security_held where id = 1560000 in DbVisualizer 7.1.1,it appears "protocol violation: unexpected line in data block" td_ind_security_held has 30 columns(1 int,6 varchar, others decimal) and 7219618 rows. so i count field's records one by one , all fields's records are equal to 7219618 except the allgain's records, it is only 658337. it obviously lost some data. when program inserted the data into the table , the monet server carshed out some times, can this be the reson for the lose? I'm working with MonetDB Server v5.22.3, based on kernel 1.40.3 (downloaded msi from sourceforge) over Windows XP SP3.

2 1

Re: [MonetDB-users] MonetDB/XQuery JDBC result issue
by Wouter Alink 04 Mar '11

04 Mar '11

Hello Rob, Yes, the good thing about the JDBC interface is that the result is streamed to the client, so that there is no large memory-consumption on the client-side. However, be aware that, due to the way MonetDB works internally, the whole result is first computed and kept in memory at the server, before it is shipped to the client. This will for most use-cases not be a problem, but if you are calculating huge result sets it might. If you only fetch the first 10 rows of a result at the client, you may want to wrap a "subsequence(<your-query>, 1, 10)" around your query. Kind regards, Wouter On Fri, Mar 4, 2011 at 3:42 PM, Rob Fynes <monetdbmail(a)gmail.com> wrote: > Hi Wouter, > > Thanks for the response. You were right about that, it's working fine now. > I was thrown slightly when I saw the MonetDB documentation that said "The > result comes back as a single-row single-column multi-line string result". I > figured that the 'entire' set of results would be stuffed into one row and > one column in the result set object. In retrospect, this wouldn't make much > sense, since you could end up with a *really* big string! > > Thanks, > -Rob > > > On Wed, Mar 2, 2011 at 1:46 PM, Wouter Alink <wouter.alink(a)gmail.com>wrote: > >> Dear Rob Fynes, >> >> You have already provided the answer in your email. XQuery specifies that >> a result is actually a sequence of items. The "XQueryResult"-node has only >> been added to convert the actual result sequence into a valid XML fragment. >> Using XQuery over JDBC works like SQL; you can iterate over the results. >> There is no artificial XQueryResult-node added. >> >> For example: >> >> ResultSet rs = conn.executeQuery("...."); >> while(rs.next()) { >> .. do something with the result .. >> } >> >> With kind regards, >> Wouter Alink >> >> >> >> On Tue, Mar 1, 2011 at 10:13 PM, Rob Fynes <monetdbmail(a)gmail.com>wrote: >> >>> Hello, >>> >>> I've been having an issue with MonetDB/XQuery using the JDBC driver. >>> Essentially, when I supply an *XPath* query such as the following: >>> >>> pf:collection("collection_name")//stations[date/year>=2010] >>> >>> ... using the mclient utility, there's no problem. The result is an >>> <XQueryResult> root node containing all the relevant 'stations' nodes as >>> requested by the query. >>> However, if I issue the same query to the server from a Java application >>> using the JDBC driver for MonetDB, the string result that is returned merely >>> contains one 'stations' node (the first one that gets processed, I think). >>> >>> My guess is that the resultset (of 'stations' nodes) is not being wrapped >>> in a root node, as is the case when querying using the mclient utility. >>> But I'm unsure if this due to a bug, or if there's something that I'm not >>> doing correctly. >>> >>> I'm also aware that this issue can be resolved by modifying the query >>> (when using JDBC) to something such as: >>> >>> <result> { >>> pf:collection("collection_name")//stations[date/year>=2010] >>> } </result> >>> >>> ... but I have reasons for not wanting to do this. >>> Any help is much appreciated. If anyone needs more information, just let >>> me know (and if it makes any difference, I'm running a local server >>> instance). >>> >>> Cheers, >>> -Rob >>> >>> >>> ------------------------------------------------------------------------------ >>> Free Software Download: Index, Search & Analyze Logs and other IT data in >>> Real-Time with Splunk. Collect, index and harness all the fast moving IT >>> data >>> generated by your applications, servers and devices whether physical, >>> virtual >>> or in the cloud. Deliver compliance at lower cost and gain new business >>> insights. http://p.sf.net/sfu/splunk-dev2dev >>> _______________________________________________ >>> MonetDB-users mailing list >>> MonetDB-users(a)lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>> >>> >> >

1 0

[MonetDB-users] MonetDB/XQuery JDBC result issue
by Rob Fynes 02 Mar '11

02 Mar '11

Hello, I've been having an issue with MonetDB/XQuery using the JDBC driver. Essentially, when I supply an *XPath* query such as the following: pf:collection("collection_name")//stations[date/year>=2010] ... using the mclient utility, there's no problem. The result is an <XQueryResult> root node containing all the relevant 'stations' nodes as requested by the query. However, if I issue the same query to the server from a Java application using the JDBC driver for MonetDB, the string result that is returned merely contains one 'stations' node (the first one that gets processed, I think). My guess is that the resultset (of 'stations' nodes) is not being wrapped in a root node, as is the case when querying using the mclient utility. But I'm unsure if this due to a bug, or if there's something that I'm not doing correctly. I'm also aware that this issue can be resolved by modifying the query (when using JDBC) to something such as: <result> { pf:collection("collection_name")//stations[date/year>=2010] } </result> ... but I have reasons for not wanting to do this. Any help is much appreciated. If anyone needs more information, just let me know (and if it makes any difference, I'm running a local server instance). Cheers, -Rob

2 1

[MonetDB-users] draft message
by Martin Kersten 01 Mar '11

01 Mar '11

Dear MonetDB users and developers, The preparations for the March 2011 release are on target. This release will feature a much simplified installation and compilation process using a unified bootstrap/configure/make/install. Ofcourse, installers and RPMs are also provided. Furthermore, the March 2011 release will be the freeze of the MonetDB XQuery source tree. It will remain downloadable from the website, but no active development or maintenance is foreseen. In April 2011 a MonetDB/SQL feature release will mark the split of the two code bases. From that point onward, we will primarily be concerned with SQL and its enhancements. Saying goodbye to the very old mserver4 code base, also triggered a major code renovation activity. The MonetDB/SQL code base will be cleaned from legacy code, 1-time experimental code, and all that is not strictly needed for the intended functionality. We expect that this cleanup will simplify maintenance in the future. The results of the renovation step will become visible in the fall of 2011. We plan for a smooth transition step for existing SQL environments. However, under the hood, we will change the MAL and kernel library interfaces significantly. To assess the consequences in the community we would like to learn about projects that directly address the MAL or GDK layer. Please send us an email at info(a)monetdb.org with the project highlights. regards, Martin Kersten

1 0

[MonetDB-users] protocol violation: unexpected line in data block
by tangray 28 Feb '11

28 Feb '11

Hi when execute sql : select * from td_ind_security_held where id = 1560000 in DbVisualizer 7.1.1,it appears "protocol violation: unexpected line in data block" td_ind_security_held has 30 columns(1 int,6 varchar, others decimal) and about 7 million rows. I'm working with MonetDB Server v5.22.3, based on kernel 1.40.3 (downloaded msi from sourceforge) over Windows XP SP3.

2 3

Re: [MonetDB-users] Query time takes equally long on whole
by dsuh 24 Feb '11

24 Feb '11

We have experienced big improvements from Nov-2009SP2 to Oct-2010SP1 as well except for views. It appears that the mitosis optimizer has problems with views and queries on them run slower than without it. (~9 sec slower on 1 min queries on views and ~20 sec slower on 2 min queries on views using default_pipe rather than no_mitosis_pipe or nov2009_pipe) The mitosis optimizer was the big change in the optimizer pipeline between these versions. Is it possible that this is still from the bug?: http://bugs.monetdb.org/show_bug.cgi?id=2472 Can anyone tell me if there are any plans for optimization improvements (either mitosis, commonTerms, or otherwise) with queries on views? David Suh > > Hello Henry, > Thank you for your reply! See inline. > > On Thu, Feb 24, 2011 at 7:54 AM, Henry Addington <hs.addington(a)gmail.com > >wrote: > > > I am no technical expert on MonetDB. But, we have been using MonetDB for > > more than a year now. So, I can give you couple of suggestions > > (or workarounds) that might help based on our experience. We are using a > > third-party application that generates queries and sends them to MonetDB > > which has a transaction table with more than 1.6billion records and > growing > > (timeseries data like yours..) linked to dimensional tables. The kinds of > > queries that are being generated are almost exact type that you are > showing, > > some sort of an aggregation query using group by and filtered with where > > clause. > > > > One thing that we noticed with queries with joins is that inner join > seems > > to be significantly faster than left/right joins even if the results are > no > > different between the 3 types of joins. I know that might not be an > option > > for you. But, it works for our use case. > > > > I've changed my queries into two separate ones: one for the total > timescope, and one for (occasionally) getting the results spread over the > interval for one item. The new total timescope query is faster on less time > as scope, and as fast as before on the total 24 hour scope, so definitely > an > improvement for me. > > > > > > Another thing that we noticed is if you add constraints, ie primary and > > foreign keys, you will get better performance. I think you might already > > have them in your tables though.... > > > > I do have them, but i didn't add them in my create statement. I''m also > going to try if this adds extra speed. > > > > > > We are in the process of testing Oct-2010SP1 release so we can upgrade > > our Nov-2009SP2 environment. We are seeing significant performance > > improvement in most of our typical queries and much smaller memory > footprint > > with the Oct-2010SP1 release. But, we see some performance degradation > with > > queries based on views instead of tables. You might want to try the > Nov-2009 > > optimizer and see if that helps. You can do this by specifyng the > Nov-2009 > > optimizer in the monetdb5.conf file. > > > > This didn't show any difference on the original query, but thanks for > mentioning. I might need it in the future. > > Kind regards, > Rob Berentsen > > > > > On Wed, Feb 23, 2011 at 4:24 AM, Rob Berentsen <rhberentsen(a)gmail.com > >wrote: > > > >> Hello, > >> > >> I'm new to monetdb and installed it two weeks ago to do some tests. I > was > >> very much impressed with the speed on large tables, but in some of my > latest > >> tests regarding subparts of these tables based on a unix timestamp the > time > >> was the same as querying the whole table. Does anyone know what I can do > to > >> get higher speeds on queries in monetdb that only use part of the table, > >> like partition pruning, explicit foreign keys or unique statements, > >> indexes or some other way? > >> > >> I have the following two tables and number of records: > >> > >> CREATE TABLE table1 (T1vid INT NOT NULL, T1Field1 VARCHAR(20) NOT > >> NULL, T1Field2 TINYINT NOT NULL); > >> This table has about 400.000 records. The records are non time bounded, > >> it's just a list. The T1vid is an increased ID, but not generated in > >> momentdb at the moment, but in mysql > >> > >> CREATE TABLE table2 (T2mid INT NOT NULL, T2vid INT NOT NULL, T2timestamp > >> INT NOT NULL, T2Field1 INT NOT NULL, T2Field2 INT NOT NULL); > >> This table has about 1.000.000 records and a unix_timestamp field. It > get > >> a few new records every second and records older then 24 hours are > deleted. > >> The T2mid-field refers to some other table that's not in momentdb's > >> database, so just an integer. The T2vid-field however refers to the > >> T1vid-field in table1 (a foreign key, but i didn't define it that way). > >> > >> The following query takes about 3,5 seconds, it doesnt't have the > >> timestamp included in the WHERE-clause: > >> SELECT SUM(t2.T2Field2), t1.T1Field1, t1.T1Field2, (t2.T2timestamp / > 3600) > >> as interval > >> FROM table2 AS t2 > >> LEFT JOIN table 1 AS t1 ON t1.T1vid=t2.T2vid > >> WHERE t2.T2Field1=8 AND t2.T2vid IN (*list of 15 unique > >> v.T1vid ID's*) > >> GROUP BY interval, t1.T1Field1, t1.T1Field2 ORDER BY > interval > >> DESC; > >> Resulting is a list of the 15 v.T1vid ID's times the number of > interval's. > >> t2.T2timestamp is now devided by 3600 and grouped on this result, so > each > >> set of 15 v.T1vid ID's would reflect 1 hour, returning max 24x15 rows. > >> ** > >> The following query also takes about 3,5 seconds, but it does have the > >> timestamp included in the WHERE-clause. It only needs to access 1 hour > of > >> data instead of the whole 24 hours, and groups it into intervals of 5 > >> minutes (300 sec): > >> SELECT SUM(t2.T2Field2), t1.T1Field1, t1.T1Field2, (t2.T2timestamp / > 300) > >> as interval > >> FROM table2 AS t2 > >> LEFT JOIN table 1 AS t1 ON t1.T1vid=t2.T2vid > >> WHERE* t2.T2timestamp BETWEEN 1298360000 AND 1298363600* > >> AND t2.T2Field1=8 AND t2.T2vid IN (*list of 15 unique > v.T1vid > >> ID's*) > >> GROUP BY interval, t1.T1Field1, t1.T1Field2 ORDER BY > interval > >> DESC; > >> > >> t2.T2timestamp is now devided by 300, so each set of 15 v.T1vid ID's > would > >> reflect 5 minuntes, returning max 12x15 rows. > >> > >> I would very much appreciate any help or hints; please let me know any > >> question you might have. > >> > >> > >> Kind regards, > >> Rob Berentsen > >> > >> > >> > ------------------------------------------------------------------------------ > >> Free Software Download: Index, Search & Analyze Logs and other IT data > in > >> Real-Time with Splunk. Collect, index and harness all the fast moving IT > >> data > >> generated by your applications, servers and devices whether physical, > >> virtual > >> or in the cloud. Deliver compliance at lower cost and gain new business > >> insights. http://p.sf.net/sfu/splunk-dev2dev > >> _______________________________________________ > >> MonetDB-users mailing list > >> MonetDB-users(a)lists.sourceforge.net > >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > >> > >> > > > > > > > ------------------------------------------------------------------------------ > > Free Software Download: Index, Search & Analyze Logs and other IT data in > > Real-Time with Splunk. Collect, index and harness all the fast moving IT > > data > > generated by your applications, servers and devices whether physical, > > virtual > > or in the cloud. Deliver compliance at lower cost and gain new business > > insights. http://p.sf.net/sfu/splunk-dev2dev > > _______________________________________________ > > MonetDB-users mailing list > > MonetDB-users(a)lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > >

1 0

[MonetDB-users] monetdb on ubuntu 10.10 - xquery results displaying only in single line!
by sagarjauhari 24 Feb '11

24 Feb '11

Hello, my xquery results are displaying only in a single line. The hello world example on http://monetdb.cwi.nl/XQuery/QuickTour/DOCMGT/ displays result correctly as: > doc("HelloWorld.xml") <?xml version="1.0" encoding="utf-8"?> <doc> <greet kind="informal">Hi </greet> <greet kind="casual">Hello </greet> <location kind="global">World</location> <location kind="local">Amsterdam</location> </doc> But this: xquery>for $a in (1 to 3) return <number>{$a}</number> outputs this: <XQueryResult><number>1</number><number>2</number><number>3</number></XQueryResult> and for the file :test.xq: (1, 42.0, "Hello World", <node attr="value">test</node>) the xquery mclient -lx -f xml test.xq outputs <?xml version="1.0" encoding="utf-8"?> <XQueryResult>1 42.000000 Hello World<node attr="value">test</node></XQueryResult> Any suggestions? Thanks in advance -- View this message in context: http://old.nabble.com/monetdb-on-ubuntu-10.10---xquery-results-displaying-o… Sent from the monetdb-users mailing list archive at Nabble.com.

2 4

[MonetDB-users] Stored Procedures in MonetDB
by Irini Fundulaki 24 Feb '11

24 Feb '11

Hi all, would it be possible to let us know whether MonetDB/SQL supports stored procedures and provide us with pointers to the features supported (e.g., recursion). Many Thanks Irini ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.

2 1

[MonetDB-users] Query time takes equally long on whole and just small part of table
by Rob Berentsen 24 Feb '11

24 Feb '11

Hello, I'm new to monetdb and installed it two weeks ago to do some tests. I was very much impressed with the speed on large tables, but in some of my latest tests regarding subparts of these tables based on a unix timestamp the time was the same as querying the whole table. Does anyone know what I can do to get higher speeds on queries in monetdb that only use part of the table, like partition pruning, explicit foreign keys or unique statements, indexes or some other way? I have the following two tables and number of records: CREATE TABLE table1 (T1vid INT NOT NULL, T1Field1 VARCHAR(20) NOT NULL, T1Field2 TINYINT NOT NULL); This table has about 400.000 records. The records are non time bounded, it's just a list. The T1vid is an increased ID, but not generated in momentdb at the moment, but in mysql CREATE TABLE table2 (T2mid INT NOT NULL, T2vid INT NOT NULL, T2timestamp INT NOT NULL, T2Field1 INT NOT NULL, T2Field2 INT NOT NULL); This table has about 1.000.000 records and a unix_timestamp field. It get a few new records every second and records older then 24 hours are deleted. The T2mid-field refers to some other table that's not in momentdb's database, so just an integer. The T2vid-field however refers to the T1vid-field in table1 (a foreign key, but i didn't define it that way). The following query takes about 3,5 seconds, it doesnt't have the timestamp included in the WHERE-clause: SELECT SUM(t2.T2Field2), t1.T1Field1, t1.T1Field2, (t2.T2timestamp / 3600) as interval FROM table2 AS t2 LEFT JOIN table 1 AS t1 ON t1.T1vid=t2.T2vid WHERE t2.T2Field1=8 AND t2.T2vid IN (*list of 15 unique v.T1vid ID's*) GROUP BY interval, t1.T1Field1, t1.T1Field2 ORDER BY interval DESC; Resulting is a list of the 15 v.T1vid ID's times the number of interval's. t2.T2timestamp is now devided by 3600 and grouped on this result, so each set of 15 v.T1vid ID's would reflect 1 hour, returning max 24x15 rows. ** The following query also takes about 3,5 seconds, but it does have the timestamp included in the WHERE-clause. It only needs to access 1 hour of data instead of the whole 24 hours, and groups it into intervals of 5 minutes (300 sec): SELECT SUM(t2.T2Field2), t1.T1Field1, t1.T1Field2, (t2.T2timestamp / 300) as interval FROM table2 AS t2 LEFT JOIN table 1 AS t1 ON t1.T1vid=t2.T2vid WHERE* t2.T2timestamp BETWEEN 1298360000 AND 1298363600* AND t2.T2Field1=8 AND t2.T2vid IN (*list of 15 unique v.T1vid ID's*) GROUP BY interval, t1.T1Field1, t1.T1Field2 ORDER BY interval DESC; t2.T2timestamp is now devided by 300, so each set of 15 v.T1vid ID's would reflect 5 minuntes, returning max 12x15 rows. I would very much appreciate any help or hints; please let me know any question you might have. Kind regards, Rob Berentsen

2 2

[MonetDB-users] MAL changes in March2011
by Mike De-La-Columnar 22 Feb '11

22 Feb '11

Hi, It seems like the whole MAL language is being upgraded. (the use of columns instead of BATs) are those changes just cosmetic, or is there something else that will benefit from it? also, will the old MAL work in the new version? -- View this message in context: http://old.nabble.com/MAL-changes-in-March2011-tp30985134p30985134.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] rdf.sql missing from Mar2011 branch
by Babis Nikolaou 18 Feb '11

18 Feb '11

Hello, the rdf.sql file for creating the schema of an RDF database is missing from the branch of Mar2011. Could you add it please? Thanks, Babis

4 12

[MonetDB-users] Kernel / FS tuning
by Franck Routier 18 Feb '11

18 Feb '11

Hi, I was wondering if anyone has suggestions about what tweaks might best suit MonetDB's needs regarding : - (linux) kernel parameters : memory management, ... - FS (type and parameters) : block size, write barriers, ... Is there any kind of best practice or even some thoughts on this ? Thanks in advance for your inputs, Franck

1 0

[MonetDB-users] Problem starting monetdbd after installing MonetDB
by Babis Nikolaou 10 Feb '11

10 Feb '11

Hi all, i get the following error after installing MonetDB from CVS (tip) and running monetdb/d. $ monetdbd cannot open config file (null) unable to retrieve startup status $ monetdb cannot open config file (null) As opposed to the previous version i had (a version of CVS during November), now there is no /etc subdirectory inside the MonetDB installation directory, where the configuration files lie. I also searched for any configuration files inside the build directory, in case sth had gone wrong during installation, but nothing has been generated. I also noticed that the installation procedure when compiling from CVS has been changed. Do i miss something? I just run bootstrap, configure, make, and last make install. One last remark: in the manpage of monetdbd (and monetdb), the configuration file in question is referenced as @MONETDB5_CONFFILE@. Shouldn't had been mapped to a filename? Thanks, Babis

3 3

[MonetDB-users] Bug in EXTRACT?
by Alex Bo. 06 Feb '11

06 Feb '11

Hi, There seems to be a bug in the October SP. If you run this query: SELECT EXTRACT(YEAR FROM f1 .a1) as a2, EXTRACT(YEAR FROM f1 .a1) as a3 FROM (select '1996-02-01' as a2, '1996-07-01' as a1) f1 it returns: 1996, 1996-02-01 Instead of: 1996, 1996 The second column is returned as timestamp instead of int. Also, this does not happen if the first aliases (a2, a3) are changed to different names, nor does it happen if the two EXTRACTS are not identical. If one is YEAR and the second is MONTH it works, but if both are MONTH the same bug happens. I tried it also on the August build and it works fine there. Any ideas? -- View this message in context: http://old.nabble.com/Bug-in-EXTRACT--tp30855882p30855882.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] query slowdown with concurrent write
by Bennett Hiles 04 Feb '11

04 Feb '11

Hi Martin and Stefan, Thanks for the quick response, and sorry if this created another thread, I'm not familiar with how to use this mailing list. I am using: MonetDB v5.22.3 on ubuntu 9.04 64bit Queries and data loading use the php library. To further explain my use case, I am planning on having monetdb be updated about once a minute with 1000-10000 new rows of data. At the same time I will be having as many clients as possible making queries on the database. I have so far seen that 5 clients can be handled with satisfiable response times. Is this a suitable application for monetdb? The query times I have seen so far are great. My problems are handling the queries with concurrent writes and if possible allowing for more clients to make simultaneous queries. Re: Martin's questions: I tried using the recycle optimizer in the default pipe and my queries didn't slow down, which is awesome. But, my problem now is that the values aren't actually added to the dataset until an insert transaction occurs when I am not querying the database. Therefore, if I continuously query the database then the inserts are never seen, but if I stop querying for a second and another insert happens the database is updated. I am fine with some delay in the insert time, but is there anyway I could have it executed slowly alongside the queries, or am I too greedy :) The pipe I created is the same as the default pipe with the recycler optimizer added: bennett_pipe=inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector My schema and and an example query are: CREATE TABLE my_test_table ( account_id bigint, dim1 bigint, dim2 bigint, dim3 bigint, dim5 varchar(50), dim6 varchar(50), dim7 varchar(256), dim8 bigint, dim9 varchar(256), dim10 varchar(256), unique_id bigint, my_time timestamp, summable int ); QUERY: select dim5, sum(summable), count(distinct unique_id) from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; Re Stefan's questions: The results from the trace are below. I apologize if any of the data looks incorrect, I scrubbed my table's field names to be more generic. I let the database cool down for 1 minute and 10 minutes, but the queries remained slow. If I restarted the database, the queries resumed their quick response times. FAST QUERY TIME: sql>trace select dim5, sum(summable) as summables, count(distinct unique_id) as unqiue_ids from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; +-----------------------+--------+--------+ | dim1 | summables | unique_ids | +=======================+========+========+ | test1 | 251 | 18 | | test2 | 48 | 11 | | test3 | 1 | 1 | | test4 | 2 | 1 | | test4 | 106 | 2 | +-----------------------+--------+--------+ 5 tuples (125.534ms) +-------+------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+==========================================================================================+ | 3 | _5 := sql.mvc(); | | 18 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_id | : : ",2); : | 7 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","accou | : : nt_id",0); : | 4 | _15:bat[:oid,:lng] <tmp_1102>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_id | : : ",1); : | 6 | _18:bat[:oid,:oid] <tmp_1357>[0] := sql.bind_dbat(_5=21685368,"sys","my_test_table",1); | | 21 | _13<tmp_1527>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat[: | : : oid,:lng][0]); : | 4 | _19<tmpr_1357>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 10 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=21685368,"sys","my_test_table" | : : ,"my_time",0); : | 27 | _14<tmp_1532>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 11 | _23 := calc.timestamp(A1="2010-07-03 00:00:00"); | | 12 | _17<tmp_1527>[8000000] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 4 | _24 := calc.timestamp(A2="2010-07-10 00:00:00"); | | 7 | _20<tmp_1532>[8000000] := algebra.kdifference(_17=nil,_19=<tmpr_1357>[0]); | | 4 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=21685368,"sys","my_test_table","my_t | : : ime",2); : | 29575 | _25<tmp_1531>[359554] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 00: | : : 00:00.000,_24=2010-07-10 00:00:00.000,true,false); : | 12 | _31:bat[:oid,:timestamp] <tmp_1132>[0] := sql.bind(_5=21685368,"sys","my_test_table","my_t | : : ime",1); : | 24 | _28<tmp_1527>[359554] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestamp] | : : [0]); : | 23 | _32<tmp_1536>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 | : : .000,_24=2010-07-10 00:00:00.000,true,false); : | 35 | _29<tmp_1501>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 | : : .000,_24=2010-07-10 00:00:00.000,true,false); : | 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","dim5",0); : | 12 | _30<tmp_1534>[359554] := algebra.kunion(_28=nil,_29=nil); | | 5 | _44:bat[:oid,:str] <tmp_1103>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5 | : : e",2); : | 23 | _33<tmp_1501>[359554] := algebra.kunion(_30=nil,_32=nil); | | 6 | _46<tmp_1536>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:bat[ | : : :oid,:str][0]); : | 8 | _34<tmp_1534>[359554] := algebra.kdifference(_33=nil,_19=nil); | | 9 | _47<tmp_1501>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); | | 4 | _48:bat[:oid,:str] <tmp_1104>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5",1); : | 10 | _50<tmp_1527>[8000000] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); | | 6 | _66:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",0); : | 3 | _68:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",2); : | 7 | _69<tmp_1501>[8000000] := algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:bat[ | : : :oid,:lng][0]); : | 9 | _70<tmp_1541>[8000000] := algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); | | 4 | _71:bat[:oid,:lng] <tmp_1102>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",1); : | 8 | _74<tmp_1501>[8000000] := algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); | | 4 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable",2) | : : ; : | 7 | _59<tmp_1541>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:bat[ | : : :oid,:int][0]); : | 8 | _60<tmp_1524>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 3 | _61:bat[:oid,:int] <tmp_1066>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable",1) | : : ; : | 8 | _62<tmp_1541>[8000000] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 15525 | _35<tmp_1536>[359554] := algebra.semijoin(_20=nil,_34=nil); | | 2446 | _36<tmp_1534>[139] := algebra.uselect(_35=nil,A0=76:lng); | | 10 | _37<tmp_1536>[139] := algebra.markT(_36=nil,0@0:oid); | | 4 | _38<tmpr_1536>[139] := bat.reverse(_37=nil); | | 33 | _75<tmp_1477>[139] := algebra.leftjoin(_38=<tmpr_1536>[139],_74=nil); | | 47 | _51<tmp_1403>[139] := algebra.leftjoin(_38=<tmpr_1536>[139],_50=nil); | | 4 | _76<tmp_1477>[139] := algebra.selectNotNil(_75=nil); | | 47 | (ext61<tmp_1502>[5],grp59<tmp_1476>[139]) := group.done(_51=<tmp_1403>[139]); | | 19 | _63<tmp_1363>[139] := algebra.leftjoin(_38=nil,_62=nil); | | 8 | _54<tmp_1541>[5] := bat.mirror(ext61=<tmp_1502>[5]); | | 3 | _64<tmp_1363>[139] := algebra.selectNotNil(_63=nil); | | 47 | _55<tmp_1471>[5] := algebra.join(_54=<tmp_1541>[5],_51=nil); | | 43 | (_77<tmp_1344>[33],grp85) := group.derive(ext61=nil,grp59=<tmp_1476>[139],_76=<tmp_1477> | : : [139]); : | 6 | _79<tmp_1527>[33] := bat.mirror(_77=nil); | | 22 | _80<tmp_1506>[33] := algebra.join(_79=nil,_76=nil); | | 20 | _81:bat[:oid,:wrd] <tmp_1527>[5] := aggr.count(_80=nil,grp59=<tmp_1476>[139],_54=<tmp_15 | : : 41>[5]); : | 21 | _65:bat[:oid,:lng] <tmp_1536>[5] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 63484 | barrier _137 := language.dataflow(); | | 9 | _82 := sql.resultSet(3,1,_55=<tmp_1471>[5]); | | 7 | sql.rsColumn(_82=1,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=1,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 5 | sql.rsColumn(_82=1,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 3 | _99 := io.stdout(); | | 41 | sql.exportResult(_99=14711568,_82=1); | | 83716 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00"); | +-------+------------------------------------------------------------------------------------------+ SLOW QUERY TIME: sql>trace select dim5, sum(summable) as summables, count(distinct unique_id) as unqiue_ids from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; +-----------------------+--------+--------+ | dim1 | summables | unique_ids | +=======================+========+========+ | test1 | 251 | 18 | | test2 | 48 | 11 | | test3 | 1 | 1 | | test4 | 2 | 1 | | test4 | 106 | 2 | +-----------------------+--------+--------+ 5 tuples (2.7s) +---------+----------------------------------------------------------------------------------------+ | ticks | stmt | +=========+========================================================================================+ | 3 | _5 := sql.mvc(); | | 20 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_ | : : id",2); : | 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","acc | : : ount_id",0); : | 25 | _13<tmp_1705>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 4 | _15:bat[:oid,:lng] <tmp_1664>[10] := sql.bind(_5=21685368,"sys","my_test_table","account | : : _id",1); : | 20 | _14<tmp_1706>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 4 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=21685368,"sys","my_test_table",1); | | 8 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 31627 | _51<tmp_1670>[0] := algebra.leftjoin(_38=<tmpr_1665>[0],_50=nil); | | 20 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","my_time",0); : | 10 | _23 := calc.timestamp(A1="2010-07-03 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-07-10 00:00:00"); | | 7243 | (ext61<tmp_1710>[0],grp59<tmp_1661>[0]) := group.done(_51=<tmp_1670>[0]); | | 16 | _54<tmp_1665>[0] := bat.mirror(ext61=<tmp_1710>[0]); | | 47358 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil); | | 6 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil); | | 13 | (_77<tmp_1710>[0],grp85) := group.derive(ext61=nil,grp59=<tmp_1661>[0],_76=<tmp_1651>[ | : : 0]); : | 16 | _79<tmp_1674>[0] := bat.mirror(_77=nil); | | 11 | _80<tmp_1710>[0] := algebra.join(_79=nil,_76=nil); | | 10 | _81:bat[:oid,:wrd] <tmp_1651>[0] := aggr.count(_80=nil,grp59=<tmp_1661>[0],_54=<tmp_16 | : : 65>[0]); : | 20 | _55<tmp_1711>[0] := algebra.join(_54=<tmp_1665>[0],_51=nil); | | 11 | _65:bat[:oid,:lng] <tmp_1674>[0] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 11 | _82 := sql.resultSet(3,1,_55=<tmp_1711>[0]); | | 9 | sql.rsColumn(_82=235,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=235,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 4 | sql.rsColumn(_82=235,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 2 | _99 := io.stdout(); | | 30 | sql.exportResult(_99=24880032,_82=235); | | 420626 | _17<tmp_1705>[8000010] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 17 | _20<tmp_1665>[8000010] := algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); | | 13 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=21685368,"sys","my_test_table","my | : : _time",2); : | 5 | _31:bat[:oid,:timestamp] <tmp_1704>[10] := sql.bind(_5=21685368,"sys","my_test_table","m | : : y_time",1); : | 13 | _32<tmp_1673>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: | : : 00.000,_24=2010-07-10 00:00:00.000,true,false); : | 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","dim5",0); : | 4 | _44:bat[:oid,:str] <tmp_1103>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5",2); : | 7 | _46<tmp_1661>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:ba | : : t[:oid,:str][0]); : | 16 | _47<tmp_1706>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); | | 3 | _5 := sql.mvc(); | | 4 | _48:bat[:oid,:str] <tmp_1631>[10] := sql.bind(_5=21685368,"sys","my_test_table","dim5",1); : | 21 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","account_ | : : id",2); : | 5 | _15:bat[:oid,:lng] <tmp_1637>[10] := sql.bind(_5=25090760,"sys","my_test_table","account | : : _id",1); : | 4 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=25090760,"sys","my_test_table",1); | | 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 5 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","my_time",0); : | 8 | _23 := calc.timestamp(A1="2010-07-27 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-08-03 00:00:00"); | | 31883 | _25<tmp_1712>[391659] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-27 0 | : : 0:00:00.000,_24=2010-08-03 00:00:00.000,true,false); : | 11 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=25090760,"sys","my_test_table","my | : : _time",2); : | 21 | _28<tmp_1674>[391659] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 33 | _29<tmp_1714>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: | : : 00.000,_24=2010-08-03 00:00:00.000,true,false); : | 11 | _30<tmp_1713>[391659] := algebra.kunion(_28=nil,_29=nil); | | 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] := sql.bind(_5=25090760,"sys","my_test_table","m | : : y_time",1); : | 12 | _32<tmp_1714>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: | : : 00.000,_24=2010-08-03 00:00:00.000,true,false); : | 10 | _33<tmp_1674>[391659] := algebra.kunion(_30=nil,_32=nil); | | 7 | _39:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",0); : | 4 | _44:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","unqiue_id",2); : | 6 | _46<tmp_1713>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 12 | _47<tmp_1715>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); | | 4 | _48:bat[:oid,:lng] <tmp_1657>[10] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",1); : | 366305 | _50<tmp_1713>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); | | 14 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=25090760,"sys","my_test_table","summable", | : : 2); : | 17 | _59<tmp_1715>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 16 | _60<tmp_1716>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1362>[10] := sql.bind(_5=25090760,"sys","my_test_table","summable" | : : ,1); : | 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","acc | : : ount_id",0); : | 10 | _13<tmp_1717>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 298001 | _62<tmp_1715>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 21 | _14<tmp_1716>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 374628 | _17<tmp_1717>[8000010] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 20 | _20<tmp_1716>[8000010] := algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); | | 15 | _34<tmp_1720>[391659] := algebra.kdifference(_33=nil,_19=nil); | | 65082 | _35<tmp_1674>[391659] := algebra.semijoin(_20=nil,_34=nil); | | 2960 | _36<tmp_1714>[0] := algebra.uselect(_35=nil,A0=239:lng); | | 12 | _37<tmp_1674>[0] := algebra.markT(_36=nil,0@0:oid); | | 5 | _38<tmpr_1674>[0] := bat.reverse(_37=nil); | | 58011 | _51<tmp_1651>[0] := algebra.leftjoin(_38=<tmpr_1674>[0],_50=nil); | | 26 | (ext61<tmp_1710>[0],grp59<tmp_1713>[0]) := group.done(_51=<tmp_1651>[0]); | | 5 | _54<tmp_1674>[0] := bat.mirror(ext61=<tmp_1710>[0]); | | 27086 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil); | | 24 | _55<tmp_1715>[0] := algebra.join(_54=<tmp_1674>[0],_51=<tmp_1651>[0]); | | 3 | _66<tmp_1651>[0] := algebra.selectNotNil(_51=nil); | | 11 | (_67<tmp_1710>[0],grp75) := group.derive(ext61=nil,grp59=<tmp_1713>[0],_66=<tmp_1651>[ | : : 0]); : | 10 | _69<tmp_1712>[0] := bat.mirror(_67=nil); | | 5 | _70<tmp_1710>[0] := algebra.join(_69=nil,_66=nil); | | 9 | _71:bat[:oid,:wrd] <tmp_1651>[0] := aggr.count(_70=nil,grp59=<tmp_1713>[0],_54=<tmp_16 | : : 74>[0]); : | 3 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil); | | 11 | _65:bat[:oid,:lng] <tmp_1712>[0] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 1226556 | barrier _119 := language.dataflow(); | | 9 | _72 := sql.resultSet(3,1,_55=<tmp_1715>[0]); | | 8 | sql.rsColumn(_72=236,"sys.my_test_table","unique_id","bigint",64,0,_55=nil); | | 4 | sql.rsColumn(_72=236,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 3 | sql.rsColumn(_72=236,"sys.my_test_table","unique_ids","wrd",64,0,_71=nil:bat[:oid,:wrd]); | | 3 | _85 := io.stdout(); | | 37 | sql.exportResult(_85=24880032,_72=236); | | 2 | _5 := sql.mvc(); | | 18 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","account_ | : : id",2); : | 4 | _15:bat[:oid,:lng] <tmp_1637>[10] := sql.bind(_5=25090760,"sys","my_test_table","account | : : _id",1); : | 3 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=25090760,"sys","my_test_table",1); | | 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 6 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","my_time",0); : | 8 | _23 := calc.timestamp(A1="2010-06-24 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-07-01 00:00:00"); | | 18 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","acc | : : ount_id",0); : | 678 | _13<tmp_1713>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 22 | _14<tmp_1674>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 49964 | _25<tmp_1714>[372221] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-06-24 0 | : : 0:00:00.000,_24=2010-07-01 00:00:00.000,true,false); : | 14 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=25090760,"sys","my_test_table","my | : : _time",2); : | 21 | _28<tmp_1713>[372221] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 24 | _29<tmp_1720>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: | : : 00.000,_24=2010-07-01 00:00:00.000,true,false); : | 11 | _30<tmp_1712>[372221] := algebra.kunion(_28=nil,_29=nil); | | 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] := sql.bind(_5=25090760,"sys","my_test_table","m | : : y_time",1); : | 13 | _32<tmp_1720>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: | : : 00.000,_24=2010-07-01 00:00:00.000,true,false); : | 10 | _33<tmp_1713>[372221] := algebra.kunion(_30=nil,_32=nil); | | 5 | _39:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",0); : | 5 | _44:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",2); : | 6 | _46<tmp_1712>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 8 | _47<tmp_1717>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); | | 3 | _48:bat[:oid,:lng] <tmp_1657>[10] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",1); : | 1381664 | _50<tmp_1661>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); | | 17 | _66:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",0); : | 4 | _68:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",2); : | 19 | _69<tmp_1706>[8000000] := algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 17 | _70<tmp_1721>[8000000] := algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); | | 4 | _71:bat[:oid,:lng] <tmp_1703>[10] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",1); : | 405606 | _50<tmp_1712>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); | | 15 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=25090760,"sys","my_test_table","summable", | : : 2); : | 20 | _59<tmp_1717>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 17 | _60<tmp_1722>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1362>[10] := sql.bind(_5=25090760,"sys","my_test_table","summable" | : : ,1); : | 374062 | _74<tmp_1706>[8000010] := algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); | | 16 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable", | : : 2); : | 18 | _59<tmp_1721>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 16 | _60<tmp_1723>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1361>[10] := sql.bind(_5=21685368,"sys","my_test_table","summable" | : : ,1); : | 1884678 | _25<tmp_1707>[359554] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 0 | : : 0:00:00.000,_24=2010-07-10 00:00:00.000,true,false); : | 28 | _28<tmp_1724>[359554] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 30 | _29<tmp_1726>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: | : : 00.000,_24=2010-07-10 00:00:00.000,true,false); : | 11 | _30<tmp_1725>[359554] := algebra.kunion(_28=nil,_29=nil); | | 10 | _33<tmp_1726>[359554] := algebra.kunion(_30=nil,_32=nil); | | 7 | _34<tmp_1725>[359554] := algebra.kdifference(_33=nil,_19=nil); | | 63572 | _35<tmp_1726>[359554] := algebra.semijoin(_20=nil,_34=nil); | | 2812 | _36<tmp_1673>[139] := algebra.uselect(_35=nil,A0=76:lng); | | 10 | _37<tmp_1726>[139] := algebra.markT(_36=nil,0@0:oid); | | 5 | _38<tmpr_1726>[139] := bat.reverse(_37=nil); | | 58840 | _75<tmp_1715>[139] := algebra.leftjoin(_38=<tmpr_1726>[139],_74=nil); | | 6 | _76<tmp_1715>[139] := algebra.selectNotNil(_75=nil); | | 21903 | _51<tmp_1711>[139] := algebra.leftjoin(_38=<tmpr_1726>[139],_50=nil); | | 62 | (ext61<tmp_1344>[5],grp59<tmp_1651>[139]) := group.done(_51=<tmp_1711>[139]); | | 11 | _54<tmp_1661>[5] := bat.mirror(ext61=<tmp_1344>[5]); | | 46 | _55<tmp_1570>[5] := algebra.join(_54=<tmp_1661>[5],_51=nil); | | 34 | (_77<tmp_1500>[33],grp85) := group.derive(ext61=nil,grp59=<tmp_1651>[139],_76=<tmp_171 | : : 5>[139]); : | 6 | _79<tmp_1706>[33] := bat.mirror(_77=nil); | | 24 | _80<tmp_1502>[33] := algebra.join(_79=nil,_76=nil); | | 22 | _81:bat[:oid,:wrd] <tmp_1706>[5] := aggr.count(_80=nil,grp59=<tmp_1651>[139],_54=<tmp_ | : : 1661>[5]); : | 554759 | _62<tmp_1717>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 541977 | _62<tmp_1721>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 41224 | _63<tmp_1356>[139] := algebra.leftjoin(_38=nil,_62=nil); | | 10 | _64<tmp_1356>[139] := algebra.selectNotNil(_63=nil); | | 48 | _65:bat[:oid,:lng] <tmp_1721>[5] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 2761640 | barrier _137 := language.dataflow(); | | 12 | _82 := sql.resultSet(3,1,_55=<tmp_1570>[5]); | | 8 | sql.rsColumn(_82=3,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=3,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 6 | sql.rsColumn(_82=3,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 3 | _99 := io.stdout(); | | 45 | sql.exportResult(_99=14711568,_82=3); | | 2761973 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00"); | +---------+----------------------------------------------------------------------------------------+

1 0

[MonetDB-users] performance degrades when date related function is used
by Ramkumarlak 03 Feb '11

03 Feb '11

Hi, I am looking for monerdb to replace mysql. I tried some queries with my test data and all were much more faster than mysql. But our actual table contains a column which stores date in unix time format and we do generate reports based on week, day, quarter, month and year. For that we are using a lot mysql date related functions. I too tried the following approach for one of my requirements. cast(timestamp '1970-01-01 00:00:00' + cast(created_date/1000 as interval second) as date) but it takes longer time. 1) Is there any other way to make this query faster ? 2) what are all the options are available in monetdb to deal these (week,quarter,hour,day and year wise reporting) kind of queries. Any help will be appreciated. Thanks and Regards Ram -- View this message in context: http://old.nabble.com/performance-degrades-when-date-related-function-is-us… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] query slowdown with concurrent write
by Bennett Hiles 03 Feb '11

03 Feb '11

Hi, MonetDB has great response times for the queries I have been running, but once I start inserting data to the table and concurrently running the queries, the queries slow down. To give you concrete numbers, I am initializing the database with 8 million records. I then run random aggregation queries repeatedly. Before I insert additional data the max query time is 0.3 seconds. If I then insert 10 rows the queries slow to a max of 1.5s. The only solution I have to getting the queries to resume their original speed is to drop the data and reload the 8 millions rows into the table. I have also noticed that if I run my inserts while I am not concurrently making queries to the database, I don't run into the slowness, and the queries keep their 0.3s response time. Has anyone else encountered this problem before? Is there a way around it? I have also tried using the copy command instead of inserts but get the same result. Thanks, Bennett

3 2

[MonetDB-users] MAL
by Michael Kusber 02 Feb '11

02 Feb '11

Hello guys, I aplogize for bothering you with what might be a simple issue for you, but I am new to monetDB and I have to solve some complex problems for which I cannot find a solution by myself. I've read about MAL in the M5 reference manual. I recognized, the normal way to execute commands is via MAPI and JDBC and stuff based on MAPI. This will be parsed into MAL, optimized and executed. Is there a way to write MAL code and give it as input to the server directly?? Another question: Is it possible to explicitely invoke execution of a query in multiple threads via MAPI or other means? Kind regards, michael

3 2