如何查看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中，有一些选项可以在慢速查询日志中添加其他信息，以显示临时表的大小(请参阅

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，并监视SHOW GLOBAL STATUS报告的Created_tmp_disk_tables与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屋！