|
There are some very large tables (TargetTable) that I am querying against, and there is a particular procedure that get stuck in its second iteration and never finishes nor crashes. The first iteration always finishes in less than a few minutes, regardless of the start of the range (loopIndex) or size of the of the range (loopStepShort).
This problem goes away if I do one of the following:
- Remove the nested part of the inner-join;
- Use an in-memory temporary table for the nested part of the inner join;
- Run each loop iteration outside of the while loop;
- Use a smaller TargetTable.
After a lot of head scratching, I suspect the source of the problem could be Not clearing out buffer(s) properly.
The procedure body is as the following:
SET loopIndex = 0;
|
SET loopMax = 20000000;
|
SET loopStepShort = 10000;
|
WHILE loopIndex < loopMax do
|
UPDATE TargetTable AS t0,
|
(SELECT __index, sessionStartAge
|
FROM SubjectTable AS t0
|
INNER JOIN (SELECT t0.id, t0.admission,
|
if(t0.startage is null and t0.endage is null, 21,
|
if(least(t0.startage, t0.endage) <= 1, 1,
|
if(least(t0.startage, t0.endage) <= 4, 2,
|
if(least(t0.startage, t0.endage) <= 9, 3,
|
if(least(t0.startage, t0.endage) <= 14, 4,
|
if(least(t0.startage, t0.endage) <= 19, 5,
|
if(least(t0.startage, t0.endage) <= 24, 6,
|
if(least(t0.startage, t0.endage) <= 29, 7,
|
if(least(t0.startage, t0.endage) <= 34, 8,
|
if(least(t0.startage, t0.endage) <= 39, 9,
|
if(least(t0.startage, t0.endage) <= 44, 10,
|
if(least(t0.startage, t0.endage) <= 49, 11,
|
if(least(t0.startage, t0.endage) <= 54, 12,
|
if(least(t0.startage, t0.endage) <= 59, 13,
|
if(least(t0.startage, t0.endage) <= 64, 14,
|
if(least(t0.startage, t0.endage) <= 69, 15,
|
if(least(t0.startage, t0.endage) <= 74, 16,
|
if(least(t0.startage, t0.endage) <= 79, 17,
|
if(least(t0.startage, t0.endage) <= 84, 18,
|
if(least(t0.startage, t0.endage) <= 89, 19,
|
if(least(t0.startage, t0.endage) <= 120, 20, 21))))))))))))))))))))) AS sessionStartAge
|
FROM SubjectTable AS t0
|
INNER JOIN ids AS t1 ON t0.id = t1.id
|
AND t1.id >= loopIndex
|
AND t1.id < (loopIndex + loopStepShort)
|
GROUP BY t0.id, t0.admission) AS t1
|
ON t0.id = t1.id AND t0.admission = t1.admission) AS t1
|
SET t0.sessionStartAge = t1.sessionStartAge
|
WHERE t0.__index = t1.__index;
|
|
SET loopIndex = loopIndex + loopStepShort;
|
END WHILE;
|
Finally, below are approximate dimensions of the tables:
TABLE: ids:
|
# TABLE ROWS: ~1,500,000 records,
|
# DATA LENGTH: ~250 MB,
|
# INDEX LENGTH: ~140 MB,
|
# TABLE SIZE: ~400 MB
|
TABLE: TargetTable:
|
# TABLE ROWS: ~6,500,000 records,
|
# DATA LENGTH: ~4 GB,
|
# INDEX LENGTH: ~350 MB,
|
# TABLE SIZE: ~4.35 MB
|
TABLE: SubjectTable:
|
# TABLE ROWS: ~6,500,000 records,
|
# DATA LENGTH: ~550 MB,
|
# INDEX LENGTH: N/A,
|
# TABLE SIZE: ~550 MB
|
This issue first raise on Stackoverflowhttp://stackoverflow.com/questions/32945135/mysql-procedure-while-loop-gets-stuck-after-one-iteration-cache-clean-up
|
