Giving monetdb a fair chance

Martin Kersten Martin.Kersten at cwi.nl
Thu Jul 11 22:44:02 CEST 2013


Hi Franck

These numbers add not up to 450 seconds. I have the suspicion that you have
encountered the Linux problem with HugePageMap management.
This can lead to unexpected stalls.

Sjoerd/Niels can give you the details to avoid it.

regards, Martin

On 7/11/13 6:40 PM, Stefan Manegold wrote:
> Hi Franck,
>
> thanks for the info!
>
> Given the complexity of your query, it would be very helpful for us if you could also send us the results of running your query prefixed with PLAN and EXPLAIN.
> Then we can check which kind of plan we generate from your query.
>
> Looking only at the most expensive operations in your trace (see below) does not instantly ring a bell with me ...
>
> Thanks,
> Stefan
>
>      | 1986607 | X_757=<tmp_14022>[36915397] := algebra.subselect(X_550=<tmp_12726>:bat[:oid,:str][36915434],X_476=<tmp_14513>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      | 1950533 | X_756=<tmp_13614>[36915434] := algebra.subselect(X_548=<tmp_14646>:bat[:oid,:str][36915434],X_474=<tmp_13534>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      | 1702679 | X_758=<tmp_13741>[36915434] := algebra.subselect(X_552=<tmp_14477>:bat[:oid,:str][36915434],X_478=<tmp_12452>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      | 1612725 | X_755=<tmp_13666>[36904002] := algebra.subselect(X_546=<tmp_14165>:bat[:oid,:str][36915434],X_472=<tmp_12430>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      | 1286316 | X_787=<tmp_14647>[18846579] := algebra.subselect(X_517=<tmp_14115>:bat[:oid,:str][36915434],X_774=<tmp_14022>[36915397],A8="CLINIQUE",A8="CLINIQUE",true,true,false |
>      | 1243002 | X_754=<tmp_13114>[36915434] := algebra.subselect(X_544=<tmp_14143>:bat[:oid,:str][36915434],X_470=<tmp_13102>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      | 1134769 | X_846=<tmp_14513>[18846579] := algebra.subselect(X_517=<tmp_14115>:bat[:oid,:str][36915434],X_774=<tmp_14022>[36915397],A10="CLINIQUE",A10="CLINIQUE",true,true,fal |
>      | 1132200 | X_759=<tmp_13162>[36915437] := algebra.subselect(X_554=<tmp_12437>:bat[:oid,:str][36915437],X_480=<tmp_14550>:bat[:oid,:oid][36915437],A7="CHUL",A7="CHUL",true,tru |
>      | 1105372 | X_841=<tmp_13635>[4137747] := algebra.subselect(X_507=<tmp_13627>:bat[:oid,:str][36915434],X_769=<tmp_13312>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  914882 | X_752=<tmp_13312>[36915434] := algebra.subselect(X_540=<tmp_12426>:bat[:oid,:str][36915434],X_466=<tmp_13635>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      |  890052 | X_753=<tmp_14415>[36915434] := algebra.subselect(X_542=<tmp_14002>:bat[:oid,:str][36915434],X_468=<tmp_13577>:bat[:oid,:oid][36915434],A7="CHUL",A7="CHUL",true,tru |
>      |  864087 | X_843=<tmp_13102>[5897070] := algebra.subselect(X_511=<tmp_13561>:bat[:oid,:str][36915434],X_771=<tmp_13114>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  764170 | X_844=<tmp_12430>[5690737] := algebra.subselect(X_513=<tmp_14027>:bat[:oid,:str][36915434],X_772=<tmp_13666>[36904002],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  719337 | X_842=<tmp_13577>[6526518] := algebra.subselect(X_509=<tmp_12625>:bat[:oid,:str][36915434],X_770=<tmp_14415>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  718974 | X_789=<tmp_13577>[3696387] := algebra.subselect(X_519=<tmp_14557>:bat[:oid,:str][36915434],X_775=<tmp_13741>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  685787 | X_783=<tmp_13617>[5690737] := algebra.subselect(X_513=<tmp_14027>:bat[:oid,:str][36915434],X_772=<tmp_13666>[36904002],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  648988 | X_847=<tmp_12452>[3696387] := algebra.subselect(X_519=<tmp_14557>:bat[:oid,:str][36915434],X_775=<tmp_13741>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  612241 | X_777=<tmp_12737>[4137747] := algebra.subselect(X_507=<tmp_13627>:bat[:oid,:str][36915434],X_769=<tmp_13312>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  541833 | X_781=<tmp_13617>[5897070] := algebra.subselect(X_511=<tmp_13561>:bat[:oid,:str][36915434],X_771=<tmp_13114>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  508678 | X_779=<tmp_13741>[6526518] := algebra.subselect(X_509=<tmp_12625>:bat[:oid,:str][36915434],X_770=<tmp_14415>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  464423 | X_785=<tmp_13102>[4803050] := algebra.subselect(X_515=<tmp_14743>:bat[:oid,:str][36915434],X_773=<tmp_13614>[36915434],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  432781 | X_845=<tmp_13534>[4803050] := algebra.subselect(X_515=<tmp_14743>:bat[:oid,:str][36915434],X_773=<tmp_13614>[36915434],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  401519 | X_791=<tmp_12775>[1531879] := algebra.subselect(X_521=<tmp_13002>:bat[:oid,:str][36915437],X_776=<tmp_13162>[36915437],A8="CLINIQUE",A8="CLINIQUE",true,true,false) |
>      |  373662 | X_848=<tmp_13617>[1531879] := algebra.subselect(X_521=<tmp_13002>:bat[:oid,:str][36915437],X_776=<tmp_13162>[36915437],A10="CLINIQUE",A10="CLINIQUE",true,true,fals |
>      |  237526 | X_877=<tmp_12441>[1057] := algebra.subselect(X_486=<tmp_14536>:bat[:oid,:str][36915434],X_867=<tmp_14513>[18846579],A11="16",A11="16",true,true,false);             |
>      |  220168 | X_814=<tmp_13710>[12845] := algebra.subselect(X_486=<tmp_14536>:bat[:oid,:str][36915434],X_806=<tmp_14647>[18846579],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco |
>      |  140860 | X_1539=<tmp_14027>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_678=<tmp_12742>:bat[:oid,:str][36915434],X_693=<tmp_4501>:bat[:oid,:oid][0],X_694=<tmp_75 |
>      |  121108 | X_1542=<tmp_14557>[7336] := sql.projectdelta(X_1047=<tmp_13741>[7336],X_684=<tmp_14357>:bat[:oid,:str][36915434],X_700=<tmp_4501>:bat[:oid,:oid][0],X_701=<tmp_7501 |
>      |  101599 | X_809=<tmp_14371>[7412] := algebra.subselect(X_481=<tmp_14362>:bat[:oid,:str][36915434],X_801=<tmp_12737>[4137747],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", |
>      |   98855 | X_875=<tmp_12452>[386] := algebra.subselect(X_484=<tmp_13122>:bat[:oid,:str][36915434],X_863=<tmp_12430>[5690737],A11="16",A11="16",true,true,false);               |
>      |   95194 | X_810=<tmp_14017>[10596] := algebra.subselect(X_482=<tmp_13672>:bat[:oid,:str][36915434],X_802=<tmp_13741>[6526518],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" |
>      |   93175 | X_812=<tmp_14320>[16687] := algebra.subselect(X_484=<tmp_13122>:bat[:oid,:str][36915434],X_804=<tmp_13617>[5690737],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" |
>      |   91491 | X_813=<tmp_13661>[2797] := algebra.subselect(X_485=<tmp_13061>:bat[:oid,:str][36915434],X_805=<tmp_13102>[4803050],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", |
>      |   78025 | X_811=<tmp_14007>[12501] := algebra.subselect(X_483=<tmp_13103>:bat[:oid,:str][36915434],X_803=<tmp_13617>[5897070],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco" |
>      |   77577 | X_1094=<tmp_12441>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_616=<tmp_13700>:bat[:oid,:str][36915434],X_632=<tmp_4501>:bat[:oid,:oid][0],X_633=<tmp_75 |
>      |   76876 | X_1541=<tmp_13725>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_682=<tmp_12722>:bat[:oid,:str][36915434],X_698=<tmp_4501>:bat[:oid,:oid][0],X_699=<tmp_75 |
>      |   73573 | X_815=<tmp_13534>[7086] := algebra.subselect(X_487=<tmp_13151>:bat[:oid,:str][36915434],X_807=<tmp_13577>[3696387],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", |
>      |   59667 | X_873=<tmp_12642>[483] := algebra.subselect(X_482=<tmp_13672>:bat[:oid,:str][36915434],X_859=<tmp_13577>[6526518],A11="16",A11="16",true,true,false);               |
>      |   55559 | X_874=<tmp_14125>[420] := algebra.subselect(X_483=<tmp_13103>:bat[:oid,:str][36915434],X_861=<tmp_13102>[5897070],A11="16",A11="16",true,true,false);               |
>      |   45781 | X_872=<tmp_14120>[276] := algebra.subselect(X_481=<tmp_14362>:bat[:oid,:str][36915434],X_857=<tmp_13635>[4137747],A11="16",A11="16",true,true,false);               |
>      |   37478 | X_878=<tmp_13472>[250] := algebra.subselect(X_487=<tmp_13151>:bat[:oid,:str][36915434],X_869=<tmp_12452>[3696387],A11="16",A11="16",true,true,false);               |
>      |   37083 | X_876=<tmp_13530>[170] := algebra.subselect(X_485=<tmp_13061>:bat[:oid,:str][36915434],X_865=<tmp_13534>[4803050],A11="16",A11="16",true,true,false);               |
>      |   27566 | X_816=<tmp_13054>[5148] := algebra.subselect(X_488=<tmp_13604>:bat[:oid,:str][36915437],X_808=<tmp_12775>[1531879],A9="cli_nb_entrees_mco",A9="cli_nb_entrees_mco", |
>      |   12331 | X_1139=<tmp_12430>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_645=<tmp_13004>:bat[:oid,:str][36915434],X_660=<tmp_4501>:bat[:oid,:oid][0],X_661=<tmp_75 |
>      |   11638 | X_879=<tmp_14550>[97] := algebra.subselect(X_488=<tmp_13604>:bat[:oid,:str][36915437],X_871=<tmp_13617>[1531879],A11="16",A11="16",true,true,false);                |
>      |    4839 | X_884=<tmp_14217>[0] := algebra.subselect(X_496=<tmp_7501>:bat[:oid,:str][0],A11="16",A11="16",true,true,false);                                                    |
>      |    3144 | X_1141=<tmp_14000>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_649=<tmp_14023>:bat[:oid,:str][36915434],X_665=<tmp_4501>:bat[:oid,:oid][0],X_666=<tmp_75 |
>      |    2756 | X_1095=<tmp_12632>[7336] := sql.projectdelta(X_1047=<tmp_13741>[7336],X_618=<tmp_13661>:bat[:oid,:str][36915434],X_634=<tmp_4501>:bat[:oid,:oid][0],X_635=<tmp_7501 |
>      |    2552 | X_2252=<tmp_14360>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_484=<tmp_13122>:bat[:oid,:str][36915434],X_495=<tmp_4501>:bat[:oid,:oid][0],X_496=<tmp_75 |
>      |    2403 | X_1089=<tmp_13627>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_606=<tmp_12760>:bat[:oid,:str][36915434],X_621=<tmp_4501>:bat[:oid,:oid][0],X_622=<tmp_7501 |
>      |    2272 | X_2328=<tmp_14017>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_707=<tmp_13204>:bat[:oid,:lng][36915434],X_722=<tmp_4501>:bat[:oid,:oid][0],X_723=<tmp_73 |
>      |    1784 | (X_1153=<tmpr_12760>[542],X_1154=<tmpr_14211>[542]) := algebra.join(X_1145=<tmp_13627>:bat[:oid,:str][542],X_127=<tmp_13033>[126847]);                              |
>      |    1754 | X_2212=<tmp_13122>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_513=<tmp_14027>:bat[:oid,:str][36915434],X_528=<tmp_4501>:bat[:oid,:oid][0],X_529=<tmp_75 |
>      |    1699 | X_1092=<tmp_13004>[17073] := sql.projectdelta(X_1044=<tmp_13661>[17073],X_612=<tmp_12737>:bat[:oid,:str][36915434],X_627=<tmp_4501>:bat[:oid,:oid][0],X_628=<tmp_75 |
>      |    1621 | X_1140=<tmp_12675>[2967] := sql.projectdelta(X_1045=<tmp_13614>[2967],X_647=<tmp_12444>:bat[:oid,:str][36915434],X_662=<tmp_4501>:bat[:oid,:oid][0],X_663=<tmp_7501 |
>      |    1571 | X_1090=<tmp_12625>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_608=<tmp_14061>:bat[:oid,:str][36915434],X_623=<tmp_4501>:bat[:oid,:oid][0],X_624=<tmp_75 |
>      |    1493 | X_1056=<tmp_14120>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_573=="<tmp_13534>":bat[:oid,:timestamp][36915434],X_588=<tmp_4501>:bat[:oid,:oid][0],X_589= |
>      |    1379 | X_2256=<tmp_14102>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_486=<tmp_14536>:bat[:oid,:str][36915434],X_500=<tmp_4501>:bat[:oid,:oid][0],X_501=<tmp_75 |
>      |    1346 | X_2246=<tmp_14371>[7688] := sql.projectdelta(X_1041=<tmp_14211>[7688],X_481=<tmp_14362>:bat[:oid,:str][36915434],X_489=<tmp_4501>:bat[:oid,:oid][0],X_490=<tmp_7501 |
>      |    1297 | X_2216=<tmp_14063>[13902] := sql.projectdelta(X_1046=<tmp_13724>[13902],X_517=<tmp_14115>:bat[:oid,:str][36915434],X_533=<tmp_4501>:bat[:oid,:oid][0],X_534=<tmp_75 |
>      |    1203 | X_2250=<tmp_14007>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_483=<tmp_13103>:bat[:oid,:str][36915434],X_493=<tmp_4501>:bat[:oid,:oid][0],X_494=<tmp_75 |
>      |    1112 | X_1091=<tmp_13561>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_610=<tmp_13062>:bat[:oid,:str][36915434],X_625=<tmp_4501>:bat[:oid,:oid][0],X_626=<tmp_75 |
>      |    1064 | X_2248=<tmp_13737>[11079] := sql.projectdelta(X_1042=<tmp_14335>[11079],X_482=<tmp_13672>:bat[:oid,:str][36915434],X_491=<tmp_4501>:bat[:oid,:oid][0],X_492=<tmp_75 |
>      |    1001 | X_2210=<tmp_13770>[12921] := sql.projectdelta(X_1043=<tmp_12445>[12921],X_511=<tmp_13561>:bat[:oid,:str][36915434],X_526=<tmp_4501>:bat[:oid,:oid][0],X_527=<tmp_75 |
>
>
> ----- Original Message -----
>> Ok, I have an example query, that takes 4 sec in MonetDB.
>> This is considered long, as Postgresql performs the same task in some
>> 200 ms...
>>
>> I have used trace to get detailed information on what happens.
>> But this output is really detailed : 2500 lines ! Here it is :
>> http://pastebin.com/EmhERAeF
>> For information, here the Pg explain plan, if that can be of any help
>> http://explain.depesz.com/s/StM
>>
>>
>> The point is that I don't know what to look for, so visualy parsing
>> this
>> output is quite hard :-)
>> Any hint ?
>>
>> Thanks a lot,
>> Franck
>>
>>
>> _______________________________________________
>> users-list mailing list
>> users-list at monetdb.org
>> http://mail.monetdb.org/mailman/listinfo/users-list
>>
>




More information about the users-list mailing list