- users-list - monetdb.org

[MonetDB-users] Get average value for intervals
by Gustav Koller 18 Jun '10

18 Jun '10

Hello, I've some time series data (time + a few value columns), now I want to generate average values for each hour. Currently I'm using the following query: SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup; I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time? Greetings Gustav

2 2

[MonetDB-users] Limit memory
by Miguel Ángel Águila Lorente 17 Jun '10

17 Jun '10

Hello, I'm trying to limit the memory that monetdb can use during its executions. I'm using mal language and I've found this operation in order to do it. command mem_maxsize(v:lng) :void address set_mem_maxsize comment "Set the maximum usable amount of physical swapspace in KB."; When I use this operation the connection is terminated. I let here some examples: (I'm working with a machine with 32 GB) mal>status.mem_maxsize(4294967296:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(4294967296:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1073741824:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1073741824:lng); ERROR = !Connection terminated mal>status.mem_maxsize(1048576:lng); MAPI = monetdb@localhost:50000 ACTION= read_line QUERY = status.mem_maxsize(1048576:lng); ERROR = !Connection terminated Looking forward to hearing from you. Thank you. Mike

4 6

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender

4 4

Re: [MonetDB-users] MonetDB XML - slow execution of query, with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Jan, thank you for your response. I thought that all documents within a collection are stored within one (or several) relational tables? I am not sure, if merging all documents into one big document is an option. The documents are supposed to be updateable ... does that give a good performance for one large document? Patrick > Message: 7 > Date: Tue, 15 Jun 2010 21:58:44 +0200 > From: Jan Rittinger <jan.rittinger(a)uni-tuebingen.de> > Subject: Re: [MonetDB-users] MonetDB XML - slow execution of query > with test on attribute > To: Communication channel for MonetDB users > <monetdb-users(a)lists.sourceforge.net> > Message-ID: <702CE627-774E-47C5-B3B7-34A5431F20CF(a)uni-tuebingen.de> > Content-Type: text/plain; charset="windows-1252" > > Hi Patrick, > > the evaluation plan of your query looks quite bad -- with respect to what could be possible... > > If you could use 'doc("tu")' (with a single big document containing all small documents), you would get the result in *no* time, as then the attribute index is used. Unfortunately I'm not aware of the difference in the attribute index with respect to collections... > > Regards, > Jan >

2 1

Re: [MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 17 Jun '10

17 Jun '10

Hi Peter, thank you for your answer. using pf:collection gives me a "Speicherzugriffsfehler" (/Segmentation fault)/ when executing the query you proposed. Is that a bug in MonetDB XML? replacing "pf:collection" by "fn:collection" leads to Trans 55.436 msec Shred 19.083 msec Query 29241.029 msec Print 0.207 msec Timer 29582.455 msec So it is basically the same query time :( Patrick > Hi, > > How about: > > smiles> { > for $i in pf:collection("tu") > where $i//xq:molecule/@id = "1" > return > <smile>{$i}</smile> > } </smiles> > > is that any faster? > > Peter > > >

1 0

Re: [MonetDB-users] Meaning of: Scenario not initialized 'sql'
by yel 17 Jun '10

17 Jun '10

Hi, I know this topic has been discussed last year, but with the MonetDB-Feb2010-SuperBall-SP2 installed, I repeatedly got the error like MALException:setScenario:Scenario not initialized 'sql' almost every time when I try to start mclient with "mclient -l sql -d dbname", even when i try it later on. Moreover, only "mclient -l mal -d dbname" is OK, sql, xquery, mil all got the error above. Any idea ? BTW: 1. MonetDB is installed by ./monetdb-install.sh --enable-sql --enable-debug 2. mapi_port is set to 50003 in etc/monetdb5.conf; Thanks. ---- Best Regards Ye Liang > > Sjoerd Mullender-2 wrote: > Alex Bo. wrote: > > Hi, > > > > Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' > > from the server when I try to start it in scenarios that usually work > > without any problems. I can't reproduce it consistently, but sometimes I get > > it. What does this exception mean? > > > > Thanks. > > This exception means that the server is not yet ready for accepting SQL > connections. The solution is to try again later. > > When the server starts up, it pretty quickly starts accepting > connections, but initially only for MAL. When the SQL engine is > initialized (which may take a while if there was any recovery to be > done), it starts accepting SQL connections as well. You get this > exception when you connect before the SQL engine is ready. > > -- > Sjoerd Mullender >

1 0

[MonetDB-users] Meaning of: Scenario not initialized 'sql'
by Alex Bo. 17 Jun '10

17 Jun '10

Hi, Sometimes I get !MALException:setScenario:Scenario not initialized 'sql' from the server when I try to start it in scenarios that usually work without any problems. I can't reproduce it consistently, but sometimes I get it. What does this exception mean? Thanks. -- View this message in context: http://www.nabble.com/Meaning-of%3A-Scenario-not-initialized-%27sql%27-tp26… Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] MonetDB XML - slow execution of query with test on attribute
by Patrick Schäfer 15 Jun '10

15 Jun '10

Hi, I am trying to write a xquery-expression that does a test on an attribute (id="1"). Unfortunately the query execution time for this query is really bad (>30 seconds). Is there any way to find out why this query takes so long? 30 seconds for a test on one single attribute is just way to high for our use-case. I read that MonetDB automatically creates indices on all attribute - is there a way to test, if there was an index created for that attribute and if that index was used for query execution? The query (in File q3.xp) is supposed to return the molecule with id "1" declare namespace xq="http://www.xml-cml.org/schema"; <smiles> { for $i in collection("tu") where $i/xq:molecule/@id = "1" return <smile>{$i}</smile> } </smiles> The query is executed like this: # mclient -t -lx -G q3.xp ... Trans 55.684 msec Shred 18.818 msec Query 29335.766 msec Print 0.258 msec The database contains 1.000.000 XML-documents in a single collection, is updatable and is about 2601 MiB. A document looks similar to this one: <?xml version="1.0" encoding="UTF-8"?> <molecule id="1" xmlns="http://www.xml-cml.org/schema"> <bondArray atomRef1="..." order="..."/> <atomArray atomID="..." elementType="..." x3="..."/> <identifier convention="iupac:smile" value="..."/> <identifier convention="iupac:inchi" value="..."/> </molecule> We are using: "MonetDB/XQuery 0.36.5, based on the pathfinder <http://www.pathfinder-xquery.org> compiler and MonetDB 4.36.1 <http://monetdb.cwi.nl>" Thank you very much for any help! Patrick

3 2

[MonetDB-users] Decimal(p,s)
by Nozhup 15 Jun '10

15 Jun '10

What is the reason that MonetDB only supports a maximum p value of 18 while for example SQL Server (see link) supports up to 38? http://www.w3schools.com/SQL/sql_datatypes.asp -- View this message in context: http://old.nabble.com/Decimal%28p%2Cs%29-tp28892721p28892721.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] MIL and GROUP BY
by Hannes Rauhe 14 Jun '10

14 Jun '10

Hello together, I installed MonetDB with the help of the latest monetdb-install.sh and played around with MIL. Now I'm stuck with translating a simple SQL-Query containing a GROUPBY. After starting the server with # Mserver --dbname test --dbinit 'module(mapi); mil_start();' and the client with # ./mclient -lmil -utest -dname=test I tried the following: var a:= bat(oid,int); a.insert(1@0, 1995); a.insert(2@0, 1992); a.insert(3@0, 1993); a.insert(4@0, 1994); a.insert(5@0, 1995); a.insert(6@0, 1996); CTgroup(a).print(); the answer is: ERROR: const operation on a BAT ERROR: CMDconstCopy: operation failed. ERROR: interpret_params: print(param 1): evaluation error. Another try after loading the xtables-Module: mil>module("xtables"); mil>CTgroup(a).print(); +-------------------------------------------------------+ | � | +-------------------------------------------------------+ 1 tuple I used a few papers and http://monetdb.cwi.nl/MonetDB/Version4/Documentation/mil/index.html (Chapter 3.7 is what I'm looking for) as tutorial but didn't find anything helpful. Where's my mistake? Best Regards, Hannes

2 1

[MonetDB-users] Performance degradation
by Michael W Schatzow 11 Jun '10

11 Jun '10

hello, I am having performance degradation issues with repeated queries of monetdb. I am attempting to use monetdb for financial tick data and make sql queries requesting all quotes for a symbol on date x where time > y and time <z. Previously monetdb had worked great for this task and was extremely quick. However, I had a crash last week of the server it was built on. After this monetdb ran incredibly slow on the order of 25 times slower than it was running before. I rebuilt monetdb and it ran quickly for a day or two and started degrading in speed. Could someone give a hint or two on how I should go about debugging this? Is there a optimization task that I need to run? I am really at a loss of how I should handle this. I have been running queries via mclient -s from my program written in python. I previously found the python bindings too slow. Stats: cpu: 2 opteron 2222 os:cent os 5.4 memory 8gb database Size: only testing with csv data of 1.3 GB indexed by date and time built with options:--enable-optimise --enable-sql Thanks for any help, Michael

1 0

[MonetDB-users] libbat.dll crash
by Alex Bo. 09 Jun '10

09 Jun '10

Hi, I am experiencing a consistent Monet crash on an XP 32 bit machine, because of libbat.dll. The exact problem is mentioned here: http://old.nabble.com/--monetdb-Bugs-2982639---Crash-during-server-startup-… However, the link over the there to sourceforge doesn't work, so I can't comment or see what's going on with that bug. I tried it on Aug09-SP1 and Feb2010-SP2 (built from CSV), and both crashed. The link suggest that maybe older Monet dlls are laying around in the system32 folder, but I do not have any of the suggested dlls in there. Any suggestions on this issue? -- View this message in context: http://old.nabble.com/libbat.dll-crash-tp28826934p28826934.html Sent from the monetdb-users mailing list archive at Nabble.com.

4 3

[MonetDB-users] PF/Tijah Question: BM25F?
by jz＠uva 07 Jun '10

07 Jun '10

I was wondering whether it would be possible to use BM25F? http://trec.nist.gov/pubs/trec13/papers/microsoft-cambridge.web.hard.pdf Anyone else tried this before with an XQuery? junte

4 7

Re: [MonetDB-users] Server Crash Upon Connect
by Hering Cheng 28 May '10

28 May '10

Resent, with some of thread details removed due to limits on message length. On Thu, May 27, 2010 at 2:49 PM, Hering Cheng <hering.cheng(a)computer.org>wrote: > Hi, > > MonetDB core dumped after I had 10 Java threads retrieving a total of 23 > million records from a single table via JDBC. The threads actually all > completed successfully. The crash seems to occur when another process tried > to open a connection to the same database. I can reproduce the crash > consistently. > > After the core file is produced, processes can connect to MonetDB > successfully, with Merovingian starting up the dead mserver5 automatically. > > $ tail > ~/gaal/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/var/log/MonetDB/merovingian.log > 2010-05-27 14:27:39 MSG merovingian[19478]: client rdcuxsrv220:61292 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61296 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61294 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61298 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61288 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61304 has > disconnected from proxy > 2010-05-27 14:27:42 MSG merovingian[19478]: client rdcuxsrv220:61302 has > disconnected from proxy > 2010-05-27 14:32:33 MSG merovingian[19478]: proxying client > rdcuxsrv220:61784 for database 'taq' to mapi:monetdb://127.0.0.1:50001/taq > 2010-05-27 14:33:01 MSG merovingian[19478]: client rdcuxsrv220:61784 has > disconnected from proxy > 2010-05-27 14:34:06 MSG merovingian[19478]: database 'taq' (17289) has > crashed (dumped core) > > This is a Solaris SPARC server and MonetDB was built from the Feb2010 > source code using Sun Studio 12.1: > > $ ( ulimit -d $[32*1024*1024] && ulimit -n $[10*1024]; export > LD_PRELOAD_64=/usr/lib/64/libumem.so:${LD_PRELOAD_64}; export > LD_PRELOAD=/usr/lib/libumem.so:${LD_PRELOAD}; export > MONETDB5CONF=/GAAL/chenher/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/etc/monetdb5.conf; > /GAAL/chenher/share/monetdb/distro-sparc-feb2010-64bit-debug/bin/mserver5 > --version; ) > MonetDB server v5.18.1 (64-bit), based on kernel v1.36.1 (64-bit oids) > Copyright (c) 1993-July 2008 CWI > Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved > Visit http://monetdb.cwi.nl/ for further information > Found 32.0GiB available memory, 16 available cpu cores > Configured for prefix: > /GAAL/chenher/share/monetdb/distro-sparc-feb2010-64bit-debug > Libraries: > libpcre: 8.01 2010-01-19 (compiled with 8.01) > openssl: OpenSSL 0.9.8k 25 Mar 2009 (compiled with ) > libxml2: 2.6.23 (compiled with 2.6.23) > Compiled by: chenher@rdcuxsrv220 (sparc-sun-solaris2.10) > Compilation: cc -m64 -xcode=pic32 > -I/GAAL/chenher/share/hans_boehm_gc/distro-sparc-6.8-64bit/include/ -g > Linking : /usr/ccs/bin/ld -m64 -L/GAAL/chenher/share/openssl-64bit/lib > -L/GAAL/chenher/share/pcre-64bit/lib > -L/GAAL/chenher/share/hans_boehm_gc/distro-sparc-6.8-64bit/lib/ > > $ dbx ~/gaal/share/monetdb/distro-sparc-feb2010-64bit-debug/bin/mserver5 > ~/gaal/rdcuxsrv220-local-disk/chenher/monetdb/feb2010/var/MonetDB5/dbfarm/taq/core > For information about new features see `help changes' > To remove this message, put `dbxenv suppress_startup_message 7.7' in your > .dbxrc > Reading mserver5 > ... > Reading lib_logger.so.5.18.1 > Reading libuuid.so.1 > t@4 (l@4) terminated by signal SEGV (no mapping at the fault address) > Current function is putName > 174 for(l= nme[0]; l && namespace.nme[l]; l= > namespace.link[l]){ > (dbx) where > current thread: t@4 > =>[1] putName(nme = 0xffffffff6aec3928 "exportValue", len = 11U), line 174 > in "mal_namespace.c" > [2] initSQLreferences(), line 49 in "sql_gencode.c" > [3] SQLinitClient(c = 0xffffffff7f352628), line 379 in "sql_scenario.c" > [4] runPhase(c = 0xffffffff7f352628, phase = 5), line 363 in > "mal_scenario.c" > [5] runScenarioBody(c = 0xffffffff7f352628), line 392 in "mal_scenario.c" > [6] runScenario(c = 0xffffffff7f352628), line 438 in "mal_scenario.c" > [7] MSserveClient(dummy = 0xffffffff7f352628), line 368 in > "mal_session.c" > (dbx) threads > t@1 a l@1 ?() LWP suspended in __pollsys() > t@2 a l@2 SERVERlistenThread() LWP suspended in __pollsys() > t@3 a l@3 mvc_logmanager() LWP suspended in __pollsys() > o> t@4 a l@4 ?() signal SIGSEGV in putName() > t@5 a l@5 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@6 a l@6 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@7 a l@7 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@8 a l@8 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@9 a l@9 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@10 a l@10 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@11 a l@11 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@12 a l@12 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@13 a l@13 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@14 a l@14 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@15 a l@15 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@16 a l@16 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@17 a l@17 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@18 a l@18 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@19 a l@19 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@20 a l@20 runDFLOWworker() sleep on 0x100c14b08 in > __lwp_park() > t@21 a l@21 ?() sleep on 0xffffffff7f352a98 in __lwp_park() > t@22 a l@22 ?() sleep on 0xffffffff7f352d58 in __lwp_park() > t@23 a l@23 ?() sleep on 0xffffffff7f353018 in __lwp_park() > t@24 a l@24 ?() sleep on 0xffffffff7f3532d8 in __lwp_park() > t@25 a l@25 ?() sleep on 0xffffffff7f353598 in __lwp_park() > t@26 a l@26 ?() sleep on 0xffffffff7f353858 in __lwp_park() > t@27 a l@27 ?() sleep on 0xffffffff7f353b18 in __lwp_park() > t@28 a l@28 ?() sleep on 0xffffffff7f353dd8 in __lwp_park() > t@29 a l@29 ?() sleep on 0xffffffff7f354098 in __lwp_park() > t@30 a l@30 ?() sleep on 0xffffffff7f354358 in __lwp_park() > t@31 a l@31 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > t@32 a l@32 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > t@33 a l@33 runDFLOWworker() sleep on 0x1012f8988 in > __lwp_park() > ... > t@174 a l@174 runDFLOWworker() sleep on 0x102c9f908 in > __lwp_park() > t@175 a l@175 runDFLOWworker() sleep on 0x102c9f908 in > __lwp_park() > > Thanks. > Hering >

1 0

[MonetDB-users] Loading problems since FEB2010+SP1 release
by Nozhup 26 May '10

26 May '10

Since the FEB2010+SP1 release we're encountering loading issues. With the FEB2010 release we were able to load records until the HDD was full (500+ million records) on PCs with 2,3 or 24 GB ram memory without going to the SWAP. This was done by loading CSV-files with 1 million records each through the COPY INTO command (in the mclient and through a c-script). Since the FEB2010+SP1 release the system starts to SWAP after loading the first couple of million records. This results in increased loadtimes per loaded CSV-file: 1th million: 14 sec 2nd million: 26 sec 3rd million: 44 sec 4th million: 50 sec 5th million: 69 sec etc. We have tested this on several PCs and all PCs encounter the same problem. Test files used are the same for both releases as is the OS (Ubuntu 9.10). The only variable that has changed is MonetDB. -- View this message in context: http://old.nabble.com/Loading-problems-since-FEB2010%2BSP1-release-tp282876… Sent from the monetdb-users mailing list archive at Nabble.com.

5 7

[MonetDB-users] June Candidate release
by Nozhup 26 May '10

26 May '10

Hi, I'm having trouble making the June Candidate release. OS Used Ubuntu 10.04 Versions Tried 20100516/ 20100525/ 20100526/ Command Used ./monetdb-install.sh --enable-sql Part Of Error Log SQLExtendedFetch.c:43: error: conflicting types for 'SQLExtendedFetch' /usr/include/sqlext.h:1853: note: previous declaration of 'SQLExtendedFetch' was here make[7]: *** [libMonetODBC_la-SQLExtendedFetch.lo] Error 1 make[7]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc/driver' make[6]: *** [all] Error 2 make[6]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc/driver' make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc' make[4]: *** [all] Error 2 make[4]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src/odbc' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src' make[2]: *** [all] Error 2 make[2]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients/src' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/var/tmp/MonetDB-1274859923/MonetDB-client/clients' -- View this message in context: http://old.nabble.com/June-Candidate-release-tp28677743p28677743.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 5

[MonetDB-users] Mapi - mal
by Petros Tsialiamanis 20 May '10

20 May '10

Hello, I would like to get the oid of a stored uri using the Mapi library. The mal commands that need to be executed are: tokenizer.open("rdf"); id := tokenizer.locate("http://uri"); io.print(); tokenizer.close(); I tried to run the following program ...tokenizer.open("rdf");... ... if ((hdl = mapi_query(dbh, "tokenizer.locate(\"http://uri\")")) == NULL || mapi_error(dbh) != MOK) die(dbh, hdl); if (mapi_close_handle(hdl) != MOK) die(dbh, hdl); while (mapi_fetch_row(hdl)) result=mapi_fetch_field(hdl, 0); ... ...tokenizer.close();... .. but I get the following error Mapi.mx:5141: mapi_fetch_row: Assertion `(hdl)->mid' failed. Could you tell me how can I solve this problem? Kind regards, Petros

2 2

[MonetDB-users] Loading and querying on the same database.
by Nozhup 19 May '10

19 May '10

Loading and querying simultaneously seems to slowdown the database significantly. I can understand if the queries became slower during loading, but it seems that only the first query before loading is finished is slow and all queries coming after. I can only fix the slowdown by using "killall mserver5" in a terminal, which is not an acceptable solution. I start 1 "copy into" action with one connection and at the same time a series of simple queries on another connection. The Simple query looks something like this: SELECT x,y FROM table WHERE x = '519'; Used OS: Ubuntu 9.10 and 10.04 MonetDB: Feb2010 release (SP! and SP2 are no option due to them giving loading problems) Results Query: 1 --> 0.000191 SECONDS : : : Query: 10501 --> 0.000170 SECONDS Query: 10502 --> 0.000169 SECONDS Query: 10503 --> 0.000169 SECONDS Query: 10504 --> 0.000172 SECONDS Query: 10505 --> 0.000172 SECONDS Query: 10506 --> 0.000174 SECONDS Query: 10507 --> 0.000178 SECONDS Query: 10508 --> 1.199149 SECONDS Load : 1 --> 3.238532 SECONDS Query: 1-10509 --> 1.238804 SECONDS Query: 1-10510 --> 1.175408 SECONDS Query: 1-10511 --> 1.141172 SECONDS Query: 1-10512 --> 1.132525 SECONDS Query: 1-10513 --> 1.159554 SECONDS Query: 1-10514 --> 1.158303 SECONDS Query: 1-10515 --> 1.160997 SECONDS How do I solve this or is this an error in MonetDB? Thanks in advance. -- View this message in context: http://old.nabble.com/Loading-and-querying-on-the-same-database.-tp28605728… Sent from the monetdb-users mailing list archive at Nabble.com.

2 5

[MonetDB-users] updates in a transaction
by Min Lu 19 May '10

19 May '10

Hi, I tried to update a column of a record more than once within a transaction. But it seemed that only the first update took effect. For example, for a table "CREATE TABLE people (id TINYINT PRIMARY KEY, name VARCHAR(128) NOT NULL)", sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 0 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ sql>start transaction; auto commit mode: off sql>update people set id = -1 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>update people set id = -2 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>commit; auto commit mode: on sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | -1 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ It is not what we would expect, isn't it? My MonetDB version is v5.18.3 Min

3 5

[MonetDB-users] crash when doing count(distinct col)
by Sam Mason 18 May '10

18 May '10

Hi, I've just pulled a largish dataset into monetdb (419851473 rows, 8 columns) and it's crashing when I try to do the following: select count(distinct a) from calldata; "a" is an integer column and monetdb gets the right answer of 886693 when I run either: select count(a) from (select a from calldata group by a) x; or select count(a) from (select distinct a from calldata) x; I'm running the latest 64bit .deb packages under ubuntu 10.4. The following is what I get back from mclient: sql>select count(distinct a) from calldata; MALException:!ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. Timer 93948.763 msec 1 rows I've not got much swap space, does it just happen to need it for the first version but not the other versions? -- Sam http://samason.me.uk/

2 2

[MonetDB-users] MonetDB performance
by dariuszs 18 May '10

18 May '10

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

7 13

[MonetDB-users] MonetDB/SQL: bulk-loading large data sets & adding key constraints
by Stefan Manegold 15 May '10

15 May '10

Dear all, derived from recent experiments and intensive testing, here are some recommendations for bulk-loading large data sets (in particular those larger than your available main memory) in MonetDB/SQL and adding constraints, i.e., primary and foreign keys. First of all, bulk loading should be done from (possibly gzip or bzip2 compressed) CSV files using the SQL `COPY INTO` command; cf., http://monetdb.cwi.nl/SQL/Documentation/Data-Manipulation.html In case your tables come with primary or foreign key constraints, it is recommended to omit these constrains when initially creating the tables. Then bulk-load (`COPY INTO`) your data. Once the data is loaded add the key constraints one by one using the SQL `ALTER TABLE ... ADD CONSTRAINT ...` command. Note, though, that the current Feb2010 release of MonetDB/SQL might experience some performance problems when adding constraints on data sets larger than main memory on multi-core machines. The reason it a non-optimal query translation and plan parallelization. For the time being, i.e., until we manage to fix this problem properly, it is recommended to resort to the optimizer pipeline of the Nov2009 release (for insiders, this effectively means disabling the "mitosis" optimizer), (only) while adding the key constraints. To do so, simply activate the Nov2009 optimizer pipeline via SET optimizer='nov2009_pipe'; just before adding the key constraints via `ALTER TABLE ... ADD CONSTRAINTS` and then restore the default optimizer pipeline via SET optimizer='default_pipe'; once you're done with adding constraints. For TPCH scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~1.5h to ~1h. For SSB scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~4h to ~1h. Hope this helps. Please don't hesitate to comment or ask questions. We'll let you know, one we managed to fix this problem properly. Kind regards, Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |

1 0

[MonetDB-users] Help,how to create hash index on table
by 冯亮 15 May '10

15 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

4 3

[MonetDB-users] Help,how to create hash index on table
by fll 09 May '10

09 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

2 1

[MonetDB-users] Move of SourceForge bugtracker to MonetDB Bugzilla
by Fabian Groffen 07 May '10

07 May '10

This message is important for all that used to report bugs on our SourceForge tracker, or are planning to report bugs in the future. With the move of our CVS repository to a new Mercurial repository, we have also migrated the bugtracker from SourceForge to a Bugzilla instance. The old SourceForge trackers are no longer in use. Please DO NOT file any bugs there, as they will get lost. We have removed their presence as far as possible to avoid any confusion here. The new location to search, read and file new bugs is here: http://dev.monetdb.org/bugzilla/ (Please bear with us while we try to get the interface/skin well suited for our project.) All bugs that were previously on the SourceForge trackers have been migrated to the new Bugzilla instance. The bug numbers could not be retained, hence all have been renumbered. See [1] for dealing with this in Bugzilla. All users that have ever reported a bug, commented on a bug, or been an assignee for a bug on SourceForge have been imported in Bugzilla. This means that for all those users a <sf-nickname>@users.sourceforge.net account has been created. Exception to this are users who work at CWI, whose addresses have been mapped to their CWI address. If you want to use your migrated account, simply click the "Forgot Password" link on the Bugzilla main page. It will send an email to your (SourceForge) email address with a token to allow you to set a password. If you are a new user, you can simply sign up on our Bugzilla using the "Open a New Account" button. In both cases, make sure you look through the preferences, in particular the "Email Preferences". For migrated accounts these preferences are set to exclude sending any bug email, which may or may not be what you prefer. Feel free to ask for any help if problems arise. [1] Searching an old bug using its bugnumber: If you have an old SourceForge bug number and need to find tha associated bug in Bugzilla, open up an advanced search, and enter the old bug number in the URL field. Make sure that you select the appropriate bug resolutions (e.g. include RESOLVED) and search. Each migrated bug has an URL set to the original bug on SourceForge, hence is easily found this way.

3 2