There are some very large tables (TargetTable) that I refer to, and there is a certain procedure that gets stuck in the second iteration and never ends and never crashes. The first iteration always ends in less than a few minutes, regardless of the start of the range (loopIndex) or the size of the range (loopStepShort).
I look forward to your thoughts and suggestions.
[Update 1] This problem disappears if I do one of the following:
- Delete the nested part of the inner join;
- Use a temporary table in memory for the nested part of the inner join (thanks to @SashaPachev);
- Run each iteration of the loop outside the while loop;
- Use a smaller target table.
[Update 2] Solved! . I think a problem could happen when some database indexes were not copied during the database transition. Since when I tried to reproduce the example, this happened for non-indexed tables (using a large number of processors and an almost infinite loop) MariaDB Server, JIRA .
The user configurations for the MySQL InnoDB engine (10.0.21-MariaDB Server, Linux x86_64, Fedora v.21) are as follows:
innodb_buffer_pool_size = 2G
net_write_timeout = 1800
net_read_timeout = 1800
join_buffer_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
max_allowed_packet = 4G
key_buffer = 2G
sort_buffer_size = 512K
And the body of the procedure is as follows:
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, the following are approximate table sizes:
- TABLE: IDS:
- TABLE ROWS: ~ 1,500,000 records,
- DATA DATA: ~ 250 MB,
- INDEX LENGTH: ~ 140 MB,
- TABLE SIZE: ~ 400 MB
- TABLE: TargetTable:
- TABLE ROWS: ~ 6,500,000 records,
- DATA DATA: ~ 4 GB,
- : ~ 350 ,
- : ~ 4.35
- TABLE: SubjectTable:
- TABLE ROWS: ~ 6 500 000 ,
- : ~ 550 ,
- : N/A,
- : ~ 550
.
, Oracle/MariaDB .