mysql Copying to tmp table on disk 影响性能的解决方法

时间:2021-12-05 06:19:38
最近常常碰到网站慢的情况,登陆到后台,查询一下 /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_tables_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所在服务器有足够的内存)。