- users-list - monetdb.org

[MonetDB-users] Database size
by Charles Samuels 22 Jul '09

22 Jul '09

Hi, I have read previously that monetdb is intended to be used only when the data can fit in memory. I'm not sure how to interpret this statement - does this mean that the entire database must fit in memory, or does it mean that only the data being operated on when selected from the database must fit in memory? We are wanting to store *huge* amounts of data, ultimately several TBs. The actual data being select at any given time will only be several hundred MBs, however. Is this a reasonable usage of MonetDB? But most importantly, I have noticed that all the row ID indexes are "int" - why the 32 bit limitation? This just seems like a symptom of the above problem; that the entire database must fit in memory. Thanks a lot for your advice, Charles -- Charles Samuels

4 3

Re: [MonetDB-users] Performance
by Roy Walter 22 Jul '09

22 Jul '09

OK so this is interesting. I don't have a dbfarm directory. I looked in monetdb.conf and noticed a couple of things. The MonetDB installation path value appears as: prefix=c:\documents and settings\sjoerd\my documents\src\stable\icc32\nt32 This means that neither the datadir nor the gdk_dbfarm entries can be properly processed. Might this explain why I [and others] have to start the XQuery Server from the command line and load the modules manually? So right now I can't find my data :) What file extensions does MonetDB use? -- Roy Lefteris wrote: > Hi, > > it looks that something got corrupted in the process. This is starting > to be very usual with windows installations. Anyway, you should delete > the contents of the dbfarm directory in the monetdb installation > directory. This will *delete* all data in your database (i guess you > dont have any important data still in cause you are testing). After > you delete dbfarm, start mserver, that will populate again dbfarm with > the default files. then, add again your data with the usual > pf:add-doc, then create the tijah indices and then run your queries:) > > If this does not work, we will have to try to "wake up" the pf/tijah > people to help us. > > Hope this will help, > > lefteris > > On Tue, Jul 21, 2009 at 10:35 AM, Roy Walter<garliestonhouse(a)yahoo.co.uk> wrote: > >> OK, I restarted and got an error message that pointed to a problem document >> in a collection. I deleted the offending document and then tried to generate >> the default index with tijah:create-ft-index(). This failed because, >> apparently, the DFLT_FT_INDEX already exists. >> >> So I thought that even though the index compilation appeared to have failed >> at the earlier error an index must have been created. >> >> I tried a tijah:query(). That failed because the DFLT_FT_INDEX does not >> exist. >> >> Hmm, so I tried tijah:delete-ft-index() and it too told me that >> DFLT_FT_INDEX does not exist. >> >> tijah:create-ft-index() still fails with: !ERROR tj_init_collection, pftijah >> collection already exists: DFLT_FT_INDEX >> >> How do I reset? >> >> -- Roy >> >> >> Lefteris wrote: >> >> This is not expected. >> >> Did you try to restart the server and retry? >> >> You might also have a corrupted dbfarm or the documents didn't shred >> correctly to begin with. Which version of monet are you using? how did >> you installed it? >> >> lefteris >> >> On Mon, Jul 20, 2009 at 8:52 PM, Roy Walter<garliestonhouse(a)yahoo.co.uk> >> wrote: >> >> >> Hi lefteris >> >> Well that seems to tick all the boxes. >> >> I tried the global index creation: >> >> tijah:create-ft-index() >> >> and it crashed the server with: >> >> !WARNING: readClient: unexpected end of file; discarding partial input >> >> Hmm... >> >> R. >> >> Lefteris wrote: >> >> >> Hi Roy, >> >> I suggest that you try the pf/tijah module for MonetDB/XQuery. >> >> http://dbappl.cs.utwente.nl/pftijah/ >> >> This will create specific indices for your queries to facilitate text >> search. >> >> Hope this helps for now. We will also investigate were the time is >> spent in your case (without pf/tijah) and come back to you. How many p >> elements your documents have? The problem might be that because monet >> does not build inverted indices on text by itself, it has to visit >> each p element and search with the help of the pcre library. Pf/tijah >> was build for that purpose and should help alot. >> >> Please feel free to contact us for further clarification and new >> findings from your tests:) >> >> cheers, >> >> lefteris >> >> On Mon, Jul 20, 2009 at 6:28 PM, Roy Walter<garliestonhouse(a)yahoo.co.uk> >> wrote: >> >> >> >> Running MonetDB/XQuery on a 2.6GHz 32-bit Windows XP box with 1GB of RAM. >> >> What is the best way to organise XML in MonetDB for rapid text searching? >> A >> run down of my recent experience might help. >> >> I created a collection of around 450 documents (153MB approx.). I ran the >> following query from the command line: >> >> collection("papers")//p[contains(., 'wind farm')] >> >> The query time is at best 19 seconds. That's bad. (It's worse than >> querying >> a Postgres database with documents stored in the XML field type.) >> >> So to get a reference point I loaded up the 114MB XMark document and ran >> this query: >> >> doc("standard")//text[contains(., "yoke")] >> >> The query time varies from 2 to 4 seconds. Better, but still not great. >> >> Now, adding more RAM (and moving to 64-bit) would speed things up I hope! >> But hardware aside: >> >> 1. Is it better to have big documents rather than big collections? >> >> 2. Is having small collections (<10 docs) of big documents also >> inefficient? >> >> Ideally I need to query collections comprising several thousand documents >> using 'text search' predicates. Are there other, better ways to run this >> type of query against a MonetDB XML database? Or should I really be using >> some other platform for this task? >> >> Thanks in advance for any pointers. >> >> -- Roy >> >> >> ------------------------------------------------------------------------------ >> Enter the BlackBerry Developer Challenge >> This is your chance to win up to $100,000 in prizes! For a limited time, >> vendors submitting new applications to BlackBerry App World(TM) will have >> the opportunity to enter the BlackBerry Developer Challenge. See full >> prize >> details at: http://p.sf.net/sfu/Challenge >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users(a)lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> >> >> >> >> >> >> >> > >

3 9

[MonetDB-users] Linux memory overcommit settings
by Franck Routier 21 Jul '09

21 Jul '09

Hi, Does anyone know, when using monetdb, what would be the common wisdom regarding the memory-overcommit settings on a Linux platform, regarding the performance impacts ? Franck

1 0

[MonetDB-users] Wrong callHistory information ?
by Franck Routier 21 Jul '09

21 Jul '09

Hi, testing monetdb, I've come accross a query that takes several minutes on my dev machine (with only 2 GB of RAM). So it will swap for a while, and finally produce the result. I wanted to get the duration of the request, so I did 'set history=true;' The request takes a few minutes, and returns 464 tuples. But when I select from callHistory, here is the outcome : +---------+----------------------------+------------------+-------------+-------+------+--------+--------+---------+---------+ | id | ctime | arguments | xtime | rtime | foot | memory | tuples | inblock | oublock | +=========+============================+==================+============= +=======+======+========+========+=========+=========+ | 6776676 | 2009-07-21 13:25:27.000000 | user.s6_1(); | 22108050926 | 0 | 0 | 0 | 1 | 0 | 0 | +---------+----------------------------+------------------+-------------+-------+------+--------+--------+---------+---------+ Obviously, xtime is wrong (the query did not last for 6 hours). I guess rtime is also a bit quick :-) And the number of tuples is also wrong... Running the same query with \t in mclient gives: Timer 322316.803 msec 464 rows This seems more like truth. Well, I am conscious that with that information, it's hard to tell where the problem is.... So my question is rather: is the information in callHistory expected to be reliable, or more like an indication ? Franck

1 0

[MonetDB-users] Performance
by Roy Walter 20 Jul '09

20 Jul '09

Running MonetDB/XQuery on a 2.6GHz 32-bit Windows XP box with 1GB of RAM. What is the best way to organise XML in MonetDB for rapid text searching? A run down of my recent experience might help. I created a collection of around 450 documents (153MB approx.). I ran the following query from the command line: collection("papers")//p[contains(., 'wind farm')] The query time is at best 19 seconds. That's bad. (It's worse than querying a Postgres database with documents stored in the XML field type.) So to get a reference point I loaded up the 114MB XMark document and ran this query: doc("standard")//text[contains(., "yoke")] The query time varies from 2 to 4 seconds. Better, but still not great. Now, adding more RAM (and moving to 64-bit) would speed things up I hope! But hardware aside: 1. Is it better to have big documents rather than big collections? 2. Is having small collections (<10 docs) of big documents also inefficient? Ideally I need to query collections comprising several thousand documents using 'text search' predicates. Are there other, better ways to run this type of query against a MonetDB XML database? Or should I really be using some other platform for this task? Thanks in advance for any pointers. -- Roy

3 5

[MonetDB-users] XQuery performance
by Roy Walter 20 Jul '09

20 Jul '09

Hi I'm just trying out MonetDB and I have a performance question. I'm running the software on an 2.6GHz 32-bit XP box with 1GB RAM. I have created a small collection of 480 documents, approx. 153MB. Using the console client I run the following query: for $p in collection("papers")//p where contains($p, 'wind farm') return($p) Enabling the timer I see that the query time is 29675 msec. In other words, way too slow to be useful. Clearly there is something fundamentally wrong here. Any pointers? -- Roy Walter

1 0

[MonetDB-users] Has someone already made the jdbkc driver compile with recent jdk-1.5 or newer?
by Marc Weber 19 Jul '09

19 Jul '09

Hi, I tried compiling monetdb with jdbc support today using jdk-1.5.0.19. However the build failed because many abstract methods are not implemented. Has someone already done some work on this? Marc Weber

2 2

[MonetDB-users] server crash
by dariuszs 19 Jul '09

19 Jul '09

Hi, What does this mean 'too many open files'? !SABAOTHgetMyStatus: IOException:sabaoth.getStatus:Unable to open directory /vol1/MonetDB5/dbfarm: Too many open files Thanks. Dariusz.

3 2

[MonetDB-users] merovingian / php
by dariuszs 18 Jul '09

18 Jul '09

Hi, If merovingian starts database on demand after a crash then how would I know which port to use for connection in php script, it errors out for port 50000? $db = monetdb_connect("sql", "localhost", 50000,"monetdb", "monetdb_password");

2 1

[MonetDB-users] production server
by dariuszs 18 Jul '09

18 Jul '09

Hi, Does anybody use MonetDB in production environment? Thanks. Dariusz.

3 4

[MonetDB-users] varchar type with copy into + misleading error message
by Franck Routier 17 Jul '09

17 Jul '09

Hi, using copy into, I run into a problem where a string does not fit into my varchar column. So the first point is that the error message is quite misleading, as it states 'field 4 not inserted, expecting type str'. Well, it is a string, but the length is too long... Using insert into, the error message will be 'SQLException:str_cast:value too long for type (var)char(100)' which is much more helpful. The second point is that my string is 99 characters long, and utf-8 with accented characters. So its length is less than 100 characters but more than 100 bytes. In postgresql, where my data come from, varchar(100) means 100 characters, whatever encoding the database happen to be in. Oracle gives the choise with varchar2(100 char) or varchar2(100 byte) and will default to NLS_LENGTH_SEMANTICS (which in turn is BYTE by default). I guess in monetdb, it means 100 bytes... Am I right stating that, and is it the expected behaviour or a bug ? Franck

2 2

[MonetDB-users] Merovingian Neighbour discovery
by Ross Bates 17 Jul '09

17 Jul '09

Trying to get Merovingian to act as a front end to MonetDB cluster. When I start the Mero service this shows up in the log. 2009-07-16 11:28:10 ERR merovingian[2276]: HELO message: error while sending message: Network is unreachable 2009-07-16 11:28:10 ERR merovingian[2276]: ANNC message: error while sending message: Network is unreachable I can connect back and forth between the two Mero/Monet instances using mclient --lsql, but for some reason the instances of Mero can't discover eachother. I think Mero is using UDP which could be contributing to the issue. Any tips for troubleshooting Mero connectivity over UDP? Related to this, is there anyway I can just tell one instance of Mero about the other without auto discovery and proxy that way? Thanks! -Ross

2 2

[MonetDB-users] Jdbc errors : no prepared statement
by Franck Routier 15 Jul '09

15 Jul '09

Hi, trying to populate my database using jdbc, I encounter the following error : java.sql.SQLException: no prepared statement with the given id at nl.cwi.monetdb.jdbc.MonetConnection $ResponseList.executeQuery(MonetConnection.java:2219) at nl.cwi.monetdb.jdbc.MonetConnection $ResponseList.processQuery(MonetConnection.java:1978) at nl.cwi.monetdb.jdbc.MonetConnection.commit(MonetConnection.java:348) The error comes at a seemingly random time, depending on the run, I could insert 22000 rows, then 9800, etc. I am using the latest published version (MaySP1). Is this a know problem ? Franck

3 6

[MonetDB-users] Inner join & nulls performance question
by Alex Bo. 15 Jul '09

15 Jul '09

Hi, I want to do a join between tables on a column that contains nulls. What should I expect performance-wise when running SELECT ... FROM... INNER JOIN... ON (a.f1 = b.f1 OR (a.f1 is null AND b.f1 is null)) compared to a normal join: SELECT ... FROM... INNER JOIN... ON (a.f1 = b.f1) Thanks. -- View this message in context: http://www.nabble.com/Inner-join---nulls-performance-question-tp24496517p24… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Select * ... LIMIT takes long time
by Alex Bo. 15 Jul '09

15 Jul '09

Hi, I am running the May2009 SP on Windows. I have populated a table with 40M rows and 20 columns. It takes 'SELECT * FROM table LIMIT 10' 2.5 minutes to return, in which memory and cpu consumption seems to be at a standstill. Why does it take so long? -- View this message in context: http://www.nabble.com/Select-*-...-LIMIT--takes-long-time-tp24493160p244931… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] server messages
by dariuszs 14 Jul '09

14 Jul '09

Hi, What do these mean? >#GDKmmap(394264576) fails, try to free up space [memory in use=36011928,virtual memory in use=511214485504] #GDKmmap(394264576) fails, try to free up space [memory in use=36011952,virtual memory in use=511214485504] #GDKmmap(394264576) result [mem=34705504,vm=286633623552] #GDKmmap: recovery ok. Continuing.. #GDKmmap(394264576) result [mem=34379040,vm=228672143360] #GDKmmap: recovery ok. Continuing.. Thanks. Dariusz.

2 1

[MonetDB-users] server crash/lib_sql.so.2.30.0
by dariuszs 14 Jul '09

14 Jul '09

Hi, What does this mean? Jul 14 11:41:05 monetdb kernel: [78349.487006] mserver5[18645]: segfault at 18 i p 00007f2ed339d62d sp 00007f2ed2cd3dc0 error 4<6>mserver5[18651]: segfault at 18 ip 00007f2ed339d62d sp 00007f2ed20cddc0 error 4 in lib_sql.so.2.30.0[7f2ed32da0 00+103000] Thanks. Dariusz.

2 3

[MonetDB-users] Data Loss after Restarting MonetDB
by JLee 14 Jul '09

14 Jul '09

Hi: We are running into an interesting problem that we can’t seem to resolve. We have a large number of xml files that we are parsing and loading to MonetDB Feb2009-SP2 sql server running on Redhat with 40gigs of memory. Basically, everything looks fine while the files are being loaded. But, once the server is shutdown and restarted (after the data load has been finished), all the data is simply gone! We have done a dump and restored the db before restarting the db, then loaded more data on top of it; and the data that was restored is intact. But, the newly loaded data is simply gone. It doesn’t seem to matter how small or large the data set being loaded is (tried as small as 1.5 gig of xml files resulting in 1.2M rows). Which leads me to believe our data is not persistent! (a minor problem.. J) We also noticed that as our parser (java app using JDBC with hibernate) is loading the data, that the memory use continues to grow and never shrinks after the data load is complete. So, it seems like the all the data being loaded is being kept in the memory; but never becomes persistent. We tried waiting 4 hours after loading 1.2M rows, thinking that there may be a lag in syncing to the disk (which seem odd, in case of a sudden server failure); but the data simply is gone. Can you provide some assistance in troubleshooting this problem? Thanks. Best regards, H

1 0

[MonetDB-users] merovingian/vaultkey
by dariuszs 14 Jul '09

14 Jul '09

Hi, How do I set vaultkey? Thanks. Dariusz.

2 4

[MonetDB-users] pcre.lib vs. pcred.lib
by Alex Bo. 13 Jul '09

13 Jul '09

Hi, I have built PCRE on windows with CMAKE as instructed by your documentation. Installing PCRE as instructed results in a "pcred.lib" file in the lib directory, and no "pcre.lib" file. When I try to build the MonetDB5 module, I get an linker error that pcre.lib wasn't found (even though I pointed the nmake command to pcred.lib with the LIB_PCRE flag). Why is it looking for pcre.lib if I defined pcred.lib in LIB_PCRE? What do I need to do to get PCRE and Monet work together on Windows? Please help me out. Thanks. (I used pcre 7.8 and CMAKE 2.6) -- View this message in context: http://www.nabble.com/pcre.lib-vs.-pcred.lib-tp24461629p24461629.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Server only runs on machine it was built on.
by Alex Bo. 13 Jul '09

13 Jul '09

Hi, I successfully built the May2009 SP on Windows from CVS. However, when I copy the files and run MSQLserver.bat on other machines, I get the following error from the server which fails to start: #WARNING: LoaderException:loadLibrary:Loading errorThe application has failed to start because its side-by-side configuration is incorrect. Please see the appli cation event log for more detail. .pcre #WARNING: LoaderException:loadLibrary:Loading errorThe application has failed to start because its side-by-side configuration is incorrect. Please see the appli cation event log for more detail. .pcre Please help. Thanks. -- View this message in context: http://www.nabble.com/Server-only-runs-on-machine-it-was-built-on.-tp244592… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] select count(*) not understood by squirrelsql
by Franck Routier 13 Jul '09

13 Jul '09

Hi, using squirrelsql to access Monetdb (so, via jdbc), the tool fails to display the result for select count(*) from table; Instead it will display <Other>. On the metadata tab, it says the result is seen as culumnType = 1111, columnTypeName = wrd Issuing the same command in montdb command line display the result. Any idea ? Franck

3 6

[MonetDB-users] mserver5/ubuntu crash
by dariuszs 12 Jul '09

12 Jul '09

Hi, mserver5 crashed on ubuntu 64bit while running this query select count(*) from table1 where c1 like 'New Y%'; root@monetdb:~# mserver5 --dbinit="include sql;" # MonetDB server v5.12.0, based on kernel v1.30.0 # Serving database 'svnp', using 8 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2009 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 /etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://monetdb:50000/ # MonetDB/SQL module v2.30.0 loaded >Bus error root@monetdb:~# /var/log/messages Jul 9 10:52:38 monetdb kernel: [260266.738514] mserver5[18561] general protection ip:7f1fa5f8996c sp:7f1f910989d8 error:0 in libbat.so.1.30.0[7f1fa5699000+d5f000] Jul 9 10:54:30 monetdb kernel: [260377.889355] mserver5[18577] trap stack segment ip:7fc1d0d161d3 sp:7fc1c8996d50 error:0

3 5

[MonetDB-users] sql module of May2009 SP doesn't compile on windows
by Alex Bo. 12 Jul '09

12 Jul '09

Hi, When building the sql module of MonetDB May2009 SP from CVS on Windows, I get the following compilation errors: sql_gencode.c .\..\..\..\..\src\backends\monet5\sql_gencode.mx(1027) : error C2065: 'leftjoinR ef' : undeclared identifier .\..\..\..\..\src\backends\monet5\sql_gencode.mx(1027) : warning C4047: 'functio n' : 'char *' differs in levels of indirection from 'int' .\..\..\..\..\src\backends\monet5\sql_gencode.mx(1027) : warning C4024: 'newStmt 2' : different types for formal and actual parameter 3 Please help. -- View this message in context: http://www.nabble.com/sql-module-of-May2009-SP-doesn%27t-compile-on-windows… Sent from the monetdb-users mailing list archive at Nabble.com.

2 6

[MonetDB-users] Is monetDB nativeXML or not?
by Mtekel 10 Jul '09

10 Jul '09

Hello, I have a question, whether MonetDB/XQuery is native XML database or not. To clarify what is native or not, http://en.wikipedia.org/wiki/XML_database : " Two major classes of XML database exist: XML-enabled: these map all XML to a traditional database (such as a relational database), accepting XML as input and rendering XML as output. This term implies that the database does the conversion itself (as opposed to relying on middleware). Native XML (NXD): the internal model of such databases depends on XML and uses XML documents as the fundamental unit of storage, which are, however, not necessarily stored in the form of text files. " Then according to this page, monetDB is native XML: http://monetdb.cwi.nl/MonetDB/Version4/Documentation/monet/index.html " 2 MonetDB Features MonetDB is database engine geared at high performance on systems with (complex) queries that have to analyze large amounts of data (i.e. OLAP, not OLTP). The features in which it most differs from other DBMSs are summarized below: Independence of SQL: MonetDB has a front-end/back-end design allowing the storage of more than just relational tables (such as object-oriented or native XML data), and allowing the use of querying paradigms other than just SQL (such as OQL and XQuery). " Whereas according to this one, it is not: http://monetdb.cwi.nl/projects/monetdb/XQuery/Documentation/Separate-Documen ts-vs-Document-Collections.html#Separate-Documents-vs-Document-Collections : " 2.9.1.1 Storage Overhead In MonetDB/XQuery all XML is stored in relational tables. " Is then monetDB really native? Thanks in advance for clarification... Regards, M. Tekel

3 2