May 14, 2025 • Written by ROMAN AGABEKOV
This article is about configuring MySQL's temporary table size settings, specifically the variables tmp_table_size and max_heap_table_size. It explains that MySQL uses the lower value between these two when allocating memory for temporary tables. Setting these values appropriately is important because if temporary tables exceed this size, they are stored on disk, which reduces query performance. The article also discusses conditions that force temporary tables to be stored on disk and how to prevent this using the MEMORY storage engine. Additionally, it highlights how to calculate maximum memory usage for temporary tables based on these settings and max_connections. Finally, it mentions Releem's tool for optimizing these parameters to enhance server performance.
Basic Details
The tmp_table_size variable sets the maximum size for temporary tables in-memory.
Releem automatically tunes tmp_table_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes tmp_table_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
tmp_table_size – Usage
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:
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.
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
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 – Configuration
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.
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.
tmp_table_size Considerations
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:
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.
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
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.
tmp_table_size related Health Checks
Temporary Disk Data - percentage that represents the amount of data that was written to temporary disk tables.
FAQ:
MySQL Temporary Table Size Configuration
MySQL Temporary Table Size Configuration
What is tmp_table_size in MySQL?
tmp_table_size sets the maximum size for in-memory temporary tables. If a temp table exceeds this, MySQL converts it to an on-disk table, which is slower.
How do I check the current tmp_table_size?
Run this SQL command:
SHOW VARIABLES LIKE 'tmp_table_size';
What is a recommended value for tmp_table_size?
Typically 64MB–256MB. However, it should match max_heap_table_size, and total memory usage must be considered.
What’s the difference between tmp_table_size and max_heap_table_size?
MySQL uses the lower of these two values when deciding if it can keep a temporary table in memory. Always set both to the same value.
How do I increase tmp_table_size in MySQL?
Edit your my.cnf:
tmp_table_size = 128M
max_heap_table_size = 128M
Restart MySQL to apply changes.
Is tmp_table_size relevant in MariaDB and Aurora?
Yes. Both MariaDB and Aurora MySQL respect this variable and use it for internal temp table handling.
How does tmp_table_size affect performance?
A low setting causes more temp tables to be written to disk, which slows down queries—especially those involving GROUP BY, ORDER BY, or subqueries.
Can I change tmp_table_size without restarting MySQL?
Yes, you can change it for the current session:
SET SESSION tmp_table_size = 128000000;
SET SESSION max_heap_table_size = 128000000;
What’s a good value for tmp_table_size on AWS RDS or Aurora?
Start with 128MB. Monitor disk-based temp tables and adjust upward if needed. RDS Parameter Groups are required for persistent changes.
How can I monitor if tmp_table_size is too small?
Check for high Created_tmp_disk_tables in:
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
Too many disk-based temp tables may indicate the need to increase tmp_table_size.
tmp_table_size sets the maximum size for in-memory temporary tables. If a temp table exceeds this, MySQL converts it to an on-disk table, which is slower.
How do I check the current tmp_table_size?
Run this SQL command:
SHOW VARIABLES LIKE 'tmp_table_size';
What is a recommended value for tmp_table_size?
Typically 64MB–256MB. However, it should match max_heap_table_size, and total memory usage must be considered.
What’s the difference between tmp_table_size and max_heap_table_size?
MySQL uses the lower of these two values when deciding if it can keep a temporary table in memory. Always set both to the same value.
How do I increase tmp_table_size in MySQL?
Edit your my.cnf:
tmp_table_size = 128M
max_heap_table_size = 128M
Restart MySQL to apply changes.
Is tmp_table_size relevant in MariaDB and Aurora?
Yes. Both MariaDB and Aurora MySQL respect this variable and use it for internal temp table handling.
How does tmp_table_size affect performance?
A low setting causes more temp tables to be written to disk, which slows down queries—especially those involving GROUP BY, ORDER BY, or subqueries.
Can I change tmp_table_size without restarting MySQL?
Yes, you can change it for the current session:
SET SESSION tmp_table_size = 128000000;
SET SESSION max_heap_table_size = 128000000;
What’s a good value for tmp_table_size on AWS RDS or Aurora?
Start with 128MB. Monitor disk-based temp tables and adjust upward if needed. RDS Parameter Groups are required for persistent changes.
How can I monitor if tmp_table_size is too small?
Check for high Created_tmp_disk_tables in:
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
Too many disk-based temp tables may indicate the need to increase tmp_table_size.