MySQL-查找临时磁盘表的原因 [英] Mysql - Finding cause of temp disk tables

问题描述

我最近注意到我的MySQL服务器正在创建相当数量的磁盘表[创建的临时磁盘表:67，创建的临时表:304].

I've recently noticed my MySQL server is creating a reasonably large number of disk tables [created temp disk tables: 67, created temp tables: 304].

我一直试图确定哪些查询正在创建这些表，但是我没有运气.我为慢查询日志启用了耗时超过1秒的查询，但是在那里显示的查询没有任何意义.慢速查询日志中定期显示的唯一查询是使用主键作为where子句更新到用户表上的一行.

I've been trying to identify what queries are creating these tables, but I've had no luck. I've enabled the slow query log for queries taking more than 1 second, but the queries showing up in there don't make sense. The only queries showing up regularly in the slow query log are updates to a single row on a user table, using the primary key as the where clause.

我已经对所有常规运行的查询进行了解释"，而我却是罪魁祸首.

I've run 'explain' on all the queries that run regularly, and I'm coming up blank on the culprit.

推荐答案

EXPLAIN报告可能会说正在使用文件排序"，但这具有误导性.这并不意味着它正在写入文件，而只是意味着它在没有索引的情况下进行排序.

The EXPLAIN report may say "Using filesort" but that's misleading. It doesn't mean it's writing to a file, it only means it's sorting without the benefit of an index.

EXPLAIN报告可能会说正在使用临时表"，但这并不意味着它正在使用磁盘上的临时表.它可以在内存中创建一个小的临时表.该表必须位于 max_heap_table_size 和 tmp_table_size .如果增加tmp_table_size，则还应增加max_heap_table_size进行匹配.

The EXPLAIN report may say "Using temporary" but this doesn't mean it's using a temporary table on disk. It can make a small temp table in memory. The table must fit within the lesser of max_heap_table_size and tmp_table_size. If you increase tmp_table_size, you should also increase max_heap_table_size to match.

另请参见 http://dev.mysql. com/doc/refman/5.1/en/internal-temporary-tables.html 了解有关临时表管理的更多信息.

See also http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html for more info on temporary table management.

但是4个演出的价值非常高！考虑到该内存可以按每个连接使用 .默认值为16兆，因此您将其增加了256倍.

But 4 gigs for that value is very high! Consider that this memory can potentially be used per connection. The default is 16 megs, so you've increased it by a factor of 256.

因此，我们要查找导致临时磁盘表的查询.

So we want to find which queries caused the temp disk tables.

如果运行MySQL 5.1或更高版本，则可以

If you run MySQL 5.1 or later, you can SET GLOBAL long_query_time=0 to make all queries output to the slow query log. Be sure to do this only temporarily and set it back to a nonzero value when you're done! :-)

如果您运行 Percona服务器，则

If you run Percona Server, the slow query log is extended with additional information and configurability, including whether the query caused a temp table or a temp disk table. You can even filter the slow-query log to include only queries that cause a temp table or temp disk table (the the docs I link to).

您还可以使用 mk-query-digest处理Percona Server的慢查询日志并过滤导致临时磁盘表的查询.

You can also process Percona Server's slow-query log with mk-query-digest and filter for queries that cause a temp disk table.

mk-query-digest /path/to/slow.log --no-report --print \
  --filter '($event->{Disk_tmp_table }||"") eq "Yes"'

这篇关于MySQL-查找临时磁盘表的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！