January 2015 - users-list

Join index structure
by Joseph D'silva 26 Jan '15

26 Jan '15

Hello, Does anyone know the Join index structure used in the database ? (implementation) and how it is being utilized by the optimizer ? I couldn't find any documentation / papers out there other than a single sentence mentioned at the end of this page, which is not much. https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers/Landscape "*Foreign Key replacements Goal: to improve multi-attribute joins over foreign key constraints Rationale: the code produced by the SQL frontend involves foreign key constraints, which provides many opportunities for speedy code using a join index. Impact: large Status: Implemented in the SQL strategic optimizer.*" Thanks Joseph

1 0

Best background refresh strategy
by Gustavo Brian 26 Jan '15

26 Jan '15

Hi, I'm trying monetdb as a bigtable for calendar based queries. I've created a 338 million rows / 54 columns table and ran queries in ms. That's perfect! I wonder to see if monetdb is suitable for a search service with high traffic (several hundred request per second) and which will the best data update strategy because during the "updates/insert/copy into" actions everything gets blocked. I would like to know how cluster mode will help as well. Thanks, Gustavo Brian

3 2

Not utilising Mutithreads
by shamsul hassan 22 Jan '15

22 Jan '15

Hi All, I have recently install monetdb "OCT 2014-SP1" release on my Oracle Enterprise 6 Linux server. Server configuration - 8 CPU Cores 64 GB RAM I have loaded a data set with 70 columns having 15 Millions Rows. I have started mserver5 as below -- mserver5 --debug=10 --set *gdk_nr_threads=8* --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set mapi_usock=/data/monetdb/testdb/.mapi.sock --set monet_vault_key=/data/monetdb/testdb/.vaultkey --set sql_optimizer=default_pipe --set max_clients=64 Now I have ran a query as below -- select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group by product_type; This query took around 90 Secs .. So I started tomograph and checked plotted graph ... which shows the parallelism usage as 39.9 % only and its utilizing only 1 thread with maximum time went in the step - group.subgroupdone almost 98%. My Question is that even I have started the server with gdk_nr_threads=8 , then why it is just using single thread. More info -- mserver5 --version MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) 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 63.0GiB available memory, 8 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: root@localhost (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Any pointers ?? Thanks

4 7

MonetDB Sharding
by gms_xy＠gmx.de 21 Jan '15

21 Jan '15

As mentioned in previous posts, I have some use cases for MonetDB, which act on tables with a billion rows. MonetDB works fast and stable on straight SELECT statements or aggregates on single tables Problems occured when doing big joins, which should produce a result set of 30 rows only, but running hours before failing. SELECT tbl1.*, tbl2.* FROM tbl1, tbl2 WHERE tbl1.xid = tbl2.xid AND tbl1.pk = 12345678 (same behavior, if written with JOIN explicitly) So I am wondering, if I can help MonetDB with partitioning or sharding. Asked already here: https://www.monetdb.org/pipermail/developers-list/2014-October/004325.html What is the state regarding manual partitioning? During last weeks I saw significant acceleration with sharding on other DBMS. Is there anybody, who can give some introduction or share experiences with sharding on MonetDB? 3rd party tools? What ist the maximum number of tables within a MonetDB database? What is the estimated overhead of a single table, multiplied a million times? Thank you Robert

3 2

GDK
by Brian Hood 20 Jan '15

20 Jan '15

Hi Guys, I have seen it around so much in Monetdb what does the acronym gdk actually stand for ? Regards, Brian Hood

2 1

Re: users-list Digest, Vol 29, Issue 14
by bhuvan Mitra 20 Jan '15

20 Jan '15

Thank you stefan. Is there any way through which I can get to know about those implementations or buying it from actian is the only way? Actually I want to contribute to c-store, open source column oriented database in this compression/decompression area. This answer from you will be very worthy to me. Thanks in advance. On Mon, Jan 19, 2015 at 9:29 PM, <users-list-request(a)monetdb.org> wrote: > 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: users-list Digest, Vol 29, Issue 12 (Stefan Manegold) > 2. Next release? (g4(a)novadsp.com) > 3. Re: Not utilising Mutithreads (shamsul hassan) > 4. Re: Next release? (Sjoerd Mullender) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 19 Jan 2015 14:58:13 +0100 (CET) > From: Stefan Manegold <Stefan.Manegold(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: users-list Digest, Vol 29, Issue 12 > Message-ID: <895297092.253122.1421675893625.JavaMail.zimbra(a)cwi.nl> > Content-Type: text/plain; charset=utf-8 > > > Hi, > > MonetDB and Actian/VectorWise are two different systems that do not share > any code. > > MonetDB does not use any compression in the way Actian/VectorWise does. > > Best, > Stefan > > > ----- Original Message ----- > > Thank you Martin. > > So, did monetdb bought the codes from actian code base and included in > it's > > source code? > > Also kindly tell me if monetdb is following any other compression > techniques > > apart from pfor, pfor-delta and pdict. > > Thanks in advance. > > > > On Mon, Jan 19, 2015 at 4:30 PM, < users-list-request(a)monetdb.org > > wrote: > > > > > > 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: Not utilising Mutithreads (Martin Kersten) > > 2. regarding source code (bhuvan Mitra) > > 3. Re: regarding source code (Martin Kersten) > > 4. information about compression/decompression techniques of > > monetdb (bhuvan Mitra) > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Sun, 18 Jan 2015 12:20:15 +0100 > > From: Martin Kersten < martin(a)monetdb.org > > > To: users-list(a)monetdb.org > > Subject: Re: Not utilising Mutithreads > > Message-ID: < 54BB96EF.3030906(a)monetdb.org > > > Content-Type: text/plain; charset=windows-1252; format=flowed > > > > Hi > > > > Each MAL instruction is executed by a single thread. > > Some operators require the complete input to be available, > > such as in your case the group.subgroup(). > > > > martin > > > > On 18/01/15 01:27, shamsul hassan wrote: > > > Hi All, > > > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > > > Enterprise 6 Linux server. > > > > > > Server configuration - > > > > > > 8 CPU Cores > > > 64 GB RAM > > > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > > > I have started mserver5 as below -- > > > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb > > > --set mapi_open=true --set mapi_port=54321 --set > > > merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > > > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > > > sql_optimizer=default_pipe --set max_clients=64 > > > > > > Now I have ran a query as below -- > > > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > > > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' > group > > > by product_type; > > > > > > This query took around 90 Secs .. > > > So I started tomograph and checked plotted graph ... which shows the > > > parallelism usage as 39.9 % only and its utilizing only 1 thread with > > > maximum time went in the step - group.subgroupdone almost 98%. > > > > > > My Question is that even I have started the server with > gdk_nr_threads=8 , > > > then why it is just using single thread. > > > > > > More info -- > > > > > > mserver5 --version > > > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > > > 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 63.0GiB available memory, 8 available cpu cores > > > > > > Libraries: > > > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips > 11 > > > Feb 2013) > > > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > > > Any pointers ?? > > > > > > Thanks > > > > > > > > > > > > _______________________________________________ > > > users-list mailing list > > > users-list(a)monetdb.org > > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > > > > > > ------------------------------ > > > > Message: 2 > > Date: Mon, 19 Jan 2015 12:28:05 +0530 > > From: bhuvan Mitra < bhuvanrjb(a)gmail.com > > > To: users-list(a)monetdb.org > > Subject: regarding source code > > Message-ID: > > < CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com > > > Content-Type: text/plain; charset="utf-8" > > > > Hello, > > > > I am very interested to know and learn about implementations of > > compression/decompression techniques that monetdb is following. I have > seen > > the monetdb's light weight compression algorithms(pfor, pfor-delta, > pdict), > > cache involved decompression techniques and excited to know how actually > > are they implemented in form of codes. Kindly tell me in which > folder/file > > is the source code of this compression/decompression algorithms present. > I > > have downloaded the source tarball from the website and could not find > > these code snippets. Plese help me to identify the path. > > > > Regards, > > mg > >

2 1

Next release?
by g4＠novadsp.com 19 Jan '15

19 Jan '15

Hello - is there any timeframe for a next release? There are a couple of post Oct2014-SP1 bug-fixes which would be really good to have included. Many thanks.

2 1

Re: users-list Digest, Vol 29, Issue 12
by bhuvan Mitra 19 Jan '15

19 Jan '15

Thank you Martin. So, did monetdb bought the codes from actian code base and included in it's source code? Also kindly tell me if monetdb is following any other compression techniques apart from pfor, pfor-delta and pdict. Thanks in advance. On Mon, Jan 19, 2015 at 4:30 PM, <users-list-request(a)monetdb.org> wrote: > 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: Not utilising Mutithreads (Martin Kersten) > 2. regarding source code (bhuvan Mitra) > 3. Re: regarding source code (Martin Kersten) > 4. information about compression/decompression techniques of > monetdb (bhuvan Mitra) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 18 Jan 2015 12:20:15 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Not utilising Mutithreads > Message-ID: <54BB96EF.3030906(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi > > Each MAL instruction is executed by a single thread. > Some operators require the complete input to be available, > such as in your case the group.subgroup(). > > martin > > On 18/01/15 01:27, shamsul hassan wrote: > > Hi All, > > > > I have recently install monetdb "OCT 2014-SP1" release on my Oracle > Enterprise 6 Linux server. > > > > Server configuration - > > > > 8 CPU Cores > > 64 GB RAM > > > > I have loaded a data set with 70 columns having 15 Millions Rows. > > > > I have started mserver5 as below -- > > > > mserver5 --debug=10 --set *gdk_nr_threads=8* > --dbpath=/data/monetdb/testdb --set mapi_open=true --set mapi_port=54321 > --set merovingian_uri=mapi:monetdb://localhost:54321/testdb --set > mapi_usock=/data/monetdb/testdb/.mapi.sock --set > > monet_vault_key=/data/monetdb/testdb/.vaultkey --set > sql_optimizer=default_pipe --set max_clients=64 > > > > Now I have ran a query as below -- > > > > select product_type,COUNT(DISTINCT sales_document),sum(ord_qty) FROM > bookings where ord_entry_date between '2014-04-01' and '2014-10-01' group > by product_type; > > > > This query took around 90 Secs .. > > So I started tomograph and checked plotted graph ... which shows the > parallelism usage as 39.9 % only and its utilizing only 1 thread with > maximum time went in the step - group.subgroupdone almost 98%. > > > > My Question is that even I have started the server with gdk_nr_threads=8 > , then why it is just using single thread. > > > > More info -- > > > > mserver5 --version > > > > MonetDB 5 server v11.19.7 "Oct2014-SP1" (64-bit, 64-bit oids) > > > > 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 63.0GiB available memory, 8 available cpu cores > > > > Libraries: > > > > libpcre: 7.8 2008-09-05 (compiled with 7.8) > > > > openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL > 1.0.1e-fips 11 Feb 2013) > > > > libxml2: 2.7.6 (compiled with 2.7.6) > > > > Compiled by: root@localhost (x86_64-unknown-linux-gnu) > > > > Compilation: gcc -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2 > > > > Linking : /usr/bin/ld -m elf_x86_64 > > > > Any pointers ?? > > > > Thanks > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > > ------------------------------ > > Message: 2 > Date: Mon, 19 Jan 2015 12:28:05 +0530 > From: bhuvan Mitra <bhuvanrjb(a)gmail.com> > To: users-list(a)monetdb.org > Subject: regarding source code > Message-ID: > < > CAMmryP6eg5AAMY1x70KJZMdc3k7P8aqjnHuS5xN-fNSJ8mN_sQ(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hello, > > I am very interested to know and learn about implementations of > compression/decompression techniques that monetdb is following. I have seen > the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), > cache involved decompression techniques and excited to know how actually > are they implemented in form of codes. Kindly tell me in which folder/file > is the source code of this compression/decompression algorithms present. I > have downloaded the source tarball from the website and could not find > these code snippets. Plese help me to identify the path. > > Regards, > mg >

2 1

information about compression/decompression techniques of monetdb
by bhuvan Mitra 19 Jan '15

19 Jan '15

I have been studying about various column-oriented databases and found monetdb to be very interesting as it is efficient and open source. Please tell me in which file/folder does source code of compression/decompression techniques written in monetdb. I am interested to learn the implementations of light weight compression algorithms and cache-decompression. I have source code of monetdb downloaded, just let me know the path of requested codes.

1 0

regarding source code
by bhuvan Mitra 19 Jan '15

19 Jan '15

Hello, I am very interested to know and learn about implementations of compression/decompression techniques that monetdb is following. I have seen the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), cache involved decompression techniques and excited to know how actually are they implemented in form of codes. Kindly tell me in which folder/file is the source code of this compression/decompression algorithms present. I have downloaded the source tarball from the website and could not find these code snippets. Plese help me to identify the path. Regards, mg

2 1