请教，mysql负载高，网页打开出现Can not connect to MySQL server

likeislike

请教一mysql的问题：
最近，网站打开的时候提示：Can not connect to MySQL server，登录mysql服务器，发现负载很高，查看mysql的错误日志，有大量如下错误报告：
130520 14:02:53 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_19.MYI'; try to repair it
130520 14:02:53 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130520 14:02:54 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_1.MYI'; try to repair it
130520 14:02:54 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130520 14:02:55 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_35.MYI'; try to repair it
130520 14:02:55 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130520 14:02:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_41.MYI'; try to repair it
130520 14:02:57 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130520 14:02:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_16.MYI'; try to repair it
130520 14:02:57 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130520 14:02:58 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_491d_9.MYI'; try to repair it
130520 14:02:58 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223

百度了一下，说是表有问题，修复表就行了，但是/tmp下是临时产生的文件，怎么修复呢？？？

请给予指点，不胜感激！！！！

likeislike

/tmp下的有如下文件：
-rw-rw---- 1 mysql mysql 385875968 May 20 14:36 #sql_491d_6.MYD
-rw-rw---- 1 mysql mysql      1024 May 20 14:35 #sql_491d_6.MYI
-rw-rw---- 1 mysql mysql 452984832 May 20 14:36 #sql_491d_7.MYD
-rw-rw---- 1 mysql mysql      1024 May 20 14:35 #sql_491d_7.MYI
-rw-rw---- 1 mysql mysql 369098752 May 20 14:35 #sql_491d_8.MYD
-rw-rw---- 1 mysql mysql      1024 May 20 14:35 #sql_491d_8.MYI
-rw-rw---- 1 mysql mysql 352321536 May 20 14:35 #sql_491d_9.MYD
-rw-rw---- 1 mysql mysql      1024 May 20 14:35 #sql_491d_9.MYI

这些文件是临时的，一会有很多，一会可能就自动消失了！！！

seesea2517

临时表或查询的中间结果文件，不需要处理吧。

Shaquile

1。错误貌似是write时发生的，因为myisam是表锁，容易在高并发时出问题， myisam对并发支持不好，不如换成innodb
2。show variables like '%connect%';看一下，连接数是否满了，或是其它的配置

闪电松鼠

mysql > show full processlist ;
看下负载高峰时刻的sql,大概就有眉目了.
你这个是有大量排序的sql, 临时表会占据 /tmp空间的. 缓解的办法可以暂时把 临时目录放到更大的磁盘上 ,根本的解决还是要调优.
把那些sql给研发吧.
慢查询也给研发吧.

action08

调优也不是什么高深的技术活，还是看经验吧

likeislike

非常感谢！！！
回复 5# 闪电松鼠


   

likeislike

今天看mysql错误日志，发现只要出现负载高的时候，就有下面这样的日志：
130521  4:12:19 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_146.MYI'; try to repair it
130521  4:12:19 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:20 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_5.MYI'; try to repair it
130521  4:12:20 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:20 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_3.MYI'; try to repair it
130521  4:12:20 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:20 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_9.MYI'; try to repair it
130521  4:12:20 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:20 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_124.MYI'; try to repair it
130521  4:12:20 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_97.MYI'; try to repair it
130521  4:12:21 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table '/tmp/#sql_6117_273.MYI'; try to repair it
130521  4:12:21 [ERROR] Got an error from unknown thread, /home/like/src/mysql-5.5.30/storage/myisam/mi_write.c:223
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Error writing file '/tmp/MYjU3qtZ' (Errcode: 2
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Error writing file '/tmp/MYNiHwBp' (Errcode: 2
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Error writing file '/tmp/MYf0gut3' (Errcode: 2
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Error writing file '/tmp/MYgayVo0' (Errcode: 2
130521  4:12:21 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Error writing file '/tmp/MYE04liE' (Errcode: 2


网上说的正如5楼闪电松鼠说的那样，应该是临时目录满了导致，先把临时目录放到别的地方吧，看看效果！！