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
如对本文有疑问， 点击进行留言回复！！
MySql添加新用户及为用户创建数据库和给用户分配权限方法介绍
我要评论
网友评论