November 2012 - users-list

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