- users-list - monetdb.org

Re: [MonetDB-users] COPY...INTO from remote
by Matthew Jones 10 Sep '09

10 Sep '09

The original suggestion was to use 'mclient' with the '<' redirect operator I'm assuming like this: mclient -lsql -s "COPY INTO table from STDIN DELIMITERS '\t';" < recordsfile but this doesn't work, it wants to interpret the contents of the file as a query... is there no way to batch load from a script from a remote monet instance? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

3 2

[MonetDB-users] monetdb-install.sh: com.sun.tools.javac.Main is not on the classpath.
by Ying Zhang 09 Sep '09

09 Sep '09

Hi all, I logged in on another INS1 desktop 'vili', and try to install monetdb there with $monetdb-install.sh --enable-xquery --enable-optimize --nightly=stable --prefix="/export/scratch0/zhang/MonetDB" and it ends in: BUILD FAILED /var/tmp/MonetDB-1252495100/MonetDB-java/java/build.xml:94: The following error occurred while executing this line: /var/tmp/MonetDB-1252495100/MonetDB-java/java/build.xml:289: Unable to find a javac compiler; com.sun.tools.javac.Main is not on the classpath. Perhaps JAVA_HOME does not point to the JDK. It is currently set to "/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/ jre" While javac says: $ which javac /usr/bin/javac $ javac -version javac 1.6.0_0 Manually setting JAVA_HOME to my own java installation ends in the same error: $env JAVA_HOME=/ufs/zhang/install/java/current monetdb-install.sh -- enable-xquery --enable-optimize --nightly=stable --prefix="/export/ scratch0/zhang/MonetDB" The log is attached. Thanks in advance. Jennie

3 4

[MonetDB-users] Serious concurrency issues via python api
by Matthew Jones 09 Sep '09

09 Sep '09

I have multiple processes reading and writing to the same table and I've noticed really persistent concurrency problems: File "/usr/lib/python2.5/site-packages/monetdb/sql/cursors.py", line 208, in execute self.__store_result(block) File "/usr/lib/python2.5/site-packages/monetdb/sql/cursors.py", line 520, in __store_result self.__exception_handler(InterfaceError, "Unknown state, %s" % block) File "/usr/lib/python2.5/site-packages/monetdb/sql/cursors.py", line 574, in __exception_handler raise exception_class(message) InterfaceError: Unknown state, &2 65541 -1 !ERROR: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead I'm not sure what the problem is, but it exists in such a way that all operations that load or update information in MonetDB have to be put into a loop to continuously retry. I'm using autocommit, but I would expect MonetDB to handle concurrency issues in a decent way, like maybe blocking a query until the other query finished. I'm not clobbering data, most of these are just bulk load operations that insert new rows. Is there any way to address this issue without having to putting retry loops around all of the code I'm writing to write to the database? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

6 10

[MonetDB-users] readline/readline.h does not contain rl_completion_func_t
by Ying Zhang 08 Sep '09

08 Sep '09

Hello, I was trying to install monet on Mac OS 10.5 with monetdb-install.sh --enable-xquery --enable-optimize --nightly=stable It has always worked, but this time, it stops due to an error while configuring MonetDB: checking readline/readline.h usability... yes checking readline/readline.h presence... yes checking for readline/readline.h... yes checking for readline in -lreadline... yes checking for rl_completion_matches in -lreadline... no checking whether rl_completion_func_t exists... configure: error: readline/readline.h does not contain rl_completion_func_t, is it GNU readline? The complete log is in the attachment. What is wrong with my readline.h? How can I find out which readline.h the install script is checking, and which readline.h is expected / should be used? Thanks in advance. Jennie

2 3

[MonetDB-users] OpenSSL and SHA512
by Alex Bo. 08 Sep '09

08 Sep '09

Hi, I have compiled the latest August build from CVS on windows. When I run mclient, I get this: server requires 'SHA512' hash, but client support was not compiled in I saw another thread regarding this before, but couldn't resolve the issue based on it. Please let me know what I might be doing wrong. Here is my server output: # MonetDB server v5.14.0, based on kernel v1.32.0 # Serving database 'demo', using 1 thread # Compiled for i686-pc-win32/32bit with 32bit 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 C:\MonetCVS_base_Aug09\MonetCVS32_cleanBuild\Install\etc\monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ > Thanks. -- View this message in context: http://www.nabble.com/OpenSSL-and-SHA512-tp25223356p25223356.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 5

[MonetDB-users] using MonetDB for INEX data
by Erik Tjong Kim Sang 07 Sep '09

07 Sep '09

Dear MonetDB users, We are trying to add load the INEX XML Wikipedia data into MonetDB but we get some strange error messages: When adding documents: $ echo 'pf:add-doc("/data/erikt/inex2009/tmp/330.xml", "330")' | /storage/erikt/MonetDB/bin/mclient -lxquery MAPI = monetdb@localhost:50000 QUERY = pf:add-doc("/data/erikt/inex2009/tmp/330.xml", "330") ERROR = !ERROR: interpret_params: insert(param 3): invalid BAT. When querying the data: $ echo -e 'let $c := collection("330")\nfor $t in $c//article\nreturn($t//title/text())' | /storage/erikt/MonetDB/bin/mclient -lxquery ... text {"The Black Stallion (film)"}, text {"Halle, Germany"}, text {"FLCL"}, write error We get only 5% of the data that was expected from the query and then it crashes with the message "write error". Does anyone know what could cause these problems? Please respond to me personally (e.f.tjong.kim.sang(at)rug.nl) since I am not a member of this mailing list. Erik TKS -------- e-mail: e.f.tjong.kim.sang(a)rug.nl WWW: http://www.let.rug.nl/erikt/

2 2

[MonetDB-users] Segmentation fault
by Sten Hjelmqvist 07 Sep '09

07 Sep '09

Using MonetDB Aug 2009 on a Ubuntu Server 9.04 with 4Gbyte memory and 60Gbyte free disk space. I import a large database, ~1.5Gbyte , using \<filename. After a while the database exits with msg: ">Segmentation fault" Can I reconfigure the database to avoid this? --------------------------------------- Sten Hjelmqvist DataKonsult MERdata AB Sveavägen 33, 111 34 Stockholm Mobil : 0708 305616 E-mejl : sten.hjelmqvist(a)merdata.se <mailto:sten.hjelmqist@merdata.se> Webb : www.merdata.se <http://www.main.se/> ---------------------------------------

3 6

[MonetDB-users] XQUF: expected insert speed
by Ying Zhang 06 Sep '09

06 Sep '09

Hi all, Does anyone have an idea of the 'normal' insert speed (e.g., #nodes per sec, or #MBs per sec) of MonetDB/XQuery? I inserted a document of ~101mb into an xmark document of 2mb, and it took more than 10 minutes: $ mclient -lx -g -t xquery>do insert doc("persons.xml")/persons into doc("xmark2mb.xml")/ site more><> Trans 12.143 msec Shred 8.429 msec Query 5.869 msec Update 619019.583 msec Timer 619058.179 msec With the algebra back-end, it ends in an error (bug report will follow), but it is also not faster: $ mclient -lx -t xquery>do insert doc("persons.xml")/persons into doc("xmark2mb.xml")/ site more><> MAPI = monetdb@localhost:50000 QUERY = do insert doc("persons.xml")/persons into doc("xmark2mb.xml")/ site ERROR = !ERROR: interpret: no matching MIL operator to '-(lng, void)'. !MAYBE YOU MEAN: ! -(dbl) : dbl ! -(lng) : lng ! -(flt) : flt ! -(wrd) : wrd ! -(int) : int ! -(sht) : sht ! -(bte) : bte ! -(chr) : chr ! -(lng, dbl) : dbl ! -(lng, flt) : dbl ! -(lng, lng) : lng ! -(lng, wrd) : lng ! -(lng, int) : lng ! -(lng, sht) : lng ! -(lng, bte) : lng ! -(lng, chr) : lng ! -(dbl, dbl) : dbl ! -(dbl, flt) : dbl ! -(dbl, lng) : dbl ! -(dbl, wrd) : dbl ! -(dbl, int) : dbl ! -(dbl, sht) : dbl ! -(dbl, bte) : dbl ! -(dbl, chr) : dbl ! -(flt, dbl) : dbl ! -(flt, flt) : flt ! -(flt, lng) : flt ! -(flt, wrd) : flt ! -(flt, int) : flt ! -(flt, sht) : flt ! -(flt, bte) : flt ! -(flt, chr) : flt ! -(wrd, dbl) : dbl ! -(wrd, flt) : flt ! -(wrd, lng) : lng ! -(wrd, wrd) : wrd ! -(wrd, int) : wrd ! -(wrd, sht) : wrd ! -(wrd, bte) : wrd ! -(wrd, chr) : wrd ! -(int, dbl) : dbl ! -(int, flt) : flt ! -(int, lng) : lng ! -(int, wrd) : wrd ! -(int, int) : int ! -(int, sht) : int ! -(int, bte) : int ! -(int, chr) : int ! -(sht, dbl) : dbl ! -(sht, flt) : flt ! -(sht, lng) : lng ! -(sht, wrd) : wrd ! -(sht, int) : int ! -(sht, sht) : sht ! -(sht, bte) : sht ! -(sht, chr) : sht ! -(bte, dbl) : dbl ! -(bte, flt) : flt ! -(bte, lng) : lng ! -(bte, wrd) : wrd ! -(bte, int) : int ! -(bte, sht) : sht ! -(bte, chr) : bte ! -(bte, bte) : bte ! -(chr, dbl) : dbl ! -(chr, flt) : flt ! -(chr, lng) : lng ! -(chr, wrd) : wrd ! -(chr, int) : int ! -(chr, sht) : sht ! -(chr, bte) : bte ! -(chr, chr) : chr Timer 632991.583 msec xquery>\q <persons> is the root node of the document 'persons.xml', which contains 234599 xmark <person> nodes. So, actually, only one large node is inserted. It looks quite slow. Is this the expected behaviour or did I do something wrong? I'm using the latest stable version of MonetDB/XQuery, compiled and installed with 'monet-install.sh --enable-xquery --enale-optimize -- nightly=stable --prefix=...'. The document 'xmark2mb.xml' was shredded as an updatable doc. with 10% holes, and the document 'persons.xml' was shredded as a read-only doc. Both documents were already loaded, by executing the query 'count(doc("[docname]")// person)'. MonetDB/XQuery is running on my CWI PC (i.e., FC10, Intel(R) Core(TM)2 Quad CPU Q6600 @2.40GHz, 8GM RAM). During the execution, one CPU is constantly busy for 100%, Mserver uses 22% ~ 27% of MEM (according to 'top'). Does anyone have an idea how to get insert faster? Regards, Jennie

1 0

[MonetDB-users] Long query response time with 'in' statement
by mkurtulus 02 Sep '09

02 Sep '09

Hi, I'm trying to create a benchmark MonetDB vs Postgres and for many types of queries, MonetDB quite faster than Postgres.But there is an interesting result when i try a query which contains an 'in' statement.The response times are MonetDB - 58 sec Postgres - 3 sec for the query : select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10, CABACT as CABACT where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') and CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf and RFOADV_10.rfoadvsup in ('5030', '5031', '5032', '5033', '5034', '5035', '5036', '5037', '5038') group by RFOADV_10.rfoadvsup i reform the query so that it can give the same result without 'in' : select RFOADV_10.rfoadvsup as c0, sum((case when cabact___rfountide = 'NB_E' then cabactqte else 0 end)) as m0 from RFOADV as RFOADV_10 join CABACT on (CABACT.cabact_c2rfodstide = RFOADV_10.rfoadvinf) join (select '5031' as pole UNION select '5032' as pole UNION select '5033' as pole UNION select '5034' as pole UNION select '5035' as pole UNION select '5036' as pole UNION select '5037' as pole UNION select '5038' as pole ) sub ON (RFOADV_10.rfoadvsup = sub.pole) where (cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001') group by RFOADV_10.rfoadvsup the response time for MonetDB decreases 100 times (0.6 sec). Do you have any idea about it ? I run both Monetdb and Postgres on a server with 10GB Ram and the tables have CABACT : 677000 RFOADV : 140000 rows. Thanks, Mehmet -- Open WebMail Project (http://openwebmail.org)

2 5

[MonetDB-users] COPY...INTO with auto increment
by Matthew Jones 31 Aug '09

31 Aug '09

I noticed that COPY...INTO for bulk loading requires that all fields be present in the data file. Is there any way to use bulk loading and have it honor the auto increment column or at least not have to specify every column in the data file? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

3 9

[MonetDB-users] Function overloading
by Alex Bo. 30 Aug '09

30 Aug '09

Hi, I am trying to create an overloaded iif function, so I created these custom functions: 1. create function iifnew(tv timestamp, fv timestamp, cond boolean) returns timestamp begin if cond then return tv; else return fv; end if; end 2. create function iifnew(tv int, fv int, cond boolean) returns int begin if cond then return tv; else return fv; end if; end 3. create function iifnew(tv string, fv string, cond boolean) returns string begin if cond then return tv; else return fv; end if; end But now, if I do something like this: update table1 set "Column1" = iifnew('hello1', 'hello2', ("Column2" = "Column3")) I get this: !SQLException:int:conversion of string 'hello1' failed Is this a bug in function overloading? I am running Feb2009 from CSV on Windows. Thanks. -- View this message in context: http://www.nabble.com/Function-overloading-tp25210862p25210862.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] tijah phrase search problem
by Roy Walter 27 Aug '09

27 Aug '09

The following query: tijah:queryall("//p[about(., 'drug treatment')]") returns a number of results from my sample document. Some of these results contain the phrase "drug misuse". The following query: tijah:queryall("//p[about(., 'drug misuse')]") returns zero results from the sample document, which is clearly incorrect since some results returned by the first query should be returned by the second. I have deleted and reloaded the sample document and I have recreated the tijah index and the result is consistently incorrect. Is this a bug? -- Roy

2 5

[MonetDB-users] What's the best file system and sector size for monetdb?
by Allen Zhang 26 Aug '09

26 Aug '09

Hi, What is the best suitable file system and sector size for monet dbfarm? Can anyone share the experience? Thanks Allen

1 0

[MonetDB-users] Performance vs postgresql
by jatin patni 24 Aug '09

24 Aug '09

Hi, I am in the process of evaluating two major databases....monetdb and postgresql for building a backend database system for backtesting on huge historical data sets around 100GB. I copied some test data(a 250mb table) using bulk copy in both and monetdb was way faster. But when I did a simple query "SELECT * from tablename", it took around 3 minutes for 1 lakh rows via mclient and a gui interface (aqua studio based on JVM simply said out of memory). The bulk copy file was in the format collumns seperated by pipes in a text file. While using postgresql, at first it was slow but once I did a vacuum(function in postgresql), the (select * from tablename) was much faster than monetdb. So, what am I missing here, Do I need to do something to enhance the performance in Monetdb....... I have high hopes from monetdb, so please help. Thanks -- Jatin Patni Tel: 91 9911 649 657 jatinpatni(a)gmail.com www.jatinpatni.co.nr

3 4

Re: [MonetDB-users] Performance vs postgresql(Stefan Manegold)
by jatin patni 23 Aug '09

23 Aug '09

Hi Stefan, Thanks for your reply. Well, I understand that a simple dump of the table wouldn't be that fast in monetdb cuz it was not built for that purpose. I am evaluating monetdb 5 and postgresql plus standard server from enterprise db. I have not yet tested performance of running complex queries on both systems. I am testing on windows 32 bit, core2duo processor, 2.8 ghz, with 3gb ram 2.8 ghz I hope this config would work fine for backtesting with around 100 gb databases. Will post a detailed result of using both postgresql and monetdb later. Let me go into some detail.....my database would have 4 collumns(sno, price, name, volume) and my backtesting would generally involve querying this dataset and checking for moving average crossovers of price with the last n days moving averages, and may grow to more complex querying involving volumes. My concern is, if I need all the collumns for the queries, will monetdb perform better or should I stick with postgresql plus standard. I'll post the pperformance results once I have copmp[leted populating the databases in postgresql......But I want to try out monetdb also, I know that the postgresql interpreter may perform very slow..... Anyways, thanks for this amazing product.....Also, when will the X100 be available as an extension in Monetdb --------------------------------------------------------- *Hi Jatin, thank you very much for your interest in MonetDB! We are glad to hear that you found MonetDB "way faster" than PostgreSQL with bulk loading your 250 MB dataset --- btw, which versions of MonetDB and PostgreSQL are you comparing on what kind of system (OS, hardware (CPU, RAM, I/O system))? With a simple select * from tablename, you basically evaluate to ability of both server and client to pump your 250 MB of data from the server to the client. That is probably an interesting measurement in case you mostly do select * from table queries. However, we do not consider such queries the "major challenges" for DBMSs and designed MonetDB not for plainly pumping all data from the server to the client in the most efficient way. Rather, we assume that the major part of data-management, i.e., processing (more) complex queries including selections, joins, aggregation, etc. are done by the DBMSs (server) and only the rather small results of such (analytical) queries are then sent to the server. Moreover, as opposed to "classical" row-stores, column-stores in general are not necessarily designed with a major focus on most efficiently re-constructing the whole table. They rather assume that most (analytical) queries usually only access a subset of all column of a table. Being able to access only the requested data and hence not having to carry-around excess-luggage of non-used columns during query processing is one of the key design differences of column-stores over row-stores. Hence, to speed up simple select * from tablename queries in MonetDB, you'd probably (at least) need to re-design and -implement MonetDB's client-server protocol (MAPI). We are curious, though, to also hear about your experiences with more complex queries that return much smaller (i.e., "more reasonable"?) result sets. Stefan ps: It is interesting to hear that the "vacuum" command in PostgreSQL had a significant impact on a simple select * from table query over bulk-loaded data, i.e., without any updates being performed --- I wouldn't know wht akind of "garbage" bulk-loading leaves behind that needs to be cleaned-up ... On Fri, Aug 21, 2009 at 07:18:46PM +0530, jatin patni wrote:* > Hi, I am in the process of evaluating two major databases....monetdb > and postgresql for building a backend database system for backtesting > on huge historical data sets around 100GB. I copied some test data(a > 250mb table) using bulk copy in both and monetdb was way faster. But > when I did a simple query "SELECT * from tablename", it took around 3 > minutes for 1 lakh rows via mclient and a gui interface (aqua studio > based on JVM simply said out of memory). The bulk copy file was in the > format collumns seperated by pipes in a text file. > While using postgresql, at first it was slow but once I did a > vacuum(function in postgresql), the (select * from tablename) was much > faster than monetdb. > > So, what am I missing here, Do I need to do something to enhance the > performance in Monetdb....... > I have high hopes from monetdb, so please help. > Thanks > > -- > Jatin Patni > Tel: 91 9911 649 657 > jatinpatni(a)gmail.com > www.jatinpatni.co.nr > > ------------------------------ ------------------------------------------------ > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day > trial. Simplify your report design, integration and deployment - and focus on > what you do best, core application coding. Discover what's new with > Crystal Reports now. http://p.sf.net/sfu/bobj-july > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/<http://www.cwi.nl/%7Emanegold/> | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 | -- Jatin Patni Tel: 91 9911 649 657 jatinpatni(a)gmail.com www.jatinpatni.co.nr

2 1

[MonetDB-users] serial vs auto_increment
by Matthew Jones 22 Aug '09

22 Aug '09

After creating a table with a 'serial' type id field I noticed this when describing it: sql>CREATE TABLE "schtest"."testt" ("id" serial, ... sql>\d testt CREATE TABLE "schtest"."testt" ( "id" int NOT NULL DEFAULT next value for "schtest"."seq_4291", ... The following page: http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Data-Types.html Says this about the serial data type: "special 64 bit integer (sequence generator)" and this about the 'int' data type: "32 bit integer" This looks like it created a table with a 32-bit integer sequence rather than a 64-bit integer sequence. If you use the auto_increment method you can set the type explicitly to be bigint, but should "serial" generate a bigint column also? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

3 3

[MonetDB-users] Announcement: New Aug2009 feature release of MonetDB suite
by Stefan Manegold 20 Aug '09

20 Aug '09

Dear all, The MonetDB team at CWI/MonetDB BV are pleased to announce the new "Aug2009" feature release of the MonetDB suite of programs. This feature release is backward compatible with the previous releases (Nov2008 through May2009-SP2). Caveat: When upgrading from a release older than May2009 (i.e., Nov2008 through Feb2009-SP2), you need to move your write-ahead log following the "recommended" instructions for SQL (<http://monetdb.cwi.nl/Development/Releases/May2009/#SQL>) and/or XQuery (<http://monetdb.cwi.nl/Development/Releases/May2009/#XQuery>). More information on the new release is available at <http://monetdb.cwi.nl/Development/Releases/Aug2009/>. The sources and binaries are available at <http://monetdb.cwi.nl/Download/>. Enjoy! The MonetDB team at CWI/MonetDB BV. -- | 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

Re: [MonetDB-users] MonetDB Installation
by listanand＠gmail.com 19 Aug '09

19 Aug '09

Thanks Stefan and Stefan :) Unfortunately, I still have not been able to install MonetDB. After installing PCRE and OpenSSL, I ran the install script again and I still get the error that OpenSSL library is not being found. This is clearly not the case as I even added it (OpenSSL's bin directory) to the system path, and am able to get to the OpenSSL prompt. Am I missing something here? Thanks again.

5 4

[MonetDB-users] MonetDB Installation
by listanand＠gmail.com 18 Aug '09

18 Aug '09

Hello all, I am trying to install MonetDB (from http://monetdb.cwi.nl/downloads/sources/May2009-SP2/MonetDB-May2009-SuperBa…) on Ubuntu Linux (Intrepid), and am running into one issue after another. First, after I ran the install script, I got an error saying that I need to install PCRE. After installing PCRE, I tried running the script again and then got an error saying that I need to install OpenSSL. Is there a way I could figure out what things are needed in advance, so I can install them all at one go? Alternatively, is there an easier way to install MonetDB? Thanks in advance. Andy

3 2

[MonetDB-users] may2009sp2 suberball patch
by Lazaros Koromilas 14 Aug '09

14 Aug '09

hello all, i needed to workaround a libtool `directory not ending in' error by adding an extra slash in a path. the ``patch'' is attached. i successfully built monetdb may 2009 sp2 with # ./monetdb-install.sh --enable-sql --enable-xquery on freebsd 7.2 release. in the attachment i include some installed packages/dependencies in case someone needs them. thanx, lazaros. -- http://www.ics.forth.gr/~koromil

1 0

[MonetDB-users] PHP XQuery error
by Gene Stevens 14 Aug '09

14 Aug '09

Hi everyone, I am a new MonetDB user. I am specifically interested in MonetDB's XQuery support with client access through PHP. I currently get parsing errors when trying any kind of XQuery through the PHP module. I'm wondering if this is a a bug in the client or if I am possibly doing something wrong. E.g.: #!/usr/bin/env php <?php $db = monetdb_connect('xquery', 'localhost', 50000, 'monetdb', 'monetdb') or die ("Failed to connect\n"); $query = 'count(1,2,3,4)'; $res = monetdb_query($query); ?> Results in: Warning: monetdb_query(): Query failed: parse error: syntax error, unexpected ;, expecting $end on line 1, column 15 (next token is `;') !parse error: XQuery parsing failed in /home/user/monet.php on line 6 ... for any variation of an XQuery. I have tried the server from the Ubuntu repository as well as I've built it from source with the same results. Here is my environment: PHP 5.2.6-3ubuntu4.1 with Suhosin-Patch 0.9.6.2 (cli) (built: Apr 23 2009 14:37:14) Ubuntu 9.04: Linux ubuntu 2.6.28-11-server #42-Ubuntu SMP Fri Apr 17 02:45:36 UTC 2009 x86_64 GNU/Linux Mserver --dbinit="module(pathfinder);" # MonetDB Server v4.30.4 # based on GDK v1.30.4 # Copyright (c) 1993-July 2008, CWI. All rights reserved. # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs; dynamically linked. # Visit http://monetdb.cwi.nl/ for further information. # PF/Tijah module v0.13.0 loaded. http://dbappl.cs.utwente.nl/pftijah # MonetDB/XQuery module v0.28.3 loaded (default back-end is 'algebra') # XRPC administrative console at http://127.0.0.1:50001/admin MonetDB> Thanks for any insight! - Gene Stevens

4 11

Re: [MonetDB-users] MonetDB/XQuery Scaleability
by listanand＠gmail.com 14 Aug '09

14 Aug '09

Hi Wouter, Thanks for your note. I will definitely give it a try. 2 more questions though. Is MonetDB amenable to being accessed in parallel? I will need to run many queries on the fly, and will certainly need multiple connections to the database. Also, how well does MonetDB/XQuery work with Perl? Is it better to stick to Java/Python for now? Thanks again for your help. Andy

2 1

[MonetDB-users] ODBC error (specified driver could not be loaded due to system error 126)
by fadk 13 Aug '09

13 Aug '09

I read that there was a bug like this before, but its been fixed. Its seems like installing ODBC drivers is a problem for windows. Can you assist with this ? I am running winXP using the MonetDB 32 installer. http://sourceforge.net/tracker/?func=detail&atid=482468&aid=1902879&group_i… Also, is it possible to only build the client (to use mapi) on winXP given that I have MonetDB installed. ? Thanks for your help. -- View this message in context: http://www.nabble.com/ODBC-error-%28specified-driver-could-not-be-loaded-du… Sent from the monetdb-users mailing list archive at Nabble.com.

4 4

[MonetDB-users] Phrase searching with 'or'
by Roy Walter 13 Aug '09

13 Aug '09

Consider the following XQuery using two phrases: tijah:queryall("//p[about(., 'wind turbine') or about(., 'wind farm')]") It returns a restricted result set where only 'wind turbine' AND 'wind farm' are present. Substituting 'and' for 'or' in the query returns the same number of rows. Is this a bug? -- Roy

2 1

[MonetDB-users] Phrase searching with 'or'
by Roy Walter 13 Aug '09

13 Aug '09

Consider the following XQuery using two phrases: tijah:queryall("//p[about(., 'wind turbine') or about(., 'wind farm')]") It returns a restricted result set where only 'wind turbine' AND 'wind farm' are present. Substituting 'and' for 'or' in the query returns the same number of rows. Is this a bug? -- Roy

1 0

[MonetDB-users] optimizer.exportChunk exception
by Alex Bo. 11 Aug '09

11 Aug '09

Hi, I got this error on running "Select * from tables": !TypeException:user.s6_2[154]:'optimizer.exportChunk' undefined in: _237:any := optimizer.exportChunk() !TypeException:user.s6_2[155]:'optimizer.affectedRows' undefined in: _241:any := optimizer.affectedRows() Consequent queries were successful. Seems like a bug. -- View this message in context: http://www.nabble.com/optimizer.exportChunk-exception-tp24915518p24915518.h… Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] Query speed up
by Allen Zhang 11 Aug '09

11 Aug '09

Hi, All, I have a client program generates the following query against monetdb, can I create index on fact columns to speed thing up? What¹s the best approach to resolve this kind performance issue? SELECT DISTINCT t1.column1 FROM (fact t0 LEFT JOIN dimension1 t1 ON t0.header_id = t1.header_id) WHERE t0.column3 = 'xxxxxx' And t0.column5 IN ('item1¹, OEitem2¹, OEitem3¹, OEitem4¹); Thanks in advance. Allen

2 4

[MonetDB-users] MonetDB/XQuery Scaleability
by listanand＠gmail.com 10 Aug '09

10 Aug '09

Dear all, I am new to MonetDB/XQuery and am considering using it for storing and processing a large number of webpages (~1million) in XML format. I am hoping to know if this is indeed the right tool, and how well it will scale to handle tasks of this magnitude. Thanks in advance

2 3

[MonetDB-users] COPY...INTO from remote
by Matthew Jones 08 Aug '09

08 Aug '09

I noticed that COPY...INTO from a file, when executed from a remote MonetDB mclient instance tries to load the file from the system that the database is actually running on rather than the system running the remote client. Is this the expected behavior? Has anyone dealt with this or found another way to batch-load from a remote client? 73, Matthew W. Jones (KI4ZIB) http://matburt.net

3 3

[MonetDB-users] MonetDB Scaleability
by Calum Miller 06 Aug '09

06 Aug '09

Hi, I've been doing performance tests on MondetDB to see how it scales. I've been running tests on Amazon EC2 (7GB of memory, 64-Bit, CentOS 5.2) The table I am using has 60 columns with most being just simple characters of length 2, here is the query: select CollectionDEPOTID as c0, CollectionCountryID as c1, sum(ParcelCount) as m0 from parcel_stage_short as parcel_stage_short group by c0, c1; Here are the load time in batches of 8,000,000 using the following command: COPY 8000000 RECORDS INTO parcel_stage_short FROM '/mnt/ parcel_stage_short.txt' USING DELIMITERS '\t','\n'; Rows Load Time Increase % Increase 8000000 240434.17 8000000 424937.54 184503.37 76.74% 8000000 599621.98 359187.81 149.39% 8000000 769276.70 528842.53 219.95% 8000000 889600.24 889600.24 209.35% msecs msecs As can be seen the load time increases by around 200,000 msec for each additional 8Million rows added. Any tips on how to reduce the load speed? Would it be faster to load the table in one go (tried loading in bigger batches but loader fails)? Here are the performance times for each additional batch of 8,000,000 rows: Rows 1st Query 1st Increase % 1st Increase 2nd Query 2nd Increase % Increase 08000000 1176.53 16000000 4838.52 3661.99 311.25% 2239.61 1168.30 109.05% 24000000 33947.84 32771.31 2785.43% 9323.29 8251.98 770.27% 32000000 837114.13 835937.61 71051.29% 5456.96 4385.65 409.37% 40000000 668380.26 667203.73 56709.60% 8538.02 7466.71 696.97% msecs msecs msecs msecs The performance of the 1st query is unsurprisingly poor just after the data has been copied in The performance of the same query a second time yields much faster results The performance of the monetDb with 1st/2nd query degrades as load increases. Are these results typical? Is there anything I can do to increase performance on the 1st or 2nd queries? Thanks in advance Calum

3 5

[MonetDB-users] tijah exact matching
by Roy Walter 06 Aug '09

06 Aug '09

Is there a way to force a tijah query to return only exact matches? For example, if I search for "wind farm", as in: ("tijah:queryall[about(., wind farm)]") I get matches for "wind farm", "wind" and "farm" but say I only want matches for "wind farm"? I can of course loop through the result with contains(., "wind farm") to exclude unwanted results but that has performance implications. -- Roy

2 1

[MonetDB-users] Get attribute value as text how?
by Roy Walter 06 Aug '09

06 Aug '09

The following XQuery fragment will add the speakername attribute as an attribute of the tag: {$res/../@speakername}: {$res} becomes: Some text... What I want is: Roy Walter: Some text... How does MonetDB/XQuery handle this? I tried: {xs:string($res/../@speakername)}: {$res} but it errors. -- Roy

2 4

[MonetDB-users] how to read/write blob data ?
by 李坤 04 Aug '09

04 Aug '09

I want to store image files into monetdb sql server. I think there are two approaches to do it: the first one is to store the file's full path in a column with varchar type, the second one is to store the image data in a column with blob type. I don't want to see a lot of small image files in the directory, so I choose the second approach. After reading the monetdb manual, I find some examples like this: sql> insert into t1 values (1, blob '012'); I think this is not enough. I also try to use odbc api to store binary data into monetdb, but failed. So please help me to write the blob data into monetdb, as well as the read operation. Maybe there are other ways I don't know, please tell me, thanks!

2 1

[MonetDB-users] problem in extract second
by fatar fatar 03 Aug '09

03 Aug '09

query: select extract(second from '2009-07-24 16:03:35.0000'); result : 0.00 could you please tell me why the result is zero??? and how can i get more spesific result??? Akses email lebih cepat. Yahoo! menyarankan Anda meng-upgrade browser ke Internet Explorer 8 baru yang dioptimalkan untuk Yahoo! Dapatkan di sini! http://downloads.yahoo.com/id/internetexplorer

1 0

[MonetDB-users] underlying storage
by Mag Gam 01 Aug '09

01 Aug '09

What is the recommended storage for MonetDB? We are planning to buy a Sun storage appliance and were wondering if it would work with MonetDB

4 7

Re: [MonetDB-users] server requires 'SHA512' hash, but client support was not compiled in
by Calum Miller 31 Jul '09

31 Jul '09

Hi, Managed to work around this issue by changing the default hashing algorithm in the configure file from SHA512 to MD. Needed a server rebuild. Calum

2 1

[MonetDB-users] server requires 'SHA512' hash, but client support was not compiled in
by Calum Miller 30 Jul '09

30 Jul '09

Hi, I built monetDb from source on Centos 5.0 but get the following error when I try to connect to the server via "mclient -lsql --time -d demo" server requires 'SHA512' hash, but client support was not compiled in I can't find a reference anywhere on how to resolve this, help much appreciated! Thanks Calum Miller

2 1

[MonetDB-users] Executing tijah query from module fails
by Roy Walter 29 Jul '09

29 Jul '09

[This is a new thread following on from "XRPC issues"] Executing a tijah query in a module from both old and new front ends fails. Using the old front end (\g) produces the following error: xquery>import module namespace test="tj" at "http://localhost:50001/export/tj.xq "; more>execute at {"localhost"} {test:tijahSearch("Heathrow")} more><> doLoopLiftedRPC: error occurred at localhost: MAPI = monetdb@localhost:50000 QUERY = import module namespace test="tj" at "http://localhost:50001/export/tj.x q"; ERROR = !ERROR: HTTP Error Code : 404 Bad Request !ERROR: SOAP Fault Code : env:Sender !ERROR: SOAP Fault Reason: !ERROR: Error occurred during execution. !ERROR: interpret: no matching MIL operator to 'tj_query_handler(bit, BA T[void,BAT], BAT[oid,BAT], BAT[void,BAT], BAT[oid,oid], BAT[oid,BAT], BAT[oid,ln g], BAT[oid,dbl], BAT[oid,str], void)'. !MAYBE YOU MEAN: ! tj_query_handler(bit, BAT[void,BAT], BAT[void,BAT], BAT[void,BAT ], BAT[oid,any], BAT[oid,BAT], BAT[void,lng], BAT[void,dbl], BAT[void,str], BAT[ void,BAT]) : BAT[void,BAT] ! ! !ERROR: CMDhttp_post: operation failed. MAPI = monetdb@localhost:50000 QUERY = import module namespace test="tj" at "http://localhost:50001/export/tj.x q"; ERROR = !ERROR: doLoopLiftedRPC: execution failed at all destinations: !ERROR: localhost: !ERROR: HTTP Error Code : 404 Bad Request !ERROR: SOAP Fault Code : env:Sender !ERROR: SOAP Fault Reason: !ERROR: Error occurred during execution. !ERROR: interpret: no matching MIL operator to 'tj_query_handler(bit, BA T[void,BAT], BAT[oid,BAT], BAT[void,BAT], BAT[oid,oid], BAT[oid,BAT], BAT[oid,ln g], BAT[oid,dbl], BAT[oid,str], void)'. !MAYBE YOU MEAN: ! tj_query_handler(bit, BAT[void,BAT], BAT[void,BAT], BAT[void,BAT ], BAT[oid,any], BAT[oid,BAT], BAT[void,lng], BAT[void,dbl], BAT[void,str], BAT[ void,BAT]) : BAT[void,BAT] ! ! !ERROR: CMDhttp_post: operation failed. !ERROR: !ERROR: xquery> Has anyone succeeded in executing a tijah query from within a module? -- Roy

3 8

[MonetDB-users] massive data loss on stop, start
by Matthew Jones 29 Jul '09

29 Jul '09

I added this information as a comment to bug *2825243* but I'm not sure if it's the same so I thought I'd post it here also to see about getting some feedback: I am experiencing widespread data corruption when restarting the May2009-SP1 MonetDB release also, but mine is much simpler. On 64-bit ubuntu: Linux dsvm01 2.6.24-23-server #1 SMP Wed Apr 1 22:14:30 UTC 2009 x86_64 GNU/Linux With a table defined as follows: CREATE TABLE "tablespace"."test_table" ( "col1" varchar(50), "col2" varchar(50), "col3" varchar(50), "col4" varchar(50), "col5" varchar(50), "col6" int, "col7" timestamp(7), "col8" int, "col9" int, "col10" int ); I imported 20998687 records into this table with all columns populated and ran a couple of queries to check the data: select count(*) from test_table ; [ 20998687 ] select count(*) from test_table where col1 = '' or col2 = '' or col3 = ''; [ 0 ] select max(col7), min(col7) from test_table ; [ 2009-07-25 14:17:15.538000, 2009-07-24 15:29:12.524000 ] During the time I loaded the data and ran these queries I did not restart the database, it had been up for about 3 days I then issued: monetdb stop db and monetdb start db and re-ran the queries: select count(*) from raw_plays ; [ 20998687 ] select count(*) from raw_plays where col1 = '' or col2 = '' or col3 = ''; [ 20852061 ] select max(col7), min(col7) from test_table ; [ 2009-07-25 14:17:15.538000, -1-01-01 00:00:00.000000 ] This is some serious data corruption, I've lost data in the vast majority of these records! 73, Matthew W. Jones (KI4ZIB) http://matburt.net

2 3

[MonetDB-users] Announcement: May2009-SP2 bug fix release
by Sjoerd Mullender 29 Jul '09

29 Jul '09

The MonetDB team at CWI/MonetDB BV is pleased to announce the May 2009-SP2 bug fix release of the MonetDB suite of programs. This is the second bug fix release for the May2009 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/May2009/#May200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

[MonetDB-users] can not delete data
by Allen Zhang 28 Jul '09

28 Jul '09

Hi, We had such a weird issue here, we are using Feb2009-SuperBall package, we managed get our data into monetdb, but some how when we try to delete data, it reappears under single user or readonly mode. Here is what we did, we put database in normal mode without gdk_single_user and gdk_readonly in monetdb5.conf. Issue a delete sql statement in mclient, now we query the data, seem the data is gone, and then we stop the database server, and start it in readonly mode. Then query the data again, the data come back!!! Seems to us the monetdb did not sync the memory with disk. Any ideas? Allen

1 0

[MonetDB-users] XRPC issues
by Roy Walter 28 Jul '09

28 Jul '09

Hi A couple of tijah related issues with query modules. 1. The following query when run from xrpc.html appears to ignore the $term argument and returns a very large result! module namespace tj = "tj"; declare function tj:tijahSearch($term as xs:string) as node()* { tijah:queryall("//speech[about(., $term)]") }; 2. Running the above query sometimes produces the following error: !ERROR: interpret: no matching MIL operator to 'tj_query_handler(bit BAT[void BAT], ... Thanks, Roy

2 10

[MonetDB-users] string coercion error
by Alex Bo. 27 Jul '09

27 Jul '09

Hi, What does the following exception mean: !SyntaxException: convertConstant: no string coercion for 'any' !SyntaxException:convertConstant:conversion error !SyntaxException efConstant:constant coercion error from void to any I can't reproduce it - I am getting this from time to time on simple queries that usually work. Thanks. -- View this message in context: http://www.nabble.com/string-coercion-error-tp24684760p24684760.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

Re: [MonetDB-users] result cache?
by Allen Zhang 27 Jul '09

27 Jul '09

Thanks Milena, Is that means monetdb does not provide final result cache? We have sql client will query same query to the database multiple times, imaging each query took 2 minutes to run, that¹s will be really painful when the client ask 10 times, since our database is readonly, so a real result cache will really help us. Regards Allen

3 6

[MonetDB-users] Segfault interpreting sql query
by Franck Routier 27 Jul '09

27 Jul '09

Hi, Monetdb is segfaulting, probably while interpreting a query or planning it. I first thought it had to do with data, but after emptying the tables, the query still makes monetdb segfault. Error in syslog says: [ 1230.812708] mserver5[5369]: segfault at 7fab4c2b5ff8 ip 00007fab4f894285 sp 00007fab4c2b6000 error 6 in lib_sql.so.2.30.2[7fab4f7e3000+116000] Following is the faulty ddl / query. Is there anything I can do to help debug this ? Franck The ddl is the following: CREATE TABLE cabact ( cabactcsu integer NOT NULL, cabact___rforefide character varying(32) NOT NULL, cabact___rteprcide character varying(32) NOT NULL, cabact___rtestdide character varying(32) NOT NULL, cabact___rfovsnide character varying(32) NOT NULL, cabactlil character varying(100) NOT NULL, cabactlic character varying(32) NOT NULL, cabacttxt character varying(1500), cabact___rfontride character varying(32), cabact_f1rfodstide character varying(32), cabact_f2rfodstide character varying(32), cabact_f3rfodstide character varying(32), cabact_f4rfodstide character varying(32), cabact_f5rfodstide character varying(32), cabact_f6rfodstide character varying(32), cabact_f7rfodstide character varying(32), cabact_f8rfodstide character varying(32), cabact_f9rfodstide character varying(32), cabact_c1rfodstide character varying(32), cabact_c2rfodstide character varying(32), cabact_c3rfodstide character varying(32), cabact_c4rfodstide character varying(32), cabact_c5rfodstide character varying(32), cabact_c6rfodstide character varying(32), cabact_c7rfodstide character varying(32), cabact_c8rfodstide character varying(32), cabact_c9rfodstide character varying(32), cabactqte double, cabact___rfountide character varying(32), cabactdtd timestamp with time zone NOT NULL, cabactdtf timestamp with time zone NOT NULL, cabactax1 integer NOT NULL DEFAULT 1, cabactax2 integer NOT NULL DEFAULT 2, cabactax3 integer NOT NULL DEFAULT 3, cabactax4 integer NOT NULL DEFAULT 4, cabactax5 integer NOT NULL DEFAULT 5, cabactax6 integer NOT NULL DEFAULT 6, cabactax7 integer NOT NULL DEFAULT 7, cabactax8 integer NOT NULL DEFAULT 8, cabactax9 integer NOT NULL DEFAULT 9 ); CREATE TABLE rfoade ( rfoade___rforefide character varying(50) NOT NULL, rfoade___rfovdeide character varying(50) NOT NULL, rfoade_i_rfodstide character varying(50) NOT NULL, rfoadeaxe integer DEFAULT 0 NOT NULL, rfoadervs integer NOT NULL, rfoadenpm integer DEFAULT 1, rfoade_s_rfodstide character varying(32) NOT NULL, rfoadegch character varying(120) DEFAULT 'AAAAA' NOT NULL, rfoadedrt character varying(120) DEFAULT 'ZZZZZ' NOT NULL, rfoadeniv integer DEFAULT 0 NOT NULL, rfoadetxt character varying(1800), rfoadenum integer DEFAULT 99999 NOT NULL, rfoadeden integer DEFAULT 999 NOT NULL, rfoadechm character varying(5500) DEFAULT 'INVALID' NOT NULL, rfoadeord integer DEFAULT 999999 NOT NULL ); And the query is: select * from cabact where cabact___rforefide = 'FHSJ' and cabact___rteprcide = 'CPTANA' and cabact___rtestdide = '100' and cabact___rfovsnide = '200805_001' and (cabact_f1rfodstide IS NULL or cabact_f1rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f2rfodstide IS NULL or cabact_f2rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f3rfodstide IS NULL or cabact_f3rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f4rfodstide IS NULL or cabact_f4rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f5rfodstide IS NULL or cabact_f5rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f6rfodstide IS NULL or cabact_f6rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f7rfodstide IS NULL or cabact_f7rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f8rfodstide IS NULL or cabact_f8rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_f9rfodstide IS NULL or cabact_f9rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c1rfodstide IS NULL or cabact_c1rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c2rfodstide IS NULL or cabact_c2rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c3rfodstide IS NULL or cabact_c3rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c4rfodstide IS NULL or cabact_c4rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c5rfodstide IS NULL or cabact_c5rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c6rfodstide IS NULL or cabact_c6rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c7rfodstide IS NULL or cabact_c7rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1')) and (cabact_c8rfodstide IS NULL or cabact_c8rfodstide IN (select rfoade_i_rfodstide from rfoade where rfoade___rfovdeide='SECA' and rfoade___rforefide = 'FHSJ' and rfoadervs='1'))

5 13

[MonetDB-users] JDBC InvalidCredentialsException
by McKennirey.Matthew 27 Jul '09

27 Jul '09

2 3

[MonetDB-users] result cache?
by Allen Zhang 24 Jul '09

24 Jul '09

Hi, Does monetDB provide result cache? How to enable it? Best Regards Allen

2 1

[MonetDB-users] Segfaulting Embedded MonetDB/SQL
by Charles Samuels 23 Jul '09

23 Jul '09

Greetings, I ran the attached program. I compiled it like so: c++ -g3 monetembedded.cpp `monetdb-sql-config --cflags --libs` `monetdb- clients-config --cflags --libs` `monetdb-config --cflags --libs` `monetdb5-config --cflags --libs` -lMapi -lembeddedsql5 -lmutils MonetDB is compiled from MonetDB-May2009-SuperBall-SP1 And it segfaults before getting out of embedded_sql(). Is my program wrong, or is there a known (or unknown) problem in MonetDB with Embedded SQL? Here're the stack traces: Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0xb169bb90 (LWP 20796)] 0xb7f039fc in sql_find_subtype () from /home/charles/.local/lib/libembeddedsql5.so.2 (gdb) bt #0 0xb7f039fc in sql_find_subtype () from /home/charles/.local/lib/libembeddedsql5.so.2 #1 0xb1c1af1e in SQLinitClient () from /usr/lib/MonetDB5/lib/lib_sql.so #2 0x09a8df68 in ?? () (gdb) info thr * 3 Thread 0xb169bb90 (LWP 20796) 0xb7f039fc in sql_find_subtype () from /home/charles/.local/lib/libembeddedsql5.so.2 1 Thread 0xb6ec78e0 (LWP 20792) 0xb7f68424 in __kernel_vsyscall () (gdb) thr 1 [Switching to thread 1 (Thread 0xb6ec78e0 (LWP 20792))]#0 0xb7f68424 in __kernel_vsyscall () (gdb) bt #0 0xb7f68424 in __kernel_vsyscall () #1 0xb7c9e48b in read () from /lib/i686/cmov/libc.so.6 #2 0xb7a24fdd in pipe_read () from /home/charles/.local/lib/libstream.so.1 #3 0xb7a238ac in stream_readSht () from /home/charles/.local/lib/libstream.so.1 #4 0xb7a23950 in bs_read () from /home/charles/.local/lib/libstream.so.1 Thanks for your help, Charles -- Charles Samuels

1 0

[MonetDB-users] put()
by Roy Walter 23 Jul '09

23 Jul '09

Hi What is the argument list for the fn:put() function? -- Roy

2 3

Re: [MonetDB-users] Database size
by Charles Samuels 23 Jul '09

23 Jul '09

On Wednesday 2009 July 22 12:25:23 pm Martin Kersten wrote: > Charles Samuels wrote: > > 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? > > MonetDB does *not* limit your database to fit in main memory. > > However, it uses main memory techniques to address the elements in > tables loaded for processing. This means that the hot-set accessed > during query execution is limited when you run on 32-bit machines. Can you elaborate a little bit? Does hot-set mean that a select on the database can only span the chunk that can fit in a 32-bit process's address space? (This seems like a perfectly reasonable limitation, I'm just making sure). > > > 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? > > The largest database we use internally to experiment with is a 6TB > astronomical database. It contains two large tables, one with 400M rows of > 500 columns and another of 20 B rows and 6 columns. Both tables are larger > then 1TB each. That's really great to hear :) > > This runs on a Linux dual quadcore with 64G Ram and lots of disk space. > Ofcourse, at some point you well notice IO behavior ;) For what kind of operations do you need that amount of memory? If you were to do a select on just a few thousand rows on that same mega-database, would it be happy with more "mundane" amounts of memory? > > > 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. > > You can use the system with both 32- and 64- bit oids. Ah, I previously had a bit of a brain failure. When reading Mapi.h, I noticed that "mapi_get_row_count" returned an int - obviously you wouldn't want select to return 2^32 rows, which is what this function counts the rows of. Thanks Martin (and also Lefteris), Charles -- Charles Samuels

2 1

[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

[MonetDB-users] Jdbc error
by Franck Routier 08 Jul '09

08 Jul '09

Hi, I am trying to populate a table using jdbc and CloverETL, but I get the following message : Caused by: java.sql.SQLException: SQLException:sql.prepare:wrong argument for prepared statement (expected decimal instead of decimal) So, Monetdb is expecting decimal, but gets ... decimal :-) What can be the problem ? Thanks, Franck

2 4

Re: [MonetDB-users] PCRE
by idf＠synctrading.com 04 Jul '09

04 Jul '09

I can't get much to work on windows version of MonetDB. It is terribly documented. On Sat Jul 4 8:39 , 'Alex Bo.' <alex.bodinner(a)gmail.com> sent: > >Hi, > >I am trying to build the new May2009 SP from CVS on Windows64, however, >building the MonetDB5 module fails because of pcre. >I built pcre successfully, and have it in 'C:\PCRE'. Then I added >'LIBPCRE=C:\PCRE' and 'PCRE_LIBS=C:\PCRE\lib\pcre.lib' to my nmake command, >but I get the following errors: > > Creating library lib_pcre.lib and object lib_pcre.exp >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_compile >referen >ced in function _pcre_compile_wrap >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_exec >referenced > in function _pcre_exec_wrap >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_study >reference >d in function _pcre_uselect >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_fullinfo >refere >nced in function _pcre_replace >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_free >referenced > in function _pcre_init >pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_malloc >referenc >ed in function _pcre_init >lib_pcre.dll : fatal error LNK1120: 6 unresolved externals > >Please help. >Thanks. > >-- >View this message in context: http://www.nabble.com/PCRE-tp24334669p24334669.html >Sent from the monetdb-users mailing list archive at Nabble.com. > > >------------------------------------------------------------------------------ >_______________________________________________ >MonetDB-users mailing list >MonetDB-users(a)lists.sourceforge.net >https://lists.sourceforge.net/lists/listinfo/monetdb-users

1 0

[MonetDB-users] PCRE
by Alex Bo. 04 Jul '09

04 Jul '09

Hi, I am trying to build the new May2009 SP from CVS on Windows64, however, building the MonetDB5 module fails because of pcre. I built pcre successfully, and have it in 'C:\PCRE'. Then I added 'LIBPCRE=C:\PCRE' and 'PCRE_LIBS=C:\PCRE\lib\pcre.lib' to my nmake command, but I get the following errors: Creating library lib_pcre.lib and object lib_pcre.exp pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_compile referen ced in function _pcre_compile_wrap pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_exec referenced in function _pcre_exec_wrap pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_study reference d in function _pcre_uselect pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_fullinfo refere nced in function _pcre_replace pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_free referenced in function _pcre_init pcre.obj : error LNK2019: unresolved external symbol __imp__pcre_malloc referenc ed in function _pcre_init lib_pcre.dll : fatal error LNK1120: 6 unresolved externals Please help. Thanks. -- View this message in context: http://www.nabble.com/PCRE-tp24334669p24334669.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] zlib dll problems in May2009 SP
by Alex Bo. 02 Jul '09

02 Jul '09

Hi, There seems to be a bug in the new service pack. Trying to compile it from CVS fails because zlib1.dll isn't found, even though it is found with the same configuration in the original May2009 release. I noticed that winrulesm.msc in the MonetDB folder doesn't handle the zlib dlls correctly for 32 bit machines. Moving the #!IFDEF HAVE_LIBZ portion of the file back to how it is the the May2009 non SP release solved the problem. -- View this message in context: http://www.nabble.com/zlib-dll-problems-in-May2009-SP-tp24307397p24307397.h… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Announcement: May2009-SP1 bug fix release
by Sjoerd Mullender 30 Jun '09

30 Jun '09

The MonetDB team at CWI/MonetDB BV is pleased to announce the May21009-SP1 bug fix release of the MonetDB suite of programs. This is a bug fix release for last month's May2009 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/May2009/#May200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

Re: [MonetDB-users] problem with MonetDB
by Stefan Manegold 29 Jun '09

29 Jun '09

Dear Christoph, I'm currently traveling, hence only a quick reply. Most probably, the reason that your haven't receive any answer to your question, yet, is that noone seems to have an answer, yet. Looking at your report, again, I do have a couple of questions and suggestions (inlined below) that might help you and us to locate the origin of your problem. On Mon, Jun 29, 2009 at 05:37:56PM +0200, Christoph Beekmans wrote: > Dear Mr. Manegold, > > i have a problem with MonetDB and i sent my problem to the user-mailing > list. > Unfortunately nobody answered on my topic and this is a few weeks ago and > i wasn't the first to mail the problem. > > So i decided to contact someone of the cwi-team directly and i hope you > are able > to help me in some way. > > Here the message i sent to the mailing list: > >> Hi everybody, >> >> I posted my issue a few weeks ago, but i got no answer, so >> i try i again. >> >> I installed MonetDB on Windows with the geom module and Which version of MonetDB are you using/talking about? >> afterwards i tried to execute a query with some geometric data types. >> But after a few changes to the query and execution i got an error. What data did you load? What kind of queries did you run? Which error did you get? >> After that i had to restart MonetDB but i got an error message that >> the system tables couldn`t be loaded. Could you quote the exact error message? >> If i start the server without geom support it starts fine but the sql >> client >> can`t connect: >> >> "Connection terminated >> Press any key to continue . . . " Does the server continue running, or does it crash in that case? If the latter, is there any error message on the server console/window? If so, which? >> If i start the server with geom support it loads the sql and geom modules >> but shuts down after that without error message. Could you try running the server on a new empty database? Does that work? If so, could you then replay the steps you did before to see, whether you can reproduce the crash and (potential) corruption of the database? >> Even a reinstall didn't fix it so the error must be somewhere else. did you remove your database before/after the reistall, or are you (still) using the same (possibly corrupoted) database? >> Has anyone an idea about that? >> >> Please help, >> Thanks. > > I would be grateful for an answer, > Christoph Hope this helps for now ... 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] String performance
by Alex Bo. 29 Jun '09

29 Jun '09

Hi, How can I know if a string field in a table has a hash table or not? I am asking this because I noticed that string fields with millions of unique values run very slowly, and from your documentation I understood that if I add too many unique strings to a column, then internally Monet will stop using a hash-table, and performance will be affected. Thanks. -- View this message in context: http://www.nabble.com/String-performance-tp24256780p24256780.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Missing JDBC-driver
by Sten Hjelmqvist 25 Jun '09

25 Jun '09

Hello, I've downloaded MonetDB5-SQL-Installer-i386-20090530.msi <http://monetdb.cwi.nl/downloads/Windows/May2009/MonetDB5-SQL-Installer-i386…> for windows. However there was no JDBC-driver in the package. After some searching on Google I found one called "jdbcclient.jar". But I still cannot connect to the database as I get a error message saying that the "demo"-database does not exist. What am I missing? Thanks in advance. --------------------------------------- Sten Hjelmqvist DataKonsult MERdata AB Sveavägen 33, 111 34 Stockholm Mobil : 0708 305616 E-mejl : sten.hjelmqvist(a)merdata.se <mailto:sten.hjelmqist@merdata.se> Webb : www.merdata.se <http://www.main.se/> ---------------------------------------

3 7

[MonetDB-users] disk usage
by dariuszs 25 Jun '09

25 Jun '09

Hi, I've loaded 60 GB of data file into a table. On Windows (NTFS) the size of dbfarm changed to 80GB but on Linux (ext4 or reiserfs) dbfarm changed to 180GB. Why is that? Thanks. Dariusz.

5 15

[MonetDB-users] connection terminated
by Christoph Beekmans 25 Jun '09

25 Jun '09

Hi everybody, I posted my issue a few weeks ago, but i got no answer, so i try i again. I installed MonetDB on Windows with the geom module and afterwards i tried to execute a query with some geometric data types. But after a few changes to the query and execution i got an error. After that i had to restart MonetDB but i got an error message that the system tables couldn`t be loaded. If i start the server without geom support it starts fine but the sql client can`t connect: "Connection terminated Press any key to continue . . . " If i start the server with geom support it loads the sql and geom modules but shuts down after that without error message. Even a reinstall didn't fix it so the error must be somewhere else. Has anyone an idea about that? Please help, Thanks.

1 0

[MonetDB-users] Deployment of MonetDB
by Martin Kersten 24 Jun '09

24 Jun '09

Dear users, In our ongoing effort to improve the MonetDB software suite we would like to hear from you. It appears that MonetDB is used for teaching, and customers use it for/within their products. Also, the divergence in primary platform is crucial for us to keep focus. We would greately appreciate if you could replay with a short message how you deploy the software and your top priorities for a next release. Thank you for you cooperation, Martin Kersten

3 6

[MonetDB-users] May2009 - can't connect on Windows64
by Alex Bo. 24 Jun '09

24 Jun '09

Hi, I installed the new May2009 release. On Windows32 I have no problem connecting through Mapi. On Windows64, however, I can't connect. I get a 'Setup connection failed' error from Mapi. (I had no problem connecting on Windows64 with the Feb09 release) Please help, Thanks. -- View this message in context: http://www.nabble.com/May2009---can%27t-connect-on-Windows64-tp24056525p240… Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

Re: [MonetDB-users] [Monetdb-checkins] load data from a file to a table
by Stefan Manegold 24 Jun '09

24 Jun '09

Hi Pedro, A) Thanks for using MonetDB. B) The users mailing list is "monetdb-users(a)lists.sourceforge.net", not "monetdb-checkins(a)lists.sourceforge.net". C) I'm afraid your report contains too little information (basically none) for us to provide any help. The least we'd (obviously) need to know is which version of MonetDB you're using on wich operating system and, in particular, what the exact problem is, i.e., the a detailed description of the symptoms and/or the error messages that you encounter. Stefan On Tue, Jun 16, 2009 at 02:54:01PM +0000, Pedro Bramao wrote: > > I'm trying to load data into a table from a file, named criminoso.sql, to a table named criminos stored in MonetDB. > I'm doing the following coomand : > > copy 2 records into criminoso from '/home/pedro/Escola/tabd/criminoso1.sql' using delimiters tuple_separator ',' record_separator ';'; > > But it's not working... i nedd some help!!!! > > thanks > > _________________________________________________________________ > Conheça os novos produtos Windows Live! Clique aqui. > http://www.windowslive.com.br > ------------------------------------------------------------------------------ > _______________________________________________ > Monetdb-checkins mailing list > Monetdb-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-checkins -- | 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

Re: [MonetDB-users] disk usage
by Jens Oehlschlägel 23 Jun '09

23 Jun '09

> > http://www.fusionio.com/Products.aspx If this SSD sits on the bus, it is not compatible with i-core7, is it?

2 1

[MonetDB-users] Schema Changes Through JDBC
by Scott Stephens 22 Jun '09

22 Jun '09

I've been seeing some weird behavior when using Eclipse SQL Explorer to execute statements that modify the schema with the MonetDB May2009 release and the monetdb-1.10-jdbc.jar file. For instance, the statement CREATE TABLE Archive.Test ( var1 int, var2 varchar(100) ); yields a pop-up error message titled "Error Executing SQL" with the text "Schema statements are directly executed and therefor cannot be debugged, explained, profiled, traced, or used in a prepare statement". I'm not a Java user and haven't tried this in Java code as opposed to through SQL Explorer, so I can't conclusively link it to the JDBC driver as opposed to the SQL Explorer code, but I seem to recall this working in previous versions of MonetDB with SQL Explorer. Thoughts anyone? Is this supposed to work? Scott

2 1

Re: [MonetDB-users] CRT and GKLoad errors
by Florian Joßbächer 16 Jun '09

16 Jun '09

Hey, this issue is still unsolved. Are there any ideas or hints? Best regards, Florian.

1 0

[MonetDB-users] Is MonetDB for me?
by Bohák András 12 Jun '09

12 Jun '09

Dear Community, I plan to bulid a database of stock market data. The database structure is very simple, there will be a main table with columns like timestamp, stockID, price, volume. The size of the database is about 4 TB. I would like to use the database to search certain patterns, like "5% stock price change in 5 minutes", so all my queries will likely touch all the data in the database. Is MonetDB for me? Is it considerably faster than an OLTP database (Mysql, Oracle)? Another question: would you use cheap SATA hard drives for this, or is it worth to invest in 15k SAS drives? Is HDD an important performance factor? Thanks in advance for all your answers, Andras Bohak

2 1

[MonetDB-users] Crash after recurring column updates
by Alex Bo. 11 Jun '09

11 Jun '09

Hi, I am working on MonetDB Feb2009SP2 compiled and run on windows64 from CVS. After Creating a 40 Million row table, I did a series of ADD COLUMN and UPDATE operations on all 40M rows. After a few update queries the server crashed. I noticed that for each query a lot of memory was allocated, but not all of it freed after the query returned, so that after each update query the total amount of memory used by MonetDB has gone up, until eventually the server crashed. (The machine is 64 bit with 8GB of RAM) I then tried to reproduce it on a smaller database with 20 Million rows. I ran this query over and over: UPDATE testtable set testtable."MonthColumn" = "month"("DateColumn"); This too eventually crashed. I attached an image of the memory consumption from task manager that was taken right after the crash. http://www.nabble.com/file/p23984181/MonetMemory.jpg Any ideas on why this happens and how I can work around it? Is it at all a memory issue like I suspected? Thanks. -- View this message in context: http://www.nabble.com/Crash-after-recurring-column-updates-tp23984181p23984… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Slow import of large XML documents
by John van Schie (DT) 11 Jun '09

11 Jun '09

Hi, Today I tried to shred a XML document of 1.6 giga bytes using mclient and it took more than 3 hours (~150 kb/s) on a quite fast machine. I was a bit suprised. because for smaller documents shredding is quite fast. Are these numbers expected? I know that it can be hard to say something about this case without the document itself. So I'm currently working to anonymize the document, but what is the prefered way to get the document to the CWI? System information: - Fedora Core 10, x86_64, 32 GB RAM (24G cached/free), 5.5 GB disk free (after import) - MonetDB4 / XQuery v4.28.4, 64bit OIDs (RPM version) Commands: $ time mclient -p 50000 -l xq -s"pf:add-doc('/tmp/structure.xml', 'structure3.xml')" real 198m49.543s user 0m0.002s sys 0m0.003s [user@host tmp]$ ls -lah /tmp/structure.xml -rw-r--r-- 1 user user 1.6G 2009-06-08 16:26 /tmp/structure.xml Regards, John van Schie

3 3

[MonetDB-users] Insert tuples with special characters especially umlauts
by Sebastian Bräuer 09 Jun '09

09 Jun '09

Hallo everybody, I would like to know whether there exist any possibilities to add tuples with umlauts as characters (ä,ö,ü) or other special characters like ß to tables. I tried to use the regular INSERT-statement as well as the COPY INTO-statement with a csv input file but without success (unexpected end of input error or missing ' to close the string). Example: CREATE TABLE Umlautnamelist(CUST_ID numeric(9,0) not null, CUST_NAME varchar(100) not null, constraint Umlautnamelist_PK primary key (CUST_ID)); INSERT INTO Umlautnamelist (123456789,'Bräuer'); Are there any chances to get it working? Thank you in advance. Sebastian

4 3

[MonetDB-users] possible bug in monetdb-install.sh
by Ying Zhang 09 Jun '09

09 Jun '09

Hi, I was using moentdb-install.sh to install the May 2009 release with the following command, which ends in an error: ================================ $ monetdb-install.sh --prefix=[pathto]/mdb_may2009 --enable-optimize --enable-xquery --22:11:36-- http://www.ins.cwi.nl/~monet/tars/latest/release/MonetDB.tar.bz2 => `/private/var/tmp/MonetDB-1244491895/MonetDB/ MonetDB.tar.bz2' Resolving www.ins.cwi.nl... 192.16.196.21 Connecting to www.ins.cwi.nl|192.16.196.21|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 882,241 (862K) [application/x-bzip2] 0K ........ ..... 100% 780.55 KB/s 22:11:37 (780.55 KB/s) - `/private/var/tmp/MonetDB-1244491895/MonetDB/ MonetDB.tar.bz2' saved [882241/882241] fetching http://www.ins.cwi.nl/~monet/tars/latest/release/MonetDB.tar {,bz2:bzip2,gz:gzip} failed! Please refer to the full log at /private/var/tmp/MonetDB-1244491895/log If you believe this is an error in the script or the software, file a bug and attach the logfile. Terminated ================================ The following code in the 'fetch()' function seems incorrect to me: for c in $LZMA $BZIP2 $GZIP ; do wget --progress=dot:mega -t 5 --passive-ftp $1.${c%:*} -P "$BUILDROOT"/$f > $QUIET ... done [[ ! -f "$BUILDROOT"/$f/$f.tar ]] && \ die "fetching $1 {$LZMA,$BZIP2,$GZIP} failed!" First, in the for-loop, e.g., MonetDB.tar.bz2 is fetched. Then, the code following the for-loop checks if ".../MonetDB.tar" exists, if not, it complains that fetching ".../MonetDB.tar.bz2" has failed, returning the error I got. Kind regards, Jennie

2 3

[MonetDB-users] connecting to mserver
by Henrik Vendelbo 08 Jun '09

08 Jun '09

I've installed mserver in a fresh Ubuntu Jaunty running in vmware, but cannot seem to connect. At first I thought it was just because I hadn't opened port 50000 in iptables, but things are a bit odd even now that I have opened the port. within Ubuntu port 50000 only seems to be open on the lo interface, while on the eth0 interface it is closed. Can this be an issue with mserver or is it just my iptables conf that is screwed?

3 2

[MonetDB-users] CRT and GKLoad errors
by Florian Joßbächer 08 Jun '09

08 Jun '09

Hi, I am trying to run the Star Schema Benchmark with Scale Factor 10 on MonetDB and get the following errors while loading the tables from a csv. The GKLoad-Errors occur just once in a while, the CRT-error is reproducible. Every time I try to run SF10, the server crashes while loading the lineorder-table (SF10) - with SF1 everything works fine. I googled this issue but only found a hint deleting a registry-key which is not existing on my machine. What can I do? Best regards, Florian. "# MonetDB server v5.10.4, based on kernel v1.28.4 # Serving database 'ssb', using 1 thread # Compiled for i686-pc-win32/64bit with 32bit 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 D:\MonetDB5\etc\monetdb5.conf) # Listening for connection requests on mapi:monetdb://CS-MONETDB:50000/ # MonetDB/SQL module v2.28.4 loaded >!ERROR: GDKload: cannot read: name=65\6560, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3440) for sys_customer_custkey !ERROR: GDKload: cannot read: name=65\6547, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3431) for sys_customer_name !ERROR: GDKload: cannot read: name=65\6553, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3435) for sys_customer_address !ERROR: GDKload: cannot read: name=65\6554, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3436) for sys_customer_city !ERROR: GDKload: cannot read: name=65\6557, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3439) for sys_customer_nation !ERROR: GDKload: cannot read: name=66\6672, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3514) for sys_customer_region !ERROR: GDKload: cannot read: name=70\7067, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3639) for sys_customer_phone !ERROR: GDKload: cannot read: name=70\7070, ext=tail, 800000 bytes missing. !OS: The operation completed successfully. !ERROR: logger: could not use bat (3640) for sys_customer_mktsegment runtime error R6031 - Attempt to initialize the CRT more than once. This indicates a bug in your application."

4 10

Re: [MonetDB-users] [Monetdb-developers] problems with starting the monetDB4xquery server
by Mtekel 07 Jun '09

07 Jun '09

Hello, I have carefully read the link you have sent me. Too bad comments are closed. I have now uninstalled my 20090424 version and installed the newest 20090530. However I still have problems running the server. The 2009 4 24 version wouldn't start even after loading all the modules by hand from the Mserver interactive commandline - nor the client or browser would connect. They would just hang, connecting forever. Now this works for the 05 30 version, but I can't use the web interface (which now works) for adding the files - I get a bug (I'm not sure If I am allowed to send attachements, so I'll include them in the next mail). Adding from commandline, mclient --language=xquery -I %1 -C WOcollection < %1 , where %1 is filename, works, but the collection name is %1 WOcollection (e.g. three different files end up in different collections instead of one). What is more interresting, if I try to run pf:add-doc("DATA_wo3.xml", "DATA_wo3.xml", "WOcollection", 1) from the client's interactive commandline, I get this error (no matter where the file really is): MAPI = monetdb@localhost:50000 QUERY = pf:add-doc("DATA_wo3.xml", "DATA_wo3.xml", "WOcollection", 1) ERROR = !ERROR: interpret: no matching MIL operator to 'ws_create(int)'. I have run procmon and created trace as described in the link, the file is attached (to the second mail). I hope this issue can be solved somehow, Best regards, Michael -----Original Message----- From: Ying Zhang [mailto:Y.Zhang@cwi.nl] Sent: 2. júna 2009 10:41 To: Mtekel Cc: monetdb-developers(a)lists.sourceforge.net Subject: Re: [Monetdb-developers] problems with starting the monetDB4xquery server Hello Michael, Thanks for using MonetDB, and sorry for the inconvenience! Your problem has been reported earlier. Unfortunately, we don't have a solution for this problem at this moment. However, there is a workaround, which seems to work according to other users. So please have a look at the following bug report (the workaround is in the report): http://sourceforge.net/tracker/index.php?func=detail&aid=2693363&group_id=56 967&atid=482468 Kind regards, Jennie On Mon, Jun 01, 2009 at 10:55:45AM +0200, Mtekel wrote: > Hello, > > I have problems starting the monetDB xquery server under windowsXP sp3. When > I click on the link in the start menu, dos window appears but then it closes > immediatelly and I can't see what's there (after pausing I can see only the > first 6 lines of the program output as described below, then after unpausing > it immediatelly quits and closes the window). If I run MXQserver.bat, or > Mserver.bat --dbinit="module(pathfinder);" I get only this: > > # MonetDB Server v4.28.4 > # based on GDK v1.28.4 > # Copyright (c) 1993-July 2008, CWI. All rights reserved. > # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. > # Compiled for i686-pc-win32/32bit with 32bit OIDs; dynamically linked. > # Visit http://monetdb.cwi.nl/ for further information. > # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah > > That's all. The server immediatelly ends. However If I run only Mserver.bat, > the server "runs" - I get the prompt, but for example I can't connect to the > web interface on the port 50000. > > Does anyone have any advice what to do? How to make it work? > Any advice appreciated, > > Best regards > > Michael > > > ---------------------------------------------------------------------------- -- > Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT > is a gathering of tech-side developers & brand creativity professionals. Meet > the minds behind Google Creative Lab, Visual Complexity, Processing, & > iPhoneDevCamp as they present alongside digital heavyweights like Barbarian > Group, R/GA, & Big Spaceship. http://p.sf.net/sfu/creativitycat-com > _______________________________________________ > Monetdb-developers mailing list > Monetdb-developers(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-developers

2 3

Re: [MonetDB-users] connection terminated
by Christoph Beekmans 05 Jun '09

05 Jun '09

> hi, > i started to have suddenly following problem with the M5Server. I can > start > the server and get the following screen which seems fine: > > # MonetDB server v5.10.2, based on kernel v1.28.2 > # Serving database 'demo', using 1 thread > # Compiled for i686-pc-win32/32bit with 32bit 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 C:\Program Files\MonetDB\MonetDB5\etc\monetdb5.conf) > # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ > # MonetDB/SQL module v2.28.2 loaded > > When i start the client I get following message and the server shuts down: > > Connection terminated > Press any key to continue . . . > > I have tried to reinstall monetDb but it does not help. can anyone > point me > into the right direction? > Thanks Hi, i have the same problem. It started after i sent a query refering to the geom module. Maybe someone has an idea? Thanks

1 0

[MonetDB-users] Announcement: New feature release of MonetDB suite
by Sjoerd Mullender 05 Jun '09

05 Jun '09

The MonetDB team at CWI/MonetDB BV are pleased to announce the new "May2009" feature release of the MonetDB suite of programs. This is a feature release, however it is is (mostly) backward compatible with the previous releases (Nov2008 and Feb2009). The two slight incompatibilities are: - The way string data is stored was changed for 64-bit architectures with 32-bit OIDs. There is however an automatic conversion on startup of the server. The biggest problem is, you can't go back to an older version once the new version has run. This incompatibility affects only the Windows 64 bit build (the other 64 bit builds use 64-bit OIDs). - The default location of the write-ahead log (WAL) has changed. This is relevant for both MonetDB5/SQL and MonetDB4/XQuery on all platforms. When the server first starts on an existing database, it will notice that the WAL is missing and will refuse to continue with a message which indicates where the WAL is now expected. You can either use a command line option to tell the server where the WAL is, or you can manually move the WAL to the new location. This latter option is done automatically on Windows when you use the Start menu to start the server. More information on the new release (and the above mentioned incompatibilities) is available at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/May2009/>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

2 1

[MonetDB-users] May 2009 Windows binaries - no Python bindings
by Pawel Lewicki 05 Jun '09

05 Jun '09

Hi, Would it be possible to get compiled Windows Python bindings? They are missing in installer and previous version is not compatible with recent server. Paweł Lewicki

2 1

Re: [MonetDB-users] [Monetdb-developers] problems with starting the monetDB4xquery server + ATTACHEMENTS
by Mtekel 04 Jun '09

04 Jun '09

This mail contains attached files I described in the previous one... Michael -----Original Message----- From: Ying Zhang [mailto:Y.Zhang@cwi.nl] Sent: 2. júna 2009 10:41 To: Mtekel Cc: monetdb-developers(a)lists.sourceforge.net Subject: Re: [Monetdb-developers] problems with starting the monetDB4xquery server Hello Michael, Thanks for using MonetDB, and sorry for the inconvenience! Your problem has been reported earlier. Unfortunately, we don't have a solution for this problem at this moment. However, there is a workaround, which seems to work according to other users. So please have a look at the following bug report (the workaround is in the report): http://sourceforge.net/tracker/index.php?func=detail&aid=2693363&group_id=56 967&atid=482468 Kind regards, Jennie On Mon, Jun 01, 2009 at 10:55:45AM +0200, Mtekel wrote: > Hello, > > I have problems starting the monetDB xquery server under windowsXP sp3. When > I click on the link in the start menu, dos window appears but then it closes > immediatelly and I can't see what's there (after pausing I can see only the > first 6 lines of the program output as described below, then after unpausing > it immediatelly quits and closes the window). If I run MXQserver.bat, or > Mserver.bat --dbinit="module(pathfinder);" I get only this: > > # MonetDB Server v4.28.4 > # based on GDK v1.28.4 > # Copyright (c) 1993-July 2008, CWI. All rights reserved. > # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. > # Compiled for i686-pc-win32/32bit with 32bit OIDs; dynamically linked. > # Visit http://monetdb.cwi.nl/ for further information. > # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah > > That's all. The server immediatelly ends. However If I run only Mserver.bat, > the server "runs" - I get the prompt, but for example I can't connect to the > web interface on the port 50000. > > Does anyone have any advice what to do? How to make it work? > Any advice appreciated, > > Best regards > > Michael > > > ---------------------------------------------------------------------------- -- > Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT > is a gathering of tech-side developers & brand creativity professionals. Meet > the minds behind Google Creative Lab, Visual Complexity, Processing, & > iPhoneDevCamp as they present alongside digital heavyweights like Barbarian > Group, R/GA, & Big Spaceship. http://p.sf.net/sfu/creativitycat-com > _______________________________________________ > Monetdb-developers mailing list > Monetdb-developers(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-developers

1 0

[MonetDB-users] Best way to lookup a varchar field?
by Henri Asseily 04 Jun '09

04 Jun '09

Hi, I've got a very simple table structure in SQL, with a non-unique varchar field and a few other fields including a CLOB. I found that it's abnormally slow to filter the varchar field with an 'IN' statement and simply return the CLOB. i.e.: select clob from x where varchar in ('a','b','c') With a couple million rows, the query is in multiple seconds if the results are around a couple thousand rows. Is there something I'm doing wrong? I should be just the varchar BAT filter, then join on the CLOB BAT. Should I manually assign the varchars to integers in another table and use ints on this table? That would be surprising... Thanks. H

2 1

[MonetDB-users] Memory Allocation errors
by Rob Wickert 04 Jun '09

04 Jun '09

Hi all, I've been testing MonetDB and I've been getting strange memory allocation errors while running large 'group by' queries. From a dataset of around 60GB, I'm running 'group by' queries against it to test for speed. Queries of the sort: select keyword, count(*) as c from raw_data group by keyword order by c desc limit 100; What's happening is that it will trundle along ok for a while, generally consuming all the available memory (98%) on 3GB box. But after a while I get these messages on the server: # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.28.4 loaded >#GDKmmap(10412883968) fails, try to free up space [memory in use=72431192,virtual memory in use=71134150656] #GDKmmap(10412883968) result [mem=71387384,vm=71134150656] #GDKvmalloc(10412883984) fails, try to free up space [memory in use=71386904,virtual memory in use=71134150656] #GDKvmalloc(10412883984) result [mem=71130480,vm=71134150656] !ERROR: HEAPalloc: Insufficient space for HEAP of 10412883968 bytes. Also while checking top it will reports a much large RES size than physical memory. E.g. (250% for %MEM). I haven't changed any out of the box settings. Is there a minimum memory size that MonetDB requires to play nice? Is there anywhere I can go to limit the amount of memory MonetDB requires? The version I'm using is: mserver5 --version MonetDB server v5.10.4 (64-bit), based on kernel v1.28.4 (64-bit oids) 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 Configured for prefix: /usr Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: mockbuild(a)surya.karan.org Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -std=c99 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 Cheers

2 3

[MonetDB-users] LIKE queries failing
by Rob Wickert 04 Jun '09

04 Jun '09

Hi all, I'm having problems with MonetDB5 while performing LIKE queries against it. Normal queries work fine, but anything with a LIKE in it gets this response: sql> select * from raw_data where keyword like 'apple%' limit 1; !MALException:batcalc.str:can not create bat 0 tuples Any ideas? This is the version of mserver5 I'm using: MonetDB server v5.10.4 (64-bit), based on kernel v1.28.4 (64-bit oids) 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 Configured for prefix: /usr Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: mockbuild(a)surya.karan.org Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -std=c99 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 Cheers

2 1

[MonetDB-users] MonetDB and NFS
by Mag Gam 04 Jun '09

04 Jun '09

Is anyone hosting their MonetDB on NFS? TIA

1 0

[MonetDB-users] May2009 branch - missing openssl/md5.h
by Alex Bo. 03 Jun '09

03 Jun '09

Hi, I am trying to build the new May2009 release from CVS on windows. Unlike in previous releases, I get this error when compiling MonetDB5: .\..\..\..\src\mal\mal_autorize.mx(97) : fatal error C1083: Cannot open include file: 'openssl/md5.h': No such file or directory There doesn't seem to be an 'openssl' directory anywhere. Any ideas? -- View this message in context: http://www.nabble.com/May2009-branch---missing-openssl-md5.h-tp23850193p238… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] 'Out of client slots' with MAPI
by Alex Bo. 03 Jun '09

03 Jun '09

Hi, I am using the MAPI programming interface to communicate with MonetDB on windows. I am using mapi_connect, mapi_query, mapi_disconnect and mapi_destroy as is shown in the examples. However, after querying for a long time, I eventually get an 'Out of client slots' exception. Moreover, it seems that ports aren't closed properly - If I run 'netstat' in the windows command prompt to see all the open ports, I see a huge list of open 50000 ports, which are used by MonetDB. These ports are marked with a 'TIME_WAIT' state, which I think means that they're inactive but not released. Could it be that I'm not using the MAPI interface properly? Or could it be a bug? I am running a compiled version of the Feb2009SP2 branch. Thanks. -- View this message in context: http://www.nabble.com/%27Out-of-client-slots%27-with-MAPI-tp23849981p238499… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] problems with starting the monetDB4 xquery server
by Mtekel 31 May '09

31 May '09

Hello, I have problems starting the monetDB xquery server under windowsXP sp3. When I click on the link in the start menu, dos window appears but then it closes immediatelly and I can't see what's there (after pausing I can see only the first 6 lines of the program output as described below, then after unpausing it immediatelly quits and closes the window). If I run MXQserver.bat, or Mserver.bat --dbinit="module(pathfinder);" I get only this: # MonetDB Server v4.28.4 # based on GDK v1.28.4 # Copyright (c) 1993-July 2008, CWI. All rights reserved. # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. # Compiled for i686-pc-win32/32bit with 32bit OIDs; dynamically linked. # Visit http://monetdb.cwi.nl/ for further information. # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah That's all. The server immediatelly ends. However If I run only Mserver.bat, the server "runs" - I get the prompt, but for example I can't connect to the web interface on the port 50000. Does anyone have any advice what to do? How to make it work? Any advice appreciated, Best regards Michael

1 0

[MonetDB-users] Python Library Problems
by Scott Stephens 31 May '09

31 May '09

I'm having problems connecting to a MonetDB 5 SQL database via the Python interface. I am working on a Mac OS X (10.5.6) system, with python 2.5.1. I downloaded and compiled the Feb 2009 SP2 release from source and added the appropriate directory to my PYTHONPATH. When running the connect command, I get some errors: >>> import MonetSQLdb >>> x = MonetSQLdb.connect(host='greensburg.local',user='scott',password='notmyrealpassword',lang='sql',dbname='HistoricalData') Traceback (most recent call last): File "<stdin>", line 1, in <module> File "MonetSQLdb/__init__.py", line 131, in __init__ self._mapi = Mapi(kwargs['host'], kwargs['port'], kwargs['user'], kwargs['password'], kwargs['lang'], kwargs['dbname']) File "MonetDB/CMapi.py", line 30, in __init__ raise IOError(self.error_str()) IOError: !InvalidCredentialsException:checkCredentials:Invalid credentials for user 'scott' I have checked and double checked that my user name and password are correct: I am able to login to the database from the same machine using SQL Explorer in Eclipse with the same credentials. The connection shows up in the merovingian log on the server as being proxied to the appropriate databse. The weird thing is, it doesn't show the connection ending until I quit() the python interactive session, or try connecting again, or do something else with the connection object (c = x.cursor() also causes a disconnect, and thows an exception that complains that x is not defined). Ideas anyone? The only thing I can think of that may be wrong is I'm not using the same releases of MonetDB for the python library and the server. mserver5 --version says I'm running MonetDB server v5.6.0 based on kernel v1.24.0 (I can't seem to remember/figure out which named release this corresponds to). Do the versions need to be the same? Thanks, Scott

3 3

[MonetDB-users] Re cycler optimizer in May2009 branch?
by Alex Bo. 31 May '09

31 May '09

Hi, Will the recycler optimizer be part of the next release? Is it enabled in the May2009 branch? Thanks. -- View this message in context: http://www.nabble.com/Recycler-optimizer-in-May2009-branch--tp23801028p2380… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] ODBC + non-ascii (RELOADED)
by Pawel Lewicki 29 May '09

29 May '09

Hi, Last year I reported problems with inserting non-ascii data using ODBC interface. I submitted a bug #2014118 and it was resolved. At least I hope so. Unfortunately I had no time to play with MonetDB and had no opportunity to use it for real data. It has recently changed so I'm back with testing. I have to report that the problem is not resolved, at least it's back in Windows 32-bit Feb2009-SP2 release. I have a problem with using queries with properly encoded utf-8 data. The same data inserted using Python bindings is perfect. I have noticed that broken "LIKE" queries give proper result on broken inserted data. It means that the same characters are being recognized. ODBC driver version is 1.28.04.01 Regards, Pawel Lewicki

2 3

[MonetDB-users] Version 5 crashing.
by Mag Gam 29 May '09

29 May '09

My monetdb has been constantly crashing in the past several days and I am not able to even get to the SQL prompt of my database. Its just hanging Some information MSG DataBase[29614]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked -bash-3.2$ /home/dbuser/MonetDB/bin/monetdb status name state uptime health last crash DataBase running 2h 34m 33s 40%, 2h 2009-05-12 16:49:50 demo stopped 100%, 4s - DataBase.1 stopped 100%, 2h - -bash-3.2$ /home/dbuser/MonetDB/bin/monetdb version MonetDB Database Server Toolkit v0.4 MSG merovingian[18807]: database 'DataBase' already running since 2009-05-12 16:52:20, up min/avg/max: 8609/8609/8609, crash average: 0.00 0.30 0.10 (5-1=3) MSG merovingian[18807]: proxying client 127.0.0.1:38462 for database 'DataBase' to mapi:monetdb://server01:50001/ MSG merovingian[18807]: client has 127.0.0.1:38462 disconnected from proxy Anything I should do to fix this? TIA

3 12

[MonetDB-users] alter table - rename?
by dariuszs 28 May '09

28 May '09

Hi, How do you rename table? Thanks. Dariusz.

1 0

Re: [MonetDB-users] Query plans
by Peter Boncz 25 May '09

25 May '09

Anthony, Suppose you installed MonetDB in the default directory, and have a directory c:\tmp with in it xquery c:\tmp\q.xq, then open a dos box and type (ignoring the prompt>): prompt> cd c:\Program Files\CWI\MonetDB4\bin prompt> set PATH=%PATH%;c:\Program Files\CWI\MonetDB4\lib\ prompt> pf c:\tmp\q.xq The plan you now see on your screen is a low-level physical binary table algebra (MIL) plan. This is what MonetDB executes in the end. The "pf" command is a standalone tool that invokes the "pathfinder" xquery compiler (www.pathfinder-xquery.org) that is integrated in MonetDB/XQuery. It provides many options (see: pf -H). Alternative things to try if you want to undertand query plans: prompt> pf -cPs15 c:\tmp\q.xq prompt> pf -lDs18 c:\tmp\q.xq prompt> pf -pDs20 c:\tmp\q.xq The first one prints the "XQuery Core" representation. At that stage, the query is still formulated in xQuery, but minimized to the subset defined in the XQuery Formal Semantics spec by W3C. The second one produces a logical relational algebra query plan, the third the physical relational algebra plan. Both are a more classical relational algebra (in the database textbook sense), these are intermediate stages the query goes through in the pathfinder. To understand these better, you would probably need to read some papers on MonetDB/XQuery and pathfinder (see the publication library). The last two commands produce output in the "dot" format, you can visualize these with dotty.exe. To do so, download and install the graphviz package (http://www.graphviz.org/Download.php) and make sure the location of "dotty.exe" is in your PATH. Then the following works: prompt> pf -pDs20 c:\tmp\q.xq > c:\tmp\ prompt> dotty c:\tmp\q.dotty (you can resize the screen, and use the menu under the right mouse buttom to zoom in) Peter PS Even though MonetDB/XQuery does not use it, you can even translate xqueries to SQL (to be executed on DB2 only, it does not work on MonetDB/SQL or other systems AFAIK): prompt> pf -Sc:\tmp\q.xq From: Anthony Gkountintas [mailto:agkount@hotmail.com] Sent: Monday, May 25, 2009 10:55 AM To: monet Subject: Query plans Hi all, Is there a possibility to view the xquery execution plans in MonetDb? I am running XQueries on windows platform. thanks Tony ________________________________________ Share your photos with Windows Live Photos – Free. Try it Now! Internal Virus Database is out of date. Checked by AVG - www.avg.com Version: 8.5.238 / Virus Database: 270.12.23/2106 - Release Date: 05/09/09 06:54:00

2 1

Re: [MonetDB-users] Query plans
by Peter Boncz 25 May '09

25 May '09

correction: ..... > Then the following works: > prompt> pf -pDs20 c:\tmp\q.xq > c:\tmp\ prompt> pf -pDs20 c:\tmp\q.xq > c:\tmp\q.dotty > prompt> dotty c:\tmp\q.dotty

1 0

[MonetDB-users] Mapi connection hangs
by Alex Bo. 24 May '09

24 May '09

Hi, I reached a certain state where there's some sort of data corruption that prevents MonetDB to start. I get this message: !ERROR: Please move away <log folder> and its corresponding dbfarm. !mvc_init: unable to create system tables. I use Mapi to connect to Monet, but the problem is that in this case I do net get back a failed mapi connection - instead the connection attempt hangs and never returns. Is this a mapi bug? If I remove the log folder Monet goes back up again. I am using v2.28.5 compiled on Windows from CVS (branch May2009). -- View this message in context: http://www.nabble.com/Mapi-connection-hangs-tp23643072p23643072.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 3

[MonetDB-users] Strange io exception
by Alex Bo. 24 May '09

24 May '09

Hi, I am running "Select * from tables" and am going over the results with Mapi commands. I tried to run this query with parallel clients, and reran the query hundreds of times. Once in a while I get this exception: !TypeException:user.s32_7[157]:'io.s3019_1' undefined in: _233:any := io.s3019_1() What does this mean? Why does this happen? I am using v2.28.5 compiled on Windows from CVS (branch May2009). -- View this message in context: http://www.nabble.com/Strange-io-exception-tp23692587p23692587.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 6

[MonetDB-users] pcre exception with LIKE inside CASE
by Alex Bo. 24 May '09

24 May '09

Hi, I can successfully run the following query: select "field1", "field2" from testlike where field2 like '%hel%' However, I cannot run this one: select "field1", "field2", (case when ("field2" like '%hel%') then cast("field1" as double) else cast("field1" as bigint) end) as dd from testlike I get this exception: !MALException:pcre_match:not available as required version of libpcre was not found by configure. (This is on Feb SP2 2009 compiled on Windows from CVS) -- View this message in context: http://www.nabble.com/pcre-exception-with-LIKE-inside-CASE-tp23694479p23694… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] time series benchmark dataset and query/analyses set
by Tom H 22 May '09

22 May '09

Hi monetDB artistes, I found an epic thread on "Tick Database Implementations" and there were lots of intelligent replies here; Subject:dataset for performance testing a time series implementation http://www.elitetrader.com/vb/showthread.php?s=&threadid=81345&perpage=6&pa… So amongst others, monetdb, kdb+ and HDF5 were mentioned, along with streamSQL and CEP/ESPER style analysis tools. However I think the proof is in the testing, so I was looking for a financial price stream orientated dataset that I could download import and run my analysis against for a metric. (obviously I would only testing performance of running retrieval annd analysis rather than storing the ticks) I had a look at the TPC site, http://www.tpc.org/ but I couldn't find anything relevant. I also found some acedemic papers, with the sort of thing that would be useful, but no datasets supplied. So I would like to suggest maybe taking a dataset like OHLC data for the S&P for the last x years, to get it up to something sizable, and then a bunch of analyses that would test the implementation. Has anyone got any suggestions on such a beast, and indeed would anyone like to compare against my monetDB system with their own HDF5 or kdb implementation? Cheers, T

1 0

[MonetDB-users] ADO .net provider & ODBC error
by Zheng Han 18 May '09

18 May '09

Hi, Is there any ADO .net provider for MonetDB? We tried to use odbc driver in C#. When fecth unicode Chinese values, some error ocurred: System.InvalidOperationException Message="Internal .Net Framework Data Provider error 30." Source="System.Data" StackTrace: at System.Data.ProviderBase.DbBuffer.Validate(Int32 offset, Int32 count) at System.Data.ProviderBase.DbBuffer.PtrToStringUni(Int32 offset, Int32 length) at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i) at System.Data.Odbc.OdbcDataReader.GetString(Int32 i) at MonetDBTest.Form1.button1_Click(_object_ sender, EventArgs e) in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Form1.cs:line 41 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.PerformClick() at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.PreProcessMessage(Message& msg) at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg) at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at MonetDBTest.Program.Main() in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(_object_ state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, _object_ state) at System.Threading.ThreadHelper.ThreadStart() InnerException: Regards, Aiyoyo

1 0

[MonetDB-users] ADO .net provider & ODBC error
by Zheng Han 18 May '09

18 May '09

Hi, Is there any ADO .net provider for MonetDB? We tried to use odbc driver in C#. When fecth unicode Chinese values, some error ocurred: System.InvalidOperationException Message="Internal .Net Framework Data Provider error 30." Source="System.Data" StackTrace: at System.Data.ProviderBase.DbBuffer.Validate(Int32 offset, Int32 count) at System.Data.ProviderBase.DbBuffer.PtrToStringUni(Int32 offset, Int32 length) at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i) at System.Data.Odbc.OdbcDataReader.GetString(Int32 i) at MonetDBTest.Form1.button1_Click(_object_ sender, EventArgs e) in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Form1.cs:line 41 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.PerformClick() at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData) at System.Windows.Forms.Control.PreProcessMessage(Message& msg) at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg) at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at MonetDBTest.Program.Main() in E:\D_Bak\~MyWorks\MonetDB\MonetDBTest\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(_object_ state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, _object_ state) at System.Threading.ThreadHelper.ThreadStart() InnerException: Regards, Zheng Han

1 0

[MonetDB-users] MAL samples
by Henri Asseily 18 May '09

18 May '09

Hi, where can I find some MAL samples? I'm trying to do very simple things such as storing and loading a bat, joining bats and iterating through them. I know I can do an explain on SQL statements, but those seem incredibly convoluted even for a simple select on a single column, which should be a straightforward sql.bind(,,,0) with follow-through sql.resultSet and sql.rsColumn if I understand this whole business correctly. Instead, it's doing a couple of algebra kunion and kdifference that I don't understand. I'm not trying to do MAL algebra on a SQL database, I'm trying to go 100% MAL and hand-coding some simple joins and iterators. I would have gone SQL all the way, but I need to iterate through a bat and calculate a result for each row, then return that sorted new bat based on the result. Doing it in SQL is overly complicated when some simple MAL statements should do the job. Thanks. --- Henri Asseily henri.tel

4 6

[MonetDB-users] Freeing memory through Mapi
by Alex Bo. 18 May '09

18 May '09

Hi, Sometimes when memory-heavy operations on MonetDB finish (I run them through Mapi), memory that is used by the MonetDB process isn't freed. Is there a Mapi command I can use too make Monet free up memory? What I do now is restart the process, but I am hoping to be able to avoid that. Thanks. -- View this message in context: http://www.nabble.com/Freeing-memory-through-Mapi-tp23595876p23595876.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] (Jdbc?) concurrency issues
by Bram Van Dam 18 May '09

18 May '09

Hi, I'm implementing a bit of a consumer-producer application in Java using the JDBC driver. When I use transactions in the producer & consumer, I'll frequently get the "ERROR: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" error. Not quite sure why this keeps happening. It's not like the same row is modified concurrently. The same *table* is (insert in one thread, vs delete in another). Could this be the cause of the problem? And more importantly, how do I get around this? thanks a bunch, - Bram

7 12

[MonetDB-users] MonetDB vs MySQL command list
by Tom H 15 May '09

15 May '09

Hi monetDB collective, I thought I would post to the list my current monetDB vs MySQL command list, as I had a bit of trouble working out how to do some routine admin stuff. I have to go and do some real work now, so I will update this later and post a more complete version. I would be grateful for any comments. Cheers, T Mysql command:CREATE DATABASE test1; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]$ monetdb create test1 warning: merovingian is not running successfully created database 'test1' Mysql command:SHOW DATABASES; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]# monetdb status warning: merovingian is not running name state uptime health last crash demo running 8m 38s 50%, 0s 2009-05-08 09:44:05 test1 stopped Mysql command:USE DATABASE test1; I don't think that you can change the database context using SQL within the client, so it would seem the way to access another database is to login to another client session with the alternative database name; shell> mclient -lsql --database=test1 sql> Mysql command:SHOW TABLES; shell> mclient -lsql --database=test1 sql>sql>select * from tables where system = false; I've not yet noted what the direct equivilents of these commands are; DESCRIBE TABLE; SHOW CREATE TABLE; GRANT ALL PRIVILEGES ON DATABASENAME.* to SOMEONE@localhost IDENTIFIED BY "PASSWORD"; mysqldump --opt -u username -p database > database_backup.sql mysql -u username -p database < database_backup.sql And I'm not entirely clear how a schema works, I'm guessing similar to the oracle model.

2 1

[MonetDB-users] rank over, sum over
by Alex Bo. 14 May '09

14 May '09

Hi, I am interested in calculating running sums, and therefor I looked for something like 'SUM OVER'. Since that doesn't exist, I looked into 'RANK OVER', and am trying to implement 'SUM OVER' according to that. I am looking at sql_rank_grp() in sql.c and rel_rankop() in rel_select.c, and am thinking how to modify them to support running sum. However, I can't figure out how to add another column to the parameters that sql_rank_grp gets (the column I want to sum over). I can't figure out how these two functions work together. Where are the incoming parameters for sql_rank_grp() created? I can see that rel_rankop() fills an sql_exp struct with the 'group by' and 'order by' columns, but I cannot find where to go from there. Any kind of guidance in the right direction would be appreciated. Thanks. -- View this message in context: http://www.nabble.com/rank-over%2C-sum-over-tp23543557p23543557.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] doc-available unuseable?
by Floris Ouwendijk (DT) 14 May '09

14 May '09

Hi, Given the following code, I'd assume to get an empty b element (for a database not containing the given document). if (doc-available('asdasda')) then doc('asdasda') else element b{} The result happens to be QUERY = if (doc-available('asdasda')) ERROR = !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: shred: cannot stat `asdasda': No such file or directory !ERROR: CMDshred_url: operation failed. !ERROR: interpret_params: leftfetchjoin(param 2): evaluation error. If I do if (doc-available('asdasda')) then element a{} else element b{} Then i get b. What is the proper way to return some default value when a document is not available in the database? Or is this the proper way and should this be reported as a bug? Kind regards, Floris

4 4

[MonetDB-users] Load times expected
by Karanbir Singh 13 May '09

13 May '09

Is this reasonable : $ time ( echo "copy 587321890 records into sample from STDIN using delimiters '\t','\n','';" ; zcat logs20090511.txt.gz ) | recode iso8859-1..utf-8 | mclient -lsql -ukb -Pkb [ 587321890 ] real 618m29.030s 618m is a bit excessive, and well over what I had expected! Background on the machine : its a dual cpu opteron 252, 6 GB ram, running centos5.3/x86_64 stock - storage is from a 2 disk raid0, local to the machine ( sata 7200 disks, md-raid ). size of this logs file, unzipped is : 56854417129 There is almost nothing else running on the machine ( load avg from pre-kickoff was ~ 0 0 0 ). An aggregate querry that I kicked off last night ( about 14 hrs back ) is still running... Are these levels of performance in line with what most people would expect ? Is there a generic tuning guide or a best-practises doc that I should be looking at about now ? As something to compare against, pgsql-8.3 on the same machine, was able to do its bulk load in under 58 min, and then took just over 10 hours to run the aggregate query ( no index's on data ) : - KB

2 1

[MonetDB-users] copy from stdin oddness
by Karanbir Singh 13 May '09

13 May '09

Hi, This is on CentOS-5/x86_64 running : [kbsingh@koala data]$ rpm -qa | grep -i Monet MonetDB-1.22.0-1.el5.kb.oid64 MonetDB5-server-5.4.0-1.el5.kb.oid64 MonetDB-SQL-2.22.0-1.el5.kb.oid64 MonetDB-client-1.22.0-1.el5.kb.oid64 MonetDB-SQL-server5-2.22.0-1.el5.kb.oid64 server started with : [root@koala ~]# mserver5 --dbinit="include sql;" on the client : [kbsingh@koala data]$ echo "\d sample" | mclient -lsql -ukb -Pkb CREATE TABLE "kb"."sample" ( "id" char(33), "sdate" char(20), "key" varchar(255), "country" char(2), "region" char(2), "city" varchar(50), "pid" int ); [kbsingh@koala data]$ echo "select 'hello';" | mclient -lsql -ukb -Pkb % . # table_name % single_value # name % char # type % 5 # length [ "hello" ] [kbsingh@koala data]$ echo "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" | mclient -lsql -ukb -Pkb [ 1000 ] also: [kbsingh@koala data]$ mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" [ 1000 ] so, works so far. However: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from STDIN using delimiters '\t','\n','';" MAPI = kb@localhost:50000 QUERY = copy 1000 records into sample from STDIN using delimiters '\t','\n',''; ERROR = !SQLException:sql:missing sep line 0 field 0 !SQLException:importTable:failed to import table given that mclient is on the right side of the pipe, the data is surely being made available on the stdin, but why does mclient fail like this ? also, adding an OFFSET makes mclient just die quietly like this: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 5 offset 1000 records into sample from STDIN using delimiters '\t','\n','';" [kbsingh@koala data]$ so, what am I getting so wrong here ? the load chain I need to setup will look like this : zcat andy1.gz | iconv -f latin1 -t utf8 | mclient ...... that compressed file is 62 GB, the changes of me being able to expand that run the load from a specific filename is zero. -- Karanbir Singh : http://www.karan.org/ : 2522219@icq

2 4

[MonetDB-users] Timestamp performance
by Alex Bo. 13 May '09

13 May '09

Hi, How fast are join operations on timestamp fields compared to joins on int/bigint fields? I thought about using timestamp.unix_epoch to store numbers instead of timestamps to speed up joins, but if timestamp joins are just as fast then that wouldn't be necessary. Thanks. -- View this message in context: http://www.nabble.com/Timestamp-performance-tp23520512p23520512.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

Re: [MonetDB-users] Version 5 crashing.
by Jens Oehlschlägel 13 May '09

13 May '09

> Yes, have to be on a network filesystem. Its pretty fast, I can get > close to 500MB/sec consistently. Also, it has over 50TB of stable > available :-) > > This issue is getting very frustrating. :-( Could it be that specific OS configuration is needed for memory-mapping network-shares? MonetDB makes heavy use of memory mapping. Just an idea Jens

1 0

[MonetDB-users] deleting sql_logs and disappearing tables
by Alex Bo. 13 May '09

13 May '09

Hi, Newly created tables disappear if I follow this sequence of actions: 1. Create a new table and insert data into it. 2. Shut down MonetDB. 3. Manually remove the sql_logs folder. 4. Start MonetDB 5. Now the newly created table is gone. What exactly is the purpose if the sql_logs folder, and when is it safe to delete it? The reason I deleted the sql_logs folder in the first place, is that a while ago I got into a corrupt DB state, and the mserver prompted me to remove the log folder. Doing so fixed the DB and it was usable again, so I assumed deleting the sql_logs folder would always be ok before starting Monet. -- View this message in context: http://www.nabble.com/deleting-sql_logs-and-disappearing-tables-tp23518145p… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] selection queries
by Mayank Maheshwari 13 May '09

13 May '09

Hi, I was trying out selection queries with varying selectivity factors on a 1 million tuple relation. The selectivity factors for predicates were 0.1%, 1% and 10% of the entries in the column and the data type of the column is int. What I'm seeing is that the execution time increases with selectivity factors going from 0.1% to 1% to 10%. I'm not sure why this is happening though, considering its a column-store system. Should this be attributed to increase in CPU time or disk I/Os or any other factor? What my understanding of column-store systems is that the execution time should remain constant for varying selectivity factors for the same column. Any help would be appreciated. Thanks Mayank Mayank Maheshwari mayank(a)cs.wisc.edu

3 2

[MonetDB-users] Odd Create Table behavior
by JLee 13 May '09

13 May '09

Hi: I must be doing something really dumb. But, I can't seem to find my way around creating a simple table in the Feb SP1 release db. I have several tables that I created couple of weeks ago as "user_test" and they have been working just fine. Today, I tried to create another table in DBVisualizer and the new table gets created with SYSTEM TABLE table_type definition. I tried logging in as monetdb as well as user_test and tried to create the table. But, the table always gets created as a SYSTEM TABLE. I thought it may be an issue with DBVisualizer. So, I used the following command to start mclient: mclient -lsql -d <dbname> -u <username> -P <password> The command returns a request for root username and password. After entering monetdb/monetdb for username/password, I get an error that says: user_test: cannot open user_test: cannot open I tried the same mclient command as monetdb and I get the same error. What am I doing wrong? Thanks! JLee

1 0

[MonetDB-users] Table Views
by Mag Gam 12 May '09

12 May '09

I have 5 tables like named this: 2009.01 2009.02 2009.03 2009.04 2009.05 Each table has a columns like this: ( id integer, ts timestamp, value char(50) ); If I would like to search from 2009.01 to 2009.04 is it possible to build a query like that? I was thinking if there is a clever VIEW I can create that would include all 5 tables as 1 table. Any thoughts or ideas? TIA

3 3

[MonetDB-users] MonetDB papers
by Mayank Maheshwari 11 May '09

11 May '09

Hi, Can someone please point me to the papers which describe the internals of MonetDB? I'm looking to find answers to issues like what are the compression techniques MonetDB uses, does it horizontally partition the tables into segments identified by segment IDs and use join indexes for joins. I have come across these ideas in C-store and I was wondering if MonetDB uses the same ideas or does something different. Thanks. Mayank Mayank Maheshwari mayank(a)cs.wisc.edu

3 2

[MonetDB-users] Database Design Question/Advice
by Mag Gam 10 May '09

10 May '09

At my university we are working on a project for financial engineering. We obtained trade data from Newyork Stock Exchange. The data looks like this: YYYYMMDD/Symbol There are about 5000 YYYYMMDD directories and 9000 Symbols The symbol is a tsv or csv file and it has 20 fields and has about 30k lines (average) Clearly, I can't place all this into 1 table (or can I? ) called Symbol. This would entail about 5000*9000*30000 = 1350000000000 entries in a table :-) I think thats crazy :-) I was wondering if its possible to partition the tables into YYYY or MM. Or maybe someone here has better ideas on howto better normalize the data. Any thoughts or ideas? TIA

1 0

[MonetDB-users] merovingian crash
by Mag Gam 10 May '09

10 May '09

While loading a lot of data, I noticed my MonetDB crashed In the log files I get this error: MSG merovingian[17779]: database 'demo' already running since 2009-05-10 14:12:21, up min/avg/max: 66/21400/101789, crash average: 0.00 0.00 0.00 (9-8=0) MSG merovingian[17779]: proxying client 127.0.0.1:51882 for database 'demo' to mapi:monetdb://srv011:50001/ MSG merovingian[17779]: starting a proxy failed: failed to connect: Connection refused ERR merovingian[17779]: client error: failed to connect: Connection refused MSG merovingian[17779]: database 'demo' already running since 2009-05-10 14:12:21, up min/avg/max: 66/21400/101789, crash average: 0.00 0.00 0.00 (9-8=0) Any ideas? TIA

3 4

[MonetDB-users] nanosleep?
by Mag Gam 10 May '09

10 May '09

I am running a query, similar to this: SELECT distinct extract(day from time), extract (month from time) from T; The query takes a long time because the table T has over 280 Million records. However I was curious to run an strace on the mserver5 process and I notced "nanosleep". Is the process sleeping or waiting? strace -p 25984 Process 25984 attached - interrupt to quit restart_syscall(<... resuming interrupted call ...>) = 0 nanosleep({5, 0}, {5, 0}) = 0 nanosleep({5, 0}, {5, 0}) = 0 nanosleep({5, 0}, {5, 0}) = 0

4 4

[MonetDB-users] Memory consumption
by Mag Gam 10 May '09

10 May '09

I have limited memory on our system 16GB of memory and our tables have more than 2 billion records. The underlying storage is extremely fast (300MB/sec read and write and rewrite), therefore I would like to depend mainly on the storage versus placing everything in memory. Is there an option with MonetDB which will let me only allocate 25% of the database into memory and rest seek disk? TIA

3 3

[MonetDB-users] Extra Tables and performance
by Mag Gam 10 May '09

10 May '09

I was wondering if I added referential integrity with a foreign keys will slow down a query. I know the loading will take longer but would MonetDB be much slower if I was doing a query? TIA

2 2

[MonetDB-users] Redundant data
by Mag Gam 09 May '09

09 May '09

Hello All, I am very new to MonetDB and I am very pleased with its performance at our university. Basically, I have data which is stored on our Unix filesystem. /research_data/life/YYYY/MM/DD/species (there are over 100 species files) I then created a very large csv file which is traverses thru this data. The file size is about 2GB. The table I created looks like this t1 ( t as timestamp species as varchar(10) weight as float color as varchar(10) ) The file species.csv looks like this weigh, color 3,red 7,green 4,blue I have a script that basically traverses thru the filesystem and creates a big csv file for example: 2008-04-01, cat,3,red 2008-04-01, cat,7,green 2008-04-01, cat,4,blue This works fine, but if I run the copy() operation again it will put redundant data. Is there anyway to avoid this?

2 2

[MonetDB-users] porting a financial time series database from mysql (how to represent datetime fields)
by Tom H 08 May '09

08 May '09

Hi monetDb collective, I am porting a test database over from a MySQL schema, and it currently looks like this; CREATE TABLE `MSFT` ( `date` datetime default NULL, `o` decimal(10,0) default NULL, `h` decimal(10,0) default NULL, `l` decimal(10,0) default NULL, `c` decimal(10,0) default NULL, `v` decimal(10,0) default NULL, `a` decimal(10,0) default NULL, `period` varchar(12) default 'daily' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1) Whats the equivalent monetDB method for representing the datetime of the start of the OHLC period? 2) As lots of queries are going to pull all the data from a particular symbol table to do things like calculate moving average, should I even be using datetime as the time index, should I use an integer value instead? Many thanks, Tom

1 1

[MonetDB-users] Trigger oddities
by Bram Van Dam 08 May '09

08 May '09

Hi, I'm experiencing some strange trigger behaviour. Not sure if I'm simply missing something, or if I've run into a bug. create table t1 (id serial, toggle boolean); create table t2 (id serial, ref bigint); insert into t1 (toggle) values (false); insert into t1 (toggle) values (false); create trigger updateMe after update on t1 referencing new row new_row for each row when new_row.toggle = true insert into t2 (ref) values (new_row.id); update t1 set toggle = true where id = 1; After this update statement, I would expect to see *1* row in t2. Instead, I see two rows: +----+-----+ | id | ref | +====+=====+ | 1 | 1 | | 2 | 2 | +----+-----+ The "when" clause seems to be ignored entirely -- I can change to "when 1 = 0" and it'll still work. If I change the trigger to create trigger updateMe after update on t1 referencing new row new_row for each row insert into t2 (ref) values (new_row.id); something equally odd happens. Instead of doing an insert for each UPDATED row, it inserts a row for each row present in t1. This seems a bit odd to me, but again, I could just be misunderstand the way this is supposed to work. Documentation on triggers is a bit sparse I'm afraid... Any help on this issue would be more than welcome! - Bram

2 2

[MonetDB-users] clarity on creating users/reseting passwords
by Tom H 08 May '09

08 May '09

Hi, I need to reset some passwords on a monetDB5 installation, and I am not clear on how to do it. Currently I can't login at all. Is there a way to enter single-user mode, or something so I can reset the monetDB user password? Also, I really don't understand the difference between running monet as mserver5 and Merovingian. Basically I want to run the server from an init.d script, which should I use? At the moment I am running the server like so; [root@localhost ~]# mserver5 --dbinit="include sql;" # MonetDB server v5.10.4, based on kernel v1.28.4 # Serving database 'demo', using 2 threads # Compiled for x86_64-redhat-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://127.0.0.1:50000/ # MonetDB/SQL module v2.28.4 loaded > Can I reset the password using MAL commands from the prompt? Thanks, Tom

2 1

[MonetDB-users] Primary-Foreign keys
by Alex Bo. 07 May '09

07 May '09

Hi, I wanted to ask what I should expect in terms of performance out of primary-foreign keys. Do join queries that have primary keys defined on their tables return faster? Are such joins materialized in the database? If not - is there any other way to control materialization of joins? Thanks. -- View this message in context: http://www.nabble.com/Primary-Foreign-keys-tp23428537p23428537.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] copy into from a file hangs
by Venu Alatzeth 06 May '09

06 May '09

Hi, I am evaluating MonetDB server v5.8.4, based on kernel v1.26.4 on FreeBSD 6.2 Loading data into a table from a file seem to hang, with mserver using high CPU. The file has only 10 records. If I use stdin instead of a file name the data can be loaded. Here is the debug output. openRead(nil) does not seem right. shouldn't it be openRead('/tmp/head.txt') ? sql>debug copy 10 records into "log_0" from '/tmp/head.txt' using delimiters '|'; mdb># mdb.start(); mdb>n mdb># user.s0_2(); mdb>n mdb># _2 := streams.openRead(nil); mdb>n What am I doing wrong? Thanks for your help, -VG

2 1

[MonetDB-users] Cannot install MonetDB with monetdb-install.sh
by Stefan Klinger 06 May '09

06 May '09

Hello, I'm having problems installing a current (--nightly=current) version of MonetDB: Running monetdb-install.sh provied at [1] displays a lot of HTTP 404 error messages. Then it successfully downloads a 0Byte MonetDB4-XQuery.tar.gz which it cannot unpack. The logfile is attached. Tested on openSUSE 10.2 and Debian GNU/Linux squeeze/sid. Just ask if you need more details. Thank you very much, Stefan ____________________ [1] http://monetdb.cwi.nl/projects/monetdb/Assets/monetdb-install.sh -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

2 1

[MonetDB-users] Query failure with 'recycle' in optimizer
by Alex Bo. 06 May '09

06 May '09

Hi, I have a query that runs without any problems when the default optimizer settings are used. But once I add 'recycle' to the existing optimizer modules, the query doesn't return. Mserver seems to be stuck in an infinite loop with 50% CPU usage. I am running Monet compiled from CVS on Windows. This is the query: select sum(t1."AddressID"), "hour"(t1."ModifiedDate") as aa from "schema1"."CustomerAddress" as t1 inner join "schema1"."CustomerAddress"as t2 on t1."CustomerID" = t2."CustomerID" group by aa This is what I get from 'select opimizer': inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,garbageCollector,dataflow,history,multiplex,recycle Thanks. -- View this message in context: http://www.nabble.com/Query-failure-with-%27recycle%27-in-optimizer-tp23389… Sent from the monetdb-users mailing list archive at Nabble.com.

5 5

[MonetDB-users] Compiling with optimizer
by Alex Bo. 30 Apr '09

30 Apr '09

Hi, I have been compiling MonetDB successfuly under Windows so far. Now I have tried compiling as I have done before, but with "optimize=yes". Now when I start the server, I get this message: !FATAL: BBPinit: BBP.dir is empty What does this mean? Am I doing something wrong? Thanks. -- View this message in context: http://www.nabble.com/Compiling-with-optimizer-tp23022199p23022199.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 8

[MonetDB-users] TPC-H for MonetDB
by mayank＠cs.wisc.edu 30 Apr '09

30 Apr '09

Hi, I'm trying to run TPC-H benchmark on MonetDB. I downloaded the package from the TPC-H site but it needs some configuration for it to be able to generate DBGEN and QGEN. I was wondering if anyone can advise me what needs to be changed in makefile, tpcd.h and for 64-bit support. I'm running this on a Dell Intel 32-bit architecture. Thanks. Mayank

3 2

[MonetDB-users] connection terminated
by djdjoko 27 Apr '09

27 Apr '09

hi, i started to have suddenly following problem with the M5Server. I can start the server and get the following screen which seems fine: # MonetDB server v5.10.2, based on kernel v1.28.2 # Serving database 'demo', using 1 thread # Compiled for i686-pc-win32/32bit with 32bit 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 C:\Program Files\MonetDB\MonetDB5\etc\monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.28.2 loaded When i start the client I get following message and the server shuts down: Connection terminated Press any key to continue . . . I have tried to reinstall monetDb but it does not help. can anyone point me into the right direction? Thanks -- View this message in context: http://www.nabble.com/connection-terminated-tp23260811p23260811.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Announcement: New bug fix release of MonetDB suite
by Sjoerd Mullender 27 Apr '09

27 Apr '09

The MonetDB team at CWI is pleased to announce the Feb2009-SP2 bug fix release of the MonetDB suite of programs. This is the second bug fix release of the Feb2009 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Feb2009/#Feb200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

[MonetDB-users] Vista M5Server Crashes
by gsd 24 Apr '09

24 Apr '09

Hi all, My monetdb server (MonetDB server v5.10.2, based on kernel v1.28.2) crashes on me in a reproducible fashion. I'm generally (but not always) accessing it via JDBC. I have 100 tables, each containing just less than 4 million records. Crash 1: - I'm testing retrieval speeds. When I hit table 17 or 18 the server crashes. Crash 2: - I'm testing insertion speeds. Again when I hit table 17 or 17 the server crashes. I'm only inserting about 1000 records per table. I've tried it via "COPY INTO" and via batch INSERT statments. Crash 3: - Using the Mclient.exe application I manually selected 5000 rows from each table. When I hit the 17th table the server crashed again. I accessed the tables in a different order to the above programmatic crashes to try and eliminate this being due to a particular table. Also, this crash also eliminates this being a JDBC-related issue. So, I'm wondering if this may be a known windows issue, or perhaps a Vista issue? Could it be due to some server internal limit being hit as it loads and caches tables? Are there any server logs I can examine? Any ideas? Thanks, Scott -- View this message in context: http://www.nabble.com/Vista-M5Server-Crashes-tp23218455p23218455.html Sent from the monetdb-users mailing list archive at Nabble.com.

4 3

[MonetDB-users] Windows JDBC Issues
by gsd 24 Apr '09

24 Apr '09

Hello (again!), I have MonetDB running on a 32-bit Windows Vista box. When I start the server it says it's "MonetDB server v5.10.2, based on kernel v1.28.2". I'm using JDBC batch updates to populate about 100 tables with approximately 4 million records per table. I've a couple of issues: 1) My batch updates fail from time-to-time. On average this might be every 10,000 batch updates with each batch containing 100 insertions. I get an exception with the following message: "Error(s) occurred while executing the batch, see next SQLExceptions for details" If I close the connection then open another one I can continue quite happily. If I try to reuse the existing connection with a cleared batch I get the same error every time until I do close the connection. I think I may not be the only person with this issue: http://wiki.pentaho.com/display/EAI/MonetDB http://wiki.pentaho.com/display/EAI/MonetDB 2) After a very large amount of insertions (more like 100+ million records, across numerous tables) the insertion simply hangs (a commit usually takes about a second and I've left it 30+ minutes). After killing the client the server becomes impossible to connect to. Even after a reboot I get the following messages: SERVER: >!ERROR: Incompatible database version 000000, this server supports version 050000 !ERROR: Please move away c:\data\monetdbdata\MonetDB5\sql_logs\demo\sql\ and its corresponding dbfarm. !mvc_init: unable to create system tables CLIENT: !SQLException:SQLinit:Catalogue initialization failed JDBC CLIENT: Connection reset (Mserver still alive?) If, however, I delete/move the sql_logs directory then restart things then I can connect and my data is still there. The last time I did this one of the log files (log.97) was ~250mb in size. Any help, advice or comments would be much appreciated. I'm going to spend this afternoon trying out the bulk COPY INTO functionality. Thanks, Scott -- View this message in context: http://www.nabble.com/Windows-JDBC-Issues-tp23196456p23196456.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

Re: [MonetDB-users] Tweaking performance
by Garrett Smith 23 Apr '09

23 Apr '09

----- "Martin Kersten" <Martin.Kersten(a)cwi.nl> wrote: > Garrett Smith wrote: >> Does anyone have some guidance on approaches for optimizing database >> performance? >> >> I realize this is a pretty broad question :) >> >> More specifically, I'm running a simple query >> >> select max(date) from metric >> >> on a table with 21 million rows (or whatever you call them in >> Monet). >> It's taking quite a bit longer than I'd expect, given a typically >> indexed column. > Given the fact that there is no (b-tree) index, the underlying > engine has to perform a scan to find the maximum. > This entails a linear scan over 21M elements in the current > implementation. Thanks! After going back and reading the docs on MonetDB, I realized my question was pretty naive. The SQL interface triggered a Pavlovian response to think in terms of a relational database with b-tree indexes. It's pretty amazing how fast that linear scan is. Still, my application calls for something else. I took a look at FastBit, which is insanely adept at selects into huge data sets, but requires a relatively expensive index update to get that performance. As I have routine updates to deal with, that doesn't seem a good fit. We'll probably stick with MySQL+MyISAM for this particular component. Garrett

1 0

[MonetDB-users] COPY INTO with dates
by gsd 23 Apr '09

23 Apr '09

Hello, I'm looking into the COPY INTO bulk insert functionality but was wondering how I should format DATEs and TIMESTAMPs? Thanks, Scott -- View this message in context: http://www.nabble.com/COPY-INTO-with-dates-tp23194496p23194496.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

Re: [MonetDB-users] Tweaking performance
by Martin Kersten 23 Apr '09

23 Apr '09

Garrett Smith wrote: > 123456789012345678901234567890123456789012345678901234567890123456789012 > > Does anyone have some guidance on approaches for optimizing database > performance? > > I realize this is a pretty broad question :) > > More specifically, I'm running a simple query > > select max(date) from metric > > on a table with 21 million rows (or whatever you call them in Monet). > It's taking quite a bit longer than I'd expect, given a typically > indexed column. Given the fact that there is no (b-tree) index, the underlying engine has to perform a scan to find the maximum. This entails a linear scan over 21M elements in the current implementation. > > On an "explain" I see that there's no difference between the plan with > or without the presence of an index on 'date'. Per the SQL index docs, I > see that indexes are more of a hint to Monet and they're often ignored. > > Cool. > > But what happens when I want to fix what seems to be a configuration > problem with my database/schema? > > Thanks! > > Garrett > > ------------------------------------------------------------------------------ > Stay on top of everything new and different, both inside and > around Java (TM) technology - register by April 22, and save > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > 300 plus technical and hands-on sessions. Register today. > Use priority code J9JMT32. http://p.sf.net/sfu/p > _______________________________________________ > MonetDB-users mailing list > MonetDB-users(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users

1 0

[MonetDB-users] Tweaking performance
by Garrett Smith 22 Apr '09

22 Apr '09

123456789012345678901234567890123456789012345678901234567890123456789012 Does anyone have some guidance on approaches for optimizing database performance? I realize this is a pretty broad question :) More specifically, I'm running a simple query select max(date) from metric on a table with 21 million rows (or whatever you call them in Monet). It's taking quite a bit longer than I'd expect, given a typically indexed column. On an "explain" I see that there's no difference between the plan with or without the presence of an index on 'date'. Per the SQL index docs, I see that indexes are more of a hint to Monet and they're often ignored. Cool. But what happens when I want to fix what seems to be a configuration problem with my database/schema? Thanks! Garrett

1 0

Re: [MonetDB-users] the x100 project
by Peter Boncz 21 Apr '09

21 Apr '09

Hi Tom, Thanks for your interest in X100. The project is very much alive in a recent CWI spin-off company called vectorwise. X100 is currently being developed into a fully functional database engine that will fly in datawarehousing and BI environments. Much more than that I cannot say about it now. More news will follow, certainly within a year. It is not yet open source (but in a future that is not off the cards), but if you need that now, you could try MonetDB. Peter > Hello, > I have been asked to evaluate MonetDB. We have interest in the x100 project, is this project still on going... is there a download for it? If > not, is there a plan to release it? > > thanks > tom

1 0

[MonetDB-users] monetdb-client ubuntu package missing binary ?
by Franck Routier 18 Apr '09

18 Apr '09

Hi, I have tried to install and test monetdb using ubuntu repository. But when it comes to using mclient, no binary seems to be installed. Indeed, looking at the package, there seems to be only docs in it. So, am I looking at the wrong place and missing some obvious point, or is the package broken ? (notice that the debian package seems to be similar) Thanks, Franck

2 4

[MonetDB-users] the x100 project
by tom edgar 17 Apr '09

17 Apr '09

Hello, I have been asked to evaluate MonetDB. We have interest in the x100 project, is this project still on going... is there a download for it? If not, is there a plan to release it? thanks tom

1 0

[MonetDB-users] loading big table
by dariuszs 16 Apr '09

16 Apr '09

Hi, I've got a table with 130 fields. When I load 130 million records into that table (about 50G) - it's not a problem, but when I try to load 160 million records (about 60G of data) into it this thing just sits for hours with no error messages. Can somebody help me? Thanks. Dariusz.

5 16

[MonetDB-users] MonetDB XQuery in Production
by Max Fedorov 14 Apr '09

14 Apr '09

The product looks very promising on paper, although not that many people are talking about it and I came across MonetDB by accident. Is anyone using MonetDB in production? Is the product stable enough? I do not feel like spending lots of time testing this XQuery server if nobody runs it in production. I loaded several million records and tested sample queries without issues for the most part, but then once the server just vanished (running latest 32bit version on WindowsXP). I restarted and everything is working again, but now I am very scared. Nothing like this ever happened when using MySQL, MS SQL Server or Oracle. Nothing ever just dies without a trace. Thank you. -Max

4 5

[MonetDB-users] timer option in monetdb
by Mayank Maheshwari 14 Apr '09

14 Apr '09

Hi, I'm working on a project comparing the performance of column-store database systems using MonetDB with the common row-stores for different query scenarios. While running the --timer option on monetDB, I was wondering if it includes the time for printing the result and any other I/O time as well. I'm trying to get the break-up of where the system spends its time while processing the query. Is there a way I could find that out? Also is it possible to find out whats the compression scheme the database is using to store the data? I'm storing a 1 million row table with 6 attributes. Thanks. Mayank Maheshwari mayank(a)cs.wisc.edu

1 0

[MonetDB-users] Fast Loading
by Alex Bo. 14 Apr '09

14 Apr '09

Hi, I came across chapter 3.2.8 in the documentation regarding 'Fast Loading'. I am trying to use it in order to improve the performance I get compared to loading tables using 'COPY INTO'. However, I am not sure how to approach this: "Each file is produced by a program that writes the binary image of the BAT directly, i.e. a binary dump of an C-array" How exactly can I produce these binary images from my source columns? How exactly are they expected by MonetDB to look like? A little more info could be very helpful. Thanks. -- View this message in context: http://www.nabble.com/Fast-Loading-tp23009932p23009932.html Sent from the monetdb-users mailing list archive at Nabble.com.

5 4

[MonetDB-users] mysql vs. monetdb
by Yue Sheng 08 Apr '09

08 Apr '09

Hi, we installed MonetDB on a Linux box, and did some comparison between MySQL and MonetDB. The result seems off by a large magnitude. Has anyone encountered similar problem please? Thanks. For MonetDB: ql>select count(ins_key) from bigtable where ins_key = 15012; +---------+ | L1 | +=========+ | 1931541 | +---------+ 1 tuple Timer 260771.478 msec 1 rows For MySQL: select count(ins_key) from bigtable where ins_key=15012; +----------------+ | count(ins_key) | +----------------+ | 1931548 | +----------------+ real 0m0.989s user 0m0.002s sys 0m0.003s Indices: MonetDB: None MySQL: index on ins_key Thanks.

3 4

[MonetDB-users] Admin GUI not working on Vista 64
by Max Fedorov 08 Apr '09

08 Apr '09

Installed 64 bit version of MoneyDB on Vista. Server and client are working fine, but Admin GUI is coming up with 404. Thanks. -Max

2 4

[MonetDB-users] MonetDB Server logs and location on Windows
by Max Fedorov 07 Apr '09

07 Apr '09

Hello. I have MonetDB installed on Windows XP. The server crashed several times for me during shredding of several files (1mill records each). I am running this Window XP instance in VM, so maybe disk space or memory was an issue. 1. Where are the logs? 2. Also, what is the physical location of the database itself? 3. What is the best strategy to prevent data loss if server crashes. Do I need to backup files or backup through Admin GUI? Thanks a lot!

2 1

[MonetDB-users] XQuery Suggestions
by Max Fedorov 07 Apr '09

07 Apr '09

Hello, please advise why these 2 queries do not work. Samples would be nice. module namespace foo = "xrpc-test-function"; declare function foo:countQueueStats($docName as xs:string) as xs:integer { count(doc($docName)/qflex:queueStats/qflex:queueStat/qflex:queueName) }; declare function foo:addQueueStat($docName as xs:string, $myElement as element()) { do insert $myElement as last into doc($docName)/queueStats }; Thank you!

4 7

[MonetDB-users] Mserver error
by Stefan Klinger 02 Apr '09

02 Apr '09

Hi. I just updated to the 'current' branch via the provided 'monetdb-install.sh'. Now I get an error message when starting Mserver: >>>>> sk@phobos18:~> Mserver # MonetDB Server v4.29.0 # based on GDK v1.29.0 # Copyright (c) 1993-July 2008, CWI. All rights reserved. # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. # Compiled for i686-pc-linux-gnu/32bit with 32bit OIDs; dynamically linked. # Visit http://monetdb.cwi.nl/ for further information. !ERROR: CMDfind: value not found. !ERROR: CMDfind: operation failed. !ERROR: interpret_params: int(param 1): evaluation error. MonetDB> <<<<< Even worse, I cannot use Pathfinder any more: >>>>> sk@phobos18:~> Mserver --dbinit='module(pathfinder);mil_start();' # MonetDB Server v4.29.0 # based on GDK v1.29.0 # Copyright (c) 1993-July 2008, CWI. All rights reserved. # Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved. # Compiled for i686-pc-linux-gnu/32bit with 32bit OIDs; dynamically linked. # Visit http://monetdb.cwi.nl/ for further information. !ERROR: CMDfind: value not found. !ERROR: CMDfind: operation failed. !ERROR: interpret_params: int(param 1): evaluation error. # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah !ERROR: interpret: unknown variable 'REMAP_PAGE_SIZE'. !ERROR: interpret_params: BAT(param 3): evaluation error. !ERROR: interpret_params: rename(param 1): evaluation error. MonetDB> <<<<< Any help wouldbe appreciated. Kind regards, Stefan -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

2 1

[MonetDB-users] MonetDB SQL 5.10.3 Unable to restore dump from previous version
by McKennirey.Matthew 31 Mar '09

31 Mar '09

In our test environment we frequently dump MonetDB databases using the mclient -D command and restore using mclient passing in the sql file created by the dump. We decided to try out the latest release (5.10.3 , February2009-SP1) We installed MonetDB from the monetdb-install script using our customary --enable-sql --nightly==stable settings. Installation appeared to complete normally. Our dump was from MonetDB server v5.4.1 (64-bit), based on kernel v1.22.1 (64-bit oids) Copyright (c) 1993-2007 CWI, all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: /monetdb Libraries: libpcre: 7.2 2007-06-19 (compiled with 7.2) openssl: OpenSSL 0.9.8e 23 Feb 2007 (compiled with OpenSSL 0.9.8e 23 Feb 2007) Compilation: gcc -g -O2 -std=c99 -fgnu89-inline Linking : ld -IPA -m elf_x86_64 We attempted to load into MonetDB server v5.10.3 (64-bit), based on kernel v1.28.3 (64-bit oids) 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 Configured for prefix: /monetdb Libraries: libpcre: 7.2 2007-06-19 (compiled with 7.2) openssl: OpenSSL 0.9.8e 23 Feb 2007 (compiled with OpenSSL 0.9.8e 23 Feb 2007) Compilation: gcc -O2 -std=c99 -fgnu89-inline Linking : ld -IPA -m elf_x86_64 We create our databases by > merovingnian > monetdb create DATABASE > monetdb start DATABASE The SQL error we get when attempting the load is ERROR = !SQLException:sql:missing sep line 0 field 0 !SQLException:importTable:failed to import table There are no errors in the merovingian log. We have tried 3 installs on 2 different machines - each install succeeds, but we have never been able to restore from the dump from 5.4.1 Our tables are 'wide and sparse', that is many columns will contain NULL for many OIDs We have no trouble creating databases on 5.10.3 and populating them with SQL files made up of INSERT statements. We tried doing a more manual load of test data (INSERT), then doing a dump (from 5.10.3), and then doing a restore (to 5.10.3) from that dump and that works.

2 1

[MonetDB-users] server crush
by dariuszs 30 Mar '09

30 Mar '09

Hi, I was selecting 1 record from a table with 100 fields and the server crashed following message: MAPI=monetdb@localhost:50000; ACTION=read_line QUERY=select * from table1; ERROR=connection terminated Press any key to continue... What does it mean? Thanks. Dariusz.

2 3

Re: [MonetDB-users] huge files and xrpc
by Ying Zhang 29 Mar '09

29 Mar '09

Hello Francois, Since the error you get also happens without XRPC, it's very possible that there is bug in the query execution, so I have CC-ed this e-mail to the monetdb-users mailing list, sothat more people could look at this problem. my answers / further questions are inlined below. On Mar 27, 2009, at 16:07 , francois guérin wrote: > Le 26 mars 2009 11:36, Ying Zhang <Y.Zhang(a)cwi.nl> a écrit : >> Hello Francois, >> >> I need some more information to be able to tell what's wrong here. >> >> - which version of monetdb/xquery are you using? how did you >> install it >> (from CVS, using the supper tarball, windows installer, RPM package)? > > the 18th mars nightly built version, with a super tarball > >> - on which OS? CPU? RAM? > > 64 bit Linux Kernel 2.6.24 Distrib Mandriva > Cpu Intel(R) Pentium(R) 4 CPU 3.40GHz > 2Go RAM > >> - how did you insert you document? using pf:add-doc() via e.g. >> mclient or >> by making an xrpc call? > > i used pf:add-doc() > >> - have you checked that the insertion of your document has >> succeeded or not? > > it has. the document appears when i call the function pf:documents() > (but i cant view the whole file, cause it's too big) It should be sufficient. To be more sure, maybe try something like count(doc("yourdoc.xml")//*), but this could take quite some time with your document :) > >> - have you inserted the document as a read-only document or updatable >> document? > > as an updatable document > >> - what is the exact xrpc request you have sent? > > get_collocations_PASSAGE("wikipedia","section") > >> - which function are you trying to call (please send me the xquery >> code)? > > declare function foo:get_collocations_PASSAGE($corpus as > xs:string,$word as xs:string){ > for $grp in doc($corpus)/DOCUMENT/Sentence/G > for $w in $grp/W/@form > return if(fn:contains($w,$word)) > then foo:groupe_text_PASSAGE($grp) > else () > }; Would you please also send us the code of the function foo:groupe_text_PASSAGE() (and the code of the other user-defined-functions called by this function, if any)? > > (that code worked with a corpus much more little than the one called > "wikipedia") What is the size of your smaller document? Would it be possible for you to investigate what is the (size of the) largest document on which your query still run? >> - can you execute the same function without xrpc? > > with an .xq file read by "mclient -lx", i got the same error. > > my .xq file: > for $grp in doc("wikipedia")/DOCUMENT/Sentence/G > for $w in $grp/W/@form > return if(fn:contains($w,"section")) > then $grp > else () So the error seems unrelated with the function foo:groupe_text_PASSAGE(). Have you executed any updating function before you execute this function/code? Since the functions you mentioned in your e-mail are all read-only functions. Could you please also try to add your (4.6GB) document as a read-only document, and execute the functions with/without XRPC to see what would happen? If your functions execute without any error on a read-only document, it's most likely that the problem is in the update related code (updatable documents are stored differently than read-only documents). To reproduce and debug your problem, we would need your document. Would it be possible to put the (zipped) document in a place we could download? > - after a document has been stored in the database, is the url (the > one appearing in pf:documents()) useful? or may i change my files name > in my computer? or even delete them once they've been stored? You can rename/remove the original documents, once they have been added to the DB. As far as I know, the URLs displayed by pf:documents() are merely adminstrative info. However, you might want to keep your original document as a backup. Kind regards, Jennie > > > Thanks, > > francois. > > >> Kind regards, >> >> Jennie >> >> On Mar 25, 2009, at 11:54 , francois guérin wrote: >> >>> Hello, >>> >>> (it's francois again) >>> >>> I've inserted a huge file (4,8G) in MonetDB xml database - all the >>> process did well - , but when i send an xrpc request, i always get >>> that error message: >>> (and i dont what that means :s ) >>> >>> <?xml version="1.0" encoding="utf-8"?> >>> <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"> >>> <env:Body> >>> <env:Fault> >>> <env:Code> >>> <env:Value>env:Sender</env:Value> >>> </env:Code> >>> <env:Reason> >>> <env:Text xml:lang="en">Error occurred during execution. >>> !ERROR: [remap]: 2 times inserted nil due to errors at tuples 3@0, >>> 5@0. >>> !ERROR: [remap]: first error was: >>> !ERROR: CMDremap: operation failed. >>> !ERROR: interpret_unpin: [remap] bat=559,stamp=-664 OVERWRITTEN >>> !ERROR: BBPdecref: 1000000006_rid_nid does not have pointer fixes. >>> !ERROR: interpret_params: leftfetchjoin(param 2): evaluation error. >>> >>> </env:Text> >>> </env:Reason> >>> </env:Fault> >>> </env:Body> >>> </env:Envelope> >>> >>> Where does it come from? >>> Perhaps, should i make several little files instead of a big one >>> (even >>> if the documentation says to prefer one big to several little)... >>> My app is to store huge xml datas (either in a big one file, or >>> several little ones) and to have a fast storage and request timing. >>> What would you advise me? >>> >>> Regards, >>> >>> >>> -- >>> francois. >> >> > > > > -- > francois.

1 0

Re: [MonetDB-users] 64bit MonetDB, JDBC Insert via RJDBC, >300 million rows
by Niels Nes 26 Mar '09

26 Mar '09

On Thu, Mar 26, 2009 at 09:13:57AM -0700, Yue Sheng wrote: > (A) For thread 4: are you sure that it is waiting for anything, > here is the iostat output, each observation one second apart: msync is called. Which is supposed to write your dirty pages to disk. This needs some investigation but would surely require a MacOS system. > > disk0 cpu load average > > KB/t tps MB/s us sy id 1m 5m 15m > > 0.00 0 0.00 0 0 100 0.04 0.05 0.00 > > So there is no cpu nor disk load. As for threads 5 and 6, one > would think that the argument '--set gdk_nr_threads=1' > constrains the number of clients that can connect. Even if that > assumption is incorrect, right now there are no clients > connected. During the lifetime of the server process though, > there have been three possible client candidates: The number of threads is for processing, ie queries will use up to the specified number of threads to compute the query. Still each connected client will use its own thread. > > - The server itself. We run the server with "bin/mserver5 > --dbfarmUsers/howardshek/Onur-tmp/dbfarm --dbname=evatick_new > --dbinit="include sql;" --set gdk_nr_threads=1 --daemon=yes ". > So I am not sure if it qualifies as a client as well. Yes its a client but not on the sql level, so doesn't take up the lock we were worried about. > > - Command line client ran by: "bin/mclient -d evatick_new -l > sql". This runs a single sql command: "drop table Japan2008;" > at the very beginning before the data load, and then I exit the > client via Ctrl-D. Should have finised ie would be strange if it still hangs on the lock. > > - The R code using JDBC. At the moment, that process is killed > too, so mserver5 might be waiting for some lock/semaphore/mutex > to finally switch to the thread relevant to that particular > client and do the cleanup. Both the killed clients may be the cause but it would need a mac box etc > > (B) My reply is getting long, and keeps getting rejected by the > user list.... Sorry about that, but I guess we cannot fix sf. Niels

4 6

[MonetDB-users] Announcement: New bug fix release of MonetDB suite
by Sjoerd Mullender 26 Mar '09

26 Mar '09

The MonetDB team at CWI is pleased to announce the Feb2009-SP1 bug fix release of the MonetDB suite of programs. This is a bug fix release of last month's Feb2009 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Feb2009/#Feb200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

Re: [MonetDB-users] monetdb output mode -f
by Maria 26 Mar '09

26 Mar '09

Thank you for quick reply. So, you mean the '-f none' option is not working?? and, when testing monetDB performance with tpc-h like benchmark, the performance is tested using 'cat file.sql |mclient -lsql -t -f raw |grep Timer' ?? Thanks so much. Maria Re: [MonetDB-users] monetdb output mode -f From: Martin Kersten <Martin.Kersten@cw...> - 2009-03-25 19:58 Maria wrote: > Hi, I would like to run query with out printing output in order to > monitor its performance. > I tried to use '-f none' in command-line, but it didn't work for > monetdb5. (I am not using xquery here) The way to avoid the timing in output rendering then you might consider replacing your queries by count(*) queries. In all other cases, the data is sent from server to mclient for rendering. If you use the mode 'raw' this amounts to a plain dump without further rendering concerns. The following might be what you want: cat file.sql |mclient -lsql -t -f raw |grep Timer > > > ./mclient -lsql -uxman -ddemo -t -f none 1.sql > > ===> then, it prints all the results on the screen and display time. > > I want it only display time, not the query results. > I also tried to redirect using /dev/null. In this case, it didn't > display time. > > Isn't the '-f none' working in monetdb5?? If so, how can I measure the > query time ? > > Thanks in advance. > Maria.

2 1

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

26 Mar '09

Now have tried the same bulk load with COPY on the latest (v5.10.0) with single thread setting. Failed again! System: OS X MonetDB complied in 64bit number of files to load: 322 total size of files: 15GB Max row in file: 3million On Wed, Mar 18, 2009 at 11:51 AM, Martin Kersten <Martin.Kersten(a)cwi.nl>wrote: > Yue Sheng wrote: > >> I'm not sure how "The (parallel) load used scratch area as well" is >> related to the question. >> > If you look at the code, you will notice that there is a two phase > loading process involving (possibly) multiple threads > >> >> Sorry if I'm a bit slow. >> >> On Wed, Mar 18, 2009 at 11:25 AM, Martin Kersten <Martin.Kersten(a)cwi.nl<mailto: >> Martin.Kersten(a)cwi.nl>> wrote: >> >> Yue Sheng wrote: >> >> Sorry, if I wasn't clear on the first question: >> >> (1) we ramp up N for the first insert to claim sufficient space. >> Sure, understand that one. >> >> But: >> >> The claimed space got "given back" *right after* the first >> insert. (this is the part I don't understand.) >> >> The (parallel) load used scratch area as well >> >> Question: how does the second, third, .... inserts get the >> "benefit" of the ramp up that we did for the first insert? >> >> Is this a bit clearer what my question pertains? >> >> Thanks. >> >> >> On Wed, Mar 18, 2009 at 10:26 AM, Martin Kersten >> <Martin.Kersten(a)cwi.nl <mailto:Martin.Kersten@cwi.nl> >> <mailto:Martin.Kersten@cwi.nl <mailto:Martin.Kersten@cwi.nl>>> >> wrote: >> >> Yue Sheng wrote: >> >> Three questions that bothers me are: >> (1) why we need to ramp up N to total of all line in >> first insert. >> >> to let the kernel claim sufficient space >> >> Reason I ask is that right after first insert, the >> allocation >> drop right down from, say 100GB to 35GB, and stays >> roughly there >> for *all* subsequent inserts. I totally do not understand >> this. >> (2) in your opinion, based on this experience, what could >> be the >> potential problem here? >> >> little to none, as the files are memory mapped, which only >> may cause >> io on some systems >> >> (3) in your opinion, would the newer version cure the >> problem? >> >> a system can never correctly guess what will come, >> especially since the source of a COPY command need not be a file >> but standard input, i.e. a stream. >> >> >> Thanks. >> >> >> On Tue, Mar 17, 2009 at 10:51 PM, Martin Kersten >> <Martin.Kersten(a)cwi.nl <mailto:Martin.Kersten@cwi.nl> >> <mailto:Martin.Kersten@cwi.nl <mailto:Martin.Kersten@cwi.nl>> >> <mailto:Martin.Kersten@cwi.nl >> <mailto:Martin.Kersten@cwi.nl> <mailto:Martin.Kersten@cwi.nl >> <mailto:Martin.Kersten@cwi.nl>>>> >> >> wrote: >> >> Yue Sheng wrote: >> >> Martin, >> >> It almost worked... >> >> This is what I did and what have happened: >> >> I have 322 files to insert into data base, >> totaling 650 >> million rows >> >> I divided the file list into two, then for each >> sub list >> >> (a) I insert first file in the list with N set to >> 650milllion >> rows, (b) all subsequent files have N set to the >> number >> of lines >> in *that* file >> >> once first list done, then >> >> (c) I insert first file in the second list with N >> set to >> 650million rows, >> (d) all subsequent files have N set to the number >> of lines in >> *that* file >> >> Then the same problem happened: it stucked at file >> number >> 316. >> >> >> ok. using the 650M enables MonetDB to allocate enough >> space >> and does >> not have to fall back on guessing. Guessing is >> painful, because >> when a file of N records has been created and it needs >> more, >> it makes >> a file of size 1.3xN. This leads to memory fragmentation. >> >> in your case i would have been a little mode spacious >> and used >> 700M as a start, because miscalculation of 1 gives a >> lot of pain. >> Such advice is only needed in (a) >> >> >> Note: This is farther then previous tries, which all >> stopped in >> the region of file number 280 +/- a few. >> >> My observation: >> (i) at (a), the VSIZE went up to around 46GB, then >> after >> first >> insert, it drops to around 36GB >> >> ok fits >> >> (ii) at (c), the VSIZE went up to around 130GB, then >> after first >> insert, it drops to around 45GB >> >> you tell the system to extend existing BATs prepare for >> another 650 M, >> which means it allocates 2*36 G, plus room for the old >> one >> gives 108GB >> then during processings some temporary BATs may be >> needed,e.g. to check >> integrity constraints after each file,. >> Then it runs out of swapspace. >> >> (iii) the "Free Memory", as reported by Activity >> Monitor, >> just >> before it failed at file number 316, dipped to as >> low as >> 7.5MB! >> >> yes, you are running out of swapspace on your system. >> This should not have happened, because the system uses >> mmapped files >> and may be an issue with the MacOS or relate to a >> problem we >> fixed >> recently >> >> >> >> My question: >> (1) why we need to ramp N up to total number of >> lines (it >> takes >> along time to do that), then only have it drop down >> to >> 30GB-40GB >> right after >> >> this might indicate that on MacOS, just like Windows, >> mmaped >> files >> need to be written to disk. With a disk bandwidth of >> 50MB/sec it >> still takes several minutes >> >> the first insert and stay roughly there? Does it >> mean we're >> giving back all the pre-allocation space back to >> the OS? Then >> should we set N always to total number of lines? >> If so, >> it would >> take much much longer to process all the files... >> (2) How come RSIZE never goes above 4GB? (3) Does >> sql log >> file >> size have some limit, that we need to tweak? >> >> no limit >> >> (4) Has anyone successfully implemented the 64bit >> version of >> MondeDB and successfully inserted more than >> 1billion rows? >> >> you platform may be the first, but Amherst has worked >> with >> Macs for >> years >> >> (5) when you say you "...The VSIZE of 44G is not too >> problematic, i am looking at queries letting it >> tumble >> between >> 20-80 GB....," What does it mean? Mine went up to >> as high as >> 135GB... >> >> explained above. >> >> regards, Martin >> >> >> Thanks, as always. >> >> >> >> >> >> >> >> >> >> >

2 4

[MonetDB-users] monetdb output mode -f
by Maria 25 Mar '09

25 Mar '09

Hi, I would like to run query with out printing output in order to monitor its performance. I tried to use '-f none' in command-line, but it didn't work for monetdb5. (I am not using xquery here) > ./mclient -lsql -uxman -ddemo -t -f none 1.sql ===> then, it prints all the results on the screen and display time. I want it only display time, not the query results. I also tried to redirect using /dev/null. In this case, it didn't display time. Isn't the '-f none' working in monetdb5?? If so, how can I measure the query time ? Thanks in advance. Maria.

2 1

[MonetDB-users] R6031
by dariuszs 25 Mar '09

25 Mar '09

Hi, I've got this messages while executing 'copy... into...' runtime error R6031 Attempt to initialize CRT more than once What does it mean? Thanks. Dariusz.

2 2

[MonetDB-users] Combined table is not really combined after all
by Yue Sheng 25 Mar '09

25 Mar '09

two tables: table_1 and table_2 both have exactly the same schema. table_1 has 2.15MM rows of data, table_2 has 3.35MM row of data. No indexing created a new table: table_combined using same schema as table_1 insert into table_combined select * from table_1; (30 minutes later) insert into table_combined select * from table_2; (40 minutes later) select count(*) * 1 from table_combined; this give ONLY 2.15MM rows with same rows as table_1 What happened to table_2?!? Yes, I've check dfarm size. It equals 2*(table_1 + table_2 ) So it's there but "just not seeing it"

5 14

Re: [MonetDB-users] Combined table is not really combined after all
by Jens Oehlschlägel 23 Mar '09

23 Mar '09

Yue, > select count(*) from table_combined; What do you get if you update one of your double columns to 1.0 and do a SUM() over this column? Jens

2 1

[MonetDB-users] Adding interval month problem
by Alfred Nordman 23 Mar '09

23 Mar '09

Hi, Consider the following table: create table "table1" ("Pdate" timestamp, "Price" double); insert into "table1" values ('2007-04-21 00:00:00.000000', 7555); insert into "table1" values ('2007-04-22 00:00:00.000000', 7834); insert into "table1" values ('2007-04-23 00:00:00.000000', 5631); insert into "table1" values ('2007-04-24 00:00:00.000000', 7555); insert into "table1" values ('2007-04-25 00:00:00.000000', 7555); I tried using the following query: SELECT ("table1"."Pdate") AS "PurchaseaDate", ("table1"."Pdate" + interval '1' month) AS "PurchaseaDate2", ("table1"."Price") AS "Price" FROM "table1" And got the error: !TypeException:user.s6_2[19]:'mtime.addmonths' undefined in: _61:any := mtime.addmonths(_59:timestamp, A0:int) !TypeException:user.s6_2[20]:'bat.insert' undefined in: _62:any := bat.insert(_53:bat[:oid,:timestamp], _58:oid, _61:any) Any ideas? Thanks, Alfred. -- View this message in context: http://www.nabble.com/Adding-interval-month-problem-tp22657190p22657190.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] sql characters
by dariuszs 22 Mar '09

22 Mar '09

Hi, How do I insert this value into a varchar(20) column? I get an error. "MERS'' Thanks. Dariusz.

3 6

[MonetDB-users] Row_Number() over problem
by Alfred Nordman 22 Mar '09

22 Mar '09

Hi, Given the following table: create table "table1" (col1 varchar(20), col2 int) insert into "table1" values ('a1', 1) insert into "table1" values ('a2', 3) insert into "table1" values ('a3', 5) insert into "table1" values ('a4', 6) insert into "table1" values ('a5', 3) insert into "table1" values ('a6', 7) The following query: select col1, "SumCol2", "rank" from ( select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" from ( select col1, (sum(col2)) as "SumCol2" from "table1" group by col1) as "temp1") as "temp2" Results: col1 SumCol2 rank a1 1 1 a2 3 3 a3 5 4 a4 6 5 a5 3 2 a6 7 6 This is good result set. But if one adds this query a where clause that involves the rank column, empty result set is returned. Example: select col1, "SumCol2", "rank" from ( select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" from ( select col1, (sum(col2)) as "SumCol2" from "table1" group by col1) as "temp1") as "temp2" where rank = 1 any ideas? Thanks Alfred -- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22577176.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 7

[MonetDB-users] sql characters
by dariuszs 21 Mar '09

21 Mar '09

Hi, How do I insert this value into a varchar(20) column? I get an error. "MERS'' Thanks. Dariusz.

1 0

[MonetDB-users] !FATAL: GDKlockHome: Database lock '.gdk_lock' denied
by Yue Sheng 21 Mar '09

21 Mar '09

System hanged for some reason. Cannot kill the process: user 2389 0.0 0.0 0 0 ?? E 6:33PM 0:00.00 (mserver5) Hence cannot restart server. Short of restarting the whole machine, is there a better way to handle this please?

1 0

[MonetDB-users] SQL DATEADD equivalent
by Yue Sheng 20 Mar '09

20 Mar '09

MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks

4 11

[MonetDB-users] speed sanity check
by Yue Sheng 20 Mar '09

20 Mar '09

I have a table with 700million rows, and 14 columns. All doubles. Each column of data have non-unique entries. No indexing. My query: SELECT * FROM bigtable WHERE col4 < 1000 limit 100; took 35 minutes to run. (a) Is this about the average time to run? (b) Should I explicitly index col4? (Website seems to suggest MonetDB takes care of indexing automatically) If so, how much benefit should I expect? Thanks.

2 2

Re: [MonetDB-users] Open source Column based DB ... for timeseries
by Jens Oehlschlägel 20 Mar '09

20 Mar '09

Yue, > I really like "ff", haven't used "bit" yet. I use ff, together with biglm for large scale regression. Thanks > I need a database to stored all my data, in the billions of rows, so that I can keep analysis within R and data retrieval in database. ff under 32 bit has a limit on vector length of integer.max ~ 2 billion elements we have not compiled under 64bit so far - you might want to try this on a machine with much RAM and a fast RAID. > You think "ff" is just as fast as MonetDB when it comes to dealing with billions of rows, and from different "ff" files (the equivalent of tables) 1) ff does not have tables (or data.frames) with columns of different types in the current version. This will happen during this year. Of course you can just work with a bunch of vectors 2) Speed is a stubborn beast which behaves very different depending on what you actually are doing. Taking your example "give me the total traded volume of IBM between 10:05am and 10:09am" If we assume that your tick data comes in in in a fixed time rythm and you are looking on historical data, then it should be possible to CALCULATE the positions of those elements falling in your time window. In this case you would get away with disk reading just those = no reading of an index or scanning a complete column. In ff you can access vector elements just by their position. Hard to believe that any DB access could be faster than that. If by contrast you need to scan many columns to decide which records you want, a column- or even row-based DB with indexes might be faster. Details matter. Let me know in case you tried compiling ff under 64 bit. Jens

1 0

[MonetDB-users] indexing
by Yue Sheng 20 Mar '09

20 Mar '09

In light of the MonetDB SQL indexing: "....MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access...." Does it mean there's no need for the user to explicitly create any index?

1 0

[MonetDB-users] Open source Column based DB
by Yue Sheng 20 Mar '09

20 Mar '09

I was wondering if anyone know of other column-based DBs suited to vase amount of time series data? Tried Googling. It'd be nice to hear from someone who has any insight on this. Thanks.

6 12

Re: [MonetDB-users] Open source Column based DB ... for timeseries
by Jens Oehlschlägel 20 Mar '09

20 Mar '09

> There is one other open source column based DB that might be worth > looking at: Infobright Community Edition (http://www.infobright.org/) > But if you want the moon on a stick, have a look at Esper, a realtime > streaming database that supports the kinds of queries you mentioned > in the UDF examples If you want to look at this from a statistical point of view: the statistical interpreter R (www.r-project.org) has now on its package repository (cran.r-project.org) package 'ff', which allows fast memory-mapped access to binary vector and matrix files. Amongst the many time-series packages R has, one ('kza') started exploiting package 'ff' for large time-series. And when it comes to quickly combining filtering conditions on large vectors, package 'bit' might interest you. Jens Oehlschlägel ( author of 'ff' and 'bit' :-)

2 2

[MonetDB-users] Windows 64 bit / PHP
by dariuszs 19 Mar '09

19 Mar '09

Hi, How do I get PHP library on Windows 64 bit? Thanks. Dariusz.

2 5

Re: [MonetDB-users] Windows 64 bit / PHP
by dariuszs 19 Mar '09

19 Mar '09

Hi, This is the error that I'm getting: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\\xampp\\php\\ext\\php_monetdb.dll' - The specified module could not be found.\r\n in Unknown on line 0 Thanks for your help. Dariusz.

1 1

[MonetDB-users] Status DateTime support in MonetDB XQuery
by Hans van Rijswijk (DT) 19 Mar '09

19 Mar '09

Hi Monet development team, Can anyone (e.g. lsidir) tell me what the status is of the development on the date/time support in xquery. If the development has been finished: - In which version of Mserver can I find a working implementation, e.g. source tarball or Fedora 10 RPMs (URL, version, date, stable, daily build)? - How does an xml representation of a date time object look like? - What XQuery extensions can I use to address / compare date objects? - Do you know of any performance improvement / degradation with respect to using date objects? If the development has not been finished: - Same questions, and when is the release planned? Regards, Hans van Rijswijk.

3 4

[MonetDB-users] parallel loading
by Yue Sheng 18 Mar '09

18 Mar '09

under 2009 release note, I see "Known issues in this release" pertaining to buggy parallel loading. I was wondering (1) what is the manifestation of this error? (2) does this bug also happen in the "nov 2008 sp2"? thx

1 0

Re: [MonetDB-users] 64bit MonetDB, JDBC Insert via RJDBC, >300 million rows
by Martin Kersten 18 Mar '09

18 Mar '09

Yue Sheng wrote: > Martin, > > It almost worked... > > This is what I did and what have happened: > > I have 322 files to insert into data base, totaling 650 million rows > > I divided the file list into two, then for each sub list > > (a) I insert first file in the list with N set to 650milllion rows, > (b) all subsequent files have N set to the number of lines in *that* file > > once first list done, then > > (c) I insert first file in the second list with N set to 650million rows, > (d) all subsequent files have N set to the number of lines in *that* file > > Then the same problem happened: it stucked at file number 316. ok. using the 650M enables MonetDB to allocate enough space and does not have to fall back on guessing. Guessing is painful, because when a file of N records has been created and it needs more, it makes a file of size 1.3xN. This leads to memory fragmentation. in your case i would have been a little mode spacious and used 700M as a start, because miscalculation of 1 gives a lot of pain. Such advice is only needed in (a) > > Note: This is farther then previous tries, which all stopped in the > region of file number 280 +/- a few. > > My observation: > (i) at (a), the VSIZE went up to around 46GB, then after first insert, > it drops to around 36GB ok fits > (ii) at (c), the VSIZE went up to around 130GB, then after first insert, > it drops to around 45GB you tell the system to extend existing BATs prepare for another 650 M, which means it allocates 2*36 G, plus room for the old one gives 108GB then during processings some temporary BATs may be needed,e.g. to check integrity constraints after each file,. Then it runs out of swapspace. > (iii) the "Free Memory", as reported by Activity Monitor, just before it > failed at file number 316, dipped to as low as 7.5MB! yes, you are running out of swapspace on your system. This should not have happened, because the system uses mmapped files and may be an issue with the MacOS or relate to a problem we fixed recently > > My question: > (1) why we need to ramp N up to total number of lines (it takes along > time to do that), then only have it drop down to 30GB-40GB right after this might indicate that on MacOS, just like Windows, mmaped files need to be written to disk. With a disk bandwidth of 50MB/sec it still takes several minutes > the first insert and stay roughly there? Does it mean we're giving back > all the pre-allocation space back to the OS? Then should we set N always > to total number of lines? If so, it would take much much longer to > process all the files... > (2) How come RSIZE never goes above 4GB? > (3) Does sql log file size have some limit, that we need to tweak? no limit > (4) Has anyone successfully implemented the 64bit version of MondeDB and > successfully inserted more than 1billion rows? you platform may be the first, but Amherst has worked with Macs for years > (5) when you say you "...The VSIZE of 44G is not too problematic, i > am looking at queries letting it tumble between 20-80 GB....," What does > it mean? Mine went up to as high as 135GB... explained above. regards, Martin > > Thanks, as always. > > > >

2 1