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.