mysql的tmp_table_size和max_heap_table_size

先说下tmp_table_size吧：

它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认:

mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ | 
+---------------+-------+

优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。这个变量不适用与用户创建的内存表(memory table).

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

这个变量和tmp_table_size一起限制了内部内存表的大小。

如果想知道更详细的信息，请参考“MySQL是怎样使用内部临时表的？”和“内存存储引擎”

Temporary tables can be created under conditions such as these:

• UNION queries use temporary tables.

• Some views require temporary tables, such those evaluated using theTEMPTABLE algorithm, or that use UNION or aggregation.

• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

• DISTINCT combined with ORDER BY may require a temporary table.

• If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.2.1, “Optimizing Queries with EXPLAIN”).

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. This differs from MEMORY tables explicitly created withCREATE TABLE: For such tables, only the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

When the server creates an internal temporary table (either in memory or on disk), it increments theCreated_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table

• Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

• The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

 max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

链接：http://www.cnblogs.com/sunss/archive/2011/01/10/1932004.html

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size

We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ?

Wrong.

In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM.

To make things more confusing this is not what you would read in MySQL manual as far as I understand it:
From http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal tables are:

* If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.
* MEMORY tables are never converted to disk tables. To ensure that you don’t accidentally do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement.

For me this description looks as there are two types of in memory tables where internal ones are controlled by tmp_table_sizeand explicit ones use max_heap_table_size value.

Interesting enough there is 2.5 years old bug on this matter which just recently started to get attention. I understand it could be complex to fix but why real behavior was not documented in the manual at least ?

But what surprises me the most is how this issue was fixed (patch pending):

ChangeSet@1.2311, 2006-11-16 04:11:16+03:00, [e-mail] +6 -0
BUG #4291 fix: new configuration option “disk-tmp-table-size”
introduced to set maximum expected on-disk temporary table size
and avoid mix-up of tmp_table_size and max_heap_table_size

ChangeSet@1.2358, 2007-01-03 14:45:26+03:00, [e-mail] +7 -0
BUG #4291: max_heap_table_size affects creation of disk-based temporary table

fix: the new system variable memory_tmp_table_size is introduced;
it stands now for the exact purpose the Manual says
tmp_table_size used to do.

tmp_table_size retains to (give a hint about a)
limit of the on-disk temporary table size. The limit imposed upon
the disk-based temporary tables is still quite relative due to MyISAM
current implementation restrictions.

So now we’re getting 4 variables instead of two ?

It is unclear about tmp_table_size – if it is going to be read only variable to tell you what maximum temporary table size is or is it going to limit on disk table size ? Any of behaviors have nothing to do with previous behavior and second one would break a lot of things.

In my opinion it would be much better to change it to match what users know about it, what is documented in the manual, config files, tons of books and articles on the web – you will have users expecting old behavior for years.

The size restriction of on disk temporary table could be good to add but that should have been another variable.

链接：http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/

没看懂，原文章下面还有讨论