MySQL tmp_table_size and max_heap_table_size size configuration _mysql

Configuration for 16G of memory

Copy Code code as follows:

Tmp_table_size = 64M

Let's say Tmp_table_size first:

It sets the maximum value of the internal memory temp table, and each thread is allocated. (The minimum value of tmp_table_size and Max_heap_table_size is actually limited.) If the memory temp table exceeds the limit, MySQL automatically converts it into a disk-based myisam table, stored in the specified Tmpdir directory, by default:

Mysql> Show variables like "Tmpdir";
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Tmpdir | /tmp/|
+---------------+-------+

When optimizing query statements, avoid using temporary tables, and if you can't avoid them, make sure they are in memory. If necessary and you have many group by statements, and you have a lot of memory, increase the value of tmp_table_size (and Max_heap_table_size). This variable does not apply to the user-created memory table (memory table).

You can compare the total number of internal disk-based temporary tables and the total number of temporary tables created in memory (Created_tmp_disk_tables and Created_tmp_tables), and the general proportional relationship 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 the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change, that is, set @max_heap_table_size =#

, but it is useless for an existing memory table unless the table is recreated (create table) or modified (ALTER TABLE) or TRUNCATE table. Service restart also sets the already existing memory table as a global max_heap_table_size value.

This variable, together with Tmp_table_size, limits the size of the internal memory table.

For more detailed information, please refer to "How does MySQL use an internal temp table?" and memory storage Engine