February 2011 - users-list

[MonetDB-users] Kernel / FS tuning
by Franck Routier 18 Feb '11

18 Feb '11

Hi, I was wondering if anyone has suggestions about what tweaks might best suit MonetDB's needs regarding : - (linux) kernel parameters : memory management, ... - FS (type and parameters) : block size, write barriers, ... Is there any kind of best practice or even some thoughts on this ? Thanks in advance for your inputs, Franck

1 0

[MonetDB-users] Problem starting monetdbd after installing MonetDB
by Babis Nikolaou 10 Feb '11

10 Feb '11

Hi all, i get the following error after installing MonetDB from CVS (tip) and running monetdb/d. $ monetdbd cannot open config file (null) unable to retrieve startup status $ monetdb cannot open config file (null) As opposed to the previous version i had (a version of CVS during November), now there is no /etc subdirectory inside the MonetDB installation directory, where the configuration files lie. I also searched for any configuration files inside the build directory, in case sth had gone wrong during installation, but nothing has been generated. I also noticed that the installation procedure when compiling from CVS has been changed. Do i miss something? I just run bootstrap, configure, make, and last make install. One last remark: in the manpage of monetdbd (and monetdb), the configuration file in question is referenced as @MONETDB5_CONFFILE@. Shouldn't had been mapped to a filename? Thanks, Babis

3 3

[MonetDB-users] Bug in EXTRACT?
by Alex Bo. 06 Feb '11

06 Feb '11

Hi, There seems to be a bug in the October SP. If you run this query: SELECT EXTRACT(YEAR FROM f1 .a1) as a2, EXTRACT(YEAR FROM f1 .a1) as a3 FROM (select '1996-02-01' as a2, '1996-07-01' as a1) f1 it returns: 1996, 1996-02-01 Instead of: 1996, 1996 The second column is returned as timestamp instead of int. Also, this does not happen if the first aliases (a2, a3) are changed to different names, nor does it happen if the two EXTRACTS are not identical. If one is YEAR and the second is MONTH it works, but if both are MONTH the same bug happens. I tried it also on the August build and it works fine there. Any ideas? -- View this message in context: http://old.nabble.com/Bug-in-EXTRACT--tp30855882p30855882.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] query slowdown with concurrent write
by Bennett Hiles 04 Feb '11

04 Feb '11

Hi Martin and Stefan, Thanks for the quick response, and sorry if this created another thread, I'm not familiar with how to use this mailing list. I am using: MonetDB v5.22.3 on ubuntu 9.04 64bit Queries and data loading use the php library. To further explain my use case, I am planning on having monetdb be updated about once a minute with 1000-10000 new rows of data. At the same time I will be having as many clients as possible making queries on the database. I have so far seen that 5 clients can be handled with satisfiable response times. Is this a suitable application for monetdb? The query times I have seen so far are great. My problems are handling the queries with concurrent writes and if possible allowing for more clients to make simultaneous queries. Re: Martin's questions: I tried using the recycle optimizer in the default pipe and my queries didn't slow down, which is awesome. But, my problem now is that the values aren't actually added to the dataset until an insert transaction occurs when I am not querying the database. Therefore, if I continuously query the database then the inserts are never seen, but if I stop querying for a second and another insert happens the database is updated. I am fine with some delay in the insert time, but is there anyway I could have it executed slowly alongside the queries, or am I too greedy :) The pipe I created is the same as the default pipe with the recycler optimizer added: bennett_pipe=inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector My schema and and an example query are: CREATE TABLE my_test_table ( account_id bigint, dim1 bigint, dim2 bigint, dim3 bigint, dim5 varchar(50), dim6 varchar(50), dim7 varchar(256), dim8 bigint, dim9 varchar(256), dim10 varchar(256), unique_id bigint, my_time timestamp, summable int ); QUERY: select dim5, sum(summable), count(distinct unique_id) from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; Re Stefan's questions: The results from the trace are below. I apologize if any of the data looks incorrect, I scrubbed my table's field names to be more generic. I let the database cool down for 1 minute and 10 minutes, but the queries remained slow. If I restarted the database, the queries resumed their quick response times. FAST QUERY TIME: sql>trace select dim5, sum(summable) as summables, count(distinct unique_id) as unqiue_ids from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; +-----------------------+--------+--------+ | dim1 | summables | unique_ids | +=======================+========+========+ | test1 | 251 | 18 | | test2 | 48 | 11 | | test3 | 1 | 1 | | test4 | 2 | 1 | | test4 | 106 | 2 | +-----------------------+--------+--------+ 5 tuples (125.534ms) +-------+------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+==========================================================================================+ | 3 | _5 := sql.mvc(); | | 18 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_id | : : ",2); : | 7 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","accou | : : nt_id",0); : | 4 | _15:bat[:oid,:lng] <tmp_1102>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_id | : : ",1); : | 6 | _18:bat[:oid,:oid] <tmp_1357>[0] := sql.bind_dbat(_5=21685368,"sys","my_test_table",1); | | 21 | _13<tmp_1527>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat[: | : : oid,:lng][0]); : | 4 | _19<tmpr_1357>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 10 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=21685368,"sys","my_test_table" | : : ,"my_time",0); : | 27 | _14<tmp_1532>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 11 | _23 := calc.timestamp(A1="2010-07-03 00:00:00"); | | 12 | _17<tmp_1527>[8000000] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 4 | _24 := calc.timestamp(A2="2010-07-10 00:00:00"); | | 7 | _20<tmp_1532>[8000000] := algebra.kdifference(_17=nil,_19=<tmpr_1357>[0]); | | 4 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=21685368,"sys","my_test_table","my_t | : : ime",2); : | 29575 | _25<tmp_1531>[359554] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 00: | : : 00:00.000,_24=2010-07-10 00:00:00.000,true,false); : | 12 | _31:bat[:oid,:timestamp] <tmp_1132>[0] := sql.bind(_5=21685368,"sys","my_test_table","my_t | : : ime",1); : | 24 | _28<tmp_1527>[359554] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestamp] | : : [0]); : | 23 | _32<tmp_1536>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 | : : .000,_24=2010-07-10 00:00:00.000,true,false); : | 35 | _29<tmp_1501>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00:00 | : : .000,_24=2010-07-10 00:00:00.000,true,false); : | 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","dim5",0); : | 12 | _30<tmp_1534>[359554] := algebra.kunion(_28=nil,_29=nil); | | 5 | _44:bat[:oid,:str] <tmp_1103>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5 | : : e",2); : | 23 | _33<tmp_1501>[359554] := algebra.kunion(_30=nil,_32=nil); | | 6 | _46<tmp_1536>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:bat[ | : : :oid,:str][0]); : | 8 | _34<tmp_1534>[359554] := algebra.kdifference(_33=nil,_19=nil); | | 9 | _47<tmp_1501>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); | | 4 | _48:bat[:oid,:str] <tmp_1104>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5",1); : | 10 | _50<tmp_1527>[8000000] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); | | 6 | _66:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",0); : | 3 | _68:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",2); : | 7 | _69<tmp_1501>[8000000] := algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:bat[ | : : :oid,:lng][0]); : | 9 | _70<tmp_1541>[8000000] := algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); | | 4 | _71:bat[:oid,:lng] <tmp_1102>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",1); : | 8 | _74<tmp_1501>[8000000] := algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); | | 4 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable",2) | : : ; : | 7 | _59<tmp_1541>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:bat[ | : : :oid,:int][0]); : | 8 | _60<tmp_1524>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 3 | _61:bat[:oid,:int] <tmp_1066>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable",1) | : : ; : | 8 | _62<tmp_1541>[8000000] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 15525 | _35<tmp_1536>[359554] := algebra.semijoin(_20=nil,_34=nil); | | 2446 | _36<tmp_1534>[139] := algebra.uselect(_35=nil,A0=76:lng); | | 10 | _37<tmp_1536>[139] := algebra.markT(_36=nil,0@0:oid); | | 4 | _38<tmpr_1536>[139] := bat.reverse(_37=nil); | | 33 | _75<tmp_1477>[139] := algebra.leftjoin(_38=<tmpr_1536>[139],_74=nil); | | 47 | _51<tmp_1403>[139] := algebra.leftjoin(_38=<tmpr_1536>[139],_50=nil); | | 4 | _76<tmp_1477>[139] := algebra.selectNotNil(_75=nil); | | 47 | (ext61<tmp_1502>[5],grp59<tmp_1476>[139]) := group.done(_51=<tmp_1403>[139]); | | 19 | _63<tmp_1363>[139] := algebra.leftjoin(_38=nil,_62=nil); | | 8 | _54<tmp_1541>[5] := bat.mirror(ext61=<tmp_1502>[5]); | | 3 | _64<tmp_1363>[139] := algebra.selectNotNil(_63=nil); | | 47 | _55<tmp_1471>[5] := algebra.join(_54=<tmp_1541>[5],_51=nil); | | 43 | (_77<tmp_1344>[33],grp85) := group.derive(ext61=nil,grp59=<tmp_1476>[139],_76=<tmp_1477> | : : [139]); : | 6 | _79<tmp_1527>[33] := bat.mirror(_77=nil); | | 22 | _80<tmp_1506>[33] := algebra.join(_79=nil,_76=nil); | | 20 | _81:bat[:oid,:wrd] <tmp_1527>[5] := aggr.count(_80=nil,grp59=<tmp_1476>[139],_54=<tmp_15 | : : 41>[5]); : | 21 | _65:bat[:oid,:lng] <tmp_1536>[5] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 63484 | barrier _137 := language.dataflow(); | | 9 | _82 := sql.resultSet(3,1,_55=<tmp_1471>[5]); | | 7 | sql.rsColumn(_82=1,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=1,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 5 | sql.rsColumn(_82=1,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 3 | _99 := io.stdout(); | | 41 | sql.exportResult(_99=14711568,_82=1); | | 83716 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00"); | +-------+------------------------------------------------------------------------------------------+ SLOW QUERY TIME: sql>trace select dim5, sum(summable) as summables, count(distinct unique_id) as unqiue_ids from my_test_table where account_id = 76 and my_time >= '2010-07-03 00:00:00' and my_time < '2010-07-10 00:00:00' group by dim5; +-----------------------+--------+--------+ | dim1 | summables | unique_ids | +=======================+========+========+ | test1 | 251 | 18 | | test2 | 48 | 11 | | test3 | 1 | 1 | | test4 | 2 | 1 | | test4 | 106 | 2 | +-----------------------+--------+--------+ 5 tuples (2.7s) +---------+----------------------------------------------------------------------------------------+ | ticks | stmt | +=========+========================================================================================+ | 3 | _5 := sql.mvc(); | | 20 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","account_ | : : id",2); : | 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","acc | : : ount_id",0); : | 25 | _13<tmp_1705>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 4 | _15:bat[:oid,:lng] <tmp_1664>[10] := sql.bind(_5=21685368,"sys","my_test_table","account | : : _id",1); : | 20 | _14<tmp_1706>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 4 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=21685368,"sys","my_test_table",1); | | 8 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 31627 | _51<tmp_1670>[0] := algebra.leftjoin(_38=<tmpr_1665>[0],_50=nil); | | 20 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","my_time",0); : | 10 | _23 := calc.timestamp(A1="2010-07-03 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-07-10 00:00:00"); | | 7243 | (ext61<tmp_1710>[0],grp59<tmp_1661>[0]) := group.done(_51=<tmp_1670>[0]); | | 16 | _54<tmp_1665>[0] := bat.mirror(ext61=<tmp_1710>[0]); | | 47358 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil); | | 6 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil); | | 13 | (_77<tmp_1710>[0],grp85) := group.derive(ext61=nil,grp59=<tmp_1661>[0],_76=<tmp_1651>[ | : : 0]); : | 16 | _79<tmp_1674>[0] := bat.mirror(_77=nil); | | 11 | _80<tmp_1710>[0] := algebra.join(_79=nil,_76=nil); | | 10 | _81:bat[:oid,:wrd] <tmp_1651>[0] := aggr.count(_80=nil,grp59=<tmp_1661>[0],_54=<tmp_16 | : : 65>[0]); : | 20 | _55<tmp_1711>[0] := algebra.join(_54=<tmp_1665>[0],_51=nil); | | 11 | _65:bat[:oid,:lng] <tmp_1674>[0] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 11 | _82 := sql.resultSet(3,1,_55=<tmp_1711>[0]); | | 9 | sql.rsColumn(_82=235,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=235,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 4 | sql.rsColumn(_82=235,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 2 | _99 := io.stdout(); | | 30 | sql.exportResult(_99=24880032,_82=235); | | 420626 | _17<tmp_1705>[8000010] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 17 | _20<tmp_1665>[8000010] := algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); | | 13 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=21685368,"sys","my_test_table","my | : : _time",2); : | 5 | _31:bat[:oid,:timestamp] <tmp_1704>[10] := sql.bind(_5=21685368,"sys","my_test_table","m | : : y_time",1); : | 13 | _32<tmp_1673>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: | : : 00.000,_24=2010-07-10 00:00:00.000,true,false); : | 8 | _39:bat[:oid,:str] <tmp_1364>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","dim5",0); : | 4 | _44:bat[:oid,:str] <tmp_1103>[0] := sql.bind(_5=21685368,"sys","my_test_table","dim5",2); : | 7 | _46<tmp_1661>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:str],_44=<tmp_1103>:ba | : : t[:oid,:str][0]); : | 16 | _47<tmp_1706>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:str]); | | 3 | _5 := sql.mvc(); | | 4 | _48:bat[:oid,:str] <tmp_1631>[10] := sql.bind(_5=21685368,"sys","my_test_table","dim5",1); : | 21 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","account_ | : : id",2); : | 5 | _15:bat[:oid,:lng] <tmp_1637>[10] := sql.bind(_5=25090760,"sys","my_test_table","account | : : _id",1); : | 4 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=25090760,"sys","my_test_table",1); | | 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 5 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","my_time",0); : | 8 | _23 := calc.timestamp(A1="2010-07-27 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-08-03 00:00:00"); | | 31883 | _25<tmp_1712>[391659] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-27 0 | : : 0:00:00.000,_24=2010-08-03 00:00:00.000,true,false); : | 11 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=25090760,"sys","my_test_table","my | : : _time",2); : | 21 | _28<tmp_1674>[391659] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 33 | _29<tmp_1714>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: | : : 00.000,_24=2010-08-03 00:00:00.000,true,false); : | 11 | _30<tmp_1713>[391659] := algebra.kunion(_28=nil,_29=nil); | | 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] := sql.bind(_5=25090760,"sys","my_test_table","m | : : y_time",1); : | 12 | _32<tmp_1714>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-07-27 00:00: | : : 00.000,_24=2010-08-03 00:00:00.000,true,false); : | 10 | _33<tmp_1674>[391659] := algebra.kunion(_30=nil,_32=nil); | | 7 | _39:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",0); : | 4 | _44:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","unqiue_id",2); : | 6 | _46<tmp_1713>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 12 | _47<tmp_1715>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); | | 4 | _48:bat[:oid,:lng] <tmp_1657>[10] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",1); : | 366305 | _50<tmp_1713>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); | | 14 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=25090760,"sys","my_test_table","summable", | : : 2); : | 17 | _59<tmp_1715>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 16 | _60<tmp_1716>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1362>[10] := sql.bind(_5=25090760,"sys","my_test_table","summable" | : : ,1); : | 5 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","acc | : : ount_id",0); : | 10 | _13<tmp_1717>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 298001 | _62<tmp_1715>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 21 | _14<tmp_1716>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 374628 | _17<tmp_1717>[8000010] := algebra.kunion(_14=nil,_15=nil:bat[:oid,:lng]); | | 20 | _20<tmp_1716>[8000010] := algebra.kdifference(_17=nil,_19=<tmpr_1070>[0]); | | 15 | _34<tmp_1720>[391659] := algebra.kdifference(_33=nil,_19=nil); | | 65082 | _35<tmp_1674>[391659] := algebra.semijoin(_20=nil,_34=nil); | | 2960 | _36<tmp_1714>[0] := algebra.uselect(_35=nil,A0=239:lng); | | 12 | _37<tmp_1674>[0] := algebra.markT(_36=nil,0@0:oid); | | 5 | _38<tmpr_1674>[0] := bat.reverse(_37=nil); | | 58011 | _51<tmp_1651>[0] := algebra.leftjoin(_38=<tmpr_1674>[0],_50=nil); | | 26 | (ext61<tmp_1710>[0],grp59<tmp_1713>[0]) := group.done(_51=<tmp_1651>[0]); | | 5 | _54<tmp_1674>[0] := bat.mirror(ext61=<tmp_1710>[0]); | | 27086 | _63<tmp_1356>[0] := algebra.leftjoin(_38=nil,_62=nil); | | 24 | _55<tmp_1715>[0] := algebra.join(_54=<tmp_1674>[0],_51=<tmp_1651>[0]); | | 3 | _66<tmp_1651>[0] := algebra.selectNotNil(_51=nil); | | 11 | (_67<tmp_1710>[0],grp75) := group.derive(ext61=nil,grp59=<tmp_1713>[0],_66=<tmp_1651>[ | : : 0]); : | 10 | _69<tmp_1712>[0] := bat.mirror(_67=nil); | | 5 | _70<tmp_1710>[0] := algebra.join(_69=nil,_66=nil); | | 9 | _71:bat[:oid,:wrd] <tmp_1651>[0] := aggr.count(_70=nil,grp59=<tmp_1713>[0],_54=<tmp_16 | : : 74>[0]); : | 3 | _64<tmp_1356>[0] := algebra.selectNotNil(_63=nil); | | 11 | _65:bat[:oid,:lng] <tmp_1712>[0] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 1226556 | barrier _119 := language.dataflow(); | | 9 | _72 := sql.resultSet(3,1,_55=<tmp_1715>[0]); | | 8 | sql.rsColumn(_72=236,"sys.my_test_table","unique_id","bigint",64,0,_55=nil); | | 4 | sql.rsColumn(_72=236,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 3 | sql.rsColumn(_72=236,"sys.my_test_table","unique_ids","wrd",64,0,_71=nil:bat[:oid,:wrd]); | | 3 | _85 := io.stdout(); | | 37 | sql.exportResult(_85=24880032,_72=236); | | 2 | _5 := sql.mvc(); | | 18 | _11:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","account_ | : : id",2); : | 4 | _15:bat[:oid,:lng] <tmp_1637>[10] := sql.bind(_5=25090760,"sys","my_test_table","account | : : _id",1); : | 3 | _18:bat[:oid,:oid] <tmp_1070>[0] := sql.bind_dbat(_5=25090760,"sys","my_test_table",1); | | 3 | _19<tmpr_1070>[0] := bat.reverse(_18=nil:bat[:oid,:oid]); | | 6 | _21:bat[:oid,:timestamp] <tmp_1412>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","my_time",0); : | 8 | _23 := calc.timestamp(A1="2010-06-24 00:00:00"); | | 3 | _24 := calc.timestamp(A2="2010-07-01 00:00:00"); | | 18 | _6:bat[:oid,:lng] <tmp_1410>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","acc | : : ount_id",0); : | 678 | _13<tmp_1713>[8000000] := algebra.kdifference(_6=nil:bat[:oid,:lng],_11=<tmp_1101>:bat | : : [:oid,:lng][0]); : | 22 | _14<tmp_1674>[8000000] := algebra.kunion(_13=nil,_11=nil:bat[:oid,:lng]); | | 49964 | _25<tmp_1714>[372221] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-06-24 0 | : : 0:00:00.000,_24=2010-07-01 00:00:00.000,true,false); : | 14 | _27:bat[:oid,:timestamp] <tmp_1131>[0] := sql.bind(_5=25090760,"sys","my_test_table","my | : : _time",2); : | 21 | _28<tmp_1713>[372221] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 24 | _29<tmp_1720>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: | : : 00.000,_24=2010-07-01 00:00:00.000,true,false); : | 11 | _30<tmp_1712>[372221] := algebra.kunion(_28=nil,_29=nil); | | 4 | _31:bat[:oid,:timestamp] <tmp_1660>[10] := sql.bind(_5=25090760,"sys","my_test_table","m | : : y_time",1); : | 13 | _32<tmp_1720>[0] := algebra.uselect(_31=nil:bat[:oid,:timestamp],_23=2010-06-24 00:00: | : : 00.000,_24=2010-07-01 00:00:00.000,true,false); : | 10 | _33<tmp_1713>[372221] := algebra.kunion(_30=nil,_32=nil); | | 5 | _39:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",0); : | 5 | _44:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",2); : | 6 | _46<tmp_1712>[8000000] := algebra.kdifference(_39=nil:bat[:oid,:lng],_44=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 8 | _47<tmp_1717>[8000000] := algebra.kunion(_46=nil,_44=nil:bat[:oid,:lng]); | | 3 | _48:bat[:oid,:lng] <tmp_1657>[10] := sql.bind(_5=25090760,"sys","my_test_table","unique_id",1); : | 1381664 | _50<tmp_1661>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:str]); | | 17 | _66:bat[:oid,:lng] <tmp_1413>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",0); : | 4 | _68:bat[:oid,:lng] <tmp_1101>[0] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",2); : | 19 | _69<tmp_1706>[8000000] := algebra.kdifference(_66=nil:bat[:oid,:lng],_68=<tmp_1101>:ba | : : t[:oid,:lng][0]); : | 17 | _70<tmp_1721>[8000000] := algebra.kunion(_69=nil,_68=nil:bat[:oid,:lng]); | | 4 | _71:bat[:oid,:lng] <tmp_1703>[10] := sql.bind(_5=21685368,"sys","my_test_table","unique_id",1); : | 405606 | _50<tmp_1712>[8000010] := algebra.kunion(_47=nil,_48=nil:bat[:oid,:lng]); | | 15 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=25090760,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=25090760,"sys","my_test_table","summable", | : : 2); : | 20 | _59<tmp_1717>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 17 | _60<tmp_1722>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1362>[10] := sql.bind(_5=25090760,"sys","my_test_table","summable" | : : ,1); : | 374062 | _74<tmp_1706>[8000010] := algebra.kunion(_70=nil,_71=nil:bat[:oid,:lng]); | | 16 | _56:bat[:oid,:int] <tmp_1366>[8000000] := sql.bind(_5=21685368,"sys","my_test_table","summable",0); : | 4 | _58:bat[:oid,:int] <tmp_1065>[0] := sql.bind(_5=21685368,"sys","my_test_table","summable", | : : 2); : | 18 | _59<tmp_1721>[8000000] := algebra.kdifference(_56=nil:bat[:oid,:int],_58=<tmp_1065>:ba | : : t[:oid,:int][0]); : | 16 | _60<tmp_1723>[8000000] := algebra.kunion(_59=nil,_58=nil:bat[:oid,:int]); | | 4 | _61:bat[:oid,:int] <tmp_1361>[10] := sql.bind(_5=21685368,"sys","my_test_table","summable" | : : ,1); : | 1884678 | _25<tmp_1707>[359554] := algebra.uselect(_21=nil:bat[:oid,:timestamp],_23=2010-07-03 0 | : : 0:00:00.000,_24=2010-07-10 00:00:00.000,true,false); : | 28 | _28<tmp_1724>[359554] := algebra.kdifference(_25=nil,_27=<tmp_1131>:bat[:oid,:timestam | : : p][0]); : | 30 | _29<tmp_1726>[0] := algebra.uselect(_27=nil:bat[:oid,:timestamp],_23=2010-07-03 00:00: | : : 00.000,_24=2010-07-10 00:00:00.000,true,false); : | 11 | _30<tmp_1725>[359554] := algebra.kunion(_28=nil,_29=nil); | | 10 | _33<tmp_1726>[359554] := algebra.kunion(_30=nil,_32=nil); | | 7 | _34<tmp_1725>[359554] := algebra.kdifference(_33=nil,_19=nil); | | 63572 | _35<tmp_1726>[359554] := algebra.semijoin(_20=nil,_34=nil); | | 2812 | _36<tmp_1673>[139] := algebra.uselect(_35=nil,A0=76:lng); | | 10 | _37<tmp_1726>[139] := algebra.markT(_36=nil,0@0:oid); | | 5 | _38<tmpr_1726>[139] := bat.reverse(_37=nil); | | 58840 | _75<tmp_1715>[139] := algebra.leftjoin(_38=<tmpr_1726>[139],_74=nil); | | 6 | _76<tmp_1715>[139] := algebra.selectNotNil(_75=nil); | | 21903 | _51<tmp_1711>[139] := algebra.leftjoin(_38=<tmpr_1726>[139],_50=nil); | | 62 | (ext61<tmp_1344>[5],grp59<tmp_1651>[139]) := group.done(_51=<tmp_1711>[139]); | | 11 | _54<tmp_1661>[5] := bat.mirror(ext61=<tmp_1344>[5]); | | 46 | _55<tmp_1570>[5] := algebra.join(_54=<tmp_1661>[5],_51=nil); | | 34 | (_77<tmp_1500>[33],grp85) := group.derive(ext61=nil,grp59=<tmp_1651>[139],_76=<tmp_171 | : : 5>[139]); : | 6 | _79<tmp_1706>[33] := bat.mirror(_77=nil); | | 24 | _80<tmp_1502>[33] := algebra.join(_79=nil,_76=nil); | | 22 | _81:bat[:oid,:wrd] <tmp_1706>[5] := aggr.count(_80=nil,grp59=<tmp_1651>[139],_54=<tmp_ | : : 1661>[5]); : | 554759 | _62<tmp_1717>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 541977 | _62<tmp_1721>[8000010] := algebra.kunion(_60=nil,_61=nil:bat[:oid,:int]); | | 41224 | _63<tmp_1356>[139] := algebra.leftjoin(_38=nil,_62=nil); | | 10 | _64<tmp_1356>[139] := algebra.selectNotNil(_63=nil); | | 48 | _65:bat[:oid,:lng] <tmp_1721>[5] := aggr.sum(_64=nil,grp59=nil,_54=nil); | | 2761640 | barrier _137 := language.dataflow(); | | 12 | _82 := sql.resultSet(3,1,_55=<tmp_1570>[5]); | | 8 | sql.rsColumn(_82=3,"sys.my_test_table","dim5","varchar",50,0,_55=nil); | | 5 | sql.rsColumn(_82=3,"sys.my_test_table","summables","bigint",32,0,_65=nil:bat[:oid,:lng]); | | 6 | sql.rsColumn(_82=3,"sys.my_test_table","unique_ids","wrd",64,0,_81=nil:bat[:oid,:wrd]); | | 3 | _99 := io.stdout(); | | 45 | sql.exportResult(_99=14711568,_82=3); | | 2761973 | user.s4_1(76:lng,"2010-07-03 00:00:00","2010-07-10 00:00:00"); | +---------+----------------------------------------------------------------------------------------+

1 0

[MonetDB-users] performance degrades when date related function is used
by Ramkumarlak 03 Feb '11

03 Feb '11

Hi, I am looking for monerdb to replace mysql. I tried some queries with my test data and all were much more faster than mysql. But our actual table contains a column which stores date in unix time format and we do generate reports based on week, day, quarter, month and year. For that we are using a lot mysql date related functions. I too tried the following approach for one of my requirements. cast(timestamp '1970-01-01 00:00:00' + cast(created_date/1000 as interval second) as date) but it takes longer time. 1) Is there any other way to make this query faster ? 2) what are all the options are available in monetdb to deal these (week,quarter,hour,day and year wise reporting) kind of queries. Any help will be appreciated. Thanks and Regards Ram -- View this message in context: http://old.nabble.com/performance-degrades-when-date-related-function-is-us… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] query slowdown with concurrent write
by Bennett Hiles 03 Feb '11

03 Feb '11

Hi, MonetDB has great response times for the queries I have been running, but once I start inserting data to the table and concurrently running the queries, the queries slow down. To give you concrete numbers, I am initializing the database with 8 million records. I then run random aggregation queries repeatedly. Before I insert additional data the max query time is 0.3 seconds. If I then insert 10 rows the queries slow to a max of 1.5s. The only solution I have to getting the queries to resume their original speed is to drop the data and reload the 8 millions rows into the table. I have also noticed that if I run my inserts while I am not concurrently making queries to the database, I don't run into the slowness, and the queries keep their 0.3s response time. Has anyone else encountered this problem before? Is there a way around it? I have also tried using the copy command instead of inserts but get the same result. Thanks, Bennett

3 2

[MonetDB-users] MAL
by Michael Kusber 02 Feb '11

02 Feb '11

Hello guys, I aplogize for bothering you with what might be a simple issue for you, but I am new to monetDB and I have to solve some complex problems for which I cannot find a solution by myself. I've read about MAL in the M5 reference manual. I recognized, the normal way to execute commands is via MAPI and JDBC and stuff based on MAPI. This will be parsed into MAL, optimized and executed. Is there a way to write MAL code and give it as input to the server directly?? Another question: Is it possible to explicitely invoke execution of a query in multiple threads via MAPI or other means? Kind regards, michael

3 2

[MonetDB-users] After delete records with a field criteria, still shows in group by SQL
by Osmin Castillo 02 Feb '11

02 Feb '11

Hi there. I'm having this extrange problem with MonetDB. After delete some records with a criteria for example: field_id = 201011 The records are deleted, but when I use a select group by, the count(0) shows results for field_id = 201011 Thanks in advance

1 0