May 2018 - users-list

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 04 Jul '19

04 Jul '19

Hi Stefan, Thanks for your reply! We have run the query a few times with different size of data. There we used 16G RAM(actually 13.5G was used), and find the size of 10G's data is the critical point that can run the query. All of the data files' size are listed below, each file name is a table name(there are only a few tables are refered -- store_sales, date_dim, item, customer, catalog_sales, web_sales): 7.4K call_center.dat 1.6M catalog_page.dat 212M catalog_returns.dat 2.9G catalog_sales.dat 27M customer_address.dat 64M customer.dat 77M customer_demographics.dat 9.9M date_dim.dat 77B dbgen_version.dat 149K household_demographics.dat 328B income_band.dat 2.6G inventory.dat 28M item.dat 61K promotion.dat 1.7K reason.dat 1.1K ship_mode.dat 27K store.dat 323M store_returns.dat 3.8G store_sales.dat 4.9M time_dim.dat 1.2K warehouse.dat 19K web_page.dat 98M web_returns.dat 1.5G web_sales.dat 12K web_site.dat So we guess that the monetdb has no memory management? For the output of `mserver5 --version` is: MonetDB 5 server v11.27.13 "Jul2017-SP4" (64-bit, 128-bit integers) Copyright (c) 1993 - July 2008 CWI Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved Visit https://www.monetdb.org/ for further information Found 17.0GiB available memory, 40 available cpu cores Libraries: libpcre: 8.38 2015-11-23 (compiled with 8.38) openssl: OpenSSL 1.0.2g 1 Mar 2016 (compiled with OpenSSL 1.0.2g 1 Mar 2016) libxml2: 2.9.3 (compiled with 2.9.3) Compiled by: monetdb(a)MonetDB-0.0 (x86_64-pc-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 And the size of processes is not limited. To let you reproduce the problem conveniently, I'll provide more details here: you can get tpc-ds from its website(we use version 2.6.0). Install the tpc-ds, access the directory v2.6.0/tools and run `./dsdgen -scale 10 -dir /home/monetdb/tpc-ds_test_data10G` to generate the data. When data has been generated, using the script /expe.sh/ to create tables and load the data. The query script is 123.tpcds.23.sql.(The syntaxs of other queries that tpc-ds generates is not suitable for monetdb all, we don't modify them all when the problem occurred). One more question, I can't get your reply email, so I don't know how to reply you, for this case, I could only send a new mail echo time. Thanks! Regards, Rancho

4 3

Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error: drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end; We can then call the table-returning function: select * from cis((select value, 1.0 from test)); If we want to re-join the results with the source table, we could use row_number() or use lateral: -- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2; -- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2; I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression: select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL? Kindly, Mark.

3 4

new MonetDB SQL overview documentation
by martin van dinther 31 May '18

31 May '18

1 0

JIT implementation of the rmath extension
by Mark Clements 30 May '18

30 May '18

The JIT functions by Mark Raasveldt provide a powerful alternative approach to bring C and C++ code into MonetDB. I have provided an alternative implementation of the MonetDB-rmath extension using JIT functions. The JIT implementation is considerably shorter and simpler, and slightly more general - where the code can deal with function arguments that are any combination of scalars and BATs. However, there are indications that the functions are considerably slower. As a minor point, I needed to rename the functions (e.g. using r_ as a prefix) -- otherwise the function names would collide with the function signatures in Rmath.h. To use the JIT-based rmath extension: git clone https://github.com/mclements/MonetDB-rmath.git git checkout jit make sudo make install monetdb create test monetdb release test monetdb set embedc=yes test mclient -d test -s "select r_pnorm(1.96,0,1);" As a challenge for JIT-based extensions, new C functions (e.g. new functions that call Rmath.h, and for which we want to call the new functions in MonetDB) will need a header file that is available to MonetDB. A fragile (non-portable?) solution to find the MonetDB include folder is to use: MONETDBINCLUDE = $(patsubst -I%,%,$(firstword $(shell pkg-config --cflags-only-I monetdb5))) in the Makefile. This assumes that the MonetDB include folder is the first include folder in CFLAGS. The extension-specific header for any new functions can then be copied to that folder: cp rmath_ext.h $(MONETDBINCLUDE) I hope that this is useful. Sincerely, Mark.

1 0

Error: Failed to import - Leftover Data
by Doherty, Adam 23 May '18

23 May '18

Hi all: I am receiving an error "Failed to import table ... Leftover data ..." I am using a simple BASH script and the mclient tool to import my data from hundreds of CSV files. The fields are pipe | separated, text values are surrounded with double-quotes, and rows end with LF (\n). This is the command I am using to process the files - for csvfile in `ls | grep -i 2018.*\.csv$`; do mclient -d mydatabase -s "COPY INTO table FROM '/path/to/csv/files/$csvfile' USING DELIMITERS '|', '\n', '\"'"; done I have a .monetdb file setup in my home directory so as not to need the username and password. Thanks kindly, Adam Doherty

3 5

MonetDB Memory usage and release cached memory
by Murthy, Gautham 17 May '18

17 May '18

Hello, How do we make MonetDB release memory after usage. We are seeing high cached memory on the DB Servers and We also see queries failing with the below error - Memory Allocation Failure : Total Memory - 1123348 Physical Memory - 1123348 Virtual Memory - 2667052 How do we overcome this issue? Thank You, Gautham

1 0

Problem/bug with remote tables
by Bernát Marcell 10 May '18

10 May '18

Hello everyone, I believe I've encountered a bug with the remote table functionality. Here are the steps to replicate it. Tested with MonetDB 11.29.3, both on Ubuntu 14.04.5 LTS and Ubuntu 16.04.4 LTS. The problem persists even after restarting the DB farms and the MonetDB daemons. On node 1 Create a normal table, insert some data and check its contents: create table testtable(id int not null, name text, valid boolean); insert into testtable (id, name) values (1, '1'); insert into testtable (id, name) values (2, '2'); insert into testtable (id) values (3); select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) On node 2 Create a remote table and check its contents to make sure it works: create remote table testtable(id int not null, name text, valid boolean) on 'mapi:monetdb://node1:50000/dbfarm'; select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) Then try to select anything with a WHERE clause: select * from testtable where name is null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.186ms) It seemingly works fine, but now when you try to select anything else, you get the same result: select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (4.466ms) If you log out from mclient and then back in, select * works fine again, so there's no data loss. However, even if you try the opposite of the previous query (is not null instead of is null), you get the same result: select * from testtable where name is not null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.358ms) Can someone else please try this to confirm it's a bug and not something on my end? Thanks in advance. Best regards, Marcell

3 4

Announcement: MonetDB-rmath
by Mark Clements 09 May '18

09 May '18

Dear all, A first version for MonetDB-rmath is now available. It includes approximately 90% of the functions in Rmath.h, including [rpdq](norm|unif|gamma|beta|lnorm|chisq|nchisq|f|t|binom|cauchy|exp|geom|hyper| nbinom|pois|weibull|logis|wilcox|signrank) and another 40 functions. The current implementation uses m4 for code generation and requires the r-mathlib package under Debian. One can clone the GitHub repository and install using: git clone https://github.com/mclements/MonetDB-rmath.git cd MonetDB-rmath make sudo make install You will then need to use a fresh database. An example script is: -- calculate the quantile and cumulative distribution function for a unit normal select qnorm(0.025,0,1), pnorm(-1.96,0,1); -- calculate E(Z^2) for Z~Normal(0,1) select sum(R_pow(value,2)*0.01*dnorm(value,0,1)) from sys.generate_series(-5.0,5.0,0.01); -- random numbers select set_seed(1,2); select rpois(value*0+100) from sys.generate_series(1,10); -- ok select rpois(100) from sys.generate_series(1,10); -- repeats the values 10 times:-< All of the functions are currently scalars. Next steps include adding BAT functions, including some of the constants at the beginning of Rmath.h, and adding some statistical tests. As an example, I have included poissonci(y,boundary) for an exact Poisson confidence interval for count y for a specific boundary (1=left, 2=right) with default confidence level of 95%: select poissonci(10,1), poissonci(10,2); The equivalent code in R would be poisson.test(10). For confidence intervals, it would be nice to have a type that is a pair of doubles. Sincerely, Mark.

2 2

Buggy attempt at MonetDB-rmath
by Mark Clements 03 May '18

03 May '18

I am trying to adapt MonetDB-gsl to use the rmath library. My code is at: https://github.com/mclements/MonetDB-rmath Under Ubuntu 16.04 LTS, I used: sudo apt install r-mathlib git clone https://github.com/mclements/MonetDB-rmath cd MonetDB-rmath make sudo make install // start up MonetDB server with a test database mclient -d test -s "select sys.pchisq(20.0,5.0);" This compiles but when I try "select sys.pchisq(20.0,5.0);" I get the error: SELECT: no such binary operator 'pchisq(decimal,decimal)' Any guidance would be appreciated. (You may ask why one would want to do this when one could use embedded R - I wanted to see how fast and easy it was to use the rmath library.) Kindly, Mark.

2 3