mysql一直copying to tmp table_mysql提示Copying to tmp table on disk

网站运行的慢了，查找原因是Copying to tmp table on disk那怎么解决这个问题呢

解决一例最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;

发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。

此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好: 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=209715200 (200M) 再次运行此查询,用/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性能优化时，发现copying to tmp table on disk占用了很长的执行时间，如下性能分析图：

上图中的第一行显示mysql向临时表复制数据花费了117毫秒的时间，这个必须想办法优化掉。

经过查资料发现mysql可以通过变量tmp_table_size和max_heap_table_size来控制内存表大小上限，如果超过上限会将数据写到磁盘上，从而会有物理磁盘的读写操作，导致影响性能。

我们可以通过调整这两个变量的值来提升性能(当然前提条件是mysql所在服务器有足够的内存)。

首先可以通过下面语句查看当前的变量值：

SHOW VARIABLES LIKE'max_heap_table_size%';

然后通过SET GLOBAL max_heap_table_size=522715200; 设置变量值为512M，你可以根据自己的情况设置合适的值；tmp_table_size变量的设置方法一样。

在使用mysql是提醒mysql Copying to tmp table on disk 错误了,下面我们来看看此问题的解决办法.

经过查资料发现mysql可以通过变量tmp_table_size和max_heap_table_size来控制内存表大小上限,如果超过上限会将数据写到磁盘上,从而会有物理磁盘的读写操作,导致影响性能.

我们可以通过调整这两个变量的值来提升性能,当然前提条件是mysql所在服务器有足够的内存.

首先可以通过下面语句查看当前的变量值:

SHOW VARIABLES LIKE ‘max_heap_table_size%’;

然后通过SET GLOBAL max_heap_table_size=522715200; 设置变量值为512M,你可以根据自己的情况设置合适的值,tmp_table_size变量的设置方法一样.

SHOW VARIABLES LIKE ‘tmp_table_size’;

SET GLOBAL max_heap_table_size=512*1024*1024;

其次发现开发人员编写的语句,根本无法使用到缓存,这个sql优化是个长期的过程,代码如下:

# Time: 140901 16:26:23

# User@Host: wealth[wealth] @ [172.20.1.70]

# Query_time: 2266.887211 Lock_time: 0.000196 Rows_sent: 17 Rows_examined: 71501657

SET timestamp=1409559983;

SELECT a.Pid,count(a.id)ascount,p.ProductId,p.ProductName,p.ProductType,p.Profit,p.StartTime,p.EndTime,p.StartMoney,p.AddTime,i.IssuerName FROM `Issuer`asi,`Count`asa left join Productasp on a.Pid=p.ProductId WHERE a.Stime > ‘time() – 3600*24*30′andp.IssuerId=i.IssuerId AND p.IsDel=0 group by a.Pid order bycount(a.id) desc limit 0,17;

总结

以上是生活随笔为你收集整理的mysql一直copying to tmp table_mysql提示Copying to tmp table on disk的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。