临时表上的键重复出现的问题

Question

我们运行一个大型论坛，其中有大量的读写操作，特别是对于posts和topics表，它们都是 innodb。

上周我开始用 innobackupex 做 12 小时的备份，因为 mysqldump 需要很长时间（posts表中有 700 多万行）。某物不喜欢这些备份，因为我每隔一天就会遇到重复出现的问题。

症状；

网站首页开始抛出错误，
日志开始显示错误，例如Error: 126 - Incorrect key file for table '/tmp/mysql/#sql_4e87_14.MYI'; try to repair it
/tmp/dir已填满，我们开始进入Error: 1030 - Got error 28 from storage engine日志。

唯一的解决方法是optimize table在每个帖子和主题表上进行。

我尽我所能阻止 MySQL 使用磁盘存储临时表，但如果它也用完了我所有的内存，我就会遇到更多问题。

我的my.cnf在这里；https://gist.github.com/cbiggins/0aa26f6defb7a14541d7

盒子有 32GB 内存，我通常用不到这么多。目前用了 15GB。

提前致谢。

更新 1：尽管 conf 看起来有复制，但实际上没有。这是一个独立实例。

更新 2：现在已经超过 24 小时没有备份了，问题又出现了。所以这不是备份的结果。

更新 3：我现在使用 tmpfs 为 MySQL 提供了 20gb 的临时空间。此处有说明。接下来会观察一段时间，看看进展如何。

更新 4：我发现了一个杀手级查询！13 秒内检查了 230 万行。同时执行 20 次，我很快就填满了新的 20GB 临时目录。我禁用了使用此查询的块，提供了一些反馈给维护者。

我决定购买一台超便宜的专用服务器来复制并运行备份。希望我们能再次看到我的正常运行时间攀升。:)

Answer 1

问题是 /tmp/ 已填满并且 MySQL 将一些文件放在那里。

MySQL 在处理子查询时可以做出的选择之一：

参考：MySQL 5.6 -子查询优化

将子查询具体化为具有索引的临时表，并使用临时表执行连接。索引用于删除重复项。索引还可能稍后在将临时表与外部表连接时用于查找；如果没有，则扫描该表。

您可以关闭此功能（subquery_materialization_cost_based），它将使用不同的策略。

参考：MySQL 5.6 -控制可切换优化

另一个选项是通过添加更多空间或让 MySQL 将其临时文件放在其他地方来防止 /tmp/ 填满。