copy to tmp table与tmp_table_size

MySQL运维 靠谱运维 320℃ 0评论

mysql默认情况下:

 

tmp_table_size 33554432

max_heap_table_size 16777216

copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时,参数中设置的临时表的大小小于结果集的大小时,就会将该表放在磁盘上,这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时,则系统会把 max_heap_table_size的值作为最大的内存临时表的上限,大于这个时,改写硬盘。

copy to tmp table的SQL语句,这条语句的时间比较长,且这个表会被加读锁,相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句,会造成更多的语句被阻塞。
tmp_table_size规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size两者的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下,默认:

mysql> show variables like “tmpdir”;

+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir | /tmp/ |
+—————+——-+

优化查询语句的时候,要避免使用临时表,如果实在避免不了的话,要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句,并且你有很多内存,增大tmp_table_size(和max_heap_table_size)的值。

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数(Created_tmp_disk_tables和Created_tmp_tables),一般的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size
这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变,即set @max_heap_table_size=#

,但是对于已经存在的内存表就没有什么用了,除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

这个变量和tmp_table_size一起限制了内部内存表的大小。
修改方法:

mysql> show full PROCESSLIST;

mysql> show VARIABLES like ‘tmp_table_size’
mysql> set GLOBAL tmp_table_size=629145600;

mysql> SHOW VARIABLES LIKE ‘max_heap_table_size%’;
mysql> SET GLOBAL max_heap_table_size=1073741824;

转载请注明:靠谱运维 » copy to tmp table与tmp_table_size

喜欢 (0)or分享 (0)
发表我的评论
取消评论

表情