users-list February 2011

users-list@monetdb.org

22 participants
15 discussions

[MonetDB-users] protocol violation: unexpected line in data block

by tangray

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.

11 years, 5 months

Re: [MonetDB-users] Query time takes equally long on whole

by dsuh

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 > > > > >

11 years, 5 months

[MonetDB-users] monetdb on ubuntu 10.10 - xquery results displaying only in single line!

by sagarjauhari

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.

11 years, 5 months

[MonetDB-users] Stored Procedures in MonetDB

by Irini Fundulaki

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.

11 years, 5 months

[MonetDB-users] Query time takes equally long on whole and just small part of table

by Rob Berentsen

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

11 years, 5 months

[MonetDB-users] MAL changes in March2011

by Mike De-La-Columnar

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.

11 years, 5 months

[MonetDB-users] rdf.sql missing from Mar2011 branch

by Babis Nikolaou

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

11 years, 5 months

[MonetDB-users] Kernel / FS tuning

by Franck Routier

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

11 years, 5 months

[MonetDB-users] Problem starting monetdbd after installing MonetDB

by Babis Nikolaou

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

11 years, 5 months

[MonetDB-users] Bug in EXTRACT?

by Alex Bo.

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.

11 years, 5 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list February 2011