mysql - 如何查看MySQL内部innodb临时表的大小

Question

我看到大量internal temporary disk tables正在被写入。我可以使用 SHOW GLOBAL STATUS 查看计数，其中 Variable_name 如“Created_tmp_disk_tables”。

我知道我可以更新 max_heap_table_size 和 tmp_table_size 来帮助防止这种情况，但如果不知道写入磁盘的表的大小，就很难知道要写入哪些值使用。

有人知道如何找到这个值吗？

Answer 1

这可不容易得到。在 Percona Server 中，可以选择在慢速查询日志中添加其他信息，以显示临时表的大小(请参阅 https://www.percona.com/doc/percona-server/5.7/diagnostics/slow_extended.html )

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# Query_time: 1.009400  Lock_time: 0.000190  Rows_sent: 4  Rows_examined: 1543719  Rows_affected: 0  Rows_read: 4
# Bytes_sent: 278  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

(上面的示例取自 Percona 文档，显示了扩展字段，尽管该示例针对的是未创建临时表的查询，因此大小显示为 0。)

在 Oracle MySQL 中，PERFORMANCE_SCHEMA 中的查询事件中提供了一些相同的扩展信息，但临时表大小不可用。

2014 年，我记录了一个功能请求来提供以下信息:https://bugs.mysql.com/bug.php?id=74484这个错误已经被承认，但据我所知，这还没有实现。

目前还不清楚如何实现这一点，因为任何给定的查询都可能创建多个不同大小的临时表。我相信 Percona 功能会显示这种情况下临时表大小的总和。

我能提供的建议是逐步增加 max_heap_table_size 和 tmp_table_size，并监控 Created_tmp_disk_tables 的增长率由 SHOW GLOBAL STATUS 报告，与 Created_tmp_tables(不使用磁盘的临时表)相比。由于允许的临时表大小能够容纳创建的临时表的更大百分比，因此您应该开始看到磁盘上临时表与内存中临时表的比率下降。

通常不需要增加 tmp_table_size 来容纳所有可能的临时表，无论有多大。您希望最大的异常值使用磁盘。但只要临时表 98% 的时间都使用内存，就应该没问题。这意味着 Created_tmp_disk_tables 与 Created_tmp_tables 的比率应为 1:50 或更高。