- users-list - monetdb.org

回复：Re: SUM() OVER (ORDER BY..)
by chenkaijiang＠bigknow.com.cn 29 Apr '16

29 Apr '16

An alternative way is to use temp tables to get the same result: select c1, sum(c2) over(partition by c3) from t1; ==> create temp table tmp1 (sum_c2 int, c3 int); insert into tmp1 select sum(c2), c3 from t1 group by c3; select c1, sum_c2, c3 from t1 join tmp1 on t1.c3=tmp1.c3; ----- 原始邮件 ----- 发件人：Roberto Cornacchia <roberto.cornacchia(a)gmail.com> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: SUM() OVER (ORDER BY..) 日期：2016-4-29 15:46:57 No, indeed, this syntax is not supported. On 29 April 2016 at 09:41, Anthony Damico <ajdamico(a)gmail.com> wrote: > hmm, that gives me the same error.. > > SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over () > AS tot_wgt, hp FROM mtcars; > > > Error in .local(conn, statement, ...) : > Unable to execute statement 'SELECT > mpg, > wt, > SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, > SUM(wt) over () AS tot_wgt, > hp > FROM mtc...'. > Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'. > > > sorry if i'm doing something silly.. > > > > > On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia < > roberto.cornacchia(a)gmail.com> wrote: > >> I think what you are looking for is SUM(..) OVER(PARTITION BY ..) >> >> Otherwise where would the groups for the sum come from? >> >> Roberto >> >> On 29 April 2016 at 09:28, Anthony Damico <ajdamico(a)gmail.com> wrote: >> >>> hi, monetdb does not support SUM() OVER commands.. does anyone have a >>> smart alternative to implement this? thanks >>> >>> >>> SELECT >>> batch_id, >>> job_count, >>> SUM(job_count) OVER (ORDER BY duration) as cumjobs, >>> SUM(job_count) over () as totjobs, >>> duration >>> FROM >>> test_data ; >>> >>> >>> _______________________________________________ >>> 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 >> >> > > _______________________________________________ > 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

The Meaning of Constants in mapi.h
by Brian Hood 28 Apr '16

28 Apr '16

Hi Guys, #define MOK 0 #define MERROR (-1) #define MTIMEOUT (-2) #define MMORE (-3) #define MSERVER (-4) So far i've discovered MOK with value of 0 means connected to MServer and your query went well. MSERVER with value of -4 means that the server doesn't like the query and its bad SQL. I've not been able to discover what the others mean hope someone can help. I've also discovered that mapi_start_talking no longer seems to be implemented in the library guessing its deprecated. Regards, Brian Hood

1 0

Crystal bindings for MonetDB
by Brian Hood 26 Apr '16

26 Apr '16

Hi Guys, Well its been a long, long day putting this together but i actually have working Crystal Language binding for MonetDB. https://github.com/puppetpies/crystal-monetdb-libmapi/blob/4e55863d453be33b… This is the very first stages. I've managed to count 2 rows so far lol. Regards, Brian Hood

2 1

Merge/Remote table causes segfault
by Anderson, David B 26 Apr '16

26 Apr '16

All, I've been able to isolate the seg-fault in gdb, but am not sure how to proceed. Should I file a bug report? There is a NULL pointer access in q_enqueue_(). The q data pointer is NULL. If I look at the flow inside the flow event d, I see the error: "SQLException:sql.bind:unable to find sys.loan201508(purpose)", so it looks like the root cause is higher up/elsewhere. Dave (gdb) where #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 #1 0x00002b39b92fbbbc in q_enqueue (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:164 #2 0x00002b39b92fdd88 in DFLOWscheduler (flow=0x1c094260, w=0x2b39b96a8e40) at mal_dataflow.c:709 #3 0x00002b39b92fe696 in runMALdataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=399, stk=0x1c08b7d0) at mal_dataflow.c:892 #4 0x00002b39b93cad1c in MALstartDataflow (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, stk=0x1c08b7d0, pci=0x1c056ac0) at language.c:147 #5 0x00002b39b92f9077 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bf02bd0, startpc=1, stoppc=402, stk=0x1c08b7d0, env=0x1be28a30, pcicaller=0x1c08b710) at mal_interpreter.c:631 #6 0x00002b39b92f9418 in runMALsequence (cntxt=0x2b39be9b6328, mb=0x1bda6560, startpc=1, stoppc=0, stk=0x1be28a30, env=0x1be28a30, pcicaller=0x0) at mal_interpreter.c:743 #7 0x00002b39b92f824c in runMAL (cntxt=0x2b39be9b6328, mb=0x1bda6560, mbcaller=0x0, env=0x1be28a30) at mal_interpreter.c:365 #8 0x00002b39b9318b63 in MALengine (c=0x2b39be9b6328) at mal_session.c:637 #9 0x00002b39b9316ebd in runPhase (c=0x2b39be9b6328, phase=4) at mal_scenario.c:515 #10 0x00002b39b931707b in runScenarioBody (c=0x2b39be9b6328) at mal_scenario.c:559 #11 0x00002b39b9317164 in runScenario (c=0x2b39be9b6328) at mal_scenario.c:579 #12 0x00002b39b9317f74 in MSserveClient (dummy=0x2b39be9b6328) at mal_session.c:439 #13 0x00002b39b9317b8b in MSscheduleClient (command=0x1be20190 "p\001\342\033", challenge=0x2b39c017b0c0 "GJMKUZJ4", fin=0x1a3fdf90, fout=0x1bda6a50) at mal_session.c:319 #14 0x00002b39b93d0464 in doChallenge (data=0x1bd88880) at mal_mapi.c:184 #15 0x00002b39b9b4697b in thread_starter (arg=0x1a3fdf60) at gdk_system.c:458 #16 0x00002b39bb03683d in start_thread () from /lib64/libpthread.so.0 #17 0x000000330ded4fdd in clone () from /lib64/libc.so.6 ... (gdb) down #0 0x00002b39b92fbb2f in q_enqueue_ (q=0x1c0089e0, d=0x1c095b78) at mal_dataflow.c:156 156 q->data[q->last++] = d; (gdb) p *q $17 = { size = 0, last = 0, exitcount = 0, data = 0x0, l = { lock = 1 }, s = { sema = { __size = '\000' <repeats 31 times>, __align = 0 }, name = 0x0 } } (gdb) p d $18 = (FlowEvent) 0x1c095b78 (gdb) p *d $19 = { flow = 0x1c094260, pc = 55, blocks = 0, state = 1, clk = 0, cost = -1, hotclaim = 0, argclaim = 0 } (gdb) p *d->flow $20 = { cntxt = 0x2b39be9b6328, mb = 0x1bf02bd0, stk = 0x1c08b7d0, start = 2, stop = 399, status = 0x1c094fe0, error = 0x2b39cc00edc0 "SQLException:sql.bind:unable to find sys.loan201508(purpose)", nodes = 0x1c09a750, edges = 0x1c09c480, flowlock = { lock = 1 }, done = 0x1c0942d0 }

1 1

Fwd: JSON Failed to import
by Ariel Abadi 26 Apr '16

26 Apr '16

Now is working! Thank you guys! Rgds > > > ---------- Forwarded message ---------- > From: Sjoerd Mullender <sjoerd(a)monetdb.org> > Date: 2016-04-21 17:51 GMT-03:00 > Subject: Re: JSON Failed to import > To: users-list(a)monetdb.org > > > This works: > > 301|{"data":[{"cdr_dialnumber":"6596871820"}]} > > So the quoting of the json string doesn't help. > > > On 04/21/2016 09:26 PM, Ariel Abadi wrote: > > 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 > > ); > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > -- > Sjoerd Mullender > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > >

1 0

Merge/Remote tables causing remote server to segfault
by Anderson, David B 25 Apr '16

25 Apr '16

All, I am seeing odd behavior with Remote/Merge tables. I have a pair of servers, one of which has a merge table that combines 12 remote & 12 local tables (24 total) into a single large table. Queries from the main server on the merge table cause the remote server to crash with a SIGSEGV. If I create a merge table which only contains the remote tables (loan2) the remote server doesn't crash, but I get different responses for the same query in the same session: /opt/flash1/gnmaloan$ mclient -d loan Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://malxcs1p:50000/loan' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; (mapi:monetdb://monetdb@malxcs2p/loan) unable to find sys.loan201402(loanseqnum) sql>select asofdate,sum(balance),count(loanseqnum) from loan2 group by asofdate; +------------+--------------------------+----------+ | asofdate | L1 | L2 | +============+==========================+==========+ | 2014-03-01 | 124075979043384 | 9177029 | | 2014-05-01 | 1.2634454179935e+14 | 9264814 | | 2014-07-01 | 126798740013672 | 9335352 | | 2014-09-01 | 126526301326951 | 9407070 | | 2014-11-01 | 1.2604434513281e+14 | 9478920 | | 2015-01-01 | 1.2644932715183e+14 | 9529639 | | 2015-03-01 | 127602153039423 | 9579604 | | 2015-05-01 | 126615476286119 | 9654842 | | 2015-07-01 | 125415108251273 | 9756258 | | 2015-09-01 | 124427385478338 | 9855118 | | 2015-11-01 | 126150962049943 | 9945919 | | 2016-01-01 | 129313947667802 | 10011556 | +------------+--------------------------+----------+ 12 tuples (0.9s) The only thing I see in the crashing server's log is: 2016-04-25 16:55:50 MSG merovingian[28226]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-04-25 16:55:50 MSG merovingian[28226]: database 'loan' (2706) was killed by signal SIGSEGV 2016-04-25 16:56:37 MSG merovingian[28226]: starting database 'loan', up min/avg/max: 2m/1h/2h, crash average: 1.00 0.90 0.33 (12-2=10) 2016-04-25 16:56:37 MSG loan[28022]: arguments: /opt/mr/monetdb/bin/mserver5 --dbpath=/opt/flash2/monetdb2/loan --set merovingian_uri=mapi:monetdb://malxcs2p:50000/loan --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/flash2/monetdb2/loan/.mapi.sock --set monet_vault_key=/opt/flash2/monetdb2/loan/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes Not sure where to start debugging this. I guess I will see if this still occurs with a small number of rows in the base tables. Thanks, Dave

1 0

MonetDB Remote / Merge bug ?
by Brian Hood 25 Apr '16

25 Apr '16

Hi All, It seems when i revert back the original query the first time it breaks and gives a MAL error then the second time it works fine its also very reproduce able. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://mdb-master-01:50000/threatmonitor' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (250.636ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (b.guid = a.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (248.468ms) !6 columns dropped, 2 fields truncated! sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; (mapi:monetdb://monetdb@172.17.0.6/threatmonitor) 'user.l10' undefined in: (rmt2533_X_18_bat_oid_str:bat[:oid,:str],rmt2535_X_21_bat_oid_str:bat[:oid,:str],rmt2537_X_22_bat_oid_int:bat[:oid,:int],rmt2539_X_24_bat_oid_int:bat[:oid,:int],rmt2541_X_25_bat_oid_str:bat[:oid,:str],rmt2543_X_26_bat_oid_str:bat[:oid,:str],rmt2545_X_27_bat_oid_str:bat[:oid,:str],rmt2547_X_28_bat_oid_str:bat[:oid,:str],rmt2549_X_29_bat_oid_str:bat[:oid,:str],rmt2551_X_30_bat_oid_str:bat[:oid,:str],rmt2553_X_31_bat_oid_int:bat[:oid,:int],rmt2555_X_32_bat_oid_int:bat[:oid,:int],rmt2556_X_33_bat_oid_lng:bat[:oid,:lng],rmt2557_X_35_bat_oid_str:bat[:oid,:str],rmt2558_X_36_bat_oid_int:bat[:oid,:int],rmt2559_X_37_bat_oid_str:bat[:oid,:str],rmt2560_X_38_bat_oid_int:bat[:oid,:int],rmt2562_X_39_bat_oid_oid:bat[:oid,:oid]) := user.l10(); sql>select * from ippacket a JOIN tcppacket b on (a.guid = b.guid) limit 1; +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ | guid | recv_date | ip_d | ip_dst | ip_h | ip_id | ip_l | ip_m | ip_o | ip_p | ip_src | ip_sum | ip_t | ip_t | ip_v | guid | recv_date | tcp_ | tcp_d | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ | tcp_ |> : : : f : : len : : en : f : ff : roto : : : os : tl : er : : : data : port : ack : fin : syn : rst : psh : urg : off :> : : : : : : : : : : : : : : : : : : _len : : : : : : : : :> +===========+============+======+============+======+=======+======+======+======+======+============+============+======+======+======+============+============+======+=======+======+======+======+======+======+======+======+ | 0413e543- | 2015-07-09 | Y | 172.17.0.1 | 5 | 28147 | 60 | N | 0 | 6 | 172.17.42. | 19108 | 0 | 64 | 4 | 0413e543-5 | 2015-07-09 | 0 | 15672 | N | N | Y | N | N | N | 10 | : 52b7-ee...> 20:50:32 : : : : : : : : : 1 : : : : : 2b7-eeda-0 : 20:50:32 : : : : : : : : : : : > +0000 : : : : : : : : : : : : : : a77-a2de...> +0000 : : : : : : : : : : +-----------+------------+------+------------+------+-------+------+------+------+------+------------+------------+------+------+------+------------+------------+------+-------+------+------+------+------+------+------+------+ 1 tuple (213.998ms) !6 columns dropped, 2 fields truncated! sql> Regards, Brian Hood

1 0

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

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

Re: How to do profiling in MAL interface?
by Constantine Feng 11 Apr '16

11 Apr '16

Hi Martin, Thanks. stethoscope does do some work. But since it is for online monitoring, it is inconvenient for offline analysis of trace profiles. After reading more docs on the web, I find Tachograph comes closest to what I want. A minor problem is that the schema of the generated files (.trace, .csv, .json) is not given. Somehow I have to guess. And seems that each file format covers a different subset of attributes. - Neo > > ------------------------------ > > Message: 2 > Date: Sat, 9 Apr 2016 08:17:15 +0200 > From: Martin Kersten <martin.kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: How to do profiling in MAL interface? > Message-ID: <57089E6B.7050504(a)cwi.nl> > Content-Type: text/plain; charset=windows-1252; format=flowed > > You better consider using the stethoscope for doing this kind of analysis. > The documentation need some updates. > > > On 09/04/16 06:07, Constantine Feng wrote: > > Hi all, > > > > I know I can use the TRACE SQL command. But I want to do it in the > "-lmal" interface (because I massaged the MAL plans a bit to test my > research ideas). > > > > Particularly, I find the information given on this website ( > https://www.monetdb.org/Documentation/Manuals/MonetDB/Modules/profiler ) > doesn't match the source code. I can't find the profiler.activate() or > profiler.deactivate(), for example. > > > > > > mal>manual.help("profiler.*"); > > profiler.cpuload > > profiler.cpustats > > profiler.cleanup > > profiler.closeStream > > profiler.dumpTrace > > profiler.getSystemTime > > profiler.getUserTime > > profiler.getDiskWrites > > profiler.getDiskReads > > profiler.getTrace > > profiler.noop > > profiler.openStream > > profiler.reset > > profiler.setHeartbeat > > profiler.stop > > profiler.stethoscope > > profiler.tomograph > > > > > > Version: (Jul2015-SP3). Build from source. > > > > - Neo > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > >

1 0

Announcement: New Jul2015-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 11 Apr '16

11 Apr '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2015-SP4 bugfix release MonetDB Common * Fixed a bug that caused various instances where old data returned or where crashes occurred. The problem was that internally data wasn't always marked dirty when it was being changed, causing later processing to not deal with the changed data correctly. Bug Fixes * 3905: MonetDB doesn't handle ANY/SOME/ALL operator correctly * 3929: R aggregate not recognized when using 3 or more parameters * 3965: Not possible to quote/escape single quote character in the name of the file to load. * 3968: Missing double use of column names

1 0

Can not enable python
by Shmagi Kavtaradze 10 Apr '16

10 Apr '16

I created function in python, but when tried to call, I receive : Embedded Python has not been enabled. Start server with --set embedded_py=true I stopped farm. used mserver5 --set embedded_py=true and got in mserver5 console output: # MonetDB/SQL module loaded # MonetDB/Python module loaded Then I started farm and still getting same error. Then I did this: monetdb stop dbname monetdb set embedpy=true dbname monetdb start dbname Still getting same. Any ideas what I am doing wrong?

2 2