April 2016 - users-list

A good strategy for loading bulk data into monetdb
by Knezevic Nikola 25 Apr '16

25 Apr '16

Hi all, I'm trying to load large amounts of data into monetdb (migrating from mysql). Since I'm using Jul2015-SP4 release, I can't create local files and I can't use external tools, my approach is as following: For each table, I partition the data based on the day, and then: 10 create one table in monetdb for each day, and a merge table that takes all these tables 20 fetch data from mysql (mysql -B -N -C ...) 30 _pipe_ data to mclient (that is invoked with mclient ... -c "COPY 2000000 RECORDS FROM STDIN INTO ..." -) 40 load data into corresponding table (the right day) 50 sleep some seconds 60 goto 20 Each daily table is around 6'000'000 rows, but I have some that have ~150'000'000 rows. However, I noticed that when I'm doing this, I quickly run out of disk space, as monetdb is creating really big logs (in /data/monetdb/some_schema/sql_logs). Size of that folder is usually 600KB, but once the process of migration starts, it goes to several GBs. Not only that, but /data/monetdb/some_schema/bat also grows a lot. The surprising part, and the motivation for this emails, is how well monetdb compresses the data. If I migrate only one day worth of data, and then restart monetdbd (once or twice) and run 'analyze some_schema', sql_logs size drops to normal levels, and I get a lot of reclaimed space (even in bat directory). I tried to put a 'sleep(120)' after each partition and running 'analyze some_schema' after each iteration, but couldn't get the same behaviour (and it is also quite slow). Is there a way to trigger the compression and reduce disk space overhead without restarting mserver, given the above constraints? Thanks, Nikola

3 4

MonetDB Driver / MAPI Implementation.
by Brian Hood 23 Apr '16

23 Apr '16

Hi All, I am have tried to convert the Ruby MonetDB driver into Crystal with little success as i want to MonetDB with the Crystal Programming Language for higher performance and to handle bigger datasets at more C level performance. I'm currently making a transition from Ruby to Crystal and want to know where the specification of MonetDB's MAPI interface lives. Any help appreciated. Regards, Brian Hood

2 1

Merovingian startup error: rapi.prelude missing
by Anderson, David B 23 Apr '16

23 Apr '16

All, I've built MonetDB from source using 11.21.19, but am having problems starting the Merovingian process. I am getting the following error even though I chose -disable-rintegration in the configure step. Does anyone know what I might have done incorrectly? Thanks, Dave 2016-04-22 23:36:11 MSG loan[7218]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-04-22 23:36:11 MSG loan[7218]: # Serving database 'loan', using 24 threads 2016-04-22 23:36:11 MSG loan[7218]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-04-22 23:36:11 MSG loan[7218]: # Found 188.787 GiB available main-memory. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) 1993-July 2008 CWI. 2016-04-22 23:36:11 MSG loan[7218]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-04-22 23:36:11 MSG loan[7218]: # Visit http://www.monetdb.org/ for further information 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:object code for command rapi.prelude missing 2016-04-22 23:36:11 MSG loan[7218]: !TypeException:user.main[14]:'rapi.prelude' undefined in: rapi.prelude(); 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' (7218) has exited with exit status 0 2016-04-22 23:36:11 MSG merovingian[6082]: database 'loan' has shut down 2016-04-22 23:36:15 ERR merovingian[6082]: client error: database 'loan' started up, but failed to open up a communication channel

3 4

force monetdb not to write intermediate results
by Shmagi Kavtaradze 22 Apr '16

22 Apr '16

I have 64gb ram on the server and want monetdb to use ram instead of writing intermediate results. Is there any possibility to do that? Thanks in advance.

1 0

hash join - why hashing on the smaller bat?
by Roberto Cornacchia 22 Apr '16

22 Apr '16

Related to my previous question about persisting hashes, I would like to throw another one. BATsubjoin has a series of heuristics to decide what type of join implementation to use. When using hash-join, the latest rule says: if nothing else applied, build a hash on the smaller bat. Could you tell me what is the rationale for this? >From what I could verify: - when sizes are comparable: it doesn't really make much difference which side is hashed - when sizes differ much: sure, building the hash table on that is much cheaper, but the join as a whole becomes 4-5 times slower then when hashing on the larger bat. In which case hashing on the larger bat is a good option? Cheers, Roberto

2 3

JSON Failed to import
by Ariel Abadi 21 Apr '16

21 Apr '16

Hi! Can someone help me ? Im trying to upload a CSV file with json data, but Im receiving an error. Does anybody has an idea how to solve this? Thks in advance mclient -s"COPY 1 RECORDS INTO traf.jsontbl FROM STDIN USING DELIMITERS '|','\n','\"' NULL AS 'null'" - < j.csv *The data on j.csv is:* 301|"{\"data\":[{\"cdr_dialnumber\":\"6596871820\"}]}" *TABLE traf.xx* sql>\d jsontbl CREATE TABLE "traf"."jsontbl" ( "a" INTEGER, "b" JSON );

3 3

The extra optimization option with configure
by Vijay Krishna 19 Apr '16

19 Apr '16

Hi, Today while compiling from MonetDB source code, I found the following option that could be given with the ./configure step of installation. --enable-optimize enable extra optimization (default=no) Will enabling this gives an extra optimization for the database's performance? Or its simply optimizing the installation process? Either cases, what does this optimize? Thanks, Vijay.

1 0

Some performance oddities in numerical comparisons on merge tables
by Marc Ballarin 18 Apr '16

18 Apr '16

Hi, I have a larger *merge table*, of which the relevant columns in this query should be: "timestamp" TIMESTAMP "abort_flag" TINYINT "timestamp" is roughly ascending, but far from monotonous. "abort_flag" has a low cardinality with the following possible values and number of occurrences: | 0 | 735660 | | 1 | 30119 | | 2 | 1486 | | 4 | 15 | Both columns are completely populated. Total row numbers are: merge table: 3,773,559,881 current partition: 30,754,353 I get the the following performance numbers: sql>select count(*) from mytable where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag in (0); +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (1.0s) sql>select count(*) from mytable where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag = 0; +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (34.0s) So there is a very significant, and IMHO surprising, performance difference between the "equals" and the "in" comparison. Doing the same query directly against the sub table has a much more consistent performance: sql>select count(*) from mytable_20160415 where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag IN (0); +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (618.914ms) sql>select count(*) from mytable_20160415 where timestamp between timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag = 0; +--------+ | L1 | +========+ | 735660 | +--------+ 1 tuple (1.1s) The same can be observed with another column, where ">= 4" is much slower than the (in my case) equivalent "IN (4,5,6)". I guess, for my uses I can always switch to the "in" conditions, because all relevant columns should have very low cardinality, but in general, this seems odd. Is there an explanation for this behaviour, like optimizer problems with merge tables? Or maybe a way to avoid it? Regards, Marc

3 3

回复：Re: Some performance oddities in numerical comparisons on merge tables
by chenkaijiang＠bigknow.com.cn 17 Apr '16

17 Apr '16

I understand that if MonetDB uses the timestamp as a filter (more selective), then it is faster. But does the MonetDB get this speed by purely sequence scan on timestamp column with 3,773,559,881 rows, or does it use some technique such as binary search? > Total row numbers are: > merge table: 3,773,559,881 > current partition: 30,754,353 > > I get the the following performance numbers: > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > in (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.0s) ----- 原始邮件 ----- 发件人：Niels Nes <Niels.Nes(a)cwi.nl> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: Some performance oddities in numerical comparisons on merge tables 日期：2016-4-17 18:31:11 On Fri, Apr 15, 2016 at 05:18:27PM +0200, Marc Ballarin wrote: > Hi, > > I have a larger *merge table*, of which the relevant columns in this > query should be: > "timestamp" TIMESTAMP > "abort_flag" TINYINT > > "timestamp" is roughly ascending, but far from monotonous. > "abort_flag" has a low cardinality with the following possible values > and number of occurrences: > | 0 | 735660 | > | 1 | 30119 | > | 2 | 1486 | > | 4 | 15 | > > Both columns are completely populated. > > Total row numbers are: > merge table: 3,773,559,881 > current partition: 30,754,353 > > I get the the following performance numbers: > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > in (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.0s) > > sql>select count(*) from mytable where timestamp between timestamp > '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and abort_flag > = 0; > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (34.0s) > > So there is a very significant, and IMHO surprising, performance > difference between the "equals" and the "in" comparison. Well not that surprising. The choice on which operator in MonetDB isn't based on the content of the table but using relatively simpel rules. A equality is done before a range, and a range before an in. So in case of the in, the very selective range query is done first. In case of the equality, the equality is done first. Niels > > Doing the same query directly against the sub table has a much more > consistent performance: > sql>select count(*) from mytable_20160415 where timestamp between > timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and > abort_flag IN (0); > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (618.914ms) > > sql>select count(*) from mytable_20160415 where timestamp between > timestamp '2016-04-15 13:05:05' and timestamp '2016-04-15 13:20:05' and > abort_flag = 0; > +--------+ > | L1 | > +========+ > | 735660 | > +--------+ > 1 tuple (1.1s) > > The same can be observed with another column, where ">= 4" is much > slower than the (in my case) equivalent "IN (4,5,6)". > > I guess, for my uses I can always switch to the "in" conditions, because > all relevant columns should have very low cardinality, but in general, > this seems odd. > > Is there an explanation for this behaviour, like optimizer problems with > merge tables? Or maybe a way to avoid it? > > Regards, > Marc > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes(a)cwi.nl _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

2 1

Use buffer instead of cache if possible
by Shmagi Kavtaradze 17 Apr '16

17 Apr '16

I am doing join and bitwise and on a huge data. Monetdb caches 61 GB of ram and writes 152GB on disk. I use dstat for resource monitoring. I see that buffer is not used at all, monetdb just caches memory and used reads/writes. I know monetdb uses mapped files, but is there a possibility to use buffer more than caching? I was not able to find any information how to force process use more buffer. P.S sorry for stupid question, I am not very strong in linux stuff. Thanks in advance.

1 0