Home > Database > Mysql Tutorial > body text

How to configure the parameter tmp_table_size in MySQL

coldplay.xixi
Release: 2020-10-12 11:24:40
Original
3873 people have browsed it

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].

How to configure the parameter tmp_table_size in MySQL

##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多
Copy after login

  • 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      : 内存临时表的数量
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template