可用到的语句:
SET GLOBAL max_heap_table_size=522715200;512M
SHOW status like 'created%';
SHOW variables LIKE '%table_size';
以下内容整理自mysql手册(tmp_table_size/
max_heap_table_size
):
Command-Line Format | --tmp_table_size=# |
Option-File Format | tmp_table_size |
System Variable Name | tmp_table_size |
Variable Scope | Global, Session |
Dynamic Variable | Yes |
Permitted Values | |
Type | numeric |
Default | system dependent |
Range | 1024 .. 4294967295 |
The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum oftmp_table_size
and max_heap_table_size
.) 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
(andmax_heap_table_size
if necessary) if you do many advanced GROUP BY
queries and you have lots of memory. This variable does not apply to user-created MEMORY
tables.
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.
Command-Line Format | --max_heap_table_size=# |
Option-File Format | max_heap_table_size |
System Variable Name | max_heap_table_size |
Variable Scope | Global, Session |
Dynamic Variable | Yes |
Permitted Values | |
Platform Bit Size | 32 |
Type | numeric |
Default | 16777216 |
Range | 16384 .. 4294967295 |
Permitted Values | |
Platform Bit Size | 64 |
Type | numeric |
Default | 16777216 |
Range | 16384 .. 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 existingMEMORY
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 globalmax_heap_table_size
value.
This variable is also used in conjunction with tmp_table_size
to limit the size of internal in-memory tables. SeeSection 8.8.5, “How MySQL Uses Internal Temporary Tables”.
8.8.5. How MySQL Uses Internal Temporary Tables
In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the MEMORY
storage engine, or stored on disk and processed by the MyISAM
storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it.
Temporary tables can be created under conditions such as these:
-
If there is an
ORDER BY
clause and a differentGROUP BY
clause, or if theORDER BY
orGROUP BY
contains columns from tables other than the first table in the join queue, a temporary table is created. -
DISTINCT
combined withORDER BY
may require a temporary table. -
If you use the
SQL_SMALL_RESULT
option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
To determine whether a query requires a temporary table, use EXPLAIN
and check the Extra
column to see whether it says Using temporary
. See Section 8.2.1, “Optimizing Queries with EXPLAIN
”.
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
The SHOW COLUMNS
and The DESCRIBE
statements use BLOB
as the type for some columns, thus the temporary table used for the results is an on-disk table.
If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size
and max_heap_table_size
values. This differs from MEMORY
tables explicitly created withCREATE TABLE
: For such tables, the max_heap_table_size
system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.
When the server creates an internal temporary table (either in memory or on disk), it increments theCreated_tmp_tables
status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables
status variable.
相关推荐
(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下,默认: mysql> show variables like ...
通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的...
内存表,就是放在内存中的表,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M,内存表与临时表并不相同,临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_...
内存表,就是放在内存中的表,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M,内存表与临时表并不相同,临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_...
这几天碰到这个错误。 至于我改的这几个值是不是正确的解决方法,目前还不知道。先贴出来。 如果过一段时间我还是没有更新,那以后同志们都参考吧 。... 还有max_heap_table_size 和tmp_table_size的值。 因
max_heap_table_size current value:16777216 // max_join_size current value:4294967295 //允许读取的连接的数量 max_sort_length current value:1024 //在排序blob或者text时使用的字节数量 max_tmp_tables ...
# 如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySQL同时将...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号...
系统因素和启动参数的调节 7.5.2. 调节服务器参数 7.5.3. 控制查询优化器的性能 7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 ...