mysql clean up_MySQL程序while循环：一次迭代后卡住 – Cache Clean up

╰半橙微兮° 2023-01-12 04:19 145阅读 0赞

我正在查询一些非常大的表(TargetTable),并且有一个特定的过程在第二次迭代中陷入困境并且永远不会完成也不会崩溃.无论范围的开始(loopIndex)或范围的大小(loopStepShort),第一次迭代总是在不到几分钟内完成.

我期待着听到你的想法和建议.

\[更新1\]如果我执行以下操作之一,此问题就会消失：

>删除内连接的嵌套部分;

>使用内存临时表作为内连接的嵌套部分(感谢@SashaPachev);

>在while循环之外运行每个循环迭代;

>使用较小的TargetTable.

\[更新2\]解决了！我认为在数据库转换过程中没有复制某些数据库索引时可能会发生问题.因为,当我试图重现一个例子时,它发生在非索引表(高CPU使用率和几乎无限循环步骤)MariaDB Server, JIRA.

MySQL InnoDB引擎(10.0.21-MariaDB Server,Linux x86\_64,Fedora v.21)的自定义配置如下：

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

程序主体如下：

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;

最后,下面是表格的近似尺寸：

>表：ids：

>表格行：约1,500,000条记录,

>数据长度：~250 MB,

> INDEX LENGTH：~140 MB,

>表大小：~400 MB

> TABLE：TargetTable：

>表格行：约6,500,000条记录,

>数据长度：~4 GB,

> INDEX LENGTH：~350 MB,

>表大小：~4.35 MB

> TABLE：SubjectTable：

>表格行：约6,500,000条记录,

>数据长度：~550 MB,

> INDEX LENGTH：N / A,

>表大小：~550 MB

提前谢谢了.

我想我必须向Oracle / MariaDB提出错误报告,并更新帖子.

最佳答案 试试这个(免责声明 – 未经测试,可能包含语法错误或错误)：

DROP TABLE IF EXISTS t1;

CREATE TEMPORARY TABLE t1 (key(id)) ENGINE=MEMORY 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;

UPDATE TargetTable AS t0,

(SELECT \_\_index, sessionStartAge

FROM SubjectTable AS t0

INNER JOIN t1 ON t0.id = t1.id AND t0.admission = t1.admission) AS t2

SET t0.sessionStartAge = t1.sessionStartAge

WHERE t0.\_\_index = t2.\_\_index;

我们的想法是使用带有密钥的临时表替换内部子查询,以便外部联接可以使用该密钥.

mysql clean up_MySQL程序while循环：一次迭代后卡住 – Cache Clean up

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关关于npm cache clean --force使用

相关 svn clean up 失败解决方法

相关 mysql clean up_MySQL程序while循环：一次迭代后卡住 – Cache Clean up

相关 SVN clean up的作用

相关 CodeForces 554B. Ohana Cleans Up【思维】

相关【codeforces】Ohana Cleans Up

相关 svn 的clean up 异常和删除操作

相关 - Cleaning Shifts

相关 SVN ---文件加锁，执行clean up命令

相关 svn clean up 失败