Mysql tmp_table_size max_heap_table_size

On my server 2 days ago my tmp_table_size = max_heap_table_size(16M).

两天前在我的服务器上，我的tmp_table_size = max_heap_table_size(16M)。

I made a cron job that runs once an hour and generates a report starting from : created_tmp_disk_tables, created_tmp_files, created_tmp_tables

我做了一个cron作业，每小时运行一次，并生成一个报告，从:created_tmp_disk_tables、created_tmp_files、created_tmp_tables开始

In my report : created_tmp_disk_tables+created_tmp_files+created_tmp_tables=100% of my temporary data

在我的报告中:created_tmp_disk_tables+created_tmp_files+created_tmp_tables=我的临时数据的100%

With that :

与:

1. with tmp_table_size=max_heap_table_size=16M the report showed me the next average report:
   • 27.37% (created_tmp_disk_tables)
   • 27.37%(created_tmp_disk_tables)
   • 1.16% (created_tmp_files)
   • 1.16%(created_tmp_files)
   • 71.48% (created_tmp_tables)
   • 71.48%(created_tmp_tables)
2. 对于tmp_table_size=max_heap_table_size=16M，报告显示了下一个平均报表:27.37% (created_tmp_disk_tables) 1.16% (created_tmp_files) 71.48% (created_tmp_tables)

How can I optimize these results?

如何优化这些结果?

2. with tmp_table_size=max_heap_table_size=20M in the first hour :

   使用tmp_table_size=max_heap_table_size=20M在第一个小时:

   • 23.48% (created_tmp_disk_tables)
   • 23.48%(created_tmp_disk_tables)
   • 32.44% (created_tmp_files)
   • 32.44%(created_tmp_files)
   • 44.07% (created_tmp_tables)
   • 44.07%(created_tmp_tables)

After 7 hours (from restart):

7小时后(重启后):

• 21.70% (created_tmp_disk_tables)
• 21.70%(created_tmp_disk_tables)
• 33.75% (created_tmp_files)
• 33.75%(created_tmp_files)
• 44.55% (created_tmp_tables)
• 44.55%(created_tmp_tables)

It's not what I expected.

这不是我想要的。

• disk tables decreased from 27.37% to 21.70% -> expected much more
• 磁盘表从27.37%下降到21.70% ->预期更多
• temporary files rise form 1.16% to 33.75% -> why ?
• 临时文件从1.16%增加到33.75% ->为什么?
• memory tables decreased from 71.48% to 44.55% -> strange; expected to rise
• 记忆表从71.48%下降到44.55% -> strange;将上升

1 个解决方案

#1

Whenever you increase tmp_table_size and max_heap_table_size, keep in mind that setting these does not make queries behave better. It actually make inefficient queries behave worse than before. Under what circumstances?

每当您增加tmp_table_size和max_heap_table_size时，请记住，设置这些不会使查询表现得更好。它实际上使低效查询的行为比以前更糟糕。在什么情况下?

When a query performs a join or sort (via ORDER BY) without the benefit of an index, a temp table has to be formed in memory. This would increment Created_tmp_tables.

当查询执行连接或排序(通过ORDER BY)而没有索引的好处时，必须在内存中形成临时表。这将增加Created_tmp_tables。

What if the temp table grows to the number of bytes in tmp_table_size and needs more space? The following sequence of events happens:

如果临时表增长到tmp_table_size中的字节数，并且需要更多的空间，该怎么办?事件发生顺序如下:

1. Query processing must stop
2. 查询处理必须停止
3. Create a temp table on disk
4. 在磁盘上创建一个临时表
5. Transfer the contents of the memory-based temp table into the disk-based temp table
6. 将基于内存的临时表的内容转移到基于磁盘的临时表中
7. Drop in the memory-based temp table
8. 在基于内存的临时表中删除。
9. Query processing continue using the disk-based temp table
10. 查询处理继续使用基于磁盘的临时表

This process increments Created_tmp_disk_tables

这个过程的增量Created_tmp_disk_tables

Knowing these mechanisms, let's explore what happened in each instance

了解了这些机制后，让我们来探究每个实例中发生了什么

disk tables decreased from 27.37% to 21.70% -> expected much more

磁盘表从27.37%下降到21.70% ->预期更多

This could easily happen if the queries that ran before have cached results remaining in RAM. This would eliminate the need to process the query from the beginning and not recreate the same large temp tables.

如果之前运行的查询在RAM中缓存了结果，那么这很容易发生。这将消除从一开始就处理查询，而不重新创建相同的大型临时表的需要。

temporary files rise form 1.16% to 33.75% -> why ?

临时文件从1.16%增加到33.75% ->为什么?

This is not surprising. This simply brings out the fact that there are queries that require temp tables. They were created in RAM first. This just indicates the presence of queries that do not join well (maybe join_buffer_size is too small) or ORDER BY non-indexed columns or columns with a temp table (maybe sort_buffer_size is too small).

这并不奇怪。这只会导致需要临时表的查询。它们首先是在RAM中创建的。这只表明存在不连接良好的查询(可能join_buffer_size太小)，或者没有索引的列或带有临时表的列(可能sort_buffer_size太小)。

memory tables decreased from 71.48% to 44.55% -> strange; expected to rise

记忆表从71.48%下降到44.55% -> strange;将上升

This is not surprising either. If there are enough calls for the same query with the same values, sorts and joins may be preempted by the fulfillment of queries from previously cached results.

这也不足为奇。如果对具有相同值的相同查询有足够的调用，那么排序和连接可以被来自先前缓存的结果的查询的执行所抢占。

RECOMMENDATION

In light of these things, here is what could be adjusted:

鉴于这些因素，以下是可以调整的:

• join_buffer_size
• join_buffer_size
• sort_buffer_size
• sort_buffer_size
• Create indexes that would be used
  • in joins via eq_ref
  • 在连接通过eq_ref
  • in sorts via index scans in order
  • 按顺序通过索引扫描进行排序
• 通过eq_ref按顺序通过索引扫描创建将在连接中使用的索引

The overall goal should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok.

总体目标应该是尽可能避免创建临时表。只需增加tmp_table_size和max_heap_table_size，就可以让效率低下的查询和缺少适当运行索引的表变得混乱。