- users-list - monetdb.org

Re: Contents of users-list digest What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 24 Jan '14

24 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M

1 0

Query speed on MonetDB with 4-5 users
by Charles Bajomo 23 Jan '14

23 Jan '14

Hello, We have just started using MonetDB in production and can I start by saying its a pretty impressive database. We are having a problem with query when we have 4 -5 users running reports and I wanted to know if anyone could recommend some optimisation tips. On particular one is multiplex-funnels. I can't find any documentation on how to create it. I read some where that it can be used to replicate data between two servers. It would be great to get some guidance on how to do this. Kind Regards

1 0

Possible mapi bug in Windows version 20131120?
by Alastair McKinley 23 Jan '14

23 Jan '14

Dear all, I am using MonetDB for 64 bit Windows from the package MonetDB5-SQL-Installer-x86_64-20131120.msi. If I compile the example mapi program from here http://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI and cause an error in a query (e.g. incorrect port number in mapi_connect params) then the application will crash with the following message: 0xC0000005: Access violation writing location 0x0000000000000024 This appears to happen if any of the following functions are called: mapi_explain(dbh, stderr); mapi_explain_query(hdl, stderr); mapi_explain_result(hdl, stderr); The same code works correctly on Ubuntu 13.04 with MonetDB-11.15.17?. Can anyone reproduce this error on Windows? Best regards and thanks, Alastair

1 0

mapi connect latency
by Matthew Russell 22 Jan '14

22 Jan '14

Hi all, I am writing a simple C++ dll which uses the mapi interface to connect to MonetDB. The query itself runs extremely fast which is fantastic. I did notice that the mapi_connect() takes approximately 1 second to connect on my machine on a local socket. Is there anyway to improve this connection latency? I can't unfortunately use a connection pool or keep the connection open due to the design of the calling application. Best regards, Matthew Russell

3 5

Re: users-list Digest, Vol 17, Issue 8
by Edgar Mejia 22 Jan '14

22 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M 2014/1/22 <users-list-request(a)monetdb.org> > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. RE: problem with Jan2014 build (Bas Kaptijn) > 2. Re: What differences between monetdb and mongodb for > analytical data? (Ying Zhang) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 21 Jan 2014 11:55:38 +0000 > From: Bas Kaptijn <b.kaptijn(a)chs.nl> > To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> > Subject: RE: problem with Jan2014 build > Message-ID: > < > a553ef932478438e82367f954b701a0f(a)AMSPR05MB211.eurprd05.prod.outlook.com> > > Content-Type: text/plain; charset="us-ascii" > > Some last info: the problem disappears when using the no_mitosis_pipe > optimizer pipeline. > > sql>set optimizer='no_mitosis_pipe'; > operation successful (0.139ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > | file > | line | source > > | > > +=============================================================================+========+====================================================================================================================================================+ > | > /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC > | 430664 | 04999999999999999999999999999999999999999 > > : > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 tuple (1.1s) > sql>set optimizer='default_pipe'; > operation successful (0.305ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > +------+------+--------+ > | file | line | source | > +======+======+========+ > +------+------+--------+ > 0 tuples (798.870ms) > > Regards, Bas >

1 0

What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 21 Jan '14

21 Jan '14

I had 6 months works as monetdb and this is great for analytical data, datawarehouses and cubes. Which is faster of these two databases in percentage for reading? I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? Thanks, Edgar M

2 1

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Ok got the Jan2014 package working now with the comments in #3419 as suggested. Thanks. I omitted the LOCKED statement and now the count of rows per value in the column "file" matches the amount of rows in the actual files that were loaded, so that seems to work. But it dows not solve the whole problem. I still get the initial reported problem with querying only the rows of which the first two characters in the value of the field 'source' matches the string '04'. When I select a count of rows it reports about 2.8M matching rows which it should. When selecting the actual rows however I get a lot less. sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 540299 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1655861 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 476629 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 886713 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1404888 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 1509934 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (94.898ms) sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (901.377ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+----------------------------------------------+ | file | line | source +=============================================================================+========+==============================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_7084_20130501_20130531_Schade_516.ASC | 268991 | 0499999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+----------------------------------------------+ 1 tuple (770.827ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (892.431ms) sql>CREATE TABLE test04 AS SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' WITH DATA; operation successful (21.4s) sql>SELECT COUNT(*) FROM test04; +--------+ | L1 | +========+ | 326372 | +--------+ Note: I didn't have this problem with the Feb2013-SP5 release. I never tested the Feb2013-SP6 release. Regards Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Additional info: A build of the latest sources of the Feb2013 branch shows the same problem. However when I omit the optional LOCKED statement in the COPY INTO queries the problem does not occur. It seems the LOCKED statement also does not improve the performance of loading data much anymore (in my case at least); I can omit it but the data loading is as fast as before, so I guess this works for me now, but still there seems to be a problem with this LOCKED data loading. Regards, Bas

1 0

problem with Jan2014 build
by Bas Kaptijn 20 Jan '14

20 Jan '14

Hi, With a build of the latest version in the Jan2014 branch I encounter a problem which worked fine in the Feb2013-SP5 release. I load multiple files into a single table with a COPY INTO ... LOCKED statement for each file. This seems to work correctly but When quering this table I get the following results (note I masked sensitive data with 9's but that does not matter): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1; +---------+ | L1 | +=========+ | 6474324 | +---------+ 1 tuple (0.662ms) sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 809294 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (13.204ms) sql>\q bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/*.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 476629 /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC 886713 /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC 1404888 /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC 1509934 /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC 6474324 total bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (1.1s) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (752.096ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+===========================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 268991 | 04999999999999999999999999999999999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ 1 tuple (832.778ms) This can't be right. I formerly compiled, build and used the Feb2013 from the mercurial site without problems. bkaptijn@DevRef1:~/DDDB$ mserver5 --version MonetDB 5 server v11.17.2 (64-bit, 64-bit oids) This is an unreleased version Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 15.6GiB available memory, 8 available cpu cores Libraries: libpcre: 8.12 2011-01-15 (compiled with 8.12) openssl: OpenSSL 1.0.1 14 Mar 2012 (compiled with OpenSSL 1.0.1 14 Mar 2012) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: bkaptijn@DevRef1 (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Is this a bug? Regards, Bas Kaptijn PS I also tried to install the Jan2014 RC1 but I can't get it working on my Ubuntu (precise) system. First it misses libstream6.so while a libstream3.so was installed... when creating a symlink referring to the libstream3 lib it fails to startup mentioning that it can not setup communication channel. The Merovingian log mentions a MALException that says that it can not write to the "querylog" (which I also can not find in the dbfarm where I though it should be) and a MALException something about sysmon.

2 2