December 2012 - users-list

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