April 2018 - users-list

MonetDB Settings and Open Files
by Murthy, Gautham 14 Apr '18

14 Apr '18

Hello, We have MonetDB open files usage reaching 65k and beyond. How do we minimize the openfiles, We see most of them are mapped to memory. What is the storage(); function? What does the storage() function do when we execute it ? The usage of open files drastically reduced when the select *from storage() was executed on the database. What are the effects of running the select *from storage(); when users are accessing the database? What is the maximum size of database MonetDB can support ? Does running the storage() function when there is a large set of open files crash the database ? Do we need to set any system level parameters ? shmmax, file-max etc., Do we have any standard settings as prerequisites that needs to be considered if we are hosting databases ranging 1TB to 2TB? What should be the open files setting if we have 6-10 databases with size ranging from 100GB to 2TB ? Do we have options to export specific schemas rather that complete database or tables? Request you to please review and share the above information. Open files Test Case - DB size 630Gb Max Open files set to - 65536 [oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 01:56:34 Open Files: 6559 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 01:56:34 Open Files Of TCP: 21 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 01:56:35 Open Files - DB Files : 5864 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 01:56:35 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 01:56:35 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 01:56:36 Open Files Of TCP - ESTABLISHED: 15 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 01:56:36 netstat -an |grep tcp : 62 [oracle@lnx1386 scripts]$ sh /home/oracle/scripts/purge_op_fl.sh 50005 TSV_PROD_DB 1>/home/oracle/scripts/logs/purge_op_fl.stout 2>/home/oracle/scripts/logs/purge_op_fl.sterr [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sh /usr/bin/mclient -p ${1} -d ${2} < /home/oracle/scripts/purge_op_fl.sql [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sql select * from storage(); [oracle@lnx1386 scripts]$ [oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 02:09:49 Open Files: 862 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 02:09:49 Open Files Of TCP: 25 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 02:09:50 Open Files - DB Files : 163 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 02:09:50 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of TCP - ESTABLISHED: 19 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 02:09:50 netstat -an |grep tcp : 66 [oracle@lnx1386 scripts]$ Thank You, Gautham

2 1

Window Functions
by Doherty, Adam 13 Apr '18

13 Apr '18

Hello: I am trying to build an event tree from a series timestamps, however for each event I want to be able to return the preceding and succeeding events +/- 2 seconds to capture a complete series of events. I believe this is called lead/lag in other databases. I'm not sure how to proceed in building a CTE, short of pulling all the events out an running a filter function on them in Python. Thanks kindly, Adam Doherty

1 0

statistical MODE function
by Sharma, Sreejith 13 Apr '18

13 Apr '18

All, Is there any statistical MODE function available in MonetDB? If not, has anyone tried it for custom function? Need help here. Regards, Sreejith

2 1

INSERT using multiple cores (inspired by COPY from CSV in LOCKED mode)
by Anton Kravchenko 12 Apr '18

12 Apr '18

Hi there, 1) It looks like INSERTing into tables is bounded by a single CPU. And it kills performance of a query that normally uses multiple CPUs... CREATE TEMP TABLE t1 AS (some 'select query' that uses multiple cores when run standalone) ON COMMIT PRESERVE ROWS; or INSERT into t1 (some 'select query' that uses multiple cores when run standalone); 2) Do you think you could implement INSERT with LOCKED option? (like it was done for COPY from CSV in LOCKED mode) Please let me know whether you think it's doable. Thank you, Anton

1 1

Server Parameter Settings
by Murthy, Gautham 11 Apr '18

11 Apr '18

Hello, Need inputs on the initial prerequisites om server parameter settings - Database Size ranges from 300Gb to 1TB. Optimal sysctl parameters to be set for the server with below configuration - [oracle@lnx1386 ~]$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 128 On-line CPU(s) list: 0-127 Thread(s) per core: 2 Core(s) per socket: 16 Socket(s): 4 NUMA node(s): 4 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E7-4850 v4 @ 2.10GHz Stepping: 1 CPU MHz: 1199.953 BogoMIPS: 4197.77 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 40960K NUMA node0 CPU(s): 0,4,8,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68,72,76,80,84,88,92,96,100,104,108,112,116,120,124 NUMA node1 CPU(s): 1,5,9,13,17,21,25,29,33,37,41,45,49,53,57,61,65,69,73,77,81,85,89,93,97,101,105,109,113,117,121,125 NUMA node2 CPU(s): 2,6,10,14,18,22,26,30,34,38,42,46,50,54,58,62,66,70,74,78,82,86,90,94,98,102,106,110,114,118,122,126 NUMA node3 CPU(s): 3,7,11,15,19,23,27,31,35,39,43,47,51,55,59,63,67,71,75,79,83,87,91,95,99,103,107,111,115,119,123,127 [oracle@lnx1386 ~]$ [oracle@lnx1386 ~]$ free -g total used free shared buff/cache available Mem: 2015 17 1945 0 52 1996 Swap: 63 0 63 [oracle@lnx1386 ~]$ I/O - Storage : 10TB SSD Disks Thank You,

1 0

MonetDB - Issue with "too many open files"
by Sharma, Sreejith 11 Apr '18

11 Apr '18

Hi All, We encountered an issue with "too many open files", however not able to trace the root cause . Any information or suggestions here will be very helpful. We have a DB which has around 10K tables with a volume of 300GB approx sizing. The default "File Descriptor Limit" of 16K was set initially and when we encountered "too many open files" issue with parallel loads. Later, it's increased to 65K and upsized the ulimit assuming this should go through. However, at some point the issue reoccurred now with concurrent select or too many concurrent connections. Is there any optimal "file open" size MonetDB recommends? Or is there an option to clean up these? We noticed too many socket connection also opened which is not released. [cid:image001.png@01D3D1F0.58291540] Regards, Sreejith

1 0

Streaming queries
by Jeff Burnett 10 Apr '18

10 Apr '18

Hello, I am searching a CEP to do streaming queries that is embedded and written in C/C++. I also want it to work on linux. Except for the language requirement, Nesper/Esper would be a perfect fit for me. I have wondered if an extension of MonetDbLite would do the job. For me all the data arrives as a time series, like the price of a stock. This can either happen in real time or the data might be replayed from stored history to test other parts of the system. The goal is to build arbitrary queries that fire events when their conditions are met. For example, a listener may be attached to a query that fires if the value of the input stream has changed more than x% over the last n observations. Other events might fire every day at 4pm, indicating the end of the day. As I said, Nesper/Esper do this out of the box, but Nesper is in .net and esper is in Java. My code base is in c++ and JNI is unappealing. Does MonetdbLite handle these things already? Where might I run into difficulty in trying to adapt monet for this project? Any help is appreciated. Please let me know if I didn’t explain the task very well. Jeff

1 0

MonetDB - Transaction aborted issue
by Sharma, Sreejith 10 Apr '18

10 Apr '18

All, We are getting below error while creating a table. In the current session no other bulk load has happened. CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME" AS SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0; INSERT INTO "SCHEMA_NAME"."TABLE_NAME" SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2"; ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead ~ Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert? Any help here is much appreciated! Regards, Sreejith

3 3

ordered index Error in optimizer garbageCollector
by Anton Kravchenko 06 Apr '18

06 Apr '18

Here is a reproducible example. create table t1(v0 int, v1 char(1)); insert into t1 values (1,'a'),(2,'b'),(3,'c'); create ordered index index_t1_v1 on t1(v1); create table t2(v1 char(1)); insert into t2 values ('a'); 1) gives "Error in optimizer garbageCollector" create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; 2) works as expected create temp table t3 as (select t1.v0 from t1,t2 where t2.v1=t1.v1) on commit preserve rows; 3) works as expected select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1; 4) works as expected drop index index_t1_v1; create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; I am using the latest MonetDB version: MonetDB 5 server v11.29.3 "Mar2018" (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 31.3GiB available memory, 4 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.2k 26 Jan 2017 (compiled with ) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: mockbuild@ (x86_64-redhat-linux-gnu) Compilation: gcc -std=gnu99 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro -Wl,-Bsymbolic-functions Thanks, Anton

2 1