March 2009 - users-list

[MonetDB-users] 64bit MonetDB, JDBC Insert via RJDBC, >300 million rows
by Yue Sheng 17 Mar '09

17 Mar '09

I'm been trying to insert from file for over 322 files, totally 12GB with over 600 million rows. The RJDBC connection hangs after 350 million row. I noticed the VSIZE in Activity Monitor is 30.85GB, closer to 32GB system memory. Is this the reason that I cannot connect to the database? What should I do? Error message I get from MonetDB server: # MonetDB server v5.8.4, based on kernel v1.26.4 # Serving database 'bigdata', using 8 threads # Compiled for i386-apple-darwin9.6.0/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008- MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /Users/someuser/MonetDB64/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.26.4 loaded >!ERROR: GDKsave: failed name=24/2460, ext=tail, mode 0 !ERROR: GDKsave: failed name=23/2317, ext=tail, mode 0 !ERROR: GDKsave: failed name=25/2503, ext=tail, mode 0 !ERROR: GDKsave: failed name=26/2657, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2274, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2224, ext=tail, mode 0 !ERROR: GDKsave: failed name=01/112, ext=head, mode 0 !ERROR: logger_exit: could not open /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log System:MacPro Dual Quad Xenon Leopard OS X 32GB RAM R 2.8.1 32Bit MonetDB v5.8.4 complied for i386-apple-darmin9.6.0/64bit Many thanks. Yuesheng

4 28

[MonetDB-users] Apples and oranges
by Nils Grimsmo 17 Mar '09

17 Mar '09

Hi! I have some questions as to how MonetDB/XQuery should be compared fairly to other systems. If I re-run a query multiple times in a single call to `mclient`, is any calculation re-used? How about if I run multiple similar queries in a single call? Example: $ cat www.xq count(doc("dblp")//www) $ cat www_s10.xq (count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www), count(doc("dblp")//www)) $ cat www_s100.xq (count(doc("dblp")//www), ... count(doc("dblp")//www)) $ mclient --language=xquery --time < www.xq 11760 Timer 22.552 msec (Assert we are running hot.) $ mclient --language=xquery --time < www.xq 11760 Timer 21.661 msec $ mclient --language=xquery --time < www_s10.xq 11760, [snip] 11760 Timer 33.063 msec $ mclient --language=xquery --time < www_s100.xq 11760, [snip] 11760 Timer 252.414 msec So the average execution times are 22, 3.3 and 2.5 milliseconds. Is the extra cost for the first query just starting up the client program, or is some calculation re-used? If we now look at more expensive queries: $ cat dblp_authors.xq count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]) Just repeating the same: $ cat dblp_authors_s10.xq (count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"])) Different but related queries: $ cat dblp_authors_x10.xq (count(doc("dblp")/dblp//author[text()="Grzegorz Rozenberg"]), count(doc("dblp")/dblp//author[text()="Wen Gao"]), count(doc("dblp")/dblp//author[text()="Irith Pomeranz"]), count(doc("dblp")/dblp//author[text()="Hector Garcia-Molina"]), count(doc("dblp")/dblp//author[text()="Moshe Y. Vardi"]), count(doc("dblp")/dblp//author[text()="Joseph Y. Halpern"]), count(doc("dblp")/dblp//author[text()="Noga Alon"]), count(doc("dblp")/dblp//author[text()="Wei Li"]), count(doc("dblp")/dblp//author[text()="Ming Li"]), count(doc("dblp")/dblp//author[text()="Donald F. Towsley"]) ) $ mclient --language=xquery --time < dblp_authors.xq 351 Timer 1238.436 msec $ mclient --language=xquery --time < dblp_authors.xq 351 Timer 1253.927 msec $ mclient --language=xquery --time < dblp_authors_s10.xq 351, ... 351 Timer 1284.191 msec $ mclient --language=xquery --time < dblp_authors_x10.xq 351, 347, 346, 341, 334, 334, 330, 320, 320, 317 Timer 2610.589 msec Here the average times are 1238, 128 and 261 milliseconds. Here the difference is clearly not just startup of the client. If this was not a client-server architecture, I would guess the difference came from opening files, getting stuff into cache, etc.. Are there similar reasons here? What parts of the calculations are actually done inside the client, if any? If the answer is none, why is this behavior seen? In conclusion: When running multiple queries, what would be the most fair way to compare MonetDB/XQuery to other client/server architectures in your view? Concatenating the queries in a single call to `mclient`, or multiple calls? When timing a single query, can it be repeated multiple times in a single call, and the average taken, without being unfair? If I use for example MS SQL Server 2008, there is no gain from a single invocation of the client, whether I have multiple SQL statements SELECT x.query('$q) FROM t; ..., SELECT x.query('$q) FROM t; Or a single SQL statement with a list of XPath queries SELECT x.query('($q, ..., $q)') FROM t; Klem fra Nils -- http://www.idi.ntnu.no/~nilsgri/ Why is this thus? What is the reason of this thusness? - Artemus Ward

4 4

[MonetDB-users] owner of files in dbfarm changed
by Ross Bates 16 Mar '09

16 Mar '09

Hi All - I've been running an Feb2009 nightly install for a few weeks now with no issues. Tried to start the database this morning and was getting tons of errors about the BACKUP directory, SQL Catalog, etc... It turns out that somehow various files dbfarm directory were changed to root ownership. Checked the system logs, bash history, etc.... and root hasn't been used to start any monetdb services or anything else related to monetdb. I know it sounds far fetched but is there something internal to the db which could have done this? Anyone seen mysterious ownership changes like this before? As for a solution, should I be ok just doing a chown on everything below my monetdb home back to the installation user? Thanks, Ross

2 1

[MonetDB-users] [Fwd: making the server accessible from other machines]
by Martin Kersten 16 Mar '09

16 Mar '09

-------- Original Message -------- Subject: making the server accessible from other machines Date: Mon, 16 Mar 2009 11:59:29 +0100 From: francois guérin <replonk(a)gmail.com> To: monet(a)cwi.nl Hi, (i'm francois, this is not the first time i come to ask you some questions about monetdb...) i've tried to make the server accessible from another machine, but it dont work... when i launch the server, and enter the statement " xprc_trusted.delete(); ", everything's fine and i can access to my server with another machine. but, when i edit the MonetBD.conf file, as it's preconized in http://monetdb.cwi.nl/projects/monetdb/XQuery/Documentation/Security.html nothing change a quick look at my monetDB.conf file: # if not set port to address the HTTP server for pathfinder = mapi_port+1 xrpc_port="" # xrpc daemon is open for outside connections by default xrpc_open=yes # Additional options to be passed to the XRPC server, currently only # "timing". xrpc_options="" # IP addresses or hostname separated by ';' who can execute scripts in # <datadir>/MonetDB/xrpc/admin/ xrpc_admin="" # IP addresses or hostname separated by ';' who can retrieve XML # documents stored in the database using a URL of the form: # http://<host>[:port]/xrpc/<name>.xml xrpc_user="" # url prefixes of trusted modules separated by ';' xrpc_trusted="" As you can see, all slots are either open, either empty (that should mean any url/modules/user are allowed)

1 0

Re: [MonetDB-users] import error in python script
by Ranadeeran 16 Mar '09

16 Mar '09

Yes i did that but didn't work.. > You should set PYTHONPATH to something like > /prefix/lib/python2.5/site-packages > > Can you confirm? > > Thanx, > Lazaros. > > On Thu, Mar 12, 2009 at 08:36:08PM +0530, Ranadeeran wrote: >> hi all >> i use MonetDB Database Server Toolkit v0.4 and ubuntu 8.10. >> im unable to issue any of the monetdb commands without prefixing them >> with >> a ./ >> that is instead of mserver5,it works only when i give ./mserver5 >> >> And im able to issue these commands only when im in the bin directory >> though i ve permanantly set the PATH variable in .bash_profile >> >> when i try to connect to monetdb using the sample python script that is >> given in the docs,i get an import error telling unable to import >> MonetDbSQL,though i ve set the PYTHONPATH variable to >> /prefix/lib/python2.5/site-packages/MonetDb directory >> >> the script is >> import MonetSQLdb >> # Let the cursor return dictionaries instead of tuples: >> # x = MonetSQLdb.connect(cursorclass = MonetSQLdb.cursors.DictCursor) >> x = MonetSQLdb.connect(host = 'localhost', user = 'monetdb',password = >> 'monet\db', lang = 'sql') >> #x = MonetSQLdb.connect(lang='sql') >> c = x.cursor() >> c.execute('select * from test') >> #print c.fetchone() >> print c.fetchall() >> x.close() >> >> But the script works fine on Debian Etch. >> >> i need to overcome these two issues... >> >> Thanks in advance, >> Mahesh >> ----------------------------------------- This email was sent using TCEMail Service. Thiagarajar College of Engineering Madurai - 625015 (India)

1 0

[MonetDB-users] import error in python script
by Ranadeeran 13 Mar '09

13 Mar '09

hi all i use MonetDB Database Server Toolkit v0.4 and ubuntu 8.10. im unable to issue any of the monetdb commands without prefixing them with a ./ that is instead of mserver5,it works only when i give ./mserver5 And im able to issue these commands only when im in the bin directory though i ve permanantly set the PATH variable in .bash_profile when i try to connect to monetdb using the sample python script that is given in the docs,i get an import error telling unable to import MonetDbSQL,though i ve set the PYTHONPATH variable to /prefix/lib/python2.5/site-packages/MonetDb directory the script is import MonetSQLdb # Let the cursor return dictionaries instead of tuples: # x = MonetSQLdb.connect(cursorclass = MonetSQLdb.cursors.DictCursor) x = MonetSQLdb.connect(host = 'localhost', user = 'monetdb',password = 'monet\db', lang = 'sql') #x = MonetSQLdb.connect(lang='sql') c = x.cursor() c.execute('select * from test') #print c.fetchone() print c.fetchall() x.close() But the script works fine on Debian Etch. i need to overcome these two issues... Thanks in advance, Mahesh ----------------------------------------- This email was sent using TCEMail Service. Thiagarajar College of Engineering Madurai - 625015 (India)

4 3

[MonetDB-users] Random errors and segfaults executing concurrent queries
by Dominique Prunier 12 Mar '09

12 Mar '09

Hi, I'm experiencing a lot of instabilities using MonetDB-Feb2009 on a moderately big database (70GB) with 3 concurrent (read only) connections. In my test setup, i have 3 identical tables which i query in parallel (from 3 jdbc connections). Almost every time, i get some weird errors: TypeException:user.s213_7[16]:'io.s13_4' undefined in: _26:any := io.s13_4() TypeException:user.s244_2[18]:'io.s14_6' undefined in: _31:any := io.s14_6() TypeException:user.s236_8[31]:'io.s18_3' undefined in: _47:any := io.s18_3() .... Finally, at some point, the server often segfault. Is there anything i can do to improve this ? Thanks,

1 0

[MonetDB-users] Strange performance behavior on bulk import with / without sequences
by Cimballi 11 Mar '09

11 Mar '09

Hi, I have a strange behavior with MonetDB and the JDBC driver, maybe you have an explanation. Here is what I do : I import about 100 000 rows in MonetDB using the JDBC driver and simple SQL statements (not prepared) with Spring JDBC API. I made the test a first time using sequences, so before inserting the data I query for the next sequence number and then I insert the data. The second time, I generate the sequence number on the application side, using a simple increment, so I directly insert the data. And the strange result : In the first test, it took 7:30 minutes for the whole insert. In the second test, it took only 30 seconds for the whole insert. So, it seems there is a problem with the select performance, and in fact I saw this problem for another select operation. Do you have any idea about where it can come from ? Is there any configuration option or the way I use the JDBC API ? Thanks, Cimballi

2 3

[MonetDB-users] writing out all fields
by dariuszs 11 Mar '09

11 Mar '09

Hi, Selecting 50 fields out of 300 takes almost no time to write out to csv file, but selecting everything takes forever - is there any setting that can improve the speed to write out all fields from a table? Thanks. Dariusz.

2 1

[MonetDB-users] MonetDB-MAL usage questions
by Maria 10 Mar '09

10 Mar '09

Hi, I am having some problems using mal. firstly, I want to find bats of a table 'st' I loaded. I created a table 'st' using sql. > ./mclient -lsql -ddemo > \<simpletable.sql ==> it creates table 'st' with two columns 'id' and 'name' > \q then, I try to use mal. > ./mclient -lmal -ddemo > bbp.open(); > bbp.prelude(); > a := bbp.bind("st", "id"); then, I got following error msg > ERROR = !MALException:bbp.find:BAT not found I thought when a table is created using sql, it stored as number of bats and I can find it through bat buffer pool. but it seems that there is nothing in the bbp ( I tried bbp.getObjects(), and printed returns which contains no bats). Where can I get bats of table 'st'? What can be the logical name of each bat? (same as column name?? or tablename_columnname??) Second question) Although I've read all documents in MonetDB site, I'm still quite confused the way to use mal. For example, if I want to create table using mal, how could it be done? sql statement: "create table st ("id" integer not null, "name" varchar(30));" To understand mal, I think I need number of examples. Is there any documents I can study about mal? Is there any way to see system generated mal when I run sql statement? If I can, it can be good example code for me. I really hope that any one could help me. Thanks.

3 2