September 2016 - users-list

RAID 0
by Vincent Sheffer 20 Sep '16

20 Sep '16

Are there any MonetDB specific advice/instructions for using a RAID 0 SSD array with MonetDB beyond make sure you have backups? I’m not sure what I’m looking for, other than may be tips on how to configure a dbfarm (i.e. monetdbd/mserver5 properties). Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 2

MonetDB Crystal client code in my article
by Brian Hood 18 Sep '16

18 Sep '16

Hi Guys, I've included a very basic MonetDB client code in the Crystal Language that actually connects to mserver5 performs a query and exits just to demonstrate how easy that is in Crystal to use a C binding but also for creating some more interest in MonetDB. https://github.com/puppetpies/Snippets/blob/master/Crystal%20-%20Ruby%20dyn… I will be publishing it on Medium / Slideshare next week. Regards, Brian Hood

1 0

COPY FROM support compressed files?
by Yves Dorfsman 16 Sep '16

16 Sep '16

Can I COPY FROM directly from compressed files? If so, what format (tried with zstd files, didn't work, but that's very new so not surprised)? Thanks. -- http://yves.zioup.com gpg: 4096R/32B0F416

3 2

Does R run in a sandbox in MonetDB?
by Pablo S 08 Sep '16

08 Sep '16

I'm refering to the MonetDB feature that allows to create R functions that run embedded in the database. For example, you can do: CREATE FUNCTION rapi00() RETURNS TABLE (d INTEGER) LANGUAGE R { seq(1,10); }; Now, I need my users to create dynamically these user defined functions by letting them enter these on the UI; the application will create the function in the database and a process will run them. Problem is that R allows to run operating system level commands, such as list.files(path=".") or even file.remove("some_file") Will MonetDB allow these external interactions with the operating system, or it runs in a sandbox?

2 1

update on tqgen
by Sahasranaman M S 07 Sep '16

07 Sep '16

Hi all, Just an update on *tqgen*, in case anyone is interested - The latest master in github.com/niocs/tqgen now runs around 8 times faster than the earlier version. Also, in the OutFilePat parameter, if there is no YYYYMMDD, then all data goes to a single file, unlike earlier when the file kept getting overwritten. The documentation on the gh page has also been updated. If there is anything that can be done to make it more useful, please let me know and I will try to add it. Thanks, Sahas On Thu, Jul 14, 2016 at 9:29 PM, Sahasranaman M S <sahasr(a)naman.ms> wrote: > Actually, I just remembered tqgen has a couple of other package > dependencies from github, so the best way to download and build it is to > use this command: > > $ go get github.com/niocs/tqgen > $ cd $GOPATH/bin > $ ./tqgen --OutFilePat /path/to/data/tq.YYYYMMDD.csv --NumStk 1000 > --Seed 234532 --DateBeg 20200101 --DateEnd 20210831 > > Thanks, > Sahas > > On Thu, Jul 14, 2016 at 9:18 PM, Sahasranaman M S <sahasr(a)naman.ms> wrote: > >> Hello, >> >> Just a suggestion, if you think the issue is related to the size of the >> data, then you could use a random data generator to test it. We were >> facing a similar issue with shareable data for testing. So I wrote a >> program called tqgen in Golang (https://github.com/niocs/tqgen). >> >> It can be used to generate large amounts of csv data for testing, and to >> share the data, we just need to share the command we used to generate it, >> and the other person can run it to generate the exact same data. >> >> It produces datewise fictional trade/quote data for fictional stocks. It >> puts each date's data into a separate file, and each file is around 128MB, >> so if we run it for a daterange of 1.5 years, it should generate around >> 70GB of data. It takes a while to finish because the data is not >> completely random and it is related to other rows. >> >> To generate, you could run: >> $ git clone https://github.com/niocs/tqgen >> $ cd tqgen >> $ go build tqgen.go >> $ ./tqgen --OutFilePat /path/to/data/tq.YYYYMMDD.csv --NumStk 1000 >> --Seed 234532 --DateBeg 20200101 --DateEnd 20210831 >> >> I usually load them one by one so that I could record load time stats, >> etc, but you could use this bash script (https://github.com/niocs/ >> hcsv/blob/master/hcat) to concatenate them without repeating the >> headers, and then load to MonetDB. (I will later add a parameter to tqgen >> to save to a single file) >> >> To do that, >> $ git clone https://github.com/niocs/hcsv >> $ hcsv/hcat /path/to/data/tq.20*.csv > /path/to/data/tq.big.csv >> >> If this causes the same issue, then others could regenerate the same >> data to try. Hope this helps. >> >> Thanks, >> Sahas >> >> >> >>> On Jul 14, 2016 10:20 AM, "Anthony Damico" <ajdamico(a)gmail.com> wrote: >>> >>>> can you winnow down the problem enough to construct a reproducible >>>> example? thanks >>>> >>>> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >>>> wrote: >>>> >>>>> Hello, >>>>> >>>>> No the data is not public, and testing in on another machine is not an >>>>> easy option right now. >>>>> >>>>> Regards, >>>>> >>>>> Stefano >>>>> >>>>> On 14 Jul 2016, at 15:49, Anthony Damico <ajdamico(a)gmail.com> wrote: >>>>> >>>>> hi, any chance these data are public? if not, can you test on a >>>>> separate box and see if the exact same thing happens or if it's >>>>> hardware-specific? >>>>> >>>>> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >>>>> wrote: >>>>> >>>>>> Hello, >>>>>> >>>>>> I currently have a table with 307524407 rows and 52 columns, it was >>>>>> loaded from a 78GB cvs file, but I do not know how much space the table >>>>>> actually takes on disk. >>>>>> If I query the table with a simple query like: >>>>>> >>>>>> select count(*) from big_table where field =1; >>>>>> >>>>>> mserver5 crashes with ‘segmentation fault' >>>>>> I tried also the following query (adding the LIMIT statement): >>>>>> select count(*) from farmap_movimento where farmacia_id =1 >>>>>> limit 10; >>>>>> >>>>>> and the output from mserver5 was: >>>>>> mserver5: gdk_select.c:869: fullscan_int: Assertion `cnt < >>>>>> (bn)->S->capacity' failed. >>>>>> Aborted >>>>>> >>>>>> Doing a more complex query (the one I am aiming for), with also a >>>>>> group by and a few aggregation functions (sum, min) on the fields, the >>>>>> outputs is: >>>>>> mserver5: gdk_bat.c:2066: BATsetcount: Assertion >>>>>> `b->S->capacity >= cnt' failed. >>>>>> Aborted >>>>>> >>>>>> This seems to be a table size problem, so…is there anything I can do? >>>>>> >>>>>> I am using MonetDB 5 server v11.22.0 >>>>>> The machine has 30GB of RAM and 60GB of swap. >>>>>> Available disk space: ~50GB >>>>>> >>>>>> Any help would be much appreciated, >>>>>> >>>>>> Regards, >>>>>> >>>>>> Stefano >>>>>> >>>>>> _______________________________________________ >>>>>> 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 >>>>> >>>>> >>>>> >>>> _______________________________________________ >>>> users-list mailing list >>>> users-list(a)monetdb.org >>>> https://www.monetdb.org/mailman/listinfo/users-list >>>> >>>> >> >

1 0

Merovingian: allow binds to given ip/interface
by Robert Koch 06 Sep '16

06 Sep '16

Referring to comment #13 of this bug report, I would like to know where exactly I can config listening to 0.0.0.0 instead of 127.0.0.1. As of Jun2016 relaease I cannot connect to Port 50000 from foreign hosts any more. https://www.monetdb.org/bugzilla/show_bug.cgi?id=2407#c13 Thank you Robert

2 1

loopback queries in embedded R
by Stefano Fioravanzo 05 Sep '16

05 Sep '16

Hello, A few months ago I was told that loopback queries for embedded R were not ready yet, but would be after some time. Is the feature ready? If so, how does it work? (I am familiar with loopback queries with embedded python) I am currently using the development default branch, compiled a couple of weeks ago. Thank you, Stefano

2 2

error while entering a newly created database
by Xu,Wenjian 01 Sep '16

01 Sep '16

Hi all, I have downloaded the latest version MonetDB-11.23.7 and built it from source. But I have encountered errors while entering a newly created database: =========================================================== $ monetdbd start ~/monetdb_dbfarm2 $ monetdb create topk_synthetic $ monetdb release topk_synthetic $ mclient -dtopk_synthetic *CREATE FUNCTION: name 'md5' (clob) already in use* *ParseException:SQLparser:CREATE FUNCTION: name 'md5' (clob) already in use* =========================================================== Logs are as follows: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 2016-09-01 09:45:21 MSG merovingian[30268]: starting database 'topk_synthetic', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-09-01 09:45:22 MSG topk_synthetic[30287]: arguments: /usr/local/bin/mserver5 --dbpath=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic --set merovingian_uri=mapi:monetdb://SourceCode:50000/topk_synthetic --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock --set monet_vault_key=/home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # MonetDB 5 server v11.23.7 "Jun2016-SP1" 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Serving database 'topk_synthetic', using 8 threads 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Found 15.625 GiB available main-memory. 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Copyright (c) 1993-July 2008 CWI. 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Visit http://www.monetdb.org/ for further information 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # Listening for UNIX domain connection requests on mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # MonetDB/SQL module loaded 2016-09-01 09:45:22 MSG merovingian[30268]: proxying client (local) for database 'topk_synthetic' to mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock?database=topk_synthetic 2016-09-01 09:45:22 MSG merovingian[30268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # SQL catalog created, loading sql scripts once 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 09_like.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 10_math.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 11_times.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 12_url.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 13_date.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 14_inet.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 15_querylog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 16_tracelog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 17_temporal.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 19_cluster.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 20_vacuum.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 21_dependency_functions.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 22_clients.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 23_skyserver.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 24_zorder.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 25_debug.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 26_sysmon.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 27_rejects.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_jsonstore.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_md5sum.sql 2016-09-01 09:45:22 MSG merovingian[30268]: proxying client localhost:54853 for database 'topk_synthetic' to mapi:monetdb:///home/zeroxwj/monetdb_dbfarm2/topk_synthetic/.mapi.sock?database=topk_synthetic 2016-09-01 09:45:22 MSG merovingian[30268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # SQL catalog created, loading sql scripts once 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 09_like.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 10_math.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 11_times.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 12_url.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 13_date.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 14_inet.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 15_querylog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 16_tracelog.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 17_temporal.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 19_cluster.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 20_vacuum.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 21_dependency_functions.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 22_clients.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 23_skyserver.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 24_zorder.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 25_debug.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 26_sysmon.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 27_rejects.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 39_analytics_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 40_json_hge.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_jsonstore.sql 2016-09-01 09:45:22 MSG topk_synthetic[30287]: # loading sql script: 41_md5sum.sql >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> As far as I understand, those sql scripts (e.g., 09_like.sql) are for initialization purpose. What is the reason that it loads those scripts twice? BTW, I can start an existing database successfully. I have never encountered such errors before. Any suggestion would be helpful. Thanks. Best regards, Wenjian

1 0