January 2014 - users-list

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

Concurrent connections to MonetDB and temporary tables
by Alastair McKinley 15 Jan '14

15 Jan '14

Dear all, I want to connect to a single MonetDB server from an application using the mapi interface. The intention is to load a small input dataset using insert statements and mapi_query(). Then I will execute a SELECT statement from mapi against another dataset resident in the MonetDB server and return the results to the application. My question is about possible concurrent connections from this application. Is it sufficient to use unique table names for my input data sets so each input data set ends up in a separate table? Is there a better approach for this use case built in to MonetDB that I have missed? Best regards, Alastair

1 0

Announcement: Release Candidate for Jan2014 Release
by Sjoerd Mullender 15 Jan '14

15 Jan '14

The MonetDB team at CWI/MonetDB BV is pleased to announce a release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org). The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

1 0

MonetDB licensing
by Basil Peace 15 Jan '14

15 Jan '14

Currently MonetDB is licensed under `fork` of MPL 1.1. This is free software license, but it is not compatible with GPL ( http://www.gnu.org/licenses/license-list.html#MPL ). Are there some plans to change license, e.g. to upgrade to MPL 2.0? I'm asking since I'm planning big project (under GPLv3) just now, and I need to choose backend database. I like MonetDB, but GPL incompatibility stopped me. Best regards, Basil Peace

2 3