如何查看 MySQL 内部 innodb 临时表的大小 [英] How to see size of MySQL internal innodb temporary tables

问题描述

我看到了大量的内部正在写入临时磁盘表.我可以通过 SHOW GLOBAL STATUS where Variable_name like 'Created_tmp_disk_tables' 查看计数.

I'm seeing a large number of internal temporary disk tables being written. I can see the count with SHOW GLOBAL STATUS where Variable_name like 'Created_tmp_disk_tables'.

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

I know I can update max_heap_table_size and tmp_table_size to help prevent this, but without knowing the size of the tables getting written to disk, it's difficult to know what values to use.

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

Does anyone know how to go about finding this value?

推荐答案

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

This is not easy to get. In Percona Server, there are options to add additional information in the slow query log that shows the size of temp tables (see 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.)

(The example above, taken from the Percona documentation, shows the extended fields, although the example is for a query that did not create temp tables, so the size is shown as 0.)

在 Oracle MySQL 中，某些相同的扩展信息在 PERFORMANCE_SCHEMA 中的查询事件中可用 - 但不是临时表大小.

In Oracle MySQL, some of the same extended information is available in query events in the PERFORMANCE_SCHEMA—but not the temp table sizes.

在 2014 年，我记录了一个功能请求以提供此信息:https://bugs.mysql.com/bug.php?id=74484 并且此错误已被确认，但据我所知尚未实现.

In 2014, I logged a feature request to supply this information: https://bugs.mysql.com/bug.php?id=74484 and this bug has been acknowledged, but this has not been implemented as far as I know.

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

It's a little bit unclear how this would be implemented, since it's possible for any given query to create multiple temp tables of different sizes. I believe the Percona feature shows the sum total of the temp table sizes in such cases.

我只能提供一个建议，以增量方式增加 max_heap_table_size 和 tmp_table_size，并监控 Created_tmp_disk_tables 的增加率由 SHOW GLOBAL STATUS 报告，与 Created_tmp_tables(不使用磁盘的临时表)相比.由于允许的 tmp 表大小能够容纳更多创建的临时表，您应该开始看到磁盘临时表与内存中临时表的比率下降.

All I can offer as a suggestion is to increase the max_heap_table_size and tmp_table_size in increments, and monitor the rate of increase of the Created_tmp_disk_tables reported by SHOW GLOBAL STATUS, compared to Created_tmp_tables (temp tables that did not use disk). As the allowed tmp table size is able to hold a greater percentage of temp tables created, you should start to see the ratio of on-disk temp tables to in-memory temp tables decrease.

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

It's typically not necessary to increase tmp_table_size to hold every possible temp table, no matter how large. You want the largest outliers to use the disk. But as long as the temp tables use memory 98% of the time, you should be fine. That would mean that the ratio of Created_tmp_disk_tables to Created_tmp_tables should be 1:50 or more.

这篇关于如何查看 MySQL 内部 innodb 临时表的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！