mysql临时表个数_mysql 的内存临时表两个参数 tmp_table_size、max_heap_table_size

mysql 5.5

>show processlist;

观察 State 列时，发现经常有 Copying to tmp table、Copying to tmp table on disk

baidu 了下，获取的信息是调整参数 tmp_table_size、max_heap_table_size

这两个参数是mysql对临时表的大小控制

其中

tmp_table_size 控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定

max_heap_table_size 用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。

下面是官方的解释

tmp_table_size

Command-Line Format --tmp-table-size=#

System Variable Name tmp_table_size

Scope Global, Session

Dynamic Yes

Permitted Values Type integer

Default 16777216

Minimum 1024

Maximum 18446744073709551615

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller.

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing

the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

See also Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size

Command-Line Format --max-heap-table-size=#

System Variable Name max_heap_table_size

Scope Global, Session

Dynamic Yes

Permitted Values (32-bit platforms) Type integer

Default 16777216

Minimum 16384

Maximum 4294967295

Permitted Values (64-bit platforms) Type integer

Default 16777216

Minimum 16384

Maximum 1844674407370954752

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow.

The value of the variable is used to calculate MEMORY table MAX_ROWS values.

Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE

or altered with ALTER TABLE or TRUNCATE TABLE.

A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables.

See Section 8.4.4, “Internal Temporary Table Use in MySQL”.

max_heap_table_size is not replicated.

See Section 17.4.1.20, “Replication and MEMORY Tables”, and Section 17.4.1.38, “Replication and Variables”, for more information.

如何修改,这里调整为2G

1)

SET GLOBAL tmp_table_size=2147483648;

SET GLOBAL max_heap_table_size=2147483648;

2)

vi /etc/my.cnf

[mysqld]

max_heap_table_size = 2048M

tmp_table_size = 2048M