August 2009 - users-list

[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 23 Aug '09

23 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