- users-list - monetdb.org

Manually Loading all SQL Script
by swapnil joshi 04 Dec '12

04 Dec '12

Hi , My SQL Script is loaded only one's, When i am creating new database using following command I have copy and paste my terminal command swapnil@swapnil-lp:~$ sudo monetdb create test5 [sudo] password for swapnil: Sorry, try again. [sudo] password for swapnil: created database in maintenance mode: test5 swapnil@swapnil-lp:~$ sudo monetdb release test5 taken database out of maintenance mode: test5 swapnil@swapnil-lp:~$ sudo monetdb start test5 starting database 'test5'... done swapnil@swapnil-lp:~$ mclient test5 Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2012-SP1) Database: MonetDB v11.11.7 (Jul2012-SP1), 'mapi:monetdb://swapnil-lp:50000/test5' Type \q to quit, \? for a list of available commands auto commit mode: on sql> After that if i am checking merovingian.log (Monetdb log file) 2012-12-04 17:57:20 MSG test2[11694]: # SQL catalog created, loading sql scripts once 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 09_like.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 10_math.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 11_times.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 12_url.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 13_date.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 14_inet.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 15_history.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 16_tracelog.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 17_compress.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 18_dictionary.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 19_cluster.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 20_vacuum.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 21_dependency_functions.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 22_clients.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 23_skyserver.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 24_zorder.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 25_debug.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 40_geom.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 80_udf.sql 2012-12-04 17:57:20 MSG test2[11694]: # loading sql script: 99_system.sql How will i manually Lode this script because if i have change in any *.sql file each time I need to create new database How will i avoid this manual work? Please help me!!!! :) -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Can Monetdb drop Calculation of dropped column?
by swapnil joshi 04 Dec '12

04 Dec '12

Hi I have executed following SQL Statement : *sql>select product_id, sum(unit_sales),avg(unit_sales), max(unit_sales), min(unit_sales),sum(store_sales),avg(store_sales), max(store_sales), min(store_sales), sum(store_cost),avg(store_cost), max(store_cost), min(store_cost) from sales_fact_1997 group by product_id; It Gives following result: (In my table very large data there for i have given starting and ending rows of result set) +-----------+--------------+--------------------------+--------------+--------------+--------------+--------------------------+--------------+--------------+ | product_i | L1 | L2 | L3 | L4 | L5 | L6 | L7 | L10 |> : d : : : : : : : : :> +===========+==============+==========================+==============+==============+==============+==========================+==============+==============+ | 337 | 333644.7415 | 3.4849771407382648 | 6.9998 | 0.0001 | 1145678.6601 | 11.966812134157813 | 23.9986 | 0.0005 | | 1512 | 336475.1207 | 3.4816655356884185 | 6.9999 | 0.0001 | 1158967.1049 | 11.992375001552128 | 23.9995 | 0.0011 | | 963 | 333727.8589 | 3.4994427668141643 | 6.9997 | 0.0003 | 1139917.6212 | 11.953082033429096 | 23.9998 | 0.0008 | | 181 | 334323.6059 | 3.4942578847801946 | 6.9999 | 0.0003 | 1145948.0178 | 11.977131815046297 | 23.9980 | 0.0002 | | 1383 | 334298.1724 | 3.4923130291254028 | 6.9999 | 0.0002 | 1145319.9025 | 11.964814492708198 | 23.9992 | 0.0015 | | 1116 | 337661.6147 | 3.4923165958195015 | 6.9999 | 0.0003 | 1161142.4618 | 12.009292477789144 | 23.9996 | 0.0003 | | 488 | 336083.8006 | 3.5025147266948111 | 7.0000 | 0.0008 | 1149952.2337 | 11.984286735448883 | 23.9992 | 0.0004 | | 121 | 336465.6749 | 3.486221285215461 | 6.9999 | 0.0001 | 1154632.2251 | 11.963489116492081 | 23.9998 | 0.0001 | | 1338 | 337204.2797 | 3.478592072170585 | 7.0000 | 0.0001 | 1162678.8747 | 11.994170179601195 | 23.9996 | 0.0015 | | 910 | 338799.1540 | 3.4927387758889044 | 6.9999 | 0.0001 | 1166834.3104 | 12.029095683549643 | 23.9996 | 0.0001 | | 438 | 335488.9579 | 3.5050823580421069 | 6.9999 | 0.0002 | 1150602.4234 | 12.021129639032544 | 23.9982 | 0.0005 | | 1560 | 338661.5694 | 3.4991844580143199 | 6.9997 | 0.0001 | 1154747.3055 | 11.93130307492018 | 23.9994 | 0.0019 | +-----------+--------------+--------------------------+--------------+--------------+--------------+--------------------------+--------------+--------------+ 1560 tuples (3m 55s) !4 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 Hear, Monetdb gives only 9 column in result table and gives message 4 columns dropped as i show in following query it gives result of only yellow highlighted statement / columns result. Monetdb can not show blue highlighted part's result. **sql>select product_id, sum(unit_sales),avg(unit_sales), max(unit_sales), min(unit_sales),sum(store_sales),avg(store_sales), max(store_sales), min(store_sales), sum(store_cost),avg(store_cost), max(store_cost), min(store_cost) from sales_fact_1997 group by product_id; my question is that, Monetdb calculate result of all SQL statement or it does not calculate dropped column value. My Next Question How will I see this Dropped result? I hope you will give me proper guidance. My Monetdb version : MonetDB-11.11.07 * * * -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Problems installing monet db in mountain lion
by Ivan Videla 03 Dec '12

03 Dec '12

Hello, i try to install monetdb under mac os x mountain lion. I downloaded monetdb for mac os x. X86_64 version. I copied monetdb folder to /usr/local, but i haven't been able to start a database. I have followed the instructions from here http://www.monetdb.org/Documentation/monetdbd when i get to this point ( monetdb release my-first-db ) after i try to do a monetdb status. I get that my-first-db is stopped. After i checked log file i get a message that the database appears to shutdown itself after start. I would like to know if someone has running succesfully monetdb in mac os x mountain lion. Regards, Ivan _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Can each time it is accessing BAT from disk or from main memory? In my own UDF (xmin)
by swapnil joshi 02 Dec '12

02 Dec '12

Hi I have done some fun with Monetdb. i have created my own user define xmin function. objective of that function is find minimum value from specific column. I have writen some code as follow : /****************************************************************************************/ /* Function Name : xmin */ /****************************************************************************************/ #define UI sht #define UU unsigned short #define UO sht #include "impl_xmin.h" #undef UI #undef UU #undef UO #define UI int #define UU unsigned short #define UO int #include "impl_xmin.h" #undef UI #undef UU #undef UO #define UI lng #define UU unsigned short #define UO lng #include "impl_xmin.h" #undef UI #undef UU #undef UO #define UI dbl #define UU unsigned short #define UO dbl #include "impl_xmin.h" #undef UI #undef UU #undef UO static str UDFBATxmin_(BAT **ret, BAT *bone) { BAT *bres = NULL; bit two_tail_sorted_unsigned = FALSE; bit two_tail_revsorted_unsigned = FALSE; BUN n; str msg = NULL; /* assert calling sanity */ assert(ret != NULL); /* handle NULL pointer */ if (bone == NULL) throw(MAL, "batudf.xmin", RUNTIME_OBJECT_MISSING); n = BATcount(bone); /* allocate result BAT */ switch (bone->ttype) { case TYPE_sht: bres = BATnew(TYPE_void, TYPE_sht, TOTALTHREAD); break; case TYPE_int: bres = BATnew(TYPE_void, TYPE_int, TOTALTHREAD); break; case TYPE_lng: bres = BATnew(TYPE_void, TYPE_lng, TOTALTHREAD); break; case TYPE_dbl: bres = BATnew(TYPE_void, TYPE_dbl, TOTALTHREAD); break; default: throw(MAL, "batudf.xmin","tails of input BATs must be one of {sht, int, lng, dbl}"); } if (bres == NULL) throw(MAL, "batudf.xmin", MAL_MALLOC_FAIL); /* advice on sequential scan */ BATaccessBegin(bone, USE_TAIL, MMAP_SEQUENTIAL); /* call type-specific core algorithm */ switch (bone->ttype) { case TYPE_sht: msg = UDFBATxmin_sht_sht ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_int: msg = UDFBATxmin_int_int ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_lng: msg = UDFBATxmin_lng_lng ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_dbl: msg = UDFBATxmin_dbl_dbl ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; default: BBPreleaseref(bres->batCacheid); throw(MAL, "batudf.xmin","tails of input BATs must be one of {sht, int, lng, dbl}"); } BATaccessEnd(bone, USE_TAIL, MMAP_SEQUENTIAL); if (msg != MAL_SUCCEED) { BBPreleaseref(bres->batCacheid); } else { /* set number of tuples in result BAT */ BATsetcount(bres, 1); /* set result properties */ bres->hdense = TRUE; /* result head is dense */ BATseqbase(bres, bone->hseqbase); /* result head has same seqbase as input */ bres->hsorted = 1; /* result head is sorted */ bres->hrevsorted = (BATcount(bres) <= 1); BATkey(bres, TRUE); /* result head is key (unique) */ /* Result tail is sorted, if the left/first input tail is * sorted and key (unique), or if the left/first input tail is * sorted and the second/right input tail is sorted and the * second/right tail values are either all >= 0 or all < 0; * otherwise, we cannot tell. */ if (BATtordered(bone) && (BATtkey(bone) || two_tail_sorted_unsigned)) bres->tsorted = 1; else bres->tsorted = (BATcount(bres) <= 1); if (BATtrevordered(bone) && (BATtkey(bone) || two_tail_revsorted_unsigned)) bres->trevsorted = 1; else bres->trevsorted = (BATcount(bres) <= 1); /* result tail is key (unique), iff both input tails are */ /*BATkey(BATmirror(bres), BATtkey(bone) || BATtkey(btwo));*/ *ret = bres; } return msg; } /* MAL wrapper */ str UDFBATxmin(bat *ires, bat *ione) { // startTimer(); BAT *bres = NULL, *bone = NULL; str msg = NULL; /* assert calling sanity */ assert(ires != NULL && ione != NULL); /* bat-id -> BAT-descriptor */ if ((bone = BATdescriptor(*ione)) == NULL) throw(MAL, "batudf.xmin", RUNTIME_OBJECT_MISSING); /* do the work */ msg = UDFBATxmin_ ( &bres, bone); /* release input BAT-descriptors */ BBPreleaseref(bone->batCacheid); if (msg == MAL_SUCCEED) { /* register result BAT in buffer pool */ BBPkeepref((*ires = bres->batCacheid)); } // printf("Total time Required to UDFBATmin = %lf",endTimer()); return msg; } /****************************************************************************************/ /* Function Name : impl_xmin.h */ /****************************************************************************************/ * impl_xmin.h * * Created on: 23-Nov-2012 * Author: swapnil */ /* concatenate two or five tokens */ #define U_CONCAT_2(a,b) a##b #define U_CONCAT_5(a,b,c,d,e) a##b##c##d##e /* function names, *_nil & TYPE_* macros */ #define UF(p,i,o,s) U_CONCAT_5(p,i,_,o,s) #define UN(t) U_CONCAT_2(t,_nil) #define UT(t) U_CONCAT_2(TYPE_,t) static str UF(UDFBATxmin_,UI,UO,) ( BAT *bres, BAT *bone, BUN n, bit *two_tail_sorted_unsigned, bit *two_tail_revsorted_unsigned ) { UI *one = NULL; UO *res = NULL; BUN i; /* assert calling sanity */ assert(bres != NULL && bone != NULL); assert(BATcapacity(bres) >= 1); assert(BATcount(bone) >= n); assert(bone->ttype == UT(UI)); assert(bres->ttype == UT(UO)); /* get direct access to the tail arrays */ one = (UI*) Tloc(bone, BUNfirst(bone)); res = (UO*) Tloc(bres, BUNfirst(bres)); int loop; startTimer(); res[0]=one[0]; for(loop=1;loop<n;loop++) { if(res[0]>one[loop]) res[0]=one[loop]; } printf("Time for clReleaseMemObject = %lf\n",endTimer()); return MAL_SUCCEED; } /* undo local defines */ #undef UT #undef UN #undef UF #undef U_CONCAT_5 #undef U_CONCAT_2 //*************************************************************************************************/ when i am executing following SQL statement then it will give result but it takes 10 to 13 seconds I know where i am executing on single core of CPU but when i executing that above statement then, I think it will taking data from disk because disk access indicator led blow when i executing that above SQL statement. means, It was not using system main memory. I will be wrong. Can each time it is accessing BAT from disk or from main memory? Can you test me Program ? and Can you give me guidance for accessing BAT? I hope you will give me proper guidance. My SQL statement results are as follow sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (3.2s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (9.3s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (14.3s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (3.4s) -- Regards, Swapnil K. Joshi _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Re: users-list Digest, Vol 4, Issue 1
by swapnil joshi 02 Dec '12

02 Dec '12

Hi Lefteris, You have asked me, which version of monetdb are you using? I am using* MonetDB-11.11.7 *version of monetdb. and my dbfarm is on my own system where i have executed this query. My System configuration is as follow: Hardware : Intel i7 2.3GHz Processor, DDR3 8GB RAM, 1TB hard disk S/W : Ubuntu 12.04, MonetDB-11.11.7 On Sat, Dec 1, 2012 at 4:30 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Same SQL Statement with different throughput time. (swapnil joshi) > 2. Re: Same SQL Statement with different throughput time. > (Charalampos Nikolaou) > 3. Re: Same SQL Statement with different throughput time. (Lefteris) > 4. Re: Same SQL Statement with different throughput time. > (Stefan Manegold) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 30 Nov 2012 16:59:57 +0530 > From: swapnil joshi <webmaster.swapnil(a)gmail.com> > To: Stefan.Manegold(a)cwi.nl, users-list(a)monetdb.org > Subject: Same SQL Statement with different throughput time. > Message-ID: > < > CAFBccRCGURekT6sCL3SSvOGMWCBwaJv+kFD_bNnyVWEkmC8f3Q(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > 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

1 0

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

database failing to start
by Flavio Coelho 22 Nov '12

22 Nov '12

Hi I had monetdb setup working for few months and recently, all of a sudden the database would not start any more. I have tried everything I could find in the docs: checking permissons reinstalling the packages, deleting and recreating the databases, Farm,, etc... Now I can't even start an new empty database. Here is the erro I get in the log: 2012-11-22 08:31:08 MSG control[15663]: (local): served status list 2012-11-22 08:31:08 MSG merovingian[15663]: startup of database under maintenance 'sapo' forced 2012-11-22 08:31:08 MSG merovingian[15663]: starting database 'sapo', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2012-11-22 08:31:08 MSG sapo[16066]: arguments: /usr/bin/mserver5 --set gdk_dbfarm=/Ldisco1/Monet_dbs/ SAP_farm --dbname=sapo --set merovingian_uri=mapi:monetdb:// bon051.cloud.fgv.br:50000/sapo --set mapi_open=false --set mapi_port=0 --set mapi_usock=/Ldisco1/Monet_dbs/SAP_farm/sapo/.mapi.sock --set monet_vault_key=/Ldisco1/Monet_dbs/SAP_farm/sapo/.vaultkey --set gdk_nr_threads=16 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-11-22 08:31:08 MSG merovingian[15663]: database 'sapo' (16066) has exited with exit status 127 2012-11-22 08:31:08 ERR control[15663]: (local): failed to fork mserver: database 'sapo' appears to shut itself down after starting, check monetdbd's logfile for possible hints Any ideas why this problem may be happening? I have another Ubuntu64 machine with the same versions of monet and it works normally. I must be overlooking some detail of configuration. It may be a good Idea to add a FAQ to the docs with possible causes for well known crashes. I am on an Ubuntu 64 bits with latest MOnetdb: $ uname -a Linux bon051.cloud.fgv.br 3.2.0-26-generic #41-Ubuntu SMP Thu Jun 14 17:49:24 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux administrador@bon051:~$ monetdb -v MonetDB Database Server Toolkit v1.0 (Oct2012-SP1) administrador@bon051:~$ monetdbd -v MonetDB Database Server v1.6 (Oct2012-SP1) thanks, -- Flávio Codeço Coelho ================ +55(21) 3799-5567 Professor Escola de Matemática Aplicada Fundação Getúlio Vargas Praia de Botafogo, 190 sala 312 Rio de Janeiro - RJ 22250-900 Brasil _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 11

Distributing databases/tables
by Pedro Salgueiro 22 Nov '12

22 Nov '12

Greetings! We have been testing MonetDB for use in AWS in the last couple of weeks and we are surprised with its overall performance, even when dealing with large tables and under-powered machines. Still, we were wondering if its possible in anyway to improve the performance by distributing the databases/tables through several machines. While looking at MonetDB documentation and the mailing list archives, I get some mixed answers about this topic and nothing very concrete. We noticed that it's possible to create a cluster of MonetDB instances, but it seems that it's main purpose is to ease the administration of several MonetDB instances and provide a single entrance point to several MonetDB instances. Still, we still have some questions about its use: Is it possible to partition a table across several MonetDB instances in order to improve the performance? Best regards, Pedro Salgueiro _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Aggregation according to multiple criteria at once
by Адель Ризаев 22 Nov '12

22 Nov '12

Hello to Everybody, I'm doing an averaging over one column using some specific range in another column multiple times. Actually the query looks like: select avg(value) from myTable where time<N and time>M. i'm calling that query from the python script multiple times. Can i call the query once in order to reduce the number of requests to the monetDB? Best regards, Adel _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 3

Announcement: New Oct2012-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 21 Nov '12

21 Nov '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2012-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Oct 2012-SP1 bugfix release Bug Fixes * 3155: Provide an option for setting discovery to false * 3161: invalid tuple received from server, got 138 columns, expected 476, ignoring * 3182: When using aggregate SQL functions, precision & decimal metadata for numeric/decimal type returned is 0,0 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Monet Upgrade--Degraded performance
by Jerry Ramey 19 Nov '12

19 Nov '12

Begin forwarded message: > From: Jerry Ramey <jerry.ramey(a)codefutures.com> > Subject: Monet Upgrade--Degraded performance > Date: November 19, 2012 11:51:34 AM MST > To: users-list(a)monetdb.org > > Hello, > > We had previously asked some advice on some slow Monet performance: > http://mail.monetdb.org/pipermail/users-list/2012-September/005765.html > > As per suggestion we upgraded to the Oct2012 release and we are seeing severely degraded performance. Many queries taking over an hour to complete--mostly they are not especially complicated SELECTs with some arithmetical computation, some concurrently though mostly in succession. > > The database is relationally spread across two servers, the most predominant table with approximately 300 million rows per server, the instances are 68.40 GB with 26 (8 cores x 3.25 units) and CentOS 5.4 x64. Each instance is overloaded with IO wait and minimal CPU usage. The available memory is quickly used and doesn't appear to be released. > > Any advice would be appreciated. Let me know what additional information you require. > > Thanks! > Drew M. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Cross-column time computation doesn't appear to work in Oct 2012 release
by Percy Wegmann 19 Nov '12

19 Nov '12

I couldn't find any existing bug logs for the below issue and want to make sure that it's not a known issue before I log it. Basically, performing time computation across columns doesn't work. percy@percy-sandbox:~$ uname -a Linux percy-sandbox 3.2.0-33-generic #52-*Ubuntu* SMP Thu Oct 18 16:29:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux percy@percy-sandbox:~$ monetdbd -v *MonetDB Database Server v1.6 (Oct2012)* * * percy@percy-sandbox:~$ mclient -u monetdb -d click password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012) Database: *MonetDB v11.13.3 (Oct2012)*, 'mapi:monetdb://percy-sandbox:50000/click' Type \q to quit, \? for a list of available commands auto commit mode: on sql>*create table time_table (atime timestamp, btime timestamp, ctime date); * operation successful (33.944ms) sql>*insert into time_table values(timestamp '1970-JAN-1', timestamp '1980-DEC-31', date '2012-JAN-1');* 1 affected row (20.649ms) sql>select * from time_table; +----------------------------+----------------------------+------------+ | atime | btime | ctime | +============================+============================+============+ | 1970-01-01 00:00:00.000000 | 1980-12-31 00:00:00.000000 | 2012-01-01 | +----------------------------+----------------------------+------------+ *1 tuple* (1.402ms) sql>select btime <> atime from time_table; +----------+ | <>_btime | +==========+ | true | +----------+ *1 tuple* (1.208ms) sql>select btime - timestamp '1975-JAN-01' from time_table; +---------------+ | sql_sub_btime | +===============+ | 189302400000 | +---------------+ *1 tuple* (1.389ms) sql>select btime - atime from time_table; +---------------+ | sql_sub_btime | +===============+ +---------------+ *0 tuples* (1.450ms) Expected result: All queries return 1 tuple Actual result: The query that performs date subtraction between two columns returns 0 tuples. Thanks, -- Percy Wegmann _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Monetdb bulk copy crash
by Medha Atre 19 Nov '12

19 Nov '12

Hi, I am using the latest MonetDB (Oct 2012 release) and creating an RDF database of 1.33 billion triples as follows: $ monetdbd start /path/to/dbfarm $ monetdb create rdf1b $ monetdb release rdf1b $ monetdb start rdf1b _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 4

How do I interpret testweb listing for the latest patches the next release?
by Brandon Jackson 17 Nov '12

17 Nov '12

45868:949c8b8db28d Was suggested to me for fixing the recent error with aggregates and scaling. Which area is the best one to go to for receiving the patches going into the next release? Thanks for the guidance. Brandon _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Client PHP too slow with many results
by Eduardo Oliveira 16 Nov '12

16 Nov '12

Hello, I'm finding php client too slow returning big result sets. I'm using php client i i downloaded from http://dev.monetdb.org/downloads/sources/Latest/ MonetDB-11.13.3.tar.bz2 Some benchmark down, anyway of get the results faster? LIMIT 50K [root@monetdb examples]# php simple_query.php *9* [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 50000' > res.txt password: real 0m4.566s <- already counting with some time to password input user 0m1.272s sys 0m0.132s ---- LIMIT 100K [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 100000' > res.txt password: *real 0m7.184s* user 0m2.545s sys 0m0.266s [root@monetdb examples]# php simple_query.php *18* ---------- Code: [root@monetdb examples]# cat simple_query.php <?php require '../lib/php_monetdb.php'; $db = monetdb_connect("sql", '127.0.0.1', 50000, 'adctest', 'adctest', 'adctest') or trigger_error(monetdb_last_error()); $start_query_monetdb = time(); $res = monetdb_query($db, monetdb_escape_string('SELECT * FROM summary_sent LIMIT 100000')) or trigger_error(monetdb_last_error()); while ( $row = monetdb_fetch_object($res) ) { } $time = time() - $start_query_monetdb; echo $time . "\n"; /* Free the result set */ monetdb_free_result($res); /* Disconnect from the database */ if (monetdb_connected($db)) { monetdb_disconnect($db); } -- *Eduardo Oliveira * *IT* ***Email:* eduardo.oliveira(a)adclick.pt <nuno.morais(a)adclick.pt> *Web: * www.adclickint.com ** <http://www.adclickint.com/> * * <http://www.adclickint.com/> <http://www.adclickint.com/> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

4 5

Ubuntu compilation problem
by Matthieu Guamis 14 Nov '12

14 Nov '12

Hello, I'm tryingto compile MonetDB on Ubuntu 12.04 x64but It fails with alot of "../mapilib/.libs/libmapi.so: undefined reference to `SHA256'".I though it was related with a library linking problem,but I use LDFLAGS=-lssl -lcrypt and CFLAGS=-Wl,-verbose which give me : attempt to open /usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libssl.so succeeded -lssl (/usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libssl.so) ... attempt to open /usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libcrypt.so succeeded -lcrypt (/usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libcrypt.so) I'm not very familiar with Make and C,so Iwould appreciate some help. PS: Could it be related to the packaging problem reported on the lastreleases notes "No Debian and Ubuntu packages were built for this release due to a packaging failure"? Thanks a lot -- *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

5 5