- users-list - monetdb.org

[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