- users-list - monetdb.org

mclient backward compatibility
by Roberto Cornacchia 18 Apr '18

18 Apr '18

mclient Mar2018 is not really compatible with mserver5 Jul2017. Not a real issue for me, but I'm just curious: is mclient supposed to be compatible with an earlier release of mserver5 (at least the previous release)? $ mclient x -u monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Mar2018) Database: MonetDB v11.27.14 (unreleased), 'mapi:monetdb:// photon.hq.spinque.com:55500/x' Type \q to quit, \? for a list of available commands auto commit mode: on sql>-- \d silently fails (nothing dumped) sql>\d sql> sql>-- \d on a table doesn't find table comments (introduced in Mar2018) sql>\d tables SELECT: no such table 'comments' Shouldn't queries that rely on the system catalog be defined server-side?

2 1

double precision IS NULL does not work ?
by Nicolas Paris 16 Apr '18

16 Apr '18

Hello I have those DOUBLE PRECISION fields, sometimes containing null values: sql>select count(1), range_low from omop.measurement group by range_low; +-----------+--------------------------+ | L2 | range_low | +===========+==========================+ | 304875924 | null | | 9448250 | 50 | | 16519843 | 0 | | 13198932 | 10 | | 14364002 | 20 | | 3964808 | 30 | | 2998528 | 3 | | 855829 | 2 | +-----------+--------------------------+ 8 tuples However, I am not able to filter based on is null: sql>select count(1) from omop.measurement where range_low is null; +------+ | L3 | +======+ | 0 | +------+ 1 tuple I am not able to understand if this behavior is normal regarding the documentation: "The types REAL, FLOAT and DOUBLE map to the underlying implementation system. No special attention is given to the value NaN." Thanks

2 1

mserver5 process consuming resources while not busy
by Nicolas Paris 15 Apr '18

15 Apr '18

Hello Sorry, I gave up digging in the mailing list because didn't found search functionality there ( https://www.monetdb.org/pipermail/users-list/ ) Then I wonder why I get that result when running "top" command on my server: > PID UTIL. PR NI VIRT RES SHR S %CPU %MEM TEMPS+ COM. > 3297 monetdb 20 0 53,007g 9,028g 8,396g S 100,5 28,8 117:20.95 mserver5 There is no query running AFAIK (select * from sys.queue is empty). I loaded my database several hours before from scratch, and then ran some sql. How can I figure out what appends in the background ? Is it indexing based on queries I made ? Apparently the disk is stable, by mean there is no creation of new indexes, or files under the hood. Any insight welcome, Thanks,

1 0

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