Re: High amount of VIRT and high amount of writing

29 Jul 2014


      Hi,
This will be most likely monetdb writing out temporary data for sorts etc.
Check the amount of disk space on the filesystem where your database is 
during query execution. Used space should go up.

Radovan

On 07/29/2014 01:54 PM, amihay gonen wrote:
...
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 http://batcalc.int(X_18);
|
|     X_20:bat[:oid,:wrd]  := batcalc.hash(X_19);
|
|     X_23 := calc.int http://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 http://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")
:
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
-- 
__________________________

Radovan Bičiště
ceos data s.r.o.
Pouchovská 153
500 03 Hradec Králové
Czech Republic

mobil CZ: +420 601 563 014
skype: rbiciste