- users-list - monetdb.org

Octopus and
by Masood Mortazavi 02 Dec '12

02 Dec '12

Hi, Where would I find any documentation or instructions to try MonetDB's Octopus functionality, assuming it is still active at (or near) the tip? Regards, Masood P.S. I would also be interested in exploring Monet's any other distributed processing functionality, say Monet/Datacyclotron or Monet/MapReduce. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 1

Re: Same SQL Statement with different throughput time.
by Stefan Manegold 30 Nov '12

30 Nov '12

I guess it's a good idea to run your query in both cases, slow & fast prefixed with TRACE to see where time is spent. BTW, are we talking about genuine MonetDB (as Letters asked: which version) and hence original MonetDB SUM()-implementation, or has the code been modified or extended; f the latter: where & how? S. -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | ----- Reply message ----- From: "Lefteris" <lsidir(a)gmail.com> To: "Communication channel for MonetDB users" <users-list(a)monetdb.org> Subject: Same SQL Statement with different throughput time. Date: Fri, Nov 30, 2012 12:58 just restarting monetdb wont do it (OS will still keep in memory the data), you will also need to clean the memory, for example alloc all memory space, write 1's and then free again the memory. Swapnil, which version of monetdb are you using? IMO, 11 seconds vs. 30 milliseconds is too much of a difference to be just I/O. But maybe I am wrong, maybe your dbfarm is on a network? On Fri, Nov 30, 2012 at 12:42 PM, Charalampos Nikolaou <charnik(a)di.uoa.gr>wrote: > I think the reason is that for the first query the time is spent > mainly for loading your table in main memory. > Then, the second query has only the overhead of computing the sum over > column V1. > > To see this, you can run the query, restart monetdbd, and then run the > query again. You should get similar performance to that of the first > run. > > Babis > > On Fri, Nov 30, 2012 at 1:29 PM, swapnil joshi > <webmaster.swapnil(a)gmail.com> wrote: > > Hi Stefan, > > If monetdb doesn't maintain any caching then when i am creating > test1 > > database > > which contain demo table which contain 100 million records. > > > > When first time i am executing "SELECT SUM(V1) FROM DEMO;" > > monetdb gives me result in (11.4s) > > when immediate i am trying to execute same query "SELECT SUM(V1) FROM > DEMO;" > > It gives me result in (31.065ms) > > > > How / why this happen? > > > > I have also checked my OS memory cache and I have erased OS Memory cache. > > Now, I am trying same query "SELECT SUM(V1) FROM DEMO;" > > now also he take same time in milliseconds approximate(30.723ms) > > > > why he first time take lot of time and every next time he take negligible > > time. > > > > I hope you will clear my problem related of throughput time. > > > > -- > > Regards, > > Swapnil K. Joshi > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > http://mail.monetdb.org/mailman/listinfo/users-list > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Same SQL Statement with different throughput time.
by swapnil joshi 30 Nov '12

30 Nov '12

Hi Stefan, If monetdb doesn't maintain any caching then when i am creating test1 database which contain demo table which contain 100 million records. When first time i am executing "SELECT SUM(V1) FROM DEMO;" monetdb gives me result in (11.4s) when immediate i am trying to execute same query "SELECT SUM(V1) FROM DEMO;" It gives me result in (31.065ms) How / why this happen? I have also checked my OS memory cache and I have erased OS Memory cache. Now, I am trying same query "SELECT SUM(V1) FROM DEMO;" now also he take same time in milliseconds approximate(30.723ms) why he first time take lot of time and every next time he take negligible time. I hope you will clear my problem related of throughput time. -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Monetdb Cacheing
by swapnil joshi 30 Nov '12

30 Nov '12

Hi, Is there monetdb maintain any SQL result cache. If yes, then How and where is it maintain? and If no then how he will get millions of rows result in millisecond. My System configuration is as follow : Hardware = Processor : Intel i7 2.3 GHz, RAM DDR3 8GB, HD 1 TB, OS = Ubuntu 12.04 I am new in IT world and i am doing some research on Monetdb. I have created my own user define function but it's result is not cached, every time it is doing operation on data and it take some in milli seconds. Please tell me about monetdb cacheing policy Can you give me some guide line? -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

define your own user define function
by swapnil joshi 29 Nov '12

29 Nov '12

Hi, i am trying to define user define function which has argument integer bat and returning integer. step 1: i have added function defination in udf_80.sql as follow create function xtest(one integer) returns integer external name udf.xtest; step 2: i have added function defination in udf.mal module udf; command xtest(b:bat[:oid,:int]):int address UDFBATxtest comment "function xtest"; module batudf; command xtest(b:bat[:oid,:int]):int address UDFBATxtest comment "function xtest"; step 3: then i have define this function in udf.c file UDFBATxtest_(int *ret, BAT *src) { return MAL_SUCCEED; } str UDFBATxtest(int *ires, bat *ione) { BAT *bone = NULL; str msg = NULL; /* assert calling sanity */ assert(ione != NULL); /* bat-id -> BAT-descriptor */ if ((bone = BATdescriptor(*ione)) == NULL) throw(MAL, "batudf.xtest", RUNTIME_OBJECT_MISSING); /* do the work */ msg = UDFBATxtest_ ( ires, bone); return msg; } step 4: then i have compiled source code and installed it: step 5: i am try to execute xtest function on mclient but i got following error sql>select xtest(id) from student; TypeException:user.s23_1[16]:'udf.xtest' undefined in: _53:any := udf.xtest(_51:int) TypeException:user.s23_1[17]:'bat.insert' undefined in: _54:any := bat.insert(_45:bat[:oid,:int], _50:oid, _53:any) program contains errors step 6: write a mail and sent to you for getting help i hope you will give me. -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 7

UDF aggregate?
by Miguel Ping 28 Nov '12

28 Nov '12

Greetings, We're currently evaluating MonetDB for a analytical DW and so far we are happy with the results. I am trying to implement a grouping function that calculates a a value over a set of strings, so that my queries would read like this: select metric, *udf_aggregate*(string_column) from table group by metric; for a bit of background, we're using a dinstinct value sketch called HyperLogLog http://metamarkets.com/2012/fast-cheap-and-98-right-cardinality-estimation-… http://blog.aggregateknowledge.com/2012/10/25/sketch-of-the-day-hyperloglog… and we're currently storing estimations for each time period (day). HLL lets you merge/aggregate a set of estimations (each estimation is a vector of numbers, we're currently storing it as a string) for an arbitrary range, and still have an accurate estimation. (I'm sure the literature doesn't call it estimations, sorry for my English) what I would like is a custom UDF like the one provided in MonetDB src (*reverse*)**but that would operate and behave like an aggregate function. Right now, I'm not considering using it for types other than /string/ (no need for polymorphic right now). Is this possible with an UDF? I found a way of registering aggregate functions on the mailing list, but the HLL is complex enough to warrant its own C impl, instead of a MAL function. Thanks, Miguel _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 3

Solid state drives for MonetDB
by Siddhartha Bagaria 28 Nov '12

28 Nov '12

Hello everyone! This is my second posting to this list and identical to the first because I am not sure if I was subscribed at the time. Please ignore if you have read this before. My apologies if this has been discussed elsewhere but a google search did not give me much. Let me give you a bit of background. We are planning to use a star schema in MonetDB as the backend for Pentaho BI reporting engine. We have a total of 2.4 B rows in the fact table and a maximum of 10 M rows in the dimension table. As you can see, this will be a read only database once the initial load is done. I was wondering what hardware architecture will give a reasonable performance over traditional BI queries. Let's define reasonable here by less than 1 minute turn-around time on joins between the fact table and the dimension table. On that note, what will be the expected performance on a machine with specs identical to the Amazon High I/O instance type which has two solid state drives - http://aws.amazon.com/ec2/instance-types/. I will of course combine the two drives in a software raid-0 configuration. My broader question is that columnar databases are typically optimized for hard drives to utilize the maximum possible disk transfer rate,; what advantages can a solid state drive offer over a traditional hard drive when used solely for MonetDB? I can imagine some advantages maybe in the swap-ins for VM pages but I am not really an expert. Also, some advantages in multi-threaded disk reads. Sid _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Looking for advice on ways to limit Monet's RAM usage
by Percy Wegmann 28 Nov '12

28 Nov '12

Hello, We're running the Oct2012-SP1 release on Ubuntu 12.04.1. It's working great, but we need to constrain its RAM usage because it's not the only process running on its machine. >From the mailing list archives, it looks like ulimit is the recommended way of doing this, but we haven't been able to get it to do what we want. Specifically, we've tried: ulimit -Sm (max resident set size) ulimit -Sv (max virtual memory) Limiting the resident set size doesn't seem to have an effect - the mserver5 process's resident size seems to grow freely. Limiting the virtual memory does in fact manage to keep mserver5 from consuming more than the allocated amount, however when mserver5 hits the limit, it just crashes (which is what I would have expected based on this document <http://www.monetdb.org/Documentation/UserGuide/resources>). Our next step is to look at Linux Containers <http://lxc.sourceforge.net/>, but before going that route I figured I'd check and see how other folks have handled this. Thanks in advance, Percy -- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 3

!TypeException:user.s1_1[76]:'aggr.sum' undefined
by swapnil joshi 28 Nov '12

28 Nov '12

Hello Sir, I have some problem in MAL execution. *setp 1:* I have open [mclient -d test1] and {explain select sum(v1) from demo;} it show following result. swapnil@swapnil-lp:~$ mclient test1 Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2012-SP1) Database: MonetDB v11.11.7 (Jul2012-SP1), 'mapi:monetdb://swapnil-lp:50000/test1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>explain select sum(v1) from demo; +-------------------------------------------------------------------------------+ | mal | +===============================================================================+ | function user.s1_1{autoCommit=true}():void; | | X_13:lng := nil:lng; | | barrier X_141 := language.dataflow(); | | X_2 := sql.mvc(); | | X_35:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,0,8); | | X_71 := algebra.selectNotNil(X_35); | | X_60:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,0,8); | | X_83 := algebra.kdifference(X_71,X_60); | | X_95 := algebra.selectNotNil(X_60); | | X_104 := algebra.kunion(X_83,X_95); | | X_11:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","demo",1); | | X_12 := bat.reverse(X_11); | | X_117 := algebra.kdifference(X_104,X_12); | | X_131 := aggr.sum(X_117); | | X_39:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,1,8); | | X_73 := algebra.selectNotNil(X_39); | | X_62:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,1,8); | | X_84 := algebra.kdifference(X_73,X_62); | | X_97 := algebra.selectNotNil(X_62); | | X_106 := algebra.kunion(X_84,X_97); | | X_118 := algebra.kdifference(X_106,X_12); | | X_132 := aggr.sum(X_118); | | X_42:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,2,8); | | X_75 := algebra.selectNotNil(X_42); | | X_63:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,2,8); | | X_85 := algebra.kdifference(X_75,X_63); | | X_98 := algebra.selectNotNil(X_63); | | X_108 := algebra.kunion(X_85,X_98); | | X_119 := algebra.kdifference(X_108,X_12); | | X_133 := aggr.sum(X_119); | | X_45:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,3,8); | | X_77 := algebra.selectNotNil(X_45); | | X_64:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,3,8); | | X_86 := algebra.kdifference(X_77,X_64); | | X_99 := algebra.selectNotNil(X_64); | | X_110 := algebra.kunion(X_86,X_99); | | X_120 := algebra.kdifference(X_110,X_12); | | X_134 := aggr.sum(X_120); | | X_48:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,4,8); | | X_78 := algebra.selectNotNil(X_48); | | X_65:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,4,8); | | X_87 := algebra.kdifference(X_78,X_65); | | X_100 := algebra.selectNotNil(X_65); | | X_112 := algebra.kunion(X_87,X_100); | | X_121 := algebra.kdifference(X_112,X_12); | | X_135 := aggr.sum(X_121); | | X_51:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,5,8); | | X_80 := algebra.selectNotNil(X_51); | | X_66:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,5,8); | | X_89 := algebra.kdifference(X_80,X_66); | | X_101 := algebra.selectNotNil(X_66); | | X_113 := algebra.kunion(X_89,X_101); | | X_123 := algebra.kdifference(X_113,X_12); | | X_136 := aggr.sum(X_123); | | X_54:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,6,8); | | X_81 := algebra.selectNotNil(X_54); | | X_68:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,6,8); | | X_91 := algebra.kdifference(X_81,X_68); | | X_102 := algebra.selectNotNil(X_68); | | X_114 := algebra.kunion(X_91,X_102); | | X_125 := algebra.kdifference(X_114,X_12); | | X_137 := aggr.sum(X_125); | | X_58:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,7,8); | | X_82 := algebra.selectNotNil(X_58); | | X_70:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,7,8); | | X_93 := algebra.kdifference(X_82,X_70); | | X_103 := algebra.selectNotNil(X_70); | | X_115 := algebra.kunion(X_93,X_103); | | X_127 := algebra.kdifference(X_115,X_12); | | X_138 := aggr.sum(X_127); | | X_8:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",1); | | X_10 := algebra.selectNotNil(X_8); | | X_129 := algebra.kdifference(X_10,X_12); | | X_139 := aggr.sum(X_129); | | X_130 := mat.pack(X_131,X_132,X_133,X_134,X_135,X_136,X_137,X_138,X_139); | | X_140 := algebra.selectNotNil(X_130); | | X_13:lng := aggr.sum(X_140); | | exit X_141; | | sql.exportValue(1,"sys.demo","L1","bigint",32,0,6,X_13,""); | | end s1_1; | +-------------------------------------------------------------------------------+ 80 tuples (2.845ms) *Setp 2:* I have copy same mal program on another teminal [mclient -lmsql -dtest1] swapnil@swapnil-lp:~$ mclient -lmsql -dtest1 Welcome to mclient, the MonetDB interactive terminal (Jul2012-SP1) Type \q to quit, \? for a list of available commands msql>function user.s1_1{autoCommit=true}():void; msql> X_13:lng := nil:lng; msql> barrier X_141 := language.dataflow(); msql> X_2 := sql.mvc(); msql> X_35:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,0,8); msql> X_71 := algebra.selectNotNil(X_35); msql> X_60:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,0,8); msql> X_83 := algebra.kdifference(X_71,X_60); msql> X_95 := algebra.selectNotNil(X_60); msql> X_104 := algebra.kunion(X_83,X_95); msql> X_11:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","demo",1); msql> X_12 := bat.reverse(X_11); msql> X_117 := algebra.kdifference(X_104,X_12); msql> X_131 := aggr.sum(X_117); msql> X_39:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,1,8); msql> X_73 := algebra.selectNotNil(X_39); msql> X_62:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,1,8); msql> X_84 := algebra.kdifference(X_73,X_62); msql> X_97 := algebra.selectNotNil(X_62); msql> X_106 := algebra.kunion(X_84,X_97); msql> X_118 := algebra.kdifference(X_106,X_12); msql> X_132 := aggr.sum(X_118); msql> X_42:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,2,8); msql> X_75 := algebra.selectNotNil(X_42); msql> X_63:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,2,8); msql> X_85 := algebra.kdifference(X_75,X_63); msql> X_98 := algebra.selectNotNil(X_63); msql> X_108 := algebra.kunion(X_85,X_98); msql> X_119 := algebra.kdifference(X_108,X_12); msql> X_133 := aggr.sum(X_119); msql> X_45:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,3,8); msql> X_77 := algebra.selectNotNil(X_45); msql> X_64:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,3,8); msql> X_86 := algebra.kdifference(X_77,X_64); msql> X_99 := algebra.selectNotNil(X_64); msql> X_110 := algebra.kunion(X_86,X_99); msql> X_120 := algebra.kdifference(X_110,X_12); msql> X_134 := aggr.sum(X_120); msql> X_48:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,4,8); msql> X_78 := algebra.selectNotNil(X_48); msql> X_65:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,4,8); msql> X_87 := algebra.kdifference(X_78,X_65); msql> X_100 := algebra.selectNotNil(X_65); msql> X_112 := algebra.kunion(X_87,X_100); msql> X_121 := algebra.kdifference(X_112,X_12); msql> X_135 := aggr.sum(X_121); msql> X_51:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,5,8); msql> X_80 := algebra.selectNotNil(X_51); msql> X_66:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,5,8); msql> X_89 := algebra.kdifference(X_80,X_66); msql> X_101 := algebra.selectNotNil(X_66); msql> X_113 := algebra.kunion(X_89,X_101); msql> X_123 := algebra.kdifference(X_113,X_12); msql> X_136 := aggr.sum(X_123); msql> X_54:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,6,8); msql> X_81 := algebra.selectNotNil(X_54); msql> X_68:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,6,8); msql> X_91 := algebra.kdifference(X_81,X_68); msql> X_102 := algebra.selectNotNil(X_68); msql> X_114 := algebra.kunion(X_91,X_102); msql> X_125 := algebra.kdifference(X_114,X_12); msql> X_137 := aggr.sum(X_125); msql> X_58:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",0,7,8); msql> X_82 := algebra.selectNotNil(X_58); msql> X_70:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",2,7,8); msql> X_93 := algebra.kdifference(X_82,X_70); msql> X_103 := algebra.selectNotNil(X_70); msql> X_115 := algebra.kunion(X_93,X_103); msql> X_127 := algebra.kdifference(X_115,X_12); msql> X_138 := aggr.sum(X_127); msql> X_8:bat[:oid,:int] := sql.bind(X_2,"sys","demo","v1",1); msql> X_10 := algebra.selectNotNil(X_8); msql> X_129 := algebra.kdifference(X_10,X_12); msql> X_139 := aggr.sum(X_129); msql> X_130 := mat.pack(X_131,X_132,X_133,X_134,X_135,X_136,X_137,X_138,X_139); msql> X_140 := algebra.selectNotNil(X_130); msql> X_13:lng := aggr.sum(X_140); msql> exit X_141; msql> sql.exportValue(1,"sys.demo","L1","bigint",32,0,6,X_13,""); msql> end s1_1; MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = end s1_1; ERROR = !TypeException:user.s1_1[76]:'aggr.sum' undefined in: X_13:lng := aggr.sum(X_140:bat[:oid,:dbl]) msql>user.s1_1 MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = user.s1_1 ERROR = !SyntaxException:parseError: !'(' expected msql>user.s1_1(); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = user.s1_1(); ERROR = !TypeException:user.main[1]:'user.s1_1' undefined in: _1:void := user.s1_1() msql> It gives such error what is reason of giving such error by monetdb. i am really very frustrate what is reasone and how will i execute mal program. please help me !!!!!!!!!!!!! :) and another question is is Monetdb maintain any cache for storing result of SQL statment. -- Regards, Swapnil K. Joshi -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Support of nulls first / last in order clause
by Matthieu Guamis 23 Nov '12

23 Nov '12

Hello, "NULLS LAST" seems to be part of the SQL 99 standardbut is not implemented in Monetdb. Do you think it couldbe implemented in Monetdb? Can I fill a bug?Can I help you in some way? The syntax is as follows: ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST] In different DBs, the sorting of nulls relative to other values is handled differently. PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 - Higher Oracle - Higher MySQL - Lower Monetdb - Lower Thanks -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 22 bis, rue des Chazots 63170 Aubière/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0