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 * 16MB = 3.2 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