High amount of VIRT and high amount of writing

29 Jul 2014

      Hi all , we are trying to compare oracle vs monetdb.

the query we are try to run is :

  select count(  client_code_new ) as s from

(

      SELECT     t.client_code_new,  t.bank, t.branch_new,  t.ACCOUNT,
t.instrument_ident,  MAX (t.system_code)

   FROM transactions t left outer join

        (  SELECT c.linked_open_transaction_new     as
linked_open_transaction_new FROM calculated_taxs c    GROUP BY
c.linked_open_transaction_new)  c_temp

on  t.transaction_no = c_temp.linked_open_transaction_new

join clients_list cl on cl.client_code = t.client_code_new AND  cl.branch =
t.branch_new

WHERE  t.opg_cls = 'O'  AND (t.close_trans_disregard_balance_new) =0  and
new_system_code = 1

  GROUP BY t.client_code_new,            t.bank,            t.branch_new,
     t.ACCOUNT,            t.instrument_ident)  a ;

tables sizes are:
transactions = 103M rows
calculated_taxs= 79M rows

The query is taking 1min to run.
we've notice that during the query run the VIRT column (from top utility)
is become very high 10G , and during the we see extensive write to disk .

why does monet is do so much writing ?

thanks,
amihay

bellow is output of minimal_pipe optimizer

+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mal

     |
+============================================================================================================================================================+
| function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void;

     |
|     X_5 := sql.mvc();

     |
|     X_6:bat[:oid,:oid]  := sql.tid(X_5,"sys","clients_list");

     |
|     X_9 := sql.bind(X_5,"sys","clients_list","client_code",0);

      |
|     (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2);

     |
|     X_15 := sql.bind(X_5,"sys","clients_list","client_code",1);

     |
|     X_17 := sql.delta(X_9,X_12,r1_12,X_15);

     |
|     X_18 := algebra.leftfetchjoin(X_6,X_17);

      |
|     X_19 := batcalc.int(X_18);

      |
|     X_20:bat[:oid,:wrd]  := batcalc.hash(X_19);

     |
|     X_23 := calc.int(22);

     |
|     X_25 := sql.bind(X_5,"sys","clients_list","branch",0);

      |
|     (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2);

      |
|     X_29 := sql.bind(X_5,"sys","clients_list","branch",1);

      |
|     X_30 := sql.delta(X_25,X_27,r1_27,X_29);

      |
|     X_31 := algebra.leftfetchjoin(X_6,X_30);

      |
|     X_32:bat[:oid,:wrd]  := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31);

      |
|     X_33:bat[:oid,:int]  := bat.new(nil:oid,nil:int);

     |
|     X_36:bat[:oid,:oid]  := sql.tid(X_5,"sys","transactions");

      |
|     X_38 :=
sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0);
                                                                   |
|     (X_40,r1_40) :=
sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2);
                                                           |
|     X_43 :=
sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1);
                                                                   |
|     X_44 := sql.delta(X_38,X_40,r1_40,X_43);

      |
|     X_45 := algebra.leftfetchjoin(X_36,X_44);

     |
|     X_46 := A1;

     |
|     X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0);

     |
|     (X_50,r1_50) :=
sql.bind(X_5,"sys","transactions","new_system_code",2);
                                                           |
|     X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1);

     |
|     X_55 := sql.delta(X_47,X_50,r1_50,X_53);

      |
|     X_56 := algebra.leftfetchjoin(X_36,X_55);

     |
|     X_57 := A2;

     |
|     X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0);

     |
|     (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2);

     |
|     X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1);

     |
|     X_63 := sql.delta(X_58,X_60,r1_60,X_62);

      |
|     X_64 := algebra.leftfetchjoin(X_36,X_63);

     |
|     X_65 := A0;

     |
|     X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false);

      |
|     X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false);

     |
|     X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false);

     |
|     X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0);

      |
|     (X_74,r1_74) :=
sql.bind(X_5,"sys","transactions","transaction_no",2);
                                                            |
|     X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1);

      |
|     X_78 := sql.delta(X_71,X_74,r1_74,X_77);

      |
|     X_79 := algebra.leftfetchjoin(X_36,X_78);

     |
|     X_80 := algebra.leftfetchjoin(X_70,X_79);

     |
|     X_81:bat[:oid,:oid]  := sql.tid(X_5,"sys","calculated_taxs");

     |
|     X_83 :=
sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0);
                                                                    |
|     (X_86,r1_86) :=
sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2);
                                                            |
|     X_89 :=
sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1);
                                                                    |
|     X_91 := sql.delta(X_83,X_86,r1_86,X_89);

      |
|     X_92 := algebra.leftfetchjoin(X_81,X_91);

     |
|     (X_93,r1_93,r2_93) := group.subgroupdone(X_92);

     |
|     X_96 := algebra.leftfetchjoin(r1_93,X_92);

      |
|     X_97 := batcalc.lng(X_96);

      |
|     (X_98,r1_98) := algebra.join(X_80,X_97);

      |
|     X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0);

      |
|     (X_102,r1_102) :=
sql.bind(X_5,"sys","transactions","client_code_new",2);
                                                         |
|     X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1);

      |
|     X_106 := sql.delta(X_100,X_102,r1_102,X_104);

     |
|     X_107 := algebra.leftfetchjoin(X_36,X_106);

     |
|     X_108 := algebra.leftfetchjoin(X_70,X_107);

     |
|     X_109 := algebra.leftfetchjoin(X_98,X_108);

     |
|     X_110 := bat.append(X_33,X_109,true);

     |
|     X_112 := bat.mirror(X_80);

      |
|     X_113 := algebra.tdiff(X_112,X_98);

     |
|     X_114 := algebra.leftfetchjoin(X_113,X_108);

      |
|     X_115 := bat.append(X_110,X_114,true);

      |
|     X_116:bat[:oid,:wrd]  := batcalc.hash(X_115);

     |
|     X_119 := calc.int(22);

      |
|     X_121:bat[:oid,:int]  := bat.new(nil:oid,nil:int);

      |
|     X_124 := sql.bind(X_5,"sys","transactions","branch_new",0);

     |
|     (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2);

      |
|     X_131 := sql.bind(X_5,"sys","transactions","branch_new",1);

     |
|     X_133 := sql.delta(X_124,X_128,r1_128,X_131);

     |
|     X_134 := algebra.leftfetchjoin(X_36,X_133);

     |
|     X_135 := algebra.leftfetchjoin(X_70,X_134);

     |
|     X_136 := algebra.leftfetchjoin(X_98,X_135);

     |
|     X_137 := bat.append(X_121,X_136,true);

      |
|     X_138 := algebra.leftfetchjoin(X_113,X_135);

      |
|     X_139 := bat.append(X_137,X_138,true);

      |
|     X_140:bat[:oid,:wrd]  :=
mkey.bulk_rotate_xor_hash(X_116,X_119,X_139);
                                                  |
|     (X_141,r1_141) := algebra.join(X_32,X_140);

     |
|     X_143 := algebra.leftfetchjoin(X_141,X_19);

     |
|     X_144 := algebra.leftfetchjoin(r1_141,X_115);

     |
|     X_145:bat[:oid,:bit]  := batcalc.==(X_143,X_144);

     |
|     X_147 := calc.bit(true);

      |
|     X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false);

      |
|     X_151 := algebra.leftfetchjoin(X_149,X_141);

      |
|     X_152 := algebra.leftfetchjoin(X_151,X_31);

     |
|     X_153 := algebra.leftfetchjoin(X_149,r1_141);

     |
|     X_154 := algebra.leftfetchjoin(X_153,X_139);

      |
|     X_155:bat[:oid,:bit]  := batcalc.==(X_152,X_154);

     |
|     X_157 := calc.bit(true);

      |
|     X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false);

      |
|     X_159 := algebra.leftfetchjoin(X_158,X_153);

      |
|     X_160:bat[:oid,:dbl]  := bat.new(nil:oid,nil:dbl);

      |
|     X_163 := sql.bind(X_5,"sys","transactions","bank",0);

     |
|     (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2);

      |
|     X_171 := sql.bind(X_5,"sys","transactions","bank",1);

     |
|     X_173 := sql.delta(X_163,X_168,r1_168,X_171);

     |
|     X_174 := algebra.leftfetchjoin(X_36,X_173);

     |
|     X_175 := algebra.leftfetchjoin(X_70,X_174);

     |
|     X_176 := algebra.leftfetchjoin(X_98,X_175);

     |
|     X_177 := bat.append(X_160,X_176,true);

      |
|     X_178 := algebra.leftfetchjoin(X_113,X_175);

      |
|     X_179 := bat.append(X_177,X_178,true);

      |
|     X_180 := algebra.leftfetchjoin(X_159,X_179);

      |
|     X_181:bat[:oid,:str]  := bat.new(nil:oid,nil:str);

      |
|     X_183 := sql.bind(X_5,"sys","transactions","account",0);

      |
|     (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2);

     |
|     X_187 := sql.bind(X_5,"sys","transactions","account",1);

      |
|     X_188 := sql.delta(X_183,X_185,r1_185,X_187);

     |
|     X_189 := algebra.leftfetchjoin(X_36,X_188);

     |
|     X_190 := algebra.leftfetchjoin(X_70,X_189);

     |
|     X_191 := algebra.leftfetchjoin(X_98,X_190);

     |
|     X_192 := bat.append(X_181,X_191,true);

      |
|     X_194 := algebra.leftfetchjoin(X_113,X_190);

      |
|     X_195 := bat.append(X_192,X_194,true);

      |
|     X_196 := algebra.leftfetchjoin(X_159,X_195);

      |
|     X_197:bat[:oid,:str]  := bat.new(nil:oid,nil:str);

      |
|     X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0);

     |
|     (X_204,r1_204) :=
sql.bind(X_5,"sys","transactions","instrument_ident",2);
                                                          |
|     X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1);

     |
|     X_209 := sql.delta(X_199,X_204,r1_204,X_207);

     |
|     X_210 := algebra.leftfetchjoin(X_36,X_209);

     |
|     X_211 := algebra.leftfetchjoin(X_70,X_210);

     |
|     X_212 := algebra.leftfetchjoin(X_98,X_211);

     |
|     X_213 := bat.append(X_197,X_212,true);

      |
|     X_214 := algebra.leftfetchjoin(X_113,X_211);

      |
|     X_215 := bat.append(X_213,X_214,true);

      |
|     X_216 := algebra.leftfetchjoin(X_159,X_215);

      |
|     X_217 := algebra.leftfetchjoin(X_159,X_115);

      |
|     X_218 := algebra.leftfetchjoin(X_159,X_139);

      |
|     (X_219,r1_219,r2_219) := group.subgroup(X_218);

     |
|     (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219);

     |
|     (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222);

     |
|     (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225);

     |
|     (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228);

     |
|     X_234 := algebra.leftfetchjoin(r1_231,X_217);

     |
|     X_235 := algebra.selectNotNil(X_234);

     |
|     X_236 := aggr.count(X_235);

     |
|     sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,"");

     |
| end s4_3;

     |
| # querylog.define("explain \nselect count(  client_code_new ) as s from
\n(\n      select     t.client_code_new,  t.bank, t.branch_new,  t.account,
    t. |
: instrument_ident,  max (t.system_code)\n   from transactions t left outer
join \n        (  select c.linked_open_transaction_new     as
linked_open_transa :
: ction_new from calculated_taxs c    group by
c.linked_open_transaction_new)  c_temp\n on  t.transaction_no =
c_temp.linked_open_transaction_new \njoin cli :
: ents_list cl on cl.client_code = t.client_code_new and  cl.branch =
t.branch_new\n where  t.opg_cls = \\'O\\'  and
(t.close_trans_disregard_balance_new) = :
: 0  and new_system_code = 1 \n  group by t.client_code_new,
 t.bank,            t.branch_new,        t.account,
 t.instrument_ident)  a :
:  ;","minimal_pipe")

     :
+------------------------------------------------------------------------------------------------------------------------------------------------------------+

High amount of VIRT and high amount of writing

amihay gonen