How to configure the parameter tmp_table_size in MySQL

MySQL configuration parameter [tmp_table_size] method: first check [tmp_table_size]; then set [tmp_table_size]; finally add [tmp_table_size] under mysqld in the MySQL configuration file [my.cnf].

##More related free learning recommendations: mysql tutorial(Video)

tmp_table_size The memory cache size of the temporary table

The temporary table refers to the temporary data table generated when sql is executed

  # tmp_table_size
  默认值 16777216
  最小值 1
  最大值 18446744073709551615
  // 单位字节 默认值也就是16M多

• View tmp_table_size
  show global variables like 'tmp_table_size';

• Set tmp_table_size
  set global tmp_table_size= 2048; (effective immediately after restart Invalid)

• Add tmp_table_size under mysqld in the MySQL configuration file my.cnf
  [mysqld]
  tmp_table_size = 100000000

Note

The

max_heap_table_size parameter in MySQL will affect the memory cache size of the temporary table.

max_heap_table_size is the table size of the MEMORY memory engine. Because the temporary table is also a memory table, it will also be restricted by this parameter. Therefore, if you want to increase the size of tmp_table_size, you also need to increase the size of max_heap_table_size at the same time.

You can analyze whether you need to increase tmp_table_size through the status of Created_tmp_disk_tables and Created_tmp_tables

View status

show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_tables';
Created_tmp_disk_tables : 磁盘临时表的数量
Created_tmp_tables      : 内存临时表的数量

The above is the detailed content of How to configure the parameter tmp_table_size in MySQL. For more information, please follow other related articles on the PHP Chinese website!