- users-list - monetdb.org

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

database was killed by signal SIGSEGV
by Miguel Costa 09 Apr '15

09 Apr '15

Hi there, I'm running a monetdb server with the 2014-SP2 version. I have 61GB of RAM with CentOS 6.5. I have one table with almost 100M rows. I do cycles of 85000 inserts and everything is fine for the first ones, but after some cycles it just gives the error "database was killed by signal SIGSEGV" On my var/log/messages gives the error : kernel: mserver5[6773]: segfault at 18 ip error 4 in lib_sql.so. In the beginning everything was fine, I manage to insert the 100M and I didn't had any problems, but now it's constantly crashing. My database size is more or less 40GB. The memory on the server occupied doesn't reach 1GB. Any suggestions of what to look for?

2 1

Querying a monetdb cluster
by José Raúl Pérez Rodríguez 09 Apr '15

09 Apr '15

Hi, I am new in monetdb. How do I make a query to a cluster? I maked a test inserting data into each particular node of a two node cluster, but I don't see the way of making queries to the cluster, I known that multiplex funnel can return data from several nodes, joining data obtained from each node, but with multiplex funnel, I can't sort the data. There is not doc that explains this problem?

1 0

Join memory overhead, COPY INTO csv, MT_mremap failure.
by Valentin Reis 09 Apr '15

09 Apr '15

Hello all, To the purpose of evaluating MonetDB, I am trying to perform the following: Load two csv files into two tables, from disk A. Perform a JOIN operation on two fields, using disk B for the database. Pipe the result to a csv file, on disk A. This results in a very high disk B usage, and subsequent MT_mremap failure, as seen in merovingian.log: 2015-04-08 12:50:14 ERR google-trace[2737]: = gdk_posix.c:428: MT_mremap(./bat/06/675.tail,7e8738e90000,91992817664,110391328768): GDKextendf() failed Does somebody have ideas or explanations? Details follow: I am loading csv data from hard drive A. The files have size: -rw-r--r-- 1 fre fre 2.6G Apr 5 16:25 task_events_cut.csv -rw-r--r-- 1 fre fre 34G Apr 5 16:04 task_usage_cut.csv I create a database in a separate hard drive B (2TB): rm -rf /mnt/diskB/mdb mkdir /mnt/diskB/mdb/ monetdbd create /mnt/diskB/mdb monetdbd start /mnt/diskB/mdb monetdb create google-trace monetdb release google-trace I then load the data into the database: mclient ct_trace_events_reduced.sql -d google-trace pv /mnt/diskA/task_events_cut.csv |mclient -d google-trace -s "COPY INTO task_events_reduced FROM STDIN USING DELIMITERS ',','\\n'" - mclient ct_trace_usage_reduced.sql -d google-trace pv /mnt/diskA/task_usage_cut.csv |mclient -d google-trace -s "COPY INTO task_usage_reduced FROM STDIN USING DELIMITERS ',','\\n'" - using very standard scripts: $ cat ct_trace_events_reduced.sql ct_trace_usage_reduced.sql DROP TABLE task_events_reduced; CREATE TABLE task_events_reduced ( "job_id" BIGINT , "task_id" BIGINT , "class" SMALLINT , "priority" SMALLINT); DROP TABLE task_usage_reduced; CREATE TABLE task_usage_reduced ( "job_id" BIGINT , "task_id" BIGINT , "cpu_mean" FLOAT , "cpu_sample" FLOAT); These two operations take about 50 minutes, which is very reasonable. I then use mclient to do my join: mclient join.sql -d google-trace using the script: $ cat join.sql COPY (SELECT te.job_id, te.task_id, te.class, te.priority, tu.cpu_mean, tu.cpu_sample FROM (SELECT * FROM task_events_reduced )AS te RIGHT JOIN (SELECT * FROM task_usage_reduced )AS tu ON(te.job_id=tu.job_id AND te.task_id=tu.task_id) ) INTO '/diskA/join.csv' USING DELIMITERS ',','\n'; This results in more than three hours of data crunching on a google compute engine machine (16 processors and 100GB RAM), where disk B is being increasingly used, until it is full (2TB HDD). Then, the aforementionned error happens. I am not hoping that MonetDB would perform streaming I/O on the right file from the join. However the disk usage seems quite high. Is there a way to force MonetDB to do a hash join? Thanks a lot, Valentin Reis University of Warsaw

4 7

MonetDB Real time analytics
by Luciano Sasso 09 Apr '15

09 Apr '15

Hello, I can work in real time with the MonetDB, for example, a job extracting and inserting data in MonetDB as stream and other job for reading data, aggregation and creation of indicators for dashboads in real or near real time ? -- Luciano Sasso Vieira Gestor - Inteligência de Negócios luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 9706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. http://www.avast.com

1 0