You are here
mysql Copying to tmp table on disk 影响性能的解决方法
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;
发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。
用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill 进程号；
后面的查询一下子都好了。 ok, 找到了问题的原因，此查询效率太低。
因此用google查询了一下，发现网上一篇文章讲得很好，
（ [url]http://clay111.blogchina.com/4721079.html[/url] 我给转贴了，感兴趣可以看看）
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。
set global tmp_table_size=2097152000 (2000M)
show global variables like 'tmp_table_size';
再次运行此查询,用/opt/mysql/bin/mysqladmin processlist; 进行观察,发现不会出现上述问题.
至此问题解决.
调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。
mysql可以通过变量tmp_table_size和max_heap_table_size来控制内存表大小上限，如果超过上限会将数据写到磁盘上，从而会有物理磁盘的读写操作，导致影响性能。
我们可以通过调整这两个变量的值来提升性能（当然前提条件是mysql所在服务器有足够的内存）。