- users-list - monetdb.org

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