stonedb icon indicating copy to clipboard operation
stonedb copied to clipboard

Published 20 hours ago • verified publisher icon stoneatom

question: SQL num 18 use too much RAM because of deep copy on parall hash join, is deep copy really necessary?

Open adofsauron opened this issue 1 year ago • 0 comments

执行完Q18的语句，内存使用了135GB，分析究竟为何使用这么多

SQL语句：

select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 300 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100;

火焰图:

内存分配分析:

主要分配内存的日志追踪:

[2022-08-02 13:44:41.214744] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 191902608 [2022-08-02 13:44:41.214796] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 0 packs_started: 0 packs_increased: 6 diff: 0.364335 [2022-08-02 13:44:41.532836] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 1 packs_started: 7 packs_increased: 6 diff: 0.318000 [2022-08-02 13:44:42.298793] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 190456956 [2022-08-02 13:44:42.298855] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 2 packs_started: 14 packs_increased: 6 diff: 0.765960 [2022-08-02 13:44:43.172810] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 188686060 [2022-08-02 13:44:43.172866] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 3 packs_started: 21 packs_increased: 6 diff: 0.873991 [2022-08-02 13:44:44.048919] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 186915668 [2022-08-02 13:44:44.048975] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 4 packs_started: 28 packs_increased: 6 diff: 0.876091 [2022-08-02 13:44:44.926210] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 185144768 [2022-08-02 13:44:44.926275] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 5 packs_started: 35 packs_increased: 6 diff: 0.877278 [2022-08-02 13:44:45.800977] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 183371700 [2022-08-02 13:44:45.801032] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 6 packs_started: 42 packs_increased: 6 diff: 0.874739 [2022-08-02 13:44:46.676865] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 181600928 [2022-08-02 13:44:46.676923] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 7 packs_started: 49 packs_increased: 6 diff: 0.875874 [2022-08-02 13:44:47.551140] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 179830032 [2022-08-02 13:44:47.551199] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 8 packs_started: 56 packs_increased: 6 diff: 0.874258 [2022-08-02 13:44:48.426784] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 178058772 [2022-08-02 13:44:48.426840] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 9 packs_started: 63 packs_increased: 6 diff: 0.875618 [2022-08-02 13:44:49.299662] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 176285324 [2022-08-02 13:44:49.299732] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 10 packs_started: 70 packs_increased: 6 diff: 0.872875 [2022-08-02 13:44:50.174840] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 174514428 [2022-08-02 13:44:50.174894] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 11 packs_started: 77 packs_increased: 6 diff: 0.875146 [2022-08-02 13:44:51.039251] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172776928 [2022-08-02 13:44:51.039306] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 12 packs_started: 84 packs_increased: 6 diff: 0.864395 [2022-08-02 13:44:51.371730] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172776852 [2022-08-02 13:44:51.371786] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 13 packs_started: 91 packs_increased: 6 diff: 0.332465 [2022-08-02 13:44:51.706190] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172777280 [2022-08-02 13:44:51.706248] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 14 packs_started: 98 packs_increased: 6 diff: 0.334445 [2022-08-02 13:44:52.039566] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172776584 [2022-08-02 13:44:52.039619] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 15 packs_started: 105 packs_increased: 6 diff: 0.333355 [2022-08-02 13:44:52.371658] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775428 [2022-08-02 13:44:52.371713] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 16 packs_started: 112 packs_increased: 6 diff: 0.332078 [2022-08-02 13:44:52.703022] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172774732 [2022-08-02 13:44:52.703076] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 17 packs_started: 119 packs_increased: 6 diff: 0.331343 [2022-08-02 13:44:53.034573] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775160 [2022-08-02 13:44:53.034627] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 18 packs_started: 126 packs_increased: 6 diff: 0.331536 [2022-08-02 13:44:53.367322] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172774904 [2022-08-02 13:44:53.367377] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 19 packs_started: 133 packs_increased: 6 diff: 0.332713 [2022-08-02 13:44:53.698292] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775444 [2022-08-02 13:44:53.698348] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 20 packs_started: 140 packs_increased: 6 diff: 0.330955 [2022-08-02 13:44:54.032992] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775132 [2022-08-02 13:44:54.033065] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 21 packs_started: 147 packs_increased: 6 diff: 0.334700 [2022-08-02 13:44:54.363304] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172774780 [2022-08-02 13:44:54.363357] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 22 packs_started: 154 packs_increased: 6 diff: 0.330275 [2022-08-02 13:44:54.699551] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775208 [2022-08-02 13:44:54.699608] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 23 packs_started: 161 packs_increased: 6 diff: 0.336235 [2022-08-02 13:44:55.030426] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775380 [2022-08-02 13:44:55.030481] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 24 packs_started: 168 packs_increased: 6 diff: 0.330857 [2022-08-02 13:44:55.365280] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775028 [2022-08-02 13:44:55.365331] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 25 packs_started: 175 packs_increased: 6 diff: 0.334835 [2022-08-02 13:44:55.697743] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775456 [2022-08-02 13:44:55.697797] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 26 packs_started: 182 packs_increased: 6 diff: 0.332451 [2022-08-02 13:44:56.030661] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775380 [2022-08-02 13:44:56.030715] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 27 packs_started: 189 packs_increased: 6 diff: 0.332901 [2022-08-02 13:44:56.363231] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775028 [2022-08-02 13:44:56.363285] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 28 packs_started: 196 packs_increased: 6 diff: 0.332555 [2022-08-02 13:44:56.689558] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775532 [2022-08-02 13:44:56.689608] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 29 packs_started: 203 packs_increased: 6 diff: 0.326307 [2022-08-02 13:44:57.013914] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 30 packs_started: 210 packs_increased: 6 diff: 0.324269 [2022-08-02 13:44:57.338472] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775252 [2022-08-02 13:44:57.338527] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 31 packs_started: 217 packs_increased: 6 diff: 0.324560 [2022-08-02 13:44:57.666565] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 32 packs_started: 224 packs_increased: 6 diff: 0.327994 [2022-08-02 13:44:57.993040] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775376 [2022-08-02 13:44:57.993114] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 33 packs_started: 231 packs_increased: 6 diff: 0.326484 [2022-08-02 13:44:58.317506] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172775132 [2022-08-02 13:44:58.317561] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 34 packs_started: 238 packs_increased: 6 diff: 0.324431 [2022-08-02 13:44:58.642967] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 35 packs_started: 245 packs_increased: 6 diff: 0.325372 [2022-08-02 13:44:58.967673] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 36 packs_started: 252 packs_increased: 6 diff: 0.324636 [2022-08-02 13:44:59.302591] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 172771728 [2022-08-02 13:44:59.302661] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 37 packs_started: 259 packs_increased: 6 diff: 0.334935 [2022-08-02 13:44:59.979852] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 171657196 [2022-08-02 13:44:59.979907] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 38 packs_started: 266 packs_increased: 6 diff: 0.677227 [2022-08-02 13:45:00.861018] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 169884252 [2022-08-02 13:45:00.861079] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 39 packs_started: 273 packs_increased: 6 diff: 0.881155 [2022-08-02 13:45:01.741819] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 168113232 [2022-08-02 13:45:01.741877] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 40 packs_started: 280 packs_increased: 6 diff: 0.880781 [2022-08-02 13:45:02.622723] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 166342336 [2022-08-02 13:45:02.622783] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 41 packs_started: 287 packs_increased: 6 diff: 0.880888 [2022-08-02 13:45:03.503628] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 164571944 [2022-08-02 13:45:03.503705] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 42 packs_started: 294 packs_increased: 6 diff: 0.880903 [2022-08-02 13:45:04.384982] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 162799124 [2022-08-02 13:45:04.385038] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 43 packs_started: 301 packs_increased: 6 diff: 0.881315 [2022-08-02 13:45:05.265687] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 161028352 [2022-08-02 13:45:05.265740] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 44 packs_started: 308 packs_increased: 6 diff: 0.880684 [2022-08-02 13:45:06.145706] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 159256804 [2022-08-02 13:45:06.145762] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 45 packs_started: 315 packs_increased: 6 diff: 0.880005 [2022-08-02 13:45:07.027947] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 157483860 [2022-08-02 13:45:07.028006] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 46 packs_started: 322 packs_increased: 6 diff: 0.882226 [2022-08-02 13:45:07.910017] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 155713092 [2022-08-02 13:45:07.910083] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 47 packs_started: 329 packs_increased: 6 diff: 0.882055 [2022-08-02 13:45:08.793998] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 153942072 [2022-08-02 13:45:08.794069] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 48 packs_started: 336 packs_increased: 6 diff: 0.883969 [2022-08-02 13:45:09.675768] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 152171176 [2022-08-02 13:45:09.675825] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 49 packs_started: 343 packs_increased: 6 diff: 0.881739 [2022-08-02 13:45:10.558104] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 150398356 [2022-08-02 13:45:10.558163] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 50 packs_started: 350 packs_increased: 6 diff: 0.882320 [2022-08-02 13:45:11.441669] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 148627700 [2022-08-02 13:45:11.441725] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 51 packs_started: 357 packs_increased: 6 diff: 0.883544 [2022-08-02 13:45:12.325844] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 146856656 [2022-08-02 13:45:12.325903] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 52 packs_started: 364 packs_increased: 6 diff: 0.884161 [2022-08-02 13:45:13.211545] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 145086384 [2022-08-02 13:45:13.211598] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 53 packs_started: 371 packs_increased: 6 diff: 0.885678 [2022-08-02 13:45:13.826858] [28074] [INFO] [engine.cpp:1352] MSG: Command: select 8/8, update 0/0, insert 0/0, load 0/0, queries 0/20 [2022-08-02 13:45:13.826935] [28074] [INFO] [engine.cpp:1364] MSG: Select: 0/2, Loaded: 0/0(0/0), dup: 0/0, insert: 0/0, failed insert: 0/0, update: 0/0 [2022-08-02 13:45:14.095108] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 143313564 [2022-08-02 13:45:14.095162] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 54 packs_started: 378 packs_increased: 6 diff: 0.883547 [2022-08-02 13:45:14.979525] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 141542004 [2022-08-02 13:45:14.979579] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 55 packs_started: 385 packs_increased: 6 diff: 0.884400 [2022-08-02 13:45:15.861484] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 139771108 [2022-08-02 13:45:15.861550] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 56 packs_started: 392 packs_increased: 6 diff: 0.881955 [2022-08-02 13:45:16.745676] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 138000336 [2022-08-02 13:45:16.745730] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 57 packs_started: 399 packs_increased: 6 diff: 0.884162 [2022-08-02 13:45:17.630072] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 136227268 [2022-08-02 13:45:17.630133] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 58 packs_started: 406 packs_increased: 6 diff: 0.884386 [2022-08-02 13:45:18.512994] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 134456876 [2022-08-02 13:45:18.513058] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 59 packs_started: 413 packs_increased: 6 diff: 0.882908 [2022-08-02 13:45:19.395846] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 132686056 [2022-08-02 13:45:19.395903] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 60 packs_started: 420 packs_increased: 6 diff: 0.882828 [2022-08-02 13:45:20.279779] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 130915036 [2022-08-02 13:45:20.279839] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 61 packs_started: 427 packs_increased: 6 diff: 0.883919 [2022-08-02 13:45:21.454241] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 129143700 [2022-08-02 13:45:21.454295] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 62 packs_started: 434 packs_increased: 6 diff: 1.174440 [2022-08-02 13:45:22.708341] [28081] [INFO] [parallel_hash_join.cpp:77] MSG: PARAL HASH [new MIL] memLeft: 127372236 [2022-08-02 13:45:22.708394] [28081] [INFO] [parallel_hash_join.cpp:792] MSG: ParallelHashJoiner::CreateMatchingTasks new MIL index: 63 packs_started: 441 packs_increased: -442 diff: 1.254084 [2022-08-02 13:45:22.708407] [28081] [INFO] [parallel_hash_join.cpp:835] MSG: Timer 41.858028 : ParallelHashJoiner::CreateMatchingTasks new iter

核心函数:

ParallelHashJoiner::CreateMatchingTasks

bool ParallelHashJoiner::CreateMatchingTasks(MIIterator &mit, int64_t rows_count, std::vector<MITaskIterator *> *task_iterators, std::string *splitting_type) { if (other_cond_exist_) { MITaskIterator *iter = new MITaskIterator(mind, matched_dims_, 0, 1, 0); task_iterators->push_back(iter); return true; }

MIIterator::SliceCapability slice_capability = mit.GetSliceCapability();
for (auto &j : other_cond_) {
    if (j.IsType_Subquery()) {
        slice_capability.type = MIIterator::SliceCapability::Type::kDisable;
        break;
    }
}

{
    STONEDB_LOG(LogCtl_Level::INFO, "ParallelHashJoiner::CreateMatchingTasks slices_size: %d type: %d",
                slice_capability.slices.size(), slice_capability.type);
    
    int index = 0;
    for (auto slice : slice_capability.slices) {
        STONEDB_LOG(LogCtl_Level::INFO, "ParallelHashJoiner::CreateMatchingTasks index:%d slice: %d", index, slice);
        index++;
    }
}

std::chrono::high_resolution_clock::time_point start = std::chrono::high_resolution_clock::now();

if (slice_capability.type == MIIterator::SliceCapability::Type::kFixed) {
    DEBUG_ASSERT(!slice_capability.slices.empty());
    size_t slices_size = slice_capability.slices.size();
    int64_t rows_started = 0;
    
    for (size_t index = 0; index < slices_size; ++index) {
        MITaskIterator *iter = new MIFixedTaskIterator(pack_power_, mind, matched_dims_, index, slices_size,
                                                       slice_capability.slices[index], rows_started, index);
        rows_started += slice_capability.slices[index];
        task_iterators->push_back(iter);
    }
    *splitting_type = "fixed";
} else if (slice_capability.type == MIIterator::SliceCapability::Type::kLinear) {
    int packs_count = (int)((rows_count + (1 << pack_power_) - 1) >> pack_power_);
    for (int index = 0; index < mind->NoDimensions(); index++) {
        if (matched_dims_[index]) {
            Filter *filter = mind->GetFilter(index);
            if (filter) packs_count = filter->NoBlocks();
        }
    }
    
    STONEDB_LOG(LogCtl_Level::INFO, "ParallelHashJoiner::CreateMatchingTasks packs_count: %d kJoinSplittedMinPacks: %d",
                packs_count, kJoinSplittedMinPacks);
    
    if (packs_count > kJoinSplittedMinPacks) {
        // Splitting using packs.
        int split_count = (stonedb_sysvar_join_parallel > 1) ? stonedb_sysvar_join_parallel
            : EvaluateMatchedFragmentsWithPacks(packs_count);
        int packs_per_fragment = (packs_count + kJoinSplittedMinPacks) / split_count;
        
        STONEDB_LOG(LogCtl_Level::INFO, "ParallelHashJoiner::CreateMatchingTasks split_count: %d packs_per_fragment: %d",
                    split_count, packs_per_fragment);
        
        for (int index = 0; index < split_count; ++index) {
            int packs_started = index * packs_per_fragment;
            if (packs_started >= packs_count) break;
            
            int packs_increased = (index == split_count - 1) ? (-1 - packs_started) : (packs_per_fragment - 1);
            
            std::chrono::high_resolution_clock::time_point __start = std::chrono::high_resolution_clock::now();
            
            MITaskIterator *iter = new MILinearPackTaskIterator(pack_power_, mind, matched_dims_, index, split_count, 0,
                                                                packs_started, packs_started + packs_increased);
            
            GetSysMemInfoLabel("new MIL");
            
            auto __diff =
                std::chrono::duration_cast<std::chrono::duration<float>>(std::chrono::high_resolution_clock::now() - __start);
            
            STONEDB_LOG(LogCtl_Level::INFO,
                        "ParallelHashJoiner::CreateMatchingTasks  new MIL index: %d packs_started: %d packs_increased: %d diff: %f", 
                        index, packs_started, packs_increased, __diff.count());
            
            task_iterators->push_back(iter);
        }
        
        *splitting_type = "packs";
    } else if (stonedb_sysvar_join_splitrows > 0) {
        // Splitting using rows.
        uint64_t origin_size = rows_count;
        for (int index = 0; index < mind->NoDimensions(); index++) {
            if (matched_dims_[index]) {
                origin_size = std::max(origin_size, mind->OrigSize(index));
            }
        }
        
        int64_t rows_unit = (1 << pack_power_) / 4;
        int split_count = (stonedb_sysvar_join_splitrows > 1)
            ? stonedb_sysvar_join_splitrows
            : EvaluateMatchedFragmentsWithRows(pack_power_, rows_unit, origin_size);
        int64_t rows_per_fragment = (origin_size + rows_unit) / split_count;
        
        STONEDB_LOG(LogCtl_Level::INFO, "ParallelHashJoiner::CreateMatchingTasks split_count: %d",
                    split_count);
        
        for (int index = 0; index < split_count; ++index) {
            int rows_started = index * rows_per_fragment;
            int rows_increased = (index == split_count - 1) ? (-1 - rows_started) : (rows_per_fragment - 1);
            MITaskIterator *iter = new MILinearRowTaskIterator(mind, matched_dims_, index, split_count, 0, rows_started,
                                                               rows_started + rows_increased);
            task_iterators->push_back(iter);
        }
        *splitting_type = "rows";
    } else {
        MITaskIterator *iter = new MITaskIterator(mind, matched_dims_, 0, 1, 0);
        task_iterators->push_back(iter);
    }
} else {
    MITaskIterator *iter = new MITaskIterator(mind, matched_dims_, 0, 1, 0);
    task_iterators->push_back(iter);
}

auto diff =
    std::chrono::duration_cast<std::chrono::duration<float>>(std::chrono::high_resolution_clock::now() - start);
STONEDB_LOG(LogCtl_Level::INFO, "Timer %f : ParallelHashJoiner::CreateMatchingTasks new iter", diff.count());

return true;

}

MultiIndex::MultiIndex

MultiIndex::MultiIndex(MultiIndex &s, bool shallow) : m_conn(s.m_conn) { p_power = s.p_power; no_dimensions = s.no_dimensions; no_tuples = s.no_tuples; no_tuples_too_big = s.no_tuples_too_big; if (no_dimensions > 0) { group_for_dim = new DimensionGroup *[no_dimensions]; group_num_for_dim = new int[no_dimensions]; dim_size = new int64_t[no_dimensions]; used_in_output = s.used_in_output; can_be_distinct = s.can_be_distinct; for (uint i = 0; i < s.dim_groups.size(); i++) dim_groups.push_back(s.dim_groups[i]->Clone(shallow));

    for (int i = 0; i < no_dimensions; i++) dim_size[i] = s.dim_size[i];
    
    FillGroupForDim();
} else {
    dim_size = NULL;
    group_for_dim = NULL;
    group_num_for_dim = NULL;
}
iterator_lock = 0;

}

DimensionGroupMultiMaterialized::Clone

DimensionGroup *DimensionGroupMultiMaterialized::Clone(bool shallow) { DimensionGroupMultiMaterialized *new_value = new DimensionGroupMultiMaterialized(no_obj, dims_used_, power_); if (shallow) return new_value; for (int index = 0; index < dims_count_; ++index) { MultiIndexTable *tables = dim_tables_[index]; if (tables) { tables->Lock(); new_value->dim_tables_[index] = new MultiIndexTable(*tables); tables->Unlock(); } } return new_value; }

调用堆栈:

(gdb) bt #0 stonedb::core::DimensionGroupMultiMaterialized::Clone (this=0x7f763c9bbfd0, shallow=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/dimension_group_multiple.cpp:203 #1 0x0000000003092a2e in stonedb::core::MultiIndex::MultiIndex (this=0x7f763c9b5b20, s=..., shallow=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/multi_index.cpp:78 #2 0x000000000317846d in stonedb::core::MITaskIterator::MITaskIterator (this=0x7f763cc04b60, mind=0x7f763c986290, dimensions=..., task_id=0, task_count=27, rows_length=196608) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parallel_hash_join.cpp:107 #3 0x0000000003181001 in stonedb::core::MIFixedTaskIterator::MIFixedTaskIterator (this=0x7f763cc04b60, mind=0x7f763c986290, dimensions=..., task_id=0, task_count=27, rows_length=196608, rows_started=0, fixed_block_index=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parallel_hash_join.cpp:158 #4 0x000000000317ad0c in stonedb::core::ParallelHashJoiner::TraverseDim (this=0x7f763c9ba220, mit=..., outer_tuples=0x7f997c1b14d0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parallel_hash_join.cpp:530 #5 0x000000000317a488 in stonedb::core::ParallelHashJoiner::ExecuteJoin (this=0x7f763c9ba220) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parallel_hash_join.cpp:467 #6 0x000000000317923d in stonedb::core::ParallelHashJoiner::ExecuteJoinConditions (this=0x7f763c9ba220, cond=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parallel_hash_join.cpp:273 #7 0x00000000030b2ffd in stonedb::core::ParameterizedFilter::UpdateJoinCondition (this=0x7f763c9aaeb0, cond=..., tips=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parameterized_filter.cpp:597 #8 0x00000000030b6ba5 in stonedb::core::ParameterizedFilter::UpdateMultiIndex (this=0x7f763c9aaeb0, count_only=false, limit=-1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/parameterized_filter.cpp:1203 #9 0x0000000002d76321 in stonedb::core::Query::Preexecute (this=0x7f997c1b2800, qu=..., sender=0x7f763c99c390, display_now=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/query.cpp:777 #10 0x0000000002d47b58 in stonedb::core::Engine::Execute (this=0x7705dd0, thd=0x7f763c000b70, lex=0x7f763c002e98, result_output=0x7f763c01e5e0, unit_for_union=0x0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/engine_execute.cpp:421 #11 0x0000000002d46e0c in stonedb::core::Engine::HandleSelect (this=0x7705dd0, thd=0x7f763c000b70, lex=0x7f763c002e98, result=@0x7f997c1b2d18: 0x7f763c01e5e0, setup_tables_done_option=0, res=@0x7f997c1b2d14: 0, optimize_after_sdb=@0x7f997c1b2d0c: 1, sdb_free_join=@0x7f997c1b2d10: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/core/engine_execute.cpp:232 #12 0x0000000002e2f643 in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f763c000b70, lex=0x7f763c002e98, result=@0x7f997c1b2d18: 0x7f763c01e5e0, setup_tables_done_option=0, res=@0x7f997c1b2d14: 0, optimize_after_sdb=@0x7f997c1b2d0c: 1, sdb_free_join=@0x7f997c1b2d10: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/stonedb/handler/ha_rcengine.cpp:82 #13 0x0000000002472f8a in execute_sqlcom_select (thd=0x7f763c000b70, all_tables=0x7f763c01af80) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5182 #14 0x000000000246c30e in mysql_execute_command (thd=0x7f763c000b70, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2831 #15 0x0000000002473f53 in mysql_parse (thd=0x7f763c000b70, parser_state=0x7f997c1b3eb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5621 #16 0x00000000024691eb in dispatch_command (thd=0x7f763c000b70, com_data=0x7f997c1b4650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1495 #17 0x0000000002468117 in do_command (thd=0x7f763c000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1034 #18 0x000000000259ad03 in handle_connection (arg=0x15643fc0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313 #19 0x0000000002c7e95e in pfs_spawn_thread (arg=0xa66ce40) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197 #20 0x00007f99d6d6bea5 in start_thread () from /lib64/libpthread.so.0 #21 0x00007f99d51a2b0d in clone () from /lib64/libc.so.6

去除深拷贝:

深拷贝控制:

MultiIndex(MultiIndex &s, bool shallow);

//----------------------------------------------MITaskIterator----------------------------------------------- MITaskIterator::MITaskIterator(MultiIndex *mind, DimensionVector &dimensions, int task_id, int task_count, int64_t rows_length) : iter_(new MIIterator(new MultiIndex(*mind, false), dimensions)) {

修改shallow不适用深拷贝:

//----------------------------------------------MITaskIterator----------------------------------------------- MITaskIterator::MITaskIterator(MultiIndex *mind, DimensionVector &dimensions, int task_id, int task_count, int64_t rows_length) : iter_(new MIIterator(new MultiIndex(*mind, true), dimensions)) {

执行耗时:

  1. 30min

执行结果:

explain分析:

mysql> explain select -> c_name, -> c_custkey, -> o_orderkey, -> o_orderdate, -> o_totalprice, -> sum(l_quantity) -> from -> customer, -> orders, -> lineitem -> where -> o_orderkey in ( -> select -> l_orderkey -> from -> lineitem -> group by -> l_orderkey -> having -> sum(l_quantity) > 300 ) -> and c_custkey = o_custkey -> and o_orderkey = l_orderkey -> group by -> c_name, -> c_custkey, -> o_orderkey, -> o_orderdate, -> o_totalprice -> order by -> o_totalprice desc, -> o_orderdate -> limit 100\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: lineitem partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 32986052 filtered: 100.00 Extra: Using where with pushed condition <in_optimizer>(tpch.lineitem.l_orderkey,tpch.lineitem.l_orderkey in ( (/* select#2 */ select tpch.lineitem.l_orderkey from tpch.lineitem group by tpch.lineitem.l_orderkey having (sum(tpch.lineitem.l_quantity) > 300) ), <primary_index_lookup>(tpch.lineitem.l_orderkey in on <auto_key> where ((tpch.lineitem.l_orderkey = materialized-subquery.l_orderkey)))))(t0) Pckrows: 504, susp. 504 (0 empty 0 full). Conditions: 1; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: orders partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tpch.lineitem.l_orderkey rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 1 select_type: PRIMARY table: customer partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tpch.orders.o_custkey rows: 1 filtered: 100.00 Extra: NULL *************************** 4. row *************************** id: 2 select_type: SUBQUERY table: lineitem partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 32986052 filtered: 100.00 Extra: Using temporary; Using filesort 4 rows in set, 1 warning (39.11 sec)

adofsauron avatar Aug 03 '22 02:08 adofsauron