tmp_table_size

The tmp_table_size variable sets the maximum size for temporary tables in-memory.

Tmp_ table_size is a variable closely related to max_heap_table_size. Refer to the corresponding variable documentation page to find tuning information for max_heap_table_size.

Temporary Tables
Both variables are involved in temporary table creation in MySQL. There are two different types of MySQL temporary tables that can be created:

• User-created temporary tables, from using the CREATE TEMPORARY TABLE command
• Automatically created temporary tables, resulting from the evaluation of certain statements and queries

Only the client that creates a temporary table can access it. Temporary table names should differ from permanent table names to prevent mistakes and accidental data loss. Temporary tables are dropped as soon as the current session is closed.

Releem can handle the optimization of tmp_table_size and max_heap_table_size, taking any guesswork out of the equation. Releem will examine the configuration of your servers and make system variable recommendations to improve server performance.

Tmp_table_size system variable can be configured using the command line or configuration file:

Command Line:
mysqld> set global tmp-table-size = XX

Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.

Configuration File:
[mysqld]
tmp_table_size = XX

Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.

REMINDER: You should be setting both max_heap_table_size and tmp_table_size at the same time. This article is addressing tmp_table_size, but please see the max_heap_table_size to learn more about that system variable and how to set it.

32M to 64M is the commonly suggested initial value to set tmp_table_size and max_heap_table_size. Important to note, that MySQL will take the LOWER of the two values assigned to these variables.

When selecting a value for tmp_table_size, anticipate the maximum size expected for temporary tables in memory. If an internal table is larger than tmp_table_size has been set, it will be stored on the disk. Storage on disk will reduce performance and slowdown queries attempting to use these tables.

Conditions that will force temporary tables to be converted to Innodb on disk:

• TEXT or BLOB column in table
• Column in a GROUP BY or DISTINCT clause larger than 512 bytes
• Column larger than 512 bytes in the SELECT list, if UNION or UNION ALL

You can prevent tables from being converted to the disk format, by using CREATE TABLE ENGINE MEMORY. When using this command, once the maximum table size is reached, no more new data will be added and the table will stay in internal memory.

The maximum memory usage from temporary tables can be determined by multiplying the value for system variable max_connections and the lower value between tmp_table_size and max_heap_table_size. For example, if variables are set like:

max_connections = 200
tmp_table_size = 32M
max_heap_table_size = 32M

The maximum server memory usage would be:
200 * 16MB = 3.2 GB

Don't limit your server performance by setting incorrect values for tmp_table_size and max_heap_table_size – Releem can help.

Releem Agent will collect metrics from your servers that are transferred to the Releem Cloud Platform. Releem Cloud Platform will make any recommendations to improve your server performance and then automatically apply these settings.