Mysql tmp_table_size and max_heap_table_size, mysqltmptablesize

Mysql tmp_table_size and max_heap_table_size, mysqltmptablesize

16 GB memory configuration

Copy codeThe Code is as follows:
Tmp_table_size = 64 M

Let's talk about tmp_table_size first:

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

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

When optimizing query statements, avoid using temporary tables. If you cannot, ensure that these temporary tables exist in the memory. If necessary, you have a lot of group by statements and a lot of memory to increase the value of tmp_table_size (and max_heap_table_size. This variable is not applicable to memory tables created by users ).

You can compare the total number of temporary tables based on disks and the total number of temporary tables created in the memory (Created_tmp_disk_tables and Created_tmp_tables). The general proportional relationship is as follows:

Created_tmp_disk_tables/Created_tmp_tables <5%

Max_heap_table_size

This variable defines the size of the memory table that can be created by the user. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic changes, that is, set @ max_heap_table_size = #

But there is no use for an existing memory table, unless the table is re-created (create table), modified (alter table), or truncate table. When the service is restarted, the existing memory table is set to the global max_heap_table_size value.

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

For more details, see "How does MySQL use an internal temporary table ?" And "memory storage engine"