主页 / dba / 问题 / 335700
Accepted
Watson
Asked: 2024-02-10 02:15:44 +0800 CST

Mysql 8.0.34 临时表占用整个磁盘

  • 772

运行 lsof +L1

命令 PID 用户 FD 类型 设备大小/关闭 NLINK 节点名称

mysqld 886068 mysql 521u REG 254,1 194824503296 0 29360306 /tmp/#29360306 （已删除）

在 innodb 集群中运行 mysql 3.0.34。磁盘上的 300 GB 内存似乎是随机地被消耗到一个 tmp 文件夹中，一旦满了就会被释放。

寻找建议/如何限制 tmp 表大小？

设置@sys.statement_truncate_len = 1000; 从系统中选择*。statements_with_temp_tables;

生成 1 个标记为 disk_tmp_tables 的项目。

查询 db exec_count Total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen 摘要 SELECT TABLE_NAME FROM information_schema。INNODB_BUFFER_PAGETABLE_NAME 位于何处？限制？，... 1 439.42 毫秒 1 1 1 100 2024-02-09 10:14:54.800889 2024-02-09 10:14:54.800889 d24fb8e96673a518f0f536b9516781644ffb7a5079f 95490cec24804437f34a9

mysql

1 个回答

  1. Best Answer

    MySQL 中没有好的方法来限制磁盘上的临时表大小。

    临时表是由某些查询自动创建的。有关此类情况的列表，请参阅https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html 。

    临时表的大小没有实际限制，因为任何给定的查询都可能执行巨大的笛卡尔积或任何其他需要大量临时空间的操作。这取决于您正在处理的数据集和查询类型。

    多个会话同时执行此类操作也是很常见的。它们每个都有自己的临时表空间，并且每个表空间都可以增长。

    我见过像你这样的情况，空间突然增长了数百GB，耗尽了所有剩余的磁盘空间。

    MySQL 8.0 使用临时表空间池。当优化器创建存储在磁盘上的临时表时，它会使用这些表空间，并根据需要扩展它们以存储给定会话的临时表。当会话断开连接时，这些表空间将被截断，但否则它们可能会继续扩展。阅读https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html了解详细信息。

    我知道减少临时表大小的最佳方法是识别使用临时表的查询，并尝试优化它们，这样它们就不会创建临时表。

    另一种方法是减小数据集的大小，使临时表更小。这可以通过使用索引优化查询来完成，这样它会检查更少的数据，从而减少需要进入临时表的数据量。

    或者最终，归档数据并将其从数据库中删除。

    您可以尝试的另一件事是配置 MySQL Server，以便该innodb_temp_tablespaces_dir选项在空间有限的专用磁盘卷上创建临时表空间。但是，如果查询无法创建所需的大型临时表，这将导致查询以惊人的方式失败。

    • 1

相关问题