users-list January 2014

users-list@monetdb.org

18 participants
23 discussions

by Luis Neves

Hi all, I've run into a behaviour that I would like to understand and possibly disable. Two times per day the mserver daeomon starts trashing the disk furiously, I see that the size on disk of data directory goes up to 115 GB from 85 GB and then comes back again to the same initial size, during this process disk utilization goes trough the roof. Can anyone tell me what is monetdb doing? There are no import/export process running at this times, it seems that monetdb is doing some kind of data compaction... is there anyway to disable this? $ mserver5 --version MonetDB 5 server v11.15.7 "Feb2013-SP2" (64-bit, 64-bit oids) Thanks! -- Luis Neves

8 years, 5 months

Re: problem with Jan2014 build

by Ying Zhang

Hai Bas, We hebben vandaag een probleem met substring gefixed die waarschijnlijk met jou probleem te maken heeft. Zou je misschien willen kijken of die fix iets voor je doet? We kunnen dat helaas zelf niet testen. Mvg, Jennie On Jan 27, 2014, at 15:02, Ying Zhang <ying.zhang(a)monetdb.com> wrote: > Hai Bas, > > Thanks again for the information. I was hoping that with some luck your problems might be gone in RC2 due to various fixes, unfortunately they are not. I'd like to look more deeply into the problems you have reported. Problems like not effective updates are rather show stoppers. > > Would you please provide me the ASC files with which the problems can be reproduced? You can send the files directly to me if necessary. Thanks! > > Regards, > > Jennie > > On Jan 27, 2014, at 13:05, Bas Kaptijn <b.kaptijn(a)chs.nl> wrote: > >> Tested with Jan 2014 RC2. All mentioned problems still occur with this release. >> >> Also, with the no_mitosis_pipe my program has problems with DELETE’s and possibly other queries >> not taking place effectively somehow. For me the investigation of the Jan2014 build stops here >> until some of the problems mentioned here are fixed >> >> Regards, Bas >> >> Van: Bas Kaptijn >> Verzonden: dinsdag 21 januari 2014 12:55 >> Aan: 'users-list(a)monetdb.org' >> Onderwerp: RE: problem with Jan2014 build >> >> Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. >> >> sql>set optimizer='no_mitosis_pipe'; >> operation successful (0.139ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> | file | line | source | >> +=============================================================================+========+====================================================================================================================================================+ >> | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 >> : >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> 1 tuple (1.1s) >> sql>set optimizer='default_pipe'; >> operation successful (0.305ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +------+------+--------+ >> | file | line | source | >> +======+======+========+ >> +------+------+--------+ >> 0 tuples (798.870ms) >> >> Regards, Bas >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >

8 years, 6 months

Dynamic queries

by imad hajj chahine

Hi, What is the recommended way to work with dynamic queries, any optimization techniques, is there a way to avoid them using functions written in MAL.

8 years, 6 months

Bug when using BAT UDFs with grouped aggregates and mitosis (related to 3396)

by Alastair McKinley

Dear all, I have been trying to integrate a BAT UDF to test the performance of MonetDB for a particular query. I am currently testing on Ubuntu 13.04 with MonetDB 11.15.19. My BAT UDF works in some cases and in others fails with the error message "b and g must be aligned" in gdk_aggr.c:89. Using gdb I can see that the BATcount() function returns different values for the length of b and g. For a minimal test case, I created a very simple BAT UDF that just multiples two doubles together in the input bats. I believe this is related to bug 3396 as passing --forcemito to mserver triggers the bug. If only the scalar version of the UDF is defined the query works correctly in all cases. Below I have included my test sql and the query plans of the working and non-working usages. I hope this enough to help track this down. Best regards, Alastair // SQL create table tab1 (group_by_col int, index_col int, f float); create table tab2 (index_col int, f float); insert into tab1 values (1,1,0.1),(1,2,0.2),(2,1,0.3),(2,2,0.4),(3,1,0.5),(3,2,0.6); insert into tab2 values (1,0.1),(2,0.2); select tab1.group_by_col,SUM(udf_multiply_dbl(tab1.f,tab2.f)) from tab2 inner join tab1 on tab1.index_col = tab2.index_col group by tab1.group_by_col; drop table tab1; drop table tab2; // Working MAL +-------------------------------------------------------------------------+ | mal | +=========================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_36 := nil:bat[:oid,:int]; | | X_57:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_86 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1"); | | X_6 := sql.bind(X_2,"sys","tab1","index_col",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","tab1","index_col",2); | | X_12 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_18 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_19,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_21 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_22 := sql.delta(X_18,X_19,r1_19,X_21); | | X_23 := algebra.leftfetchjoin(X_16,X_22); | | (X_24,r1_24) := algebra.join(X_15,X_23); | | X_85 := algebra.leftfetchjoin(X_24,X_3); | | X_26 := sql.bind(X_2,"sys","tab1","group_by_col",0); | | (X_28,r1_28) := sql.bind(X_2,"sys","tab1","group_by_col",2); | | X_30 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_31 := sql.delta(X_26,X_28,r1_28,X_30); | | X_32:bat[:oid,:int] := algebra.leftfetchjoin(X_85,X_31); | | (X_33,r1_34,r2_34) := group.subgroupdone(X_32); | | X_36 := algebra.leftfetchjoin(r1_34,X_32); | | X_37 := sql.bind(X_2,"sys","tab1","f",0); | | (X_42,r1_43) := sql.bind(X_2,"sys","tab1","f",2); | | X_45 := sql.bind(X_2,"sys","tab1","f",1); | | X_47 := sql.delta(X_37,X_42,r1_43,X_45); | | X_48:bat[:oid,:dbl] := algebra.leftfetchjoin(X_85,X_47); | | X_49 := sql.bind(X_2,"sys","tab2","f",0); | | (X_51,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_53 := sql.bind(X_2,"sys","tab2","f",1); | | X_54 := sql.delta(X_49,X_51,r1_53,X_53); | | X_55:bat[:oid,:dbl] := algebra.leftfetchjoinPath(r1_24,X_16,X_54); | | X_56:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_48,X_55); | | X_57:bat[:oid,:dbl] := aggr.subsum(X_56,X_33,r1_34,true,true); | | language.pass(X_3); | | language.pass(X_32); | | language.pass(X_85); | | language.pass(X_16); | | language.pass(r1_34); | | exit X_86; | | X_59 := sql.resultSet(2,1,X_36); | | sql.rsColumn(X_59,"sys.tab1","group_by_col","int",32,0,X_36); | | sql.rsColumn(X_59,"sys.L1","L1","double",53,0,X_57); | | X_71 := io.stdout(); | | sql.exportResult(X_71,X_59); | | end s2_1; | +-------------------------------------------------------------------------+ // Non working MAL with --forcemito +-------------------------------------------------------------------------------------------------------+ | mal | +=======================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_23 := nil:bat[:oid,:int]; | | X_41:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_196 := language.dataflow(); | | X_2 := sql.mvc(); | | X_70:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",0,4); | | X_78:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,0,4); | | (X_82:bat[:oid,:oid] ,X_83:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,0,4); | | X_119 := sql.delta(X_78,X_82,X_83); | | X_123 := algebra.leftfetchjoin(X_70,X_119); | | X_10:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_12 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_13,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_15 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_16 := sql.delta(X_12,X_13,r1_19,X_15); | | X_17 := algebra.leftfetchjoin(X_10,X_16); | | (X_127,X_128) := algebra.join(X_123,X_17); | | X_180 := algebra.leftfetchjoin(X_127,X_70); | | X_90:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,0,4); | | (X_94:bat[:oid,:oid] ,X_95:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,0,4); | | X_136 := sql.delta(X_90,X_94,X_95); | | X_143:bat[:oid,:int] := algebra.leftfetchjoin(X_180,X_136); | | (X_147,X_148,X_149) := group.subgroupdone(X_143); | | X_150 := algebra.leftfetchjoin(X_148,X_143); | | X_72:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",1,4); | | X_79:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,1,4); | | (X_84:bat[:oid,:oid] ,X_85:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,1,4); | | X_120 := sql.delta(X_79,X_84,X_85); | | X_124 := algebra.leftfetchjoin(X_72,X_120); | | (X_129,X_130) := algebra.join(X_124,X_17); | | X_181 := algebra.leftfetchjoin(X_129,X_72); | | X_91:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,1,4); | | (X_96:bat[:oid,:oid] ,X_97:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,1,4); | | X_138 := sql.delta(X_91,X_96,X_97); | | X_144:bat[:oid,:int] := algebra.leftfetchjoin(X_181,X_138); | | (X_151,X_152,X_153) := group.subgroupdone(X_144); | | X_154 := algebra.leftfetchjoin(X_152,X_144); | | X_74:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",2,4); | | X_80:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,2,4); | | (X_86:bat[:oid,:oid] ,X_87:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,2,4); | | X_121 := sql.delta(X_80,X_86,X_87); | | X_125 := algebra.leftfetchjoin(X_74,X_121); | | (X_131,X_132) := algebra.join(X_125,X_17); | | X_182 := algebra.leftfetchjoin(X_131,X_74); | | X_92:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,2,4); | | (X_98:bat[:oid,:oid] ,X_99:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,2,4); | | X_140 := sql.delta(X_92,X_98,X_99); | | X_145:bat[:oid,:int] := algebra.leftfetchjoin(X_182,X_140); | | (X_155,X_156,X_157) := group.subgroupdone(X_145); | | X_158 := algebra.leftfetchjoin(X_156,X_145); | | X_76:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",3,4); | | X_81:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,3,4); | | (X_88:bat[:oid,:oid] ,X_89:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,3,4); | | X_8 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_122 := sql.delta(X_81,X_88,X_89,X_8); | | X_126 := algebra.leftfetchjoin(X_76,X_122); | | (X_133,X_134) := algebra.join(X_126,X_17); | | X_183 := algebra.leftfetchjoin(X_133,X_76); | | X_93:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,3,4); | | (X_100:bat[:oid,:oid] ,X_101:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,3,4); | | X_19 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_142 := sql.delta(X_93,X_100,X_101,X_19); | | X_146:bat[:oid,:int] := algebra.leftfetchjoin(X_183,X_142); | | (X_159,X_160,X_161) := group.subgroupdone(X_146); | | X_162 := algebra.leftfetchjoin(X_160,X_146); | | X_184 := mat.packIncrement(X_150,4); | | X_185 := mat.packIncrement(X_184,X_154); | | X_186 := mat.packIncrement(X_185,X_158); | | X_20 := mat.packIncrement(X_186,X_162); | | (X_21,r1_34,X_163) := group.subgroupdone(X_20); | | X_23 := algebra.leftfetchjoin(r1_34,X_20); | | X_103:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,0,4); | | (X_111:bat[:oid,:oid] ,X_112:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,0,4); | | X_168 := sql.delta(X_103,X_111,X_112); | | X_172:bat[:oid,:dbl] := algebra.leftfetchjoin(X_180,X_168); | | X_105:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,1,4); | | (X_113:bat[:oid,:oid] ,X_114:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,1,4); | | X_169 := sql.delta(X_105,X_113,X_114); | | X_173:bat[:oid,:dbl] := algebra.leftfetchjoin(X_181,X_169); | | X_107:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,2,4); | | (X_115:bat[:oid,:oid] ,X_116:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,2,4); | | X_170 := sql.delta(X_107,X_115,X_116); | | X_174:bat[:oid,:dbl] := algebra.leftfetchjoin(X_182,X_170); | | X_109:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,3,4); | | (X_117:bat[:oid,:oid] ,X_118:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,3,4); | | X_29 := sql.bind(X_2,"sys","tab1","f",1); | | X_171 := sql.delta(X_109,X_117,X_118,X_29); | | X_175:bat[:oid,:dbl] := algebra.leftfetchjoin(X_183,X_171); | | X_188 := mat.packIncrement(X_172,4); | | X_189 := mat.packIncrement(X_188,X_173); | | X_190 := mat.packIncrement(X_189,X_174); | | X_31 := mat.packIncrement(X_190,X_175); | | X_32 := sql.bind(X_2,"sys","tab2","f",0); | | (X_34,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_36 := sql.bind(X_2,"sys","tab2","f",1); | | X_37 := sql.delta(X_32,X_34,r1_53,X_36); | | X_38 := algebra.leftfetchjoin(X_10,X_37); | | X_176 := algebra.leftfetchjoin(X_128,X_38); | | X_177 := algebra.leftfetchjoin(X_130,X_38); | | X_178 := algebra.leftfetchjoin(X_132,X_38); | | X_179 := algebra.leftfetchjoin(X_134,X_38); | | X_192 := mat.packIncrement(X_176,4); | | X_193 := mat.packIncrement(X_192,X_177); | | X_194 := mat.packIncrement(X_193,X_178); | | X_39 := mat.packIncrement(X_194,X_179); | | X_40:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_31,X_39); | | X_41:bat[:oid,:dbl] := aggr.subsum(X_40,X_21,r1_34,true,true); | | language.pass(X_70); | | language.pass(X_143); | | language.pass(X_72); | | language.pass(X_144); | | language.pass(X_74); | | language.pass(X_145); | | language.pass(X_17); | | language.pass(X_76); | | language.pass(X_146); | | language.pass(X_20); | | language.pass(X_180); | | language.pass(X_181); | | language.pass(X_182); | | language.pass(X_183); | | language.pass(X_10); | | language.pass(X_38); | | language.pass(r1_34); | | exit X_196; | | X_187:bat[:oid,:int] := nil:bat[:oid,:int]; | | X_191:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_195:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_43 := sql.resultSet(2,1,X_23); | | sql.rsColumn(X_43,"sys.tab1","group_by_col","int",32,0,X_23); | | sql.rsColumn(X_43,"sys.L1","L1","double",53,0,X_41); | | X_55 := io.stdout(); | | sql.exportResult(X_55,X_43); | | end s2_1; | +-------------------------------------------------------------------------------------------------------+

8 years, 6 months

RE: problem with Jan2014 build

by Bas Kaptijn

Tested with Jan 2014 RC2. All mentioned problems still occur with this release. Also, with the no_mitosis_pipe my program has problems with DELETE's and possibly other queries not taking place effectively somehow. For me the investigation of the Jan2014 build stops here until some of the problems mentioned here are fixed Regards, Bas Van: Bas Kaptijn Verzonden: dinsdag 21 januari 2014 12:55 Aan: 'users-list(a)monetdb.org' Onderwerp: RE: problem with Jan2014 build Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

8 years, 6 months

Announcement: Second Release Candidate for Jan2014 Release

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce a second release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org). A number of bugs in the previous release candidate have been fixed. Among others, packaging errors on Debian and Ubuntu have been fixed, and a problem with slow startup has been fixed. The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

8 years, 6 months

Re: Contents of users-list digest What differences between monetdb and mongodb for analytical data?

by Edgar Mejia

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M

8 years, 6 months

Query speed on MonetDB with 4-5 users

by Charles Bajomo

Hello, We have just started using MonetDB in production and can I start by saying its a pretty impressive database. We are having a problem with query when we have 4 -5 users running reports and I wanted to know if anyone could recommend some optimisation tips. On particular one is multiplex-funnels. I can't find any documentation on how to create it. I read some where that it can be used to replicate data between two servers. It would be great to get some guidance on how to do this. Kind Regards

8 years, 6 months

Possible mapi bug in Windows version 20131120?

by Alastair McKinley

Dear all, I am using MonetDB for 64 bit Windows from the package MonetDB5-SQL-Installer-x86_64-20131120.msi. If I compile the example mapi program from here http://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI and cause an error in a query (e.g. incorrect port number in mapi_connect params) then the application will crash with the following message: 0xC0000005: Access violation writing location 0x0000000000000024 This appears to happen if any of the following functions are called: mapi_explain(dbh, stderr); mapi_explain_query(hdl, stderr); mapi_explain_result(hdl, stderr); The same code works correctly on Ubuntu 13.04 with MonetDB-11.15.17?. Can anyone reproduce this error on Windows? Best regards and thanks, Alastair

8 years, 6 months

mapi connect latency

by Matthew Russell

Hi all, I am writing a simple C++ dll which uses the mapi interface to connect to MonetDB. The query itself runs extremely fast which is fantastic. I did notice that the mapi_connect() takes approximately 1 second to connect on my machine on a local socket. Is there anyway to improve this connection latency? I can't unfortunately use a connection pool or keep the connection open due to the design of the calling application. Best regards, Matthew Russell

8 years, 6 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list January 2014