mysql lock ,网站访问慢 ,Copying to tmp table !(转)

最近常常碰到网站慢的情况,登陆到后台,查询一下 /opt/mysql/bin/mysqladmin processlist;
发现一个查询状态为: Copying to tmp table 而且此查询速度非常慢,基本一分钟左右才出来,后面是很多查询,状态为lock。
用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill 进程号;
后面的查询一下子都好了。 ok, 找到了问题的原因,此查询效率太低。问一下程序员,找来此查询的代码,用工具进行一下简单分析。


(说明:这里是我不喜欢mysql的原因之一,mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。)
explain
SELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,
IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`
FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` = ‘2006-03-22’
AND b.`gid` = a.`id` LEFT JOIN `visit_stat` AS c ON c.`stat_id` = a.`id` AND c.`type` = ‘league’
AND c.`day`=’2006_03_22′ WHERE a.`validate`=’1′ GROUP BY a.`id`
ORDER BY day_b_times DESC, a.`id`;
+—-+————-+——-+——+——————————-+——————+———+———————+——+———————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+——————————-+——————+———+———————+——+———————————————-+
| 1 | SIMPLE | a | ref | league_info_idx4 | league_info_idx4 | 1 | const | 1441 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | mark_logs_idx1,mark_logs_idx2 | mark_logs_idx1 | 4 | new5jia1.a.id | 56 | |
| 1 | SIMPLE | c | ref | visit_stat_idx1 | visit_stat_idx1 | 26 | new5jia1.a.id,const | 10 | |
+—-+————-+——-+——+——————————-+——————+———+———————+——+———————————————-+

看了一下,其实此查询嵌套用得不好,作为程序员应该尽量避免用 not in ,in, left join ,right join 等等,不过这些不归我管,我只能提一些建议。
(顺便说一声:oracle里面可以用 exist ,not exist, minus等代替in ,not in 效率高出很多 )
此分析对我没有太大的作用,因此用google查询了一下,发现网上一篇文章讲得很好,
( http://clay111.blogchina.com/4721079.html 我给转贴了,感兴趣可以看看)
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)

~~~~~~~~~~~~~~~~~update 2012.04.11~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

除不能用 TEXT、BLOB、單一欄位不超過 512 bytes 等注意事項外, 還有兩個參數會影響到是否使用硬碟存 temporary table:

  • max_heap_table_size: in-memory table 的上限 (即 MEMORY engine 的上限)。
  • tmp_table_size: in-memory temporary table 的上限 (即用 MEMORY engine 當 temporary table 的上限)。

若 temporary table 需要大於 min(max_heap_table_size, 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会被装载到内存。那样会增加数据库访问速度。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注