- users-list - monetdb.org

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

Problem with COPY INTO
by Knezevic Nikola 15 Apr '16

15 Apr '16

Hi all, I've lost a significant time trying to get data from mysql database into monetdb. Since I have a lot of data, I wanted to make it as fast and automated as possible, but to no avail. There are several issues I encountered, and I'd appreciate any help on any of them. I'm running the latest monetDB on CentOS: % mclient --version mclient, the MonetDB interactive terminal (Jul2015-SP4) support for command-line editing compiled-in character encoding: UTF-8 1. I tried following the last example on https://www.monetdb.org/Documentation/SQLreference/Permissions in order to get another user to be able to perform COPY INTO, but it fails: % mclient -u monetdb -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>GRANT COPY FROM TO copy_role; syntax error, unexpected COPY in: "grant copy" 2. Ok, since I can't run COPY INTO FROM FILE, I decided to go with FROM STDIN version. To do so, I dump the mysql database and import that in monetdb. That also fails: % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;"|mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" % mclient -u writer -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d errortable CREATE TABLE "some_db"."errortable" ( "rejectreason" INTEGER NOT NULL, "text" CHARACTER LARGE OBJECT ); Another try also fails (still using STDIN): % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" <test.out Failed to import table Column value 1 missing % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - <test.out Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" (note different error messages, and also note that 100 is a perfectly good sqlINT) If I try the following, this succeeds. However, this is something I'm trying to avoid, as I'd had to create huge files (100+GB), which is an absolute waste of space and time. % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "errortable" FROM STDIN' instead # obviously, fails due to (1) % mclient -u monetdb -d some_db -s "COPY 15 RECORDS INTO some_db.errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" password: 15 affected rows The file in question is super simple, a list of integers with textual description: 100 Some limit exceeded 101 Another limit exceeded 404 Service temporarily not available 405 Service not available 500 Server not available 700 End of world as we know it 701 User already logged in 10000 Book not found 10001 Price reasonability doubtfull 10002 Client is well known 20001 Activation in progress 20002 Pre-load limit exceeded 30000 Pre-load limit exceeded again 1 test 5 Invalid value 0 to divide with Any hints on how to solve this super annoying problem? Thanks, Nikola

6 12

Re: Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi Martin, Thanks for the reply. Is there any workaround to restrict the memory used by a single query or client connection - like some configuration variables? I see a lot of worker threads executing a single query in parallel. I studied that the number of worker threads can be controlled by the gdk_threads variable. But is there anyway to restrict the maximum memory that can be used by any particular worker thread? Or can this be controlled at the OS level? We thought of restricting the memory used by the entire mserver with ulimit, but that still doesn't solve the problem of the possibility of a single query causing a trouble. Is there any other way that this can be controlled as the OS level? @Martin and Others, If both are NO, then what are the safety measures to be followed in production to avoid such problems? (Or, I may sound little dumb - is this not a production problem at all?) Has anyone faced any such issues? Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305. On Thu, Apr 14, 2016 at 1:20 PM, Vijay Krishna <vijayakrishna55(a)gmail.com> wrote: > Hi Martin, > > Thanks for the reply. > > Is there any workaround to restrict the memory used by a single query or > client connection - like some configuration variables? > > I see a lot of worker threads executing a single query in parallel (please > refer screenshot attached). I studied that the number of worker threads can > be controlled by the gdk_threads variable. But is there anyway to restrict > the maximum memory that can be used by any particular worker thread? > > Or can this be controlled at the OS level? > We thought of restricting the memory used by the entire mserver with > ulimit, but that still doesn't solve the problem of the possibility of a > single query causing a trouble. Is there any other way that this can be > controlled as the OS level? > > @Martin and Others, > If both are NO, then what are the safety measures to be followed in > production to avoid such problems? > (Or, I may sound little dumb - is this not a production problem at all?) > Has anyone faced any such issues? > > Thanks in advance. > > > > > Thanks & Regards, > > Vijayakrishna.P. > Mobile : (+91) 9500402305. > > On Thu, Apr 14, 2016 at 12:15 PM, Martin Kersten <martin.kersten(a)cwi.nl> > wrote: > >> On 14/04/16 08:30, Vijay Krishna wrote: >> >>> Hi, >>> >>> As per MonetDB's architecture, the memory allocated to a single query >>> (thread) is left with the OS and is not controlled by the mserver. >>> >>> If so, I am wondering how MonetDB would react to the following scenario. >>> >>> I receive a client connection with a single costly query whose execution >>> would require all of the RAM's memory. >>> >> Hi >> >> Both queries will run concurrently and fight for the total memory. >> However, after some time, the oldest query is put on lower priority >> >> >>> Now, when I receive a second connection that runs a similar costly >>> operation, will MonetDB start processing this second query by using the >>> available meagre memory (RAM leftovers or from disk swap) or will MonetDB >>> wait till the first query releases some of >>> its memory? >>> >>> >>> >>> Thanks & Regards, >>> >>> Vijayakrishna.P. >>> Mobile : (+91) 9500402305. >>> >>> >>> _______________________________________________ >>> 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

Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi, As per MonetDB's architecture, the memory allocated to a single query (thread) is left with the OS and is not controlled by the mserver. If so, I am wondering how MonetDB would react to the following scenario. I receive a client connection with a single costly query whose execution would require all of the RAM's memory. Now, when I receive a second connection that runs a similar costly operation, will MonetDB start processing this second query by using the available meagre memory (RAM leftovers or from disk swap) or will MonetDB wait till the first query releases some of its memory? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Strange behaviour of epoch() when converting from Unix time
by Marc Ballarin 12 Apr '16

12 Apr '16

Hi, I am observing a surprising behaviour of the epoch function: The following works as expected: SELECT epoch(now()) AS "NOW", epoch(now() - INTERVAL '5' MINUTE) AS "5 MIN AGO"; +------------+------------+ | NOW | 5 MIN AGO | +============+============+ | 1460453860 | 1460453560 | +------------+------------+ The inverse does not: SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | +----------------------------+----------------------------+ The "now" part is fine, the "300 seconds ago" part is obviously not what I expect. Am I missing something or is this a bug? Regards, Marc

2 1

Check reads per sec and CPU utilization
by Shmagi Kavtaradze 11 Apr '16

11 Apr '16

I was not able to find any information how to check CPU utilization and read per sec for a query. Any ideas how to get these results? Thanks in advance.

3 3