MySQL 临时表位置

Question

运行查询时出现以下错误：

[ERROR] [MY-013132] [Server] The table '/tmp/#sql1127b_9_0' is full!

我进入 /tmp 目录，但是找不到这个表，我猜测当查询运行结束后它可能被删除了。

于是我在查询运行时进入该目录（该查询大约需要几分钟 - 针对 4000 万条记录），但我仍然看不到这个表。我刷新了目录，但还是看不到它。那我该怎么办呢？我想看到这个表，并确定其位置，以便找出为什么在有大量可用磁盘空间的情况下它会填满。

我使用的是 MySQL 8.0.23 - 我之前在同一台机器上使用 MySQL 5.7.33 和相同的数据库和查询，从未遇到过问题。

我的磁盘空间如下：

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           3.2G  3.7M  3.2G   1% /run
/dev/sdc3       215G   38G  167G  19% /
tmpfs            16G     0   16G   0% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
tmpfs           4.0M     0  4.0M   0% /sys/fs/cgroup
/dev/sdb6       107G   81G   21G  80% /media/Kingston_SSD_120GB
/dev/sda        459G  335G  101G  77% /media/Hitachi
/dev/sdc2        33M  7.8M   25M  24% /boot/efi
tmpfs           3.2G  120K  3.2G   1% /run/user/1001

我没有为/tmp设置任何磁盘空间 - 可以看到，/上有167GB的可用空间。

- Charlie Coder

---

你是立即收到错误还是过了一段时间后才出现的？ - Derviş Kayımbaşıoğlu

嗨Derviş。几分钟后我会收到错误。谢谢。 - Charlie Coder

Answer 1

MySQL使用了一种技巧，这种技巧已经成为POSIX系统的一部分。它打开临时文件，然后立即取消链接。因此，在任何目录列表中都无法查看它。但是，像UNIX和Linux这样的POSIX系统在进程保持对未链接文件的打开文件句柄时不应该实际删除它。因此，一旦使用临时表的查询完成，它将关闭文件句柄，然后操作系统将自动删除文件并释放它使用的存储空间。

这通常比要求服务器代码在完成后记住删除临时文件要好。它还考虑到线程终止或mysqld崩溃等情况。至少它不会留下过时的临时文件在你的文件系统上。

您可以使用查看未链接文件的大小。我会让你自己去查找如何使用该命令的示例（Google是你的朋友）。

有可能一个临时文件占用了您的167GB的可用空间。

或者它只使用了8GB的临时文件，但您可能有20个线程同时执行相同的查询。我曾经见过这种情况发生过一次。

但更有可能的是，您的值限制了临时表的大小。

如果达到限制，您可以提高该配置选项，可以作为需要时的会话变量或全局变量在中。

但我首先会尝试优化查询。为什么需要创建如此大的临时表？它是否可以优化以检查更少的行，或者完全避免创建临时表？

- Bill Karwin

---

非常感谢你提供的精彩信息，Bill！这确实解释了那个看不见的文件！我会尝试你的建议并回复你。我不是MySQL的专家，所以查询可能不够好，并且它正在从一个PHP脚本中查询4000万条记录/行，该脚本将许多（大）值存储在变量中，这些变量都是由我自己手工编织的！只是有点困惑为什么旧版MySQL之前可以完美运行。 - Charlie Coder

每个MySQL版本都会更改其优化器代码，必然会有边缘情况的查询在新版本中不使用索引，即使它们在旧版本中使用了索引。当您进行重大版本升级时，您可能需要重新分析查询优化。 - Bill Karwin

再次感谢您，比尔。我已经接受了这个答案，因为它回答了我关于临时文件位置的原始问题。在尝试其他增加方法失败后，我将tmp_table_size和max_heap_table_size增加到大于磁盘可用大小的值。仍然出现表满错误，但是您给了我很好的指引。 - Charlie Coder

你不应该将max_heap_table_size设置为大于你想要使用的RAM量。 - Bill Karwin

Answer 2

来自官方文档：

在Unix系统上，MySQL使用TMPDIR环境变量的值作为存储临时文件的目录路径名。如果未设置TMPDIR，则MySQL使用系统默认值，通常为/tmp、/var/tmp或/usr/tmp。

因此，如果您没有配置TMPDIR，则您的文件需要位于/tmp、/var/tmp或/usr/tmp下。

如果您立即（在一两秒内）收到此错误，则我怀疑这是权限问题。但否则，看起来临时表确实填满了磁盘。

编辑：尝试在执行查询时查找文件。因为SQL错误后该文件将被删除。

此外，此帖子可以帮助您

如何限制临时表的大小？

- Derviş Kayımbaşıoğlu

---

再次感谢Derviş。我运行了mysql> SHOW VARIABLES LIKE 'tmpdir'; 得到：| tmpdir | /tmp，这与我得到的错误一致。我尝试了你的建议，在其他目录中查找，但那里什么也没有。MySQL告诉我临时目录是/tmp - 错误告诉我临时目录是/tmp，但我在那里找不到表文件。我将研究临时表的大小，但在MySQL 5上一切都运行得非常完美 - 只有在升级到MySQL 8后才出现此错误，因此不愿意在以前完美运行的东西上做额外的工作。 - Charlie Coder

在查询运行的几分钟内，我还尝试使用“find / -name '#sql*'”在整个文件系统中搜索“#sql”，但没有找到任何结果。 - Charlie Coder