April 2015 - users-list

MonetDB Cluster
by Luciano Sasso 17 Apr '15

17 Apr '15

Hello, I read all posts about cluster in MonetDB, but dont understand, I will make some specific quaestions, I think it will be more objective: 1- MonetDB makes automatic replication between nodes? 2- The writing is performed in a just node and this replicate to the other? 3. The reading can be done on any node? 4- There is a router that distributes queries between the nodes? 5- If a query from a client is very cost, this query can be distributed between nodes? 6- MonetDB makes sharding transparently? 7- How to funnel and multiplex ? Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

3 2

MonetDB vs Vertica CE
by Luciano Sasso 17 Apr '15

17 Apr '15

Hi, The team of staff this questioning me about using Vertica, but it has limitations on the CE version. Someone already used the Vertica has some advantage over MonetDB? -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

7 10

MonetDB vs Vertica CE
by Luciano Sasso 17 Apr '15

17 Apr '15

Hi, thanks for awnsers. Precisely, I tried last year to deploy Vertical, but had trouble install and configure, since the MonetDB, was simply install and use, so do I use to this day with a database analytical about 30G, and 50 million records in the main table. I use Pentaho (Saiku) to explore the data, and also queries for creating dashboards. Now I pretty increase the volume of data, so that is posted another question about MonetDB clustered, it is the only thi still worries me, if I need to scalling. I dont think I still need something like HBase / Hadoop. Also have questioned me about using MongoDB, but from what I saw on MongoDB, needs a lot of programming to performance aggregations etc. with the MonetDB it is transparent. Today my data for analysis in MonetDB are fully denormalized not use model schema star. I see that many proposes the architecture with MongoDB for DW and Vertica or MonetDB, for analysis, but that think with volume that I have, I can use the MonetDB dw as well. I see there are many things about trying to force the use of Big data tools without really need it. Att, On 16/04/2015 10:38, 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: MonetDB vs Vertica CE (Ruben Silva) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 16 Apr 2015 14:37:47 +0100 > From: Ruben Silva <ruben.silva(a)cortex-intelligence.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: MonetDB vs Vertica CE > Message-ID: > <CAER5Ptm+M8e1B5r0bgXKnMrADGhW+ch6Wcdy5co1jw8b=CehCA(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > Hello Luciano, > > I don't have a full grasp of the new stuff introduced into MonetDB in the > last year, but as far as I know there are still major differences between > the two systems for me the most important is scalability. Vertica has no > single point of failure when working as a cluster. However the CE version > is limited to three nodes, so scalability is also limited in that case. > > Vertica relies heavily on projections as a way to improve performance. If > your data model is relatively stable it is something fairly easy to use, > but if your data model changes at a high rate you will have a considerable > amount of additional work managing the projections. Also if you use many > projections they will hurt your DML statements performance. > > MonetDB feeds on CPU and has no boundaries for its appetite, consuming as > much resources as possible in order to respond to a single statement. That > can be very good or very bad, it depends on your use case. Vertica has > resource pools that are used to regulate the stuff (IO, Ram, CPU) that each > query is allowed to use. > > I don't know your use case (and that is crucial for a good advice), but if > you are considering a scenario where you have a single machine for the > database system, then go with MonetDB without any doubt. It is just faster > and simpler. > > Cumprimentos (Regards), > > 2015-04-16 14:04 GMT+01:00 Luciano Sasso <luciano(a)gsgroup.com.br>: > >> Hi, >> >> The team of staff this questioning me about using Vertica, but it has >> limitations on >> the CE version. Someone already used the Vertica has some advantage over >> MonetDB? >> >> -- >> Luciano Sasso Vieira >> Data Scientist & Solutions Architect >> >> luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 >> 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br >> >> >> ------------------------------ >> [image: Avast logo] <http://www.avast.com/> >> >> Este email foi escaneado pelo Avast antiv?rus. >> www.avast.com >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >

2 1

MonetDB clustered
by Luciano Sasso 16 Apr '15

16 Apr '15

Hi, I have a question about MonetDB clustered 1- It only replicates data between nodes? 2- He replicates data between nodes doing sharding? 3- When a query is sent to the cluster, it is processed by one node just or distributed by several nodes? 4- Cluster is related to multiplex-funnel? Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

2 1

Re: MonetDB vs Vertica CE
by Luciano Sasso 16 Apr '15

16 Apr '15

Hi, Right, but in the case when you take the data 30 minutes, you take all the data, and the bank have to see what should be changed or inserted? is not better you take only the new data in this way will always be inserted? I do it today Att, On 16/04/2015 10:47, 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: MonetDB vs Vertica CE (shamsul hassan) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Thu, 16 Apr 2015 14:47:46 +0100 > From: shamsul hassan <shamsulbuddy(a)gmail.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: MonetDB vs Vertica CE > Message-ID: > <CAONo-du24VrQ_PR4nzNq7rT7Uw47fKQb5Pr376UWEDmTbCN7qQ(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > there is only one thing which has stopped me for using MonetDB in > production is the lack of UPSERT operation. > In my case new data comes every 30 mins and I cannot truncate and load the > whole table again. Ideally an UPSERT would have helped here as it will only > touch those records which are new or changed without truncating the whole > table. > > I raised the question few days before in Community list but didn't get any > useful answer. > > If i get any workaround for these UPSERTS the i would be as happy as pig in > dirty water :) > > Thanks > > > On Thu, Apr 16, 2015 at 2:37 PM, Ruben Silva < > ruben.silva(a)cortex-intelligence.com> wrote: > >> Hello Luciano, >> >> I don't have a full grasp of the new stuff introduced into MonetDB in the >> last year, but as far as I know there are still major differences between >> the two systems for me the most important is scalability. Vertica has no >> single point of failure when working as a cluster. However the CE version >> is limited to three nodes, so scalability is also limited in that case. >> >> Vertica relies heavily on projections as a way to improve performance. If >> your data model is relatively stable it is something fairly easy to use, >> but if your data model changes at a high rate you will have a considerable >> amount of additional work managing the projections. Also if you use many >> projections they will hurt your DML statements performance. >> >> MonetDB feeds on CPU and has no boundaries for its appetite, consuming as >> much resources as possible in order to respond to a single statement. That >> can be very good or very bad, it depends on your use case. Vertica has >> resource pools that are used to regulate the stuff (IO, Ram, CPU) that each >> query is allowed to use. >> >> I don't know your use case (and that is crucial for a good advice), but if >> you are considering a scenario where you have a single machine for the >> database system, then go with MonetDB without any doubt. It is just faster >> and simpler. >> >> Cumprimentos (Regards), >> >> 2015-04-16 14:04 GMT+01:00 Luciano Sasso <luciano(a)gsgroup.com.br>: >> >>> Hi, >>> >>> The team of staff this questioning me about using Vertica, but it has >>> limitations on >>> the CE version. Someone already used the Vertica has some advantage over >>> MonetDB? >>> >>> -- >>> Luciano Sasso Vieira >>> Data Scientist & Solutions Architect >>> >>> luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 >>> 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br >>> >>> >>> ------------------------------ >>> [image: Avast logo] <http://www.avast.com/> >>> >>> Este email foi escaneado pelo Avast antiv?rus. >>> www.avast.com >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >

1 0

MonetDB with R Integration for Windows Issues
by Bogdan Dimitrov 15 Apr '15

15 Apr '15

Hello everyone, I have been trying to get Embedded R in MonetDB to run on Windows 7 and I ran into a bunch of problems. I was wondering if anybody else has been able to get it running or whether there are better workarounds for the issues that I found. I am using manually built MonetDB version 11.19.9 with R integration enabled. The problems I had to fix seem to stem from the fact that the MonetDB is built using the VS2010 compiler, while the R assemblies are built with the MinGW compiler, so sometimes the two do not play nicely together and the server crashes with access violation errors on several occasions. After some debugging of both MonetDB and R source I identified the following problems: 1. The call in rapi.c line 373: R_tryEvalSilent(VECTOR_ELT(R_ParseVector(mkString(rapiinclude), 1, &status, R_NilValue), 0), R_GlobalEnv, &evalErr); fails with an access violation error. The problem is that the path contained in the char array called rapiinclude uses backslashes (Windows style), while the R code expects a UNIX style path with forward slashes. I added a bit of code to rapi.c that replaces the backslashes with forward slashes in the path to work around this issue. 2. The call in rapi.c line 742: fprintf(stdout, "# MonetDB/R module loaded\n"); fails with an access violation (as well as the rest of fprintf calls in rapi.c). Passing file handles across dll boundaries can cause problems even when same compiler is used, here it is passed between dlls built with different compilers (VS2010 compiled lib_rapi.dll to MinGW compiled R.dll). I replaced all fprintf calls with printf to work around this issue. Fixing the above issues I managed to run MonetDB with embedded R on Windows 7 and all the R scripts I tried seemed to work fine until I tried to use an R library that had to be downloaded first automatically using the overloaded library() function as described in https://www.monetdb.org/content/embedded-r-monetdb . 3. The call in rapi.R line 32: install.packages(package,repos=c("http://cran.rstudio.com/"),lib=.rapi.libdir,quiet=T) causes access violation as well, but I have not been able to find a workaround for this yet (apart from manually downloading the R package and dropping it in rapi_packages folder in dbfarm, so no downloading needs to happen). These should be Windows specific issues as MonetDB and R can be compiled with the same compiler for *NIX systems, while the MonetDB build instructions for Windows explicitly state that there is no longer support for compilers other than Microsoft Visual Studio's. I would appreciate it if anybody can help me solve the problem with downloading R libraries or knows a way to get the R integration running on Windows without the custom code changes described above. Best regards, Bogdan Dimitrov ________________________________

2 1

segfault in function when return table from IF (or any code branch)
by Caroptions Caroptions 14 Apr '15

14 Apr '15

DROP FUNCTION test(text); CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD date) BEGIN IF a is NULL THEN return table( select cast(sdate as date) from quotes limit 10 ); ELSE return table( select cast(sdate as date) from quotes limit 10 ); END IF; END;-- ****** OR ******CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD double) BEGIN declare table t(f double); select 0.0 into t; return t; END; -- ****** OR ******CREATE FUNCTION test(a text) RETURNS table(SOMEFIELD double) BEGIN declare table t(f double); set t = table (select 0.0); return t; END; When submit (not even execute) -> segfaultBug: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3699Thanks,Vasili

1 0

Time comparison is not correct
by Caroptions Caroptions 14 Apr '15

14 Apr '15

select cast(timestamptz '2001-01-01 19:00:00 -04:00' as timetz)< cast( timestamptz '2001-01-01 23:00:00 -04:00' as timetz); -> false. Bug: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3700 As an idea (and it seems PostgreSQL uses this approach) when you convert to UTC, you should not "reset" count at 24 but allow hours more that 24:00. This way 23:00 -04:00 will become 27:00 UTC then the comparison will be correct regardless timezone. Thanks,John

1 0

NThreads Parameter and the Performance Related to the Number of Database Instance
by Joseph Mate 10 Apr '15

10 Apr '15

Hello MonetDB Users, In short: 1) What does the nthreads setting mean? 2) Why does performance increase as you increase the number of database instances of a single farm? Is there any way to avoid this? In long: What does the nthreads setting mean? From the manpage[1] it's the number of worker threads that perform main processing. Is this the total number of threads for that database instance? Or is it per query? I've compared the performance of different settings. I find it strange that nthreads=8 would perform the best because I have 12 cores. I confirmed the number of cores by checking nproc. Here are my plots showing that 8 threads performs best on my system: Throughput: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/throughput-v… Average Response Ttime: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-avg-vs… 99th Percentile Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-p99-vs… The second thing is: why does the performance increase if I distribute the conncurent clients over multiple database instances on the same data farm? I expected the opposite; performance should decrease. I thought I would increase the overhead by adding more database instances to the same farm. Is there any way to avoid this? Below are the plots I have. I used 40 concurrent TPC-H clients for all: Throughput: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/throughput-v… Average Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-avg-vs… 99th Percentile Response Time: https://cs.uwaterloo.ca/~jmate/nthreads-and-database-instances/rsptm-p99-vs… Context: To give you guys some context on my project: I'm a Master's student doing my research on DBaaS tenant placement. I am evaluating some placement algorithms by using the TPC-H workload and MonetDB as the database. I am using separate database instances on a single farm for isolation. The workload I'm testing with runs read only queries from the TPC-H benchmark. Each TPC-H client is a thread each with its own persistent connection to the database running the following pseudocode: while true for queryNum 1 ... 22 # except query 15 which creates a tmp table run queury queryNum Each database instance has 100MB of data. Thank you for your time, Joseph References: [1] https://www.monetdb.org/Documentation/monetdb-man-page

3 5

first_value/last_value solution in MonetDB
by Caroptions Caroptions 10 Apr '15

10 Apr '15

Hi, Till I am trying to resolve my business problem I discovered the segfault bug which I have reported in MonetDB bug tracking system. I believe Monetdb is great product and team is moving in right direction utilizing latests advances in technology. Nevertheless my "business" problem is to build OHLC Time Series for longer time frame from shorter ohlc time series. I need partition by interval (5 minutes, 30 minutes, 1 hour, 1 day) and get first record of open, max of high, min of low and last of close from let's say 1 minute ohlc series. In postgresql there are 2 functions: first_value and last_value, which allows to do exactly this. Are there any function/approach/techniques which could be utilized to have the same results in MonetDB, I could do this aggregation on client side but this way I will not utilize DB potential and mainly the network traffic will be prohibitive for the solution. I am sure this question is already addressed somewhere, but I couldn't find, the solution should be trivial and pretty standard but I no luck on my side, if you push me in right directly I will really appreciate. Thank you in advance!John

2 5