April 2018 - users-list

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