- users-list - monetdb.org

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

cannot access db with mserver5
by Stefano Fioravanzo 31 Aug '16

31 Aug '16

Hello, I have been using monetdb starting my db farm with monetdbd and it has always worked fine. I have tried to start manually mserver5 with the command mserver5 --set embedded_py=true --dbpath=/path/to/db --daemon=yes —set mapi_open=true The process starts correctly, but when I try to launch mclient it says InvalidCredentialsException:checkCredentials:invalid credentials for user ‘monetdb’, what can I do? I want to start the database with mserver5 because I need remote access (—set mapi_open=true), is there a way to enable this flag using monetdbd? Thanks, Stefano

3 3

GEOM Module installed in Sys Schema
by Vincent Sheffer 30 Aug '16

30 Aug '16

It seems that the functions of the Geom module is only accessible from the sys schema. That further implies that only users with access to the sys schema can call Geom functions. What is the best way to ensure that all users can access the functions from the Geom module? Geom types seem to work fine. 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 1

segfault remote table count: could not find L1.L1 NOT NULL as L1 L1.L1
by Anton Kravchenko 25 Aug '16

25 Aug '16

Hi there, when I run 'count' on remote table I get a segfault on worker server: 'could not find L1.L1 NOT NULL as L1 L1.L1 Segmentation fault' Here is master(server/client) and worker (server/client), all are living on localhost (as it was shown in example https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… ). master client [akravchenko@hnode1 ~]$ mclient -d mst Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'mst' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE REMOTE TABLE t1 (i int) on 'mapi:monetdb://localhost:50001/rt1'; operation successful (1.357ms) sql>\d REMOTE TABLE sys.t1 sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (16.205ms) *sql>select count(*) from t1;* *an error occurred on connection: Connection lost* *sql>* worker client [akravchenko@hnode1 ~]$ mclient -d rt1 -p 50001 Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'rt1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d TABLE sys.t1 sql>select count(*) from t1; +------+ | L1 | +======+ | 2 | +------+ 1 tuple (0.594ms) worker server [akravchenko@hnode1 ~]$ mserver5 --dbpath=rt1 --set mapi_port=50001 # MonetDB 5 server v11.23.7 "Jun2016-SP1" # Serving database 'rt1', using 4 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 31.357 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ # MonetDB/SQL module loaded *>could not find L1.L1 NOT NULL as L1* *L1.L1* *Segmentation fault* master server [akravchenko@hnode1 ~]$ mserver5 --dbpath=mst # MonetDB 5 server v11.23.7 "Jun2016-SP1" # Serving database 'mst', using 4 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 31.357 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module loaded Thank you, Anton

2 3

Is it possible to access two databases in a single select?
by Pablo S 25 Aug '16

25 Aug '16

Is this possible in MonetDB? select * from database1.table1 t1, database2.table2 t2 where t1.col1 = t2.col2 where database1 and database2 are two databases running simultaneously.

2 1

Concurrent query execution issue
by Stefano Fioravanzo 24 Aug '16

24 Aug '16

Hello, I am trying to execute in parallel a few queries in MonetDB. I have read in the documentation that Monet uses the OCC transaction model so read operations should not block the table. I have 3 CREATE TABLE queries that read from the same source table, I launch 3 different mclient processes and launch one query on each client. The queries execute in parallel as expected, I can see them running in sys.queue(). The first query that completes, creates the table and commits fine. When the other two queries finish their job I can see the message “operation successful” but right after the client prints "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead”. What am I doing wrong? Apart from the newly created table, I did non write anything to the db so it should work fine. Any suggestion would be much appreciated, Thanks, Stefano

2 2

Run two MonetDB databases simoultaneously in Windows
by Pablo S 21 Aug '16

21 Aug '16

I'm trying to run two MonetDB databases simoultaneously in Windows. I named the two databases mydb and mydb2. I changed the database name in M5server.bat to mydb, and added -database=mydb to mclient.bat Then I copied the M5server.bat file and changed the farm to mydb2. I also copied the mclient.bat file and added -database=mydb2. I run the two server bats to start the two databases and that seems to work fine (no errors). Then I run the first mclient.bat and it connects to the server with no issues. But when I run the copied mclient.bat and try to connect using user/password monetdb/monetdb, I get the following error and cannot connect: request for database 'mydb2', but this is database 'mydb', did you mean to connect to monetdbd instead? How to run two databases simoultaneously in Windows? How to connect to two databases at the same time?

3 2

Program contains errors
by Stefano Fioravanzo 19 Aug '16

19 Aug '16

Hello, I will briefly recap the problem I have already reported a few weeks ago: I am using monetdb default branch (I did not compile the stable version because I need embedded python) and I have a large table (~80 GB). A simple query like SELECT * FROM large_table WHERE field=x, fails. mserver5 reports this error: mserver5: gdk_select.c:867: fullscan_int: Assertion `cnt < (bn)->S.capacity' failed. It seems that monet thinks it doesn’t have enough space to do a full scan of the table, maybe? (The machine has 800GB of free disk space and 30GB of RAM - Ubuntu 14.04). I was using a default branch of december 2015, I have tried to use the latest default branch (4/08/16), recompiled, installed but nothing, always the same error. So, as suggested, I have compiled the latest stable release (Jun2016-SP1). Now even a query which does not require monet to read the whole table (like SELECT * FROM large_table LIMIT 10 - This query worked fine in the default branch version) fails with ‘Program contains error’ message by client. Selecting a single column of the table works just fine. Moreover, with the stable release, the same issue arises with the second biggest table of the database (~35 GB). [Here you can find the configuration of MonetDB: http://pastie.org/private/r8gtssg944aqwnigrysa] All queries on all the other tables in the database (they are all much smaller than these two) work fine. I do not understand if the issue is in the size of table or somewhere else. How can I know what ‘error’ the message ‘program contains errors’ is referring to? NOTE: At the beginning, using the default compiled branch everything worked just fine! Every query on my large_table worked as expected. Then I had to recreate the database from scratch so I deleted the dbfarm and created a new one. That’s when this headache started… At this point I don’t know what to do, any help would be MUCH appreciated. Thanks, Stefano

4 7

synchronize data from mysql performance
by wang.tong＠shomop.com 19 Aug '16

19 Aug '16

Hi, I try to synchronize data from mysql to monetdb. Steps: 1. Read the modified rows from mysql table periodically. 2. Delete the rows in monetdb by primary key. (To avoid 'PRIMARY KEY constraint violated' error.) The sql is like 'delete from a where id in (1, 2, 3)'. 3. Insert the rows into monetdb by 'copy into ... from stdin'. I find step 2 may be too slow. When there are 2 million rows, delete 10,000 rows takes about 1 minute. When there are 30 million rows, delete 10,000 rows takes about 20 minute. I have to do join on the 30 million rows table, so I cannot split it. I try to upgrade the server from 2 core 8 G memory to 8 core 32 G memory, but it has no effect. It seems the limitation is CPU, and the deleted operation can only use 1 core. How can I optimize the performance? Is there any better approach to synchronize data? Thanks in advance! OS version: centos-release-7-0.1406.el7.centos.2.5.x86_64 mserver5 --version MonetDB 5 server v11.23.7 "Jun2016-SP1" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 31.0GiB available memory, 8 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: root@jstu87d9w6zj4u (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 wang.tong(a)shomop.com

2 2

database was killed by signal SIGSEGV
by 王彤 19 Aug '16

19 Aug '16

Hi, I try to synchronize data from mysql to monetdb. Total rows is about 15 million. I insert the rows in batch. Each batch has about 10000 rows. I use 'copy into from stdin' as in http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java . The virtual memory continuously grows. When it grows to about 80G, the server is killed by signal SIGSEGV. The database size on disk is about 30GB. Below is the server infomation and error log. Any help would be greatly appreciated! OS version: centos-release-7-0.1406.el7.centos.2.5.x86_64 mserver5 --version MonetDB 5 server v11.23.7 "Jun2016-SP1" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 7.4GiB available memory, 2 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: root@jstu87d9w6zj4u (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 in /data/dbfarm/merovingian.log 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) fails, try to free up space [memory in use=98280928,virtual memory in use=3779503584] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) result [mem=98280928,vm=3779503584] 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.173.87.201:46953 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.153.193.249:55915 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) fails, try to free up space [memory in use=185008352,virtual memory in use=3840344288] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(38862848) result [mem=185008352,vm=3840344288] 2016-08-18 17:29:04 MSG merovingian[10194]: proxying client 10.173.87.201:46963 for database 'shomopcrm' to mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock?database=shomopcrm 2016-08-18 17:29:04 MSG merovingian[10194]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(51838976) fails, try to free up space [memory in use=99455712,virtual memory in use=3766391520] 2016-08-18 17:29:04 ERR shomopcrm[10246]: #GDKmmap(51838976) result [mem=99455712,vm=3766391520] 2016-08-18 17:29:04 MSG merovingian[10194]: database 'shomopcrm' (10246) was killed by signal SIGSEGV 2016-08-18 17:29:04 MSG merovingian[10194]: starting database 'shomopcrm', up min/avg/max: 17s/15h/5d, crash average: 1.00 0.90 0.40 (23-11=12) 2016-08-18 17:29:05 MSG shomopcrm[984]: arguments: /usr/local/bin/mserver5 --dbpath=/data/dbfarm/shomopcrm --set merovingian_uri=mapi:monetdb://jstu87d9w6zj4u:30001/shomopcrm --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/dbfarm/shomopcrm/.mapi.sock --set monet_vault_key=/data/dbfarm/shomopcrm/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-08-18 17:29:05 MSG shomopcrm[984]: # MonetDB 5 server v11.23.7 "Jun2016-SP1" 2016-08-18 17:29:05 MSG shomopcrm[984]: # Serving database 'shomopcrm', using 2 threads 2016-08-18 17:29:05 MSG shomopcrm[984]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-08-18 17:29:05 MSG shomopcrm[984]: # Found 7.390 GiB available main-memory. 2016-08-18 17:29:05 MSG shomopcrm[984]: # Copyright (c) 1993-July 2008 CWI. 2016-08-18 17:29:05 MSG shomopcrm[984]: # Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved 2016-08-18 17:29:05 MSG shomopcrm[984]: # Visit http://www.monetdb.org/ for further information 2016-08-18 17:29:05 MSG shomopcrm[984]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/dbfarm/shomopcrm/.mapi.sock 2016-08-18 17:29:05 MSG shomopcrm[984]: # MonetDB/SQL module loaded in /var/log/messages, there is an error: kernel: mserver5[981]: segfault at 18 ip 00007fe10f845a89 sp 00007fcf38e79760 error 4 in lib_sql.so[7fe10f817000+175000] wang.tong(a)shomop.com

2 1

Distributed Query Processing questions
by Anton Kravchenko 18 Aug '16

18 Aug '16

Hi there, I have few questions on merge/remote tables. Are there known MonetDB users that use(d) merge/remote tables in production? Are there official MonetDB performance benchmarks for merge/remote vs local tables? Thank you, Anton

2 1

distributed query processing example
by Anton Kravchenko 16 Aug '16

16 Aug '16

Hi there, I am trying to follow a distributed query processing example from https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DistributedQuery… and here is my experience: Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016-SP1) Database: MonetDB v11.23.7 (Jun2016-SP1), 'mapi:monetdb://hnode3.secdev.local:52018/db3' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE MERGE TABLE mt1 (t int); operation successful (2.367ms) sql>CREATE REMOTE TABLE t1 (i int) on 'mapi:monetdb://hnode1.secdev.local:52016/db1'; operation successful (1.287ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (4.694ms) *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *Connection lost* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *illegal input, not a JSON header (got '')* *sql>select count(*) from t1;* *an error occurred on connection: Connection lost* *sql>select count(*) from t1;* *Connection lost* sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (508.682ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (6.227ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (5.065ms) sql>select * from t1; +------+ | i | +======+ | 11 | | 13 | +------+ 2 tuples (4.178ms) sql> P.S. Is there something wrong with my monetdb cluster setup or is it a real bug? On hnode3 (CentOS 6.8 machine): [akravchenko@hnode3 ~]$ monetdbd -v MonetDB Database Server v1.7 (Jun2016-SP1) [akravchenko@hnode3 ~]$ monetdb discover location hnode3.secdev.local:52018* mapi:monetdb://hnode3.secdev.local:52018/db3 [akravchenko@hnode3 ~]$ monetdb status name state health remarks db3 R 20h 100% 11m mapi:monetdb://hnode3.secdev.local:52018/db3 [akravchenko@hnode3 ~]$ monetdbd get all hnode3-dbfarm property value hostname hnode3.secdev.local dbfarm hnode3-dbfarm status monetdbd[18052] 1.7 (Jun2016-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode3-dbfarm/merovingian.log pidfile hnode3-dbfarm/merovingian.pid sockdir /tmp listenaddr 0.0.0.0 port 52018 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.52018 controlsock /tmp/.s.merovingian.52018 and on hnode1 (CentOS 6.8 machine): [akravchenko@hnode1 ~]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jun2016-SP1) [akravchenko@hnode1 ~]$ monetdb discover location hnode1.secdev.local:52016* mapi:monetdb://hnode1.secdev.local:52016/db1 [akravchenko@hnode1 ~]$ monetdb status name state health remarks db1 R 4m 15% 32m mapi:monetdb://hnode1.secdev.local:52016/db1 [akravchenko@hnode1 ~]$ monetdbd get all hnode1-dbfarm/ property value hostname hnode1.secdev.local dbfarm hnode1-dbfarm/ status monetdbd[1355] 1.7 (Jun2016-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode1-dbfarm//merovingian.log pidfile hnode1-dbfarm//merovingian.pid sockdir /tmp listenaddr 0.0.0.0 port 52016 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.52016 controlsock /tmp/.s.merovingian.52016 [akravchenko@hnode1 ~]$ Thank you, Anton

1 0

Add a new optimizer pipeline.
by Xu,Wenjian 10 Aug '16

10 Aug '16

Hi all, MonetDB version: 11.23.7 Ubuntu version: 14.04 I created a new optimizer pipeline by adding an additional optimization step after 'default_pipe', i.e.: '*my_customized_pipe*'='optimizer.inline();optimizer.candidates(); optimizer.remap();optimizer.costModel();optimizer.coercions(); optimizer.evaluate();optimizer.pushselect();optimizer.aliases(); optimizer.mitosis();optimizer.mergetable();optimizer.deadcode(); optimizer.aliases();optimizer.constants();optimizer.commonTerms(); optimizer.projectionpath();optimizer.deadcode();optimizer.reorder(); optimizer.reduce();optimizer.matpack();optimizer.dataflow(); optimizer.querylog();optimizer.multiplex();optimizer.generator(); optimizer.profiler();optimizer.garbageCollector(); *optimizer.customizedstep()*' What I have done is: 1) Create an empty optimizer step: -- 1.1 add *opt_customizedstep.c/h* in package *monetdb/optimizer/**, leave function body *OPTcustomizedstepImplementation() *empty*, *i.e. with one statement **return 0** * -- *1.2 register the step by modifying *opt_wrapper.c *and* optimizer.mal* 2) Add the definition of the new pipeline in *opt_pipes.c* 3) Modify the configuration file to compile the added source files. After re-build the system, I can enable the new optimizer pipeline by *set optimizer='my_customized_pipe'*. However, I have two issues: i) using EXPLAIN statement, I found the MAL plan different from the one generated by 'default_pipe' (which should be the same since I did nothing in the added step?). Specifically, the plan seems to be not affected by *optimizer.reorder() *step, i.e., bat.new() and bat.append() instructions appear in the end of the plan. ii) then I entered mdb mode by DEBUG statement. But it seems that some command did not work as before. For example, *help* command does not display all commands available; *optimizer* command does not display actions performed by each optimizer step. What's wrong with my procedure to add a new optimizer pipeline? Any comment would be helpful. Best regards, Wenjian

3 3

[Low level] Understanding duplicate entries in delta BAT (delete BAT)
by Frédéric Jolliton 08 Aug '16

08 Aug '16

Hi, As per MonetDB Solution suggestion, we are asking here about this topic. We are studying how MonetDB store its database on disk. So far we were able to understand pretty much all the files (BAT, journal, BBP.dir, heap, hash, imprints, and so on). We have custom tools to decode and dump them (experimental at this point). There is one thing however that is unclear: we thought that "delta BAT", which are the BAT assigned to an SQL tables to list the OIDs of rows that were deleted (named "D_<schema>_<table>" internaly), would contains only *uniques* values (since you can never delete twice the same row). But on several databases that we are running, we found that some of theses BATs contain duplicates. Lot of duplicates actually. Especially on D_sys__columns and D_sys__tables BATs (respectively for the sys._columns and sys._tables system tables). Some databases do not have this "issue" (while they all have the same schema, and process the same kind of data as the other ones). Can someone explain if duplicates are expected in theses BATs ? Here is an excerpt of D_sys__tables: # hexdump -C 02/210.tail 00000000 2e 00 00 00 00 00 00 00 2f 00 00 00 00 00 00 00 |......../.......| 00000010 30 00 00 00 00 00 00 00 31 00 00 00 00 00 00 00 |0.......1.......| 00000020 32 00 00 00 00 00 00 00 33 00 00 00 00 00 00 00 |2.......3.......| 00000030 34 00 00 00 00 00 00 00 35 00 00 00 00 00 00 00 |4.......5.......| 00000040 36 00 00 00 00 00 00 00 37 00 00 00 00 00 00 00 |6.......7.......| 00000050 42 00 00 00 00 00 00 00 43 00 00 00 00 00 00 00 |B.......C.......| 00000060 44 00 00 00 00 00 00 00 45 00 00 00 00 00 00 00 |D.......E.......| 00000070 46 00 00 00 00 00 00 00 47 00 00 00 00 00 00 00 |F.......G.......| 00000080 48 00 00 00 00 00 00 00 49 00 00 00 00 00 00 00 |H.......I.......| 00000090 4a 00 00 00 00 00 00 00 4b 00 00 00 00 00 00 00 |J.......K.......| 000000a0 4c 00 00 00 00 00 00 00 4d 00 00 00 00 00 00 00 |L.......M.......| 000000b0 4e 00 00 00 00 00 00 00 4f 00 00 00 00 00 00 00 |N.......O.......| 000000c0 50 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |P.......P.......| 000000d0 51 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |Q.......P.......| 000000e0 51 00 00 00 00 00 00 00 52 00 00 00 00 00 00 00 |Q.......R.......| 000000f0 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000100 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| 00000110 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000120 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| ... You can clearly see duplicates OID (the first one being 0x50 at 0xc8). Its entry in BBP.dir (split into sections): 136 32 tmp_210 tmpr_210 02/210 610523782 2 171807 0 0 171807 172032 0 0 0 0 void 0 1 1793 0 0 0 0 0 1000651 0 0 0 oid 8 0 1024 24 25 27 1 46 773603235 1374456 1376256 1 We are using: MonetDB 5 server v11.21.14 (64-bit, 64-bit oids, 128-bit integers). -- Frédéric Jolliton Sécuractive

4 3

Does R run in a sandbox in MonetDB/R?
by Pablo S 07 Aug '16

07 Aug '16

MonetDB 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 entering R statements on a screen; the application will create the functions in the database and 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?

1 0

How could I obtain the running time information in the MAL interface of mclient?
by Xu,Wenjian 07 Aug '16

07 Aug '16

Hi all, MonetDB version: 11.23.7 Ubuntu version: 14.04 How could I obtain the running time information in the MAL interface of mclient? I tried to use the *--interactive* option but it did not work. Specifically, I do the following: =============================================== $ *mclient -dnba2009 -lmsql --interactive=ms* Welcome to mclient, the MonetDB interactive terminal (Jun2016-SP1) Type \q to quit, \? for a list of available commands msql>\<query.txt // I save the MAL instructions of a query (obtained from EXPLAIN statement) in this file msql>user.s1_3("1998"); // call the function of that query % sys.player, sys.player, sys.player # table_name % p_lastname, p_lastseason, p_weight # name % char, int, int # type % 100, 4, 3 # length [ "Wheeler", 1998, 165 ] [ "Baker", 1998, 175 ] [ "Garnett", 1998, 186 ] ...... ======================================================== I would like to obtain the running time information after getting the result of that query. How can I do it? Any help would be appreciated. Best, Wenjian

4 6

Strange behaviour with optimizer.pushselect and batalgebra.like
by Moritz Bruder 05 Aug '16

05 Aug '16

Hi, I get a really strange behaviour with the *pushselect* optimizer for this piece of MAL code: http://codepad.org/bqulPEtV . The problem I'm having is, that depending on whether the *pushselect* optimizer is used or not, the query will yield different results. Context info: 1. MonetDB version number: 11.23.3 2. Operating system platform: Linux 4.6.4-1-ARCH #1 SMP PREEMPT Mon Jul 11 19:12:32 CEST 2016 x86_64 GNU/Linux 3. Compiled from source tarball 4. http://codepad.org/bqulPEtV Regards, Moritz

1 0

Using MonetDB with Scala/Slick
by Pablo S 05 Aug '16

05 Aug '16

I develop in Scala, and need to access a MonetDB database. I use the Scala standard, Slick, however Slick doesn't have a driver to access MonetDB. I'm interested in developing a Slick/MonetDB driver (it's a JDBC wrapper) but I'd like to know first if someone already started the process (we could join forces). Please let me know. Thanks.

1 0

When would the function group.multicolumn() used?
by Xu,Wenjian 03 Aug '16

03 Aug '16

Hi all, I found the following new MAL function in the new release of version 11.23.3: ============================================================ pattern group.multicolumn(b:bat[:any]...) (ref:bat[:oid],grp:bat[:oid],hist:bat[:any]) address GROUPmulticolumngroup; comment Derivation of a group index over multiple columns. ============================================================ I thought I may be used by queries with multiple attributes in the GROUP BY clause. So I tried to EXPLAIN such a query (e.g., SELECT ... FROM... GROUP BY ColA, ColB), but it is still compiled with multiple group.subgroup() followed by a group.subgroupdone(). So, when would this function group.multicolumn() used? Thanks, Wenjian

1 0

a few good stackoverflow qs without answers..
by Anthony Damico 30 Jul '16

30 Jul '16

http://stackoverflow.com/questions/tagged/monetdb?sort=newest&pageSize=50

1 0

infinity value
by Stefano Fioravanzo 26 Jul '16

26 Jul '16

Hello, I am creating a table from the result of a python function. One column is composed of double values, some of them are np.inf and are translated in monet as inf I cannot find a way to query for these values, how does monetdb handle infinity? Moreover, if I later do some query on this table and apply an arithmetic operation to the inf values, monetdb throws ‘overflow in calculation’ error, I would expect any operation (except for special cases) to result in inf. Is this a known issue or an intended design? Regards, Stefano

1 0

mapi.class.php
by Alberto Ferrari 26 Jul '16

26 Jul '16

For those who are interested, here is (attached) the "php_mapi.inc" which I changed to a php class (OOP) Feel free to use or even upload to any repository. Regards.

2 1