MySQL - Temporary Tables

tmp_table_size: The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

max_heap_table_size: This variable sets the maximum size to which MEMORY tables are allowed to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

tmp_table_size: Specifies the maximum size of temporary tables that can be stored in the memory. If the value is too small, MySQL will place the temporary table on disk. To determine the proper value for tmp_table_size, compare the values of Created_tmp_tables (number of temporary tables that are created), and Created_tmp_disk_tables (number of temporary tables that are placed on disk) status variables. You should increase tmp_table_size if the ratio of Created_tmp_disk_tables and Created_tmp_tables exceeds 2%. See http://www.mactech.com/articles/mactech/Vol.22/22.10/WebPerformanceTuning/index.html

A temporary table differs from a non-TEMPORARY table in the following ways:

  • A temporary table is visible only to the client that created it and may only be used by that client. This means that different clients can create TEMPORARY tables that have the same name and no conflict occur
  • A temporary table exists only for the duration of the connection in which it was created. The server drops a TEMPORARY table automatically when the client connection ends.
  • A temporary table may have the same name as a non-temporary table. The non-temporary table becomes hidden to the client that created the temporary table as long as the temporary table exists.
  • A temporary table can be renamed only with ALTER TABLE. You cannot use RENAME TABLE.

A table created with TEMPORARY is not the same thing as a MEMORY table. A MEMORY table is temporary in the sense that its contents are lost if you restart the server, but the table definition continues to exists in its database. A TEMPORARY table exists only while the client that created it remains connected, and then disappears completely. A MEMORY table is available to any client that has permission to access it, not just to the client that created it.

A TEMPORARY table is created using CREATE TEMPORARY TABLE.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size
http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html
http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/
http://www.mysqlperformanceblog.com/?s=set+tmp_table_size
http://www.webhostingtalk.com/showthread.php?t=618602
http://www.linuxweblog.com/tune-my.cnf

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License