- users-list - monetdb.org

[MonetDB-users] Delete is slow?
by Edmund Dengler 14 Feb '05

14 Feb '05

Howdy! I'm taking some time to see what MonetDB is all about, and I have some questions re performance. I created 6 BATs (basically BAT[LNG, X] where X = INT, OID, TIMESTAMP, BLOB) to test. I loaded up about 250k rows (loading was quite reasonable, even though I was using the Perl interface to do it one row at a time). I am currently trying to delete the BUNs by doing a series of "bat("bat_name").delete();", and it is just taking forever (currently at over an hour!!). I/O is extremely minimal, CPU is nailed to 100% (this is running the latest release candidate off of sourceforge on a Linux 2.6 Gentoo system). Questions: Should delete be so slow? Is there a dependency between tables that is somehow forcing an N^2 or N^3 algorithm to run? Is there an equivalent to "truncate" other than destroy() and recreate? Regards! Ed

5 17

[MonetDB-users] ascii_io
by Edmund Dengler 11 Feb '05

11 Feb '05

Greetings! Looking at the ascii_io module, I can't seem to find a way for loading up multiple BATs where one column of the input is specified as the head value. Is there such an option, or is it always a VOID loadup? In particular, I have a << lng >> column as the primary key. So, using my current understanding, I would need to load up a set of dummy/temp BATs, and then using these load the data to the real BATs. Regards! Ed

3 5

[MonetDB-users] Design of bat layout
by Edmund Dengler 11 Feb '05

11 Feb '05

Greetings! I have a question concerning how to design the data structures for a particular problem. I have a set of records, each record may have 0 to 20 strings associated with it. If I was to use an SQL database, I would use a table such as: table strings ( long record_id, int index, string string ); I was thinking of using a BAT within a BAT, something like: BAT[ BAT[ lng, int ], str ] but this cannot be made persistent. Using: bat(lng, str).rename("string_1"); bat(lng, str).rename("string_2"); ... bat(lng, str).rename("string_20"); just feels wrong (if for no other reason, what if there is a 21st string in the future?). I could do: bat(lng::1, lng::2).rename("record_strings"); bat(lng::1, int).rename("record_string_index"); bat(lng::1, str).rename("record_string_string"); But this means I am essentially limiting my record IDs to not be the full rnage of a long. What would be the suggested structure to use in this case? Regards! Ed

1 0

[MonetDB-users] New release-candidates "MonetDB-4.6.0_rc0" & "MonetDB-SQL-2.6.0_rc0"
by Stefan.Manegold＠cwi.nl 21 Jan '05

21 Jan '05

[ Please accept my apologies for this "broadcast mail". ] [ Future announcements concerning MonetDB and its elated projects will only ] [ go to the monetdb-announce mailing list ] [ (http://lists.sourceforge.net/lists/listinfo/monetdb-announce) so please ] [ do not forget to subscribe there, in case you are not subscribed, yet. ] Dear MonetDB users and developers (and those who want to become either or both ;-), In preparation for the release of the new MonetDB-4.6.0 & MonetDB-SQL-2.6.0 (planned for February 14 2005, cf. http://monetdb.cwi.nl/Development/Roadmap/) I just prepared the respective release-candidate branches "MonetDB_4-6" & "SQL_2-6" in our CVS repository on SourceForge (http://sourceforge.net/cvs/?group_id=56967) The first release-candidate versions are tagged as "MonetDB_4-6-0_rc0" & "SQL_2-6-0_rc0", respectively. Pre-packaged source- & binary-distributions are not available, yet, but they will be made available for download with in a few days, and announced separately. All users are highly advised (actually "requested" ;-) to use the new release-candidate( branche)s as of now (see below for details concerning CVS checkout/update). Please do not hesitate to ask for support and report bugs or problems, preferably via the bug tracing system at SourceForge (http://sourceforge.net/tracker/?group_id=56967&atid=482468) or via email to monet(a)cwi.nl; however, please always state clearly, which version of MonetDB-SQL you are using (as the Mserver tells you at startup)! Any support for the now obsolete "MonetDB-4.4.x" & "MonetDB-SQL_2.4.x" will stop as of now. Contents: ========= - Background - Change Logs - CVS update - CVS check-ins - Testing Background ========== Trying to stick to our road-map (http://monetdb.cwi.nl/Development/Roadmap/) we plan the release of MonetDB-4.6.0 + MonetDB-SQL-2.6.0 for February 14 2004. As with earlier "pre-releases" of MonetDB & MonetDB-SQL, we start with "release-candidates" to allow testing by a larger group of users before we finally make the actual releases. Hence, starting to use the release-candidates as soon as possible will only be for your own benefit ;-) According to our TestWeb (http://monetdb.cwi.nl/Development/TestWeb/) both MonetDB and SQL are doing quite well right now; hence, I don't expect any (major) problems. Change Logs =========== Information about the major changes since MonetDB-4.4.2 / MonetDB-SQL_2.4.2 will follow with the pre-packaged source- & binary-distributions within a few days. CVS update ========== If you are already using the release branches "MonetDB_4-4-0" and "SQL_2-4-0" from CVS, simply run "cvs update -dP" to update your code to this new release candidate version. If you are already using a CVS version of MonetDB/MonetDB-SQL, you can run either "cvs update -dP -rMonetDB_4-6-0_rc0" "cvs update -dP -rSQL_2-6-0_rc0" to get this very release candidate, or "cvs update -dP -rMonetDB_4-6" "cvs update -dP -rSQL_2-6" to get the most recent version in the release candidate branch. The difference is, that via the first option, you are "stuck" with this very release candidate, i.e., future "cvs update -dP" (without "-r..." or "-A") will not change your checked-out version; via the second option, however, future "cvs update -dP" will get you again the latest version in the release candidate branch. CVS check-ins ============= All bug fixes (and only these!) on MonetDB and/or SQL are bound to go to the new release-candidate branches ("MonetDB_4-6"/"SQL_2-6"), only. On a regular basis, I will then take care of propagating these bug-fixes back to the respective main branch. All new features are bound to go to the main branches (and only there!). This release-candidate branch will become the new stable/release branch within the next 3 weeks. Testing ======= Nightly automatic testing will proceed "as usual". See http://monetdb.cwi.nl/Development/TestWeb/ for details. As usually, any (constructive;-) comments and feedback are more than welcome! Enjoy! Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

[MonetDB-users] Bulk Load
by Sandy Ganz 04 Jan '05

04 Jan '05

Just started playing with MonetDB, and it is a tremendous piece of work. I do have a question on bulk loading table, i.e, a dump of some large tables we have in sybase for some testing. I found the ascii_io module and it looks like it is what is supposed to be used, if so is their and example of the format for the load_format() and any other helpfull tips. Looking at loading about 30 Million rows for some testing. Thanks for any help Sandy

1 0

[MonetDB-users] Mserver hangs on quit(); on RedHat 8.0 (psyche)
by Stefan.Manegold＠cwi.nl 15 Dec '04

15 Dec '04

Hi again, with the latest MonetDB 4.5.0 on RedHat 8.0 (psyche), Jan (F.;-) experiences a "consistently" hang of the Mserver, once he tries to quit it. See below for details. It did work with older versions of MonetDB, but we hanven't locate more precisely, yet, since which version/code change the hang occurs. My question is, has anybody else ever experienced similar problems? I now vaguely recall, that there might have been some related issue, but I don't remember the details, yet --- could someone who does refresh my mind? Thank you very much in advance! Stefan Forwarded message: > From flokstra(a)cs.utwente.nl Wed Dec 15 09:30:02 2004 > X-Sieve: CMU Sieve 2.2 > Content-Type: text/plain; > charset="us-ascii" > From: Jan Flokstra <flokstra(a)cs.utwente.nl> > Organization: University Twente Enschede > To: Stefan.Manegold(a)cwi.nl (Stefan Manegold) > Subject: Re: Monet problem > Date: Wed, 15 Dec 2004 09:27:45 +0100 > User-Agent: KMail/1.4.3 > References: <20041213155606.9656241044(a)draco.ins.cwi.nl> > In-Reply-To: <20041213155606.9656241044(a)draco.ins.cwi.nl> > Cc: "Peter Boncz" <P.Boncz(a)cwi.nl> > MIME-Version: 1.0 > Old-Content-Transfer-Encoding: quoted-printable > Message-Id: <200412150927.47729.flokstra(a)cs.utwente.nl> > X-Spam-Score: -4.9 () BAYES_00 > X-Scanned-By: milter-spamc/0.25.320 (styx.cwi.nl [192.16.191.21]); Wed, 15 Dec 2004 09:28:11 +0100 > X-Scanned-By: MIMEDefang 2.44 > X-Spam-Flag: NO > X-Spam-Status: NO, hits=0.00 required=5.00 > X-Spam-Level: > X-Spam-Report: Content analysis details: (0.0 points, 5.0 required) > Tests (points, rulename, description): > ____ > X-Virus-Scanned: by cwi.nl (CWI/$Revision: 1.25 $) > Content-Transfer-Encoding: 8bit > > Hi Stefan, > > I am using Redhat Linux, version (uname -a) > > Linux psyche 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux > > and the default compilers on this version. I made an strace of the Mserver > and got the following output (just the tail) > It looks it never returns from the rt_sigsuspend() syscall and this is > indeed very strange! > > Regards, Jan. > > > brk(0x8175000) = 0x8175000 > brk(0x8177000) = 0x8177000 > brk(0x8178000) = 0x8178000 > brk(0x8179000) = 0x8179000 > brk(0x817b000) = 0x817b000 > brk(0x817d000) = 0x817d000 > brk(0x817f000) = 0x817f000 > brk(0x8180000) = 0x8180000 > brk(0x8181000) = 0x8181000 > brk(0x8182000) = 0x8182000 > brk(0x8183000) = 0x8183000 > brk(0x8185000) = 0x8185000 > brk(0x8186000) = 0x8186000 > close(7) = 0 > open("/usr/local/flokstra/lib/MonetDB", > O_RDONLY|O_NONBLOCK|O_LARGEFILE|O_DIRECTORY) = 7 > fstat64(7, {st_mode=S_IFDIR|0755, st_size=8192, ...}) = 0 > fcntl64(7, F_SETFD, FD_CLOEXEC) = 0 > lseek(7, 0, SEEK_SET) = 0 > getdents64(0x7, 0x80a1b90, 0x1000, 0x4006c4eb) = 4096 > open("/usr/local/flokstra/lib/MonetDB/trans.mil", O_RDONLY|O_LARGEFILE) = 8 > fstat64(8, {st_mode=S_IFREG|0755, st_size=70, ...}) = 0 > fstat64(8, {st_mode=S_IFREG|0755, st_size=70, ...}) = 0 > mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = > 0x4045f000 > read(8, "# This module is generated using"..., 4096) = 70 > read(8, "", 4096) = 0 > close(8) = 0 > munmap(0x4045f000, 4096) = 0 > close(7) = 0 > open("/usr/local/flokstra/lib/MonetDB", > O_RDONLY|O_NONBLOCK|O_LARGEFILE|O_DIRECTORY) = 7 > fstat64(7, {st_mode=S_IFDIR|0755, st_size=8192, ...}) = 0 > fcntl64(7, F_SETFD, FD_CLOEXEC) = 0 > lseek(7, 0, SEEK_SET) = 0 > getdents64(0x7, 0x80a1b90, 0x1000, 0x400709c6) = 4096 > getdents64(0x7, 0x80a1b90, 0x1000, 0x400709c6) = 2504 > open("/usr/local/flokstra/lib/MonetDB/lib_trans.so", O_RDONLY) = 8 > read(8, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\314\16"..., 1024) = > 1024 > fstat64(8, {st_mode=S_IFREG|0755, st_size=78034, ...}) = 0 > old_mmap(NULL, 16136, PROT_READ|PROT_EXEC, MAP_PRIVATE, 8, 0) = 0x419f7000 > mprotect(0x419fa000, 3848, PROT_NONE) = 0 > old_mmap(0x419fa000, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 8, > 0x2000) = 0x419fa000 > close(8) = 0 > write(1, "MonetDB>", 8MonetDB>) = 8 > fstat64(0, {st_mode=S_IFCHR|0600, st_rdev=makedev(136, 1), ...}) = 0 > mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = > 0x4045f000 > read(0, quit(); > "quit();\n", 1024) = 8 > write(6, "\200\20G@\2\0\0\0\0\0\0\0\222\253\0@\304\363\377\277\300"..., 148) = > 148 > rt_sigprocmask(SIG_SETMASK, NULL, [RTMIN], 8) = 0 > rt_sigsuspend([] > > > -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

[MonetDB-users] "pseudo" nested BATs
by Stefan.Manegold＠cwi.nl 15 Dec '04

15 Dec '04

Hi MonetDB developers and users, sorry for this "blunt" posting, but I thought, the problem, respectively the solution/recommendation that will hopefully be initiated by this question, might be interesting for some more of you: Triggered by a question from Agustin, I noticed that I (believe it or not ;-) apparently lack some knowledge of / experiences with MonetDB/MIL: Consider you have a variable "a" holding a (possible transient) BAT, and another variable "b" holding a second BAT, which happens to be a view of the first one. Both BATs haven't been baptized ("rename()"), yet; hence they still hold there default names: var a := new(void,int).seqbase(0(a)0).insert(nil,1).insert(nil,2).access(BAT_READ); var b := reverse(a); print(a); #-----------------# # h tmp_29 # name # void int # type #-----------------# [ 0@0, 1 ] [ 1@0, 2 ] print(b); #-----------------# # t tmp_29 # name # int void # type #-----------------# [ 1, 0@0 ] [ 2, 1@0 ] Then, these BATs should be gathered in a "container"-BAT. Since (persistent) nested BATs have disappeared (for good reasons!), we do this by gathering the BAT's name in the container: var c := new(void,str).seqbase(10@0); c.insert(nil,bbpname(a)).insert(nil,bbpname(b)); print(c); #-------------------------# # h tmp_28 # name # void str # type #-------------------------# [ 10@0, "tmp_29" ] [ 11@0, "tmp_29" ] Since the second BAT is only a view of the first one, they share not only the same memory/storage, but also the same (bbp-)name; hence, they are not distinguishable by (only) their (bbp-)names, and thus the container c actually contains the first BAT twice iso each BAT once: print(bat(fetch(c,0))); #-----------------# # h tmp_29 # name # void int # type #-----------------# [ 0@0, 1 ] [ 1@0, 2 ] print(bat(fetch(c,1))); #-----------------# # h tmp_29 # name # void int # type #-----------------# [ 0@0, 1 ] [ 1@0, 2 ] print(bbpname(a)); [ "tmp_29" ] print(bbpname(b)); [ "tmp_29" ] Obviously, the problem can be solved/avoided by baptizing the two BATs, and hence making them unique "individuals": rename(a,"BAT_a"); rename(b,"BAT_b"); print(bbpname(a)); [ "BAT_a" ] print(bbpname(b)); [ "BAT_b" ] c.insert(nil,bbpname(a)).insert(nil,bbpname(b)); print(c); #-------------------------# # h tmp_28 # name # void str # type #-------------------------# [ 10@0, "tmp_29" ] [ 11@0, "tmp_29" ] [ 12@0, "BAT_a" ] [ 13@0, "BAT_b" ] print(bat(fetch(c,0))); !ERROR: interpret_params: print(param 1): invalid BAT. print(bat(fetch(c,1))); !ERROR: interpret_params: print(param 1): invalid BAT. print(bat(fetch(c,2))); #-----------------# # h BAT_b # name # void int # type #-----------------# [ 0@0, 1 ] [ 1@0, 2 ] print(bat(fetch(c,3))); #-----------------# # t BAT_b # name # int void # type #-----------------# [ 1, 0@0 ] [ 2, 1@0 ] My question is now: - Is this the only solution, or am I just not aware of another command iso bbpname() that would return unique "default" name for non-renamed view? Of course, given the fact that only MonetDB itself has control over the default "tmp_*" names, it is always recommendable to assign names that you can control yourself, whenever you use a BAT's name as its only reference... Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

2 1

[MonetDB-users] Where is the user documentation in the Windows install?
by Tolkin, Steve 06 Oct '04

06 Oct '04

Dear Monet users and developers, I installed Monet on Windows XP using MonetDB-Installer-4.4.0.zip But there is no user documentation! I took the default directory "c:\program files\CWI\MonetDB" and looked in and below it -- but did not see any documentation. I want to know the basics, e.g. how to create a table in SQL, populate it, and query it. OI am guessing that a statement ends with a semicolon, but do not know even this. I am guessing that it accepts standard SQL, but that s not clear either. I am able to run the Server, and the Client, and the Mknife tool. I tried a few ways in the Mknife tool, but without success. For example in the SQL client tool I entered sql>create table t (i integer); And got back the message: # 6 # querytype I assume this is some kind of error. Here is some more of my attempts to do something. Why are the values for token number much higher than the number of tokens in my statement? sql>create tabel t (c char(1)) more>/ more>; MAPI = monetdb@localhost:45123 QUERY = create tabel t (c char(1)) ERROR = !ERROR parse error at token (258) in statement: create tabel sql>create table t (c char(1)); MAPI = monetdb@localhost:45123 QUERY = create table t (c char(1)); ERROR = !current transaction is aborted (please ROLLBACK) sql>rollback more>create table t (c char(1)); MAPI = monetdb@localhost:45123 QUERY = rollback ERROR = !ERROR parse error at token (372) in statement: rollback !create sql>rollback; # 7 # querytype sql>rollback more>/ more>; MAPI = monetdb@localhost:45123 QUERY = rollback ERROR = !ERROR parse error at token (47) in statement: rollback !/ sql>rollback more>; # 7 # querytype sql>ROLLBACK; # 7 # querytype sql> I then tried looking for the documentation in MonetDB-4.4.0.tar.gz But this explains how to compile the system, not how to use it. I think there may be user documentation in the *.rpm files intended for Linux, but I do think I can use those on Windows. Hopefully helpfully yours, Steve -- Steve Tolkin Steve . Tolkin at FMR dot COM 617-563-0516 Fidelity Investments 82 Devonshire St. V4D Boston MA 02109 There is nothing so practical as a good theory. Comments are by me, not Fidelity Investments, its subsidiaries or affiliates.

2 1