May 14, 2025 • Written by ROMAN AGABEKOV
This article explains how tmp_table_size and max_heap_table_size relate to temporary table limits in Oracle MySQL. The applicable limit depends on the table type, storage engine, and MySQL version. Releem provides configuration recommendations.
Basic Details
In Oracle MySQL, tmp_table_size affects limits for certain internal in-memory temporary tables, depending on the storage engine and version.
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:
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: Check tmp_table_size and max_heap_table_size in the context of the temporary table engine and workload. See the max_heap_table_size page for details.
Command Line:
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: Check tmp_table_size and max_heap_table_size in the context of the temporary table engine and workload. See the max_heap_table_size page for details.
tmp_table_size Considerations
For internal in-memory temporary tables using the MEMORY engine, the per-table limit is the smaller of tmp_table_size and max_heap_table_size. In Oracle MySQL 8.0.28 and later, tmp_table_size also limits an individual internal TempTable table; max_heap_table_size does not limit TempTable. Temporary-table memory depends on the workload, concurrency, engine, and other TempTable limits. Multiplying a table limit by max_connections does not establish a server-wide memory maximum. For more information about memory planning, see our MySQL Memory Usage guide.
Don't limit your server performance by setting incorrect values for tmp_table_size and max_heap_table_size – Releem can help.
For more detailed memory planning, check out our MySQL Memory Calculators to optimize your configuration precisely.
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.
Don't limit your server performance by setting incorrect values for tmp_table_size and max_heap_table_size – Releem can help.
For more detailed memory planning, check out our MySQL Memory Calculators to optimize your configuration precisely.
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?
In Oracle MySQL, tmp_table_size affects certain internal in-memory temporary table limits. Its effect depends on the storage engine and MySQL version.
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?
Choose a value based on your workload, MySQL version, temporary table engine and available memory. There is no universal value for every server.
What’s the difference between tmp_table_size and max_heap_table_size?
For an internal MEMORY table, the smaller value is the per-table limit. For TempTable in MySQL 8.0.28 and later, tmp_table_size limits an individual internal table; max_heap_table_size does not limit TempTable.
How do I increase tmp_table_size in MySQL?
Review the temporary table engine, workload and memory available before changing tmp_table_size. Check how changes are applied and persisted in your MySQL environment.
Is tmp_table_size relevant in MariaDB and Aurora?
Check the documentation for your MariaDB or Aurora MySQL version: temporary table behavior and configuration can differ from Oracle MySQL.
How does tmp_table_size affect performance?
Temporary table limits can affect whether internal tables use memory or disk, but behavior depends on the engine, version and workload. Measure the effect before changing the setting.
Can I change tmp_table_size without restarting MySQL?
In Oracle MySQL, tmp_table_size can be changed dynamically at session or global scope. A global change does not persist through a restart by itself. Check your environment before making changes.
What’s a good value for tmp_table_size on AWS RDS or Aurora?
Choose a value for your workload and available memory, and consult the documentation for your RDS or Aurora engine version and parameter group settings.
How can I monitor if tmp_table_size is too small?
Review Created_tmp_tables and Created_tmp_disk_tables alongside your workload and temporary table engine settings. Created_tmp_disk_tables does not count all TempTable overflow through memory-mapped files.
In Oracle MySQL, tmp_table_size affects certain internal in-memory temporary table limits. Its effect depends on the storage engine and MySQL version.
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?
Choose a value based on your workload, MySQL version, temporary table engine and available memory. There is no universal value for every server.
What’s the difference between tmp_table_size and max_heap_table_size?
For an internal MEMORY table, the smaller value is the per-table limit. For TempTable in MySQL 8.0.28 and later, tmp_table_size limits an individual internal table; max_heap_table_size does not limit TempTable.
How do I increase tmp_table_size in MySQL?
Review the temporary table engine, workload and memory available before changing tmp_table_size. Check how changes are applied and persisted in your MySQL environment.
Is tmp_table_size relevant in MariaDB and Aurora?
Check the documentation for your MariaDB or Aurora MySQL version: temporary table behavior and configuration can differ from Oracle MySQL.
How does tmp_table_size affect performance?
Temporary table limits can affect whether internal tables use memory or disk, but behavior depends on the engine, version and workload. Measure the effect before changing the setting.
Can I change tmp_table_size without restarting MySQL?
In Oracle MySQL, tmp_table_size can be changed dynamically at session or global scope. A global change does not persist through a restart by itself. Check your environment before making changes.
What’s a good value for tmp_table_size on AWS RDS or Aurora?
Choose a value for your workload and available memory, and consult the documentation for your RDS or Aurora engine version and parameter group settings.
How can I monitor if tmp_table_size is too small?
Review Created_tmp_tables and Created_tmp_disk_tables alongside your workload and temporary table engine settings. Created_tmp_disk_tables does not count all TempTable overflow through memory-mapped files.
Turn MySQL Pain Into Performance.
Get expert insights, real benchmarks, and practical tuning tips — straight from the team behind Releem.
Monthly newsletter. Zero fluff. All signal.