March 2010 - users-list

[MonetDB-users] Virtual Memory Requirement
by Hering Cheng 25 Mar '10

25 Mar '10

Hi, I am trying to understand the limitations of MonetDB with regard to memory. It seems that the amount of virtual memory (RAM plus swap space) available to MonetDB must be greater than the amount of data. Before I give up on MonetDB, can someone please confirm or correct my understanding? I did not understand the virtual memory requirement when I read the following in its documentation ( http://monetdb.cwi.nl/MonetDB/Documentation/Size-Limitations.html#Size-Limi… ): *The maximal database size supported by MonetBD depends on the underlying processing platform, e.g., a 32- or 64-bit processor, and storage device, e.g., the file system and disk raids. * For example, I started up merovingian (using the Feb 2010 release), limiting it and its child processes to 32 GB of virtual memory (via ulimit -v). I then try to load 115 GB of data into a single table. mclient reports the following: MAPI = chenher@myserver50000 COPY 472112651 RECORDS INTO mytable FROM 'mydata.txt' DELIMITERS '|', '\n'; ERROR = !SQLException:importTable:failed to import table !ERROR: HEAPextend: failed to extend to 1888450604 for 17/1734tail !ERROR: TABLETcreate_bats: Failed to create bat of size 472112651 I understand if MonetDB must be able to fit all its data within virtual memory. But if this is the case, then unfortunately I cannot use it, since I have terabytes of data and I do not have that much virtual memory on my hardware. Thank you. Hering

4 8

[MonetDB-users] How to run monetdb and create a database, with the geom module?
by Michael Pheasant 25 Mar '10

25 Mar '10

I cannot figure out how to do geom stuff with the Fedora 12 rpm binary install. If I run $ sudo mserver5 --dbinit="include geom; include sql;sql.start();" then in another shell try and create a test database i get an error than monetdb (merovingian) is not running: $ sudo monetdb create test monetdb: cannot perform: MonetDB Database Server (merovingian) is not running If I run merovingian, then it starts the MonetDB with --dbinit="include sql" I can then create a database, but I cant load the GEOM extensions. So I feel like I end up in a catch-22. Can someone explain how I can run monetdb, and create a database, with the geom module? Cheers Mike.

2 1

[MonetDB-users] Load a file.txt in bats
by Miguel Ángel Águila Lorente 24 Mar '10

24 Mar '10

Hello, I'm creating a database with MAL. I would need help for creating bats with the information of a file. May be It could be posible to do with tablet.load but I still not know how it works. The format of the file is quite simple: int;int;int; int;int;int; I expect you can help me. Thank you. Mike

2 3

[MonetDB-users] Java executeBatch() behavior
by sateesh 23 Mar '10

23 Mar '10

Hi All, When we try to execute a Set of SQL Statements using executeBatch() and if one of the SQL Query fails then MonetDB seems to be ignoring the rest. Is there a way we can configure MonetDB to execute the remaining SQL Queries in the Batch ?? Thanks Sateesh -- View this message in context: http://old.nabble.com/Java-executeBatch%28%29-behavior-tp27783879p27783879.… Sent from the monetdb-users mailing list archive at Nabble.com.

2 6

[MonetDB-users] Transaction Management
by אייל 22 Mar '10

22 Mar '10

hi, this is from the documentation of Transaction Management: "If at commit time it can be assured that the data prepared for update affects tables not changed in the mean time, the results are merged." dose it mean that two users cannot change DIFFERENT records of the same table at the same time? what isolation levels dose MonetDB supports? thanks.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 22 Mar '10

22 Mar '10

Martin, thanks again for your response. lastly i made experiments with mysql (innoDB and innoDB-plugin), postgreSQL and MonetDB. MonetDB won even those queries that weren't the reason MonetDB invented for. yes it's an amazing platform. doing what you suggested with JDBC is possible, but not convenient. for me, without getting into the details of the project that already works against mssql, i would just say it's too much of an effort. i think this feature can help MonetDB become more like a real world database, than an amazing platform for academic research. thanks.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 21 Mar '10

21 Mar '10

sending it again due to a mistake in the subject. sorry. Martin, thanks for your response. my actual application is now served by a mssql server, which has many stored-procedures, some of them are 45kb of TSQL-code, that may return about ten result-sets at once. these procedures are normally executed within about 50msec. having this in mind... for the sake of our discussion only, lets define : "optimization" : automatically turning queries into query templates. "compilation" : parsing the bulk of statements + deciding for each of the queries which "optimization" should be used (or built - if no suitable "optimization" found). "bandwidth": sending a large bulk of statements from the client to the server. now i would like ask: dose MonetDB stores "compilation" for each stored-procedure for reuse? if the answer is "no", then i understand: "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay only once :) if the answer is "yes": "optimization" i pay only once :) "compilation" i pay only once :) "bandwidth" i pay only once :) the situation today is i cannot use stored-procedure (because they don't return result sets), so it actually dose not meter whether the answer is "yes" or "no": "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay many times :( since MonetDB actually support returning multiple result-sets (without stored procedure), and it even support returning a single result set (using stored-function), it looks like adding this feature to stored-procedures as well, is not so much of a work (i'm thinking of contributing this work myself, having your kindly help and general directions). but if MonetDB dose NOT even store a "compilation" for each stored procedure, i guess we are too much far away... note: every time i said "stored-procedure", i actually meant "stored-function" as well. thanks again.

2 1

Re: [MonetDB-users] Virtual Memory Requirement
by אייל 21 Mar '10

21 Mar '10

Martin, thanks for your response. my actual application is now served by a mssql server, which has many stored-procedures, some of them are 45kb of TSQL-code, that may return about ten result-sets at once. these procedures are normally executed within about 50msec. having this in mind... for the sake of our discussion only, lets define : "optimization" : automatically turning queries into query templates. "compilation" : parsing the bulk of statements + deciding for each of the queries which "optimization" should be used (or built - if no suitable "optimization" found). "bandwidth": sending a large bulk of statements from the client to the server. now i would like ask: dose MonetDB stores "compilation" for each stored-procedure for reuse? if the answer is "no", then i understand: "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay only once :) if the answer is "yes": "optimization" i pay only once :) "compilation" i pay only once :) "bandwidth" i pay only once :) the situation today is i cannot use stored-procedure (because they don't return result sets), so it actually dose not meter whether the answer is "yes" or "no": "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay many times :( since MonetDB actually support returning multiple result-sets (without stored procedure), and it even support returning a single result set (using stored-function), it looks like adding this feature to stored-procedures as well, is not so much of a work (i'm thinking of contributing this work myself, having your kindly help and general directions). but if MonetDB dose NOT even store a "compilation" for each stored procedure, i guess we are too much far away... note: every time i said "stored-procedure", i actually meant "stored-function" as well. thanks again.

1 0

Re: [MonetDB-users] multiple result sets
by אייל 18 Mar '10

18 Mar '10

Fabian, thanks for your response. i did try it. as far as i can see, stored procedures cannot return result set at all... i hoped to get an official verification for my findings. and maybe any work around. about the network traffic: the result would be the same with or without stored procedure, but the request would be much smaller. sending a request like: "call sp1(x,y,z);" is much smaller than a very large bulk of statements. of course, the more critical point is to avoid unnecessary re-compilation of a large and complex bulk of statements, that should be able to execute many times during a single second. in mssql, and mysql, procedures do able to return multiple result sets. i don't know what is the standard sql2003 about it. do you think it worth a bug\feature report? thanks, again.

3 2

[MonetDB-users] multiple result sets
by אייל 18 Mar '10

18 Mar '10

hi, dose monetdb support returning multiple result sets by a stored procedure or a stored function? something equevalent to this (mssql code): create procedure p1 as begin select 'a' as f1; select 'b' as f1, 'c' as f2 end; any workaround?? i realized that if i do (in mclient terminal): sql>select 'a' as f1; select 'b' as f1 ,'c' as f2; i get: f1 -- a f1|f2 ----- b |c but this is not good enough for me. the actual procedures are long and complex, and i want to gain the benefit of avoiding the re-compilation and reduced network traffic (in mssql it makes big difference for me). i also realized that i can do: create function f1() returns table... but this is good for a single result only. BTW: dose the monetdb-jdbc-driver support multiple result sets (java.sql.Statement.getMoreResult() etc.)? thanks!

2 1