March 2009 - users-list

[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