max_heap_table_size

The max_heap_table_size variable sets the maximum size of an user-created table that can be created in memory.

Releem automatically tunes max_heap_table_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.

Max_heap_table_size is a variable closely related to tmp_table_size. Refer to the corresponding variable documentation page to find tuning information for tmp_table_size.

Temporary Tables
Both variables are involved in temporary tables in MySQL. Temporary tables allow users to store a temporary result set from a query. This special type of MySQL table can be reused as necessary within a session. Users cannot directly control the creation of every temporary table. Temporary tables will be automatically created by the server under a variety of conditions. Users can also create temporary tables. This proves especially useful when wanting to store the result of a query and process that result with another query.

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.

Want all of this information to be automatically optimized? Releem can automatically inspect your MySQL, Percona, or MariaDB servers and provide recommended values for max_heap_table_size and tmp_table_size to improve server performance.

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

Command Line:
mysqld> set global max_heap_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]
max_heap_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 max_heap_table_size for clarity, but please see the tmp_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 max_heap_table_size, anticipate the maximum size expected for a memory storage-engine table. If an table is larger than max_heap_table_size, 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. 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 for 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 * 32MB = 6.4 GB

If you're confused, let Releem take handle the configuration process and remove all uncertainty from your system variable settings.

Releem Agent will collect metrics and important system information that is transferred to the Releem Cloud Agent. Releem Cloud Agent will calculate MySQL Performance Score and make any recommendations to improve your server performance. Releem can automatically apply these settings, including any recommendations for max_heap_table_size and tmp_table_size.