mysql tmp_table_size max_heap_table_size

Mysql tmp_table_size max_heap_table_size

提问by user1804332

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=16Mthe report showed me the next average report:
   • 27.37% (created_tmp_disk_tables)
   • 1.16% (created_tmp_files)
   • 71.48% (created_tmp_tables)

1. 与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?

如何优化这些结果？

1. with tmp_table_size=max_heap_table_size=20Min the first hour :

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

2. 与tmp_table_size= max_heap_table_size=20M在第一个小时：

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

After 7 hours (from restart):

7 小时后（从重启开始）：

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

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

It's not what I expected.

这不是我所期望的。

• disk tables decreased from 27.37%to 21.70%-> expected much more
• temporary files rise form 1.16%to 33.75%-> why ?
• memory tables decreased from 71.48%to 44.55%-> strange; expected to rise

• 磁盘表减少27.37%到21.70%-> 预期更多
• 临时文件上升1.16%到33.75%-> 为什么？
• 内存表从减少71.48%到44.55%-> 奇怪；预计会上涨

回答by RolandoMySQLDBA

Whenever you increase tmp_table_sizeand 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没有索引的情况下执行连接或排序（via ）时，必须在内存中形成临时表。这将增加Created_tmp_tables。

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

如果临时表增长到tmp_table_size 中的字节数并且需要更多空间怎么办？发生以下事件序列：

1. Query processing must stop
2. Create a temp table on disk
3. Transfer the contents of the memory-based temp table into the disk-based temp table
4. Drop in the memory-based temp table
5. Query processing continue using the disk-based temp table

1. 查询处理必须停止
2. 在磁盘上创建临时表
3. 将基于内存的临时表的内容转移到基于磁盘的临时表中
4. 删除基于内存的临时表
5. 查询处理继续使用基于磁盘的临时表

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_sizeis too small) or ORDER BYnon-indexed columns or columns with a temp table (maybe sort_buffer_sizeis too small).

这并不奇怪。这只是表明存在需要临时表的查询的事实。它们首先在 RAM 中创建。这只是表明存在连接不好的查询（可能join_buffer_size太小）ORDER BY或非索引列或带有临时表的列（可能sort_buffer_size太小）。

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

内存表从 71.48% 减少到 44.55% -> 奇怪；预计会上涨

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
• sort_buffer_size
• Create indexes that would be used
  • in joins via eq_ref
  • in sorts via index scans in order

• join_buffer_size
• sort_buffer_size
• 创建将要使用的索引
  • 通过 eq_ref 连接
  • 按顺序通过索引扫描排序

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

总体目标应该是尽可能防止临时表的创建。简单地增加tmp_table_size和max_heap_table_size 会导致低效的查询和缺乏适当索引的表横行。