mysql tmp_table_size and max_heap_table_size size configurations

Configuration for 16G memory

tmp_table_size = 64M

Let's start with tmp_table_size:

It specifies the maximum value of the internal memory temporary table, to be allocated per thread. (The actual limit is the minimum for tmp_table_size and max_heap_table_size.) If the memory temporary table exceeds the limit, MySQL automatically converts it to a disk-based MyISAM table and stores it in the specified tmpdir directory. By default:

mysql > show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp/ |
+---------------+-------+

When optimizing queries, avoid using temporary tables, and if you can't avoid them, make sure they are in memory. Increase tmp_table_size(and max_heap_table_size) if you need to and you have a lot of group by statements and you have a lot of memory. This variable does not apply to memory tables created by the user (memory table).

You can compare the total number of internal disk-based temporary tables to the total number of temporary tables created in memory (Created_tmp_disk_tables and Created_tmp_tables), and the one-to-one ratio is:

Created_tmp_disk_tables/Created_tmp_tables < 5%

max_heap_table_size

This variable defines the size of the memory table (memory table) that users can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change, i.e. set @max_heap_table_size =#

But there is no use for an existing in-memory table unless the table is recreated (create table) or modified (alter table) or truncate table. Service restart also sets the existing memory table to the value of global max_heap_table_size.

This variable and tmp_table_size1 limit the size of the internal memory table.

For more detailed information, see "How does MySQL use internal temporary tables?" And the memory storage engine.