Incorrect key file for table '/tmp/....'錯誤的解決--記錄

本文主要描述  Incorrect key file for table '/tmp/....' 錯誤的發現和簡單的解決方案，作此記錄。

歡迎轉載，請註明作者、出處。 作者：張正 blog：http://space.itpub.net/26355921  QQ：176036317 如有疑問，歡迎聯絡。     突然接到開發打來電話說MySQL資料庫執行SQL報錯，讓排查一下原因。

登陸MySQL伺服器看了一眼錯誤記錄檔：
        140904 12:06:20 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it
        140904 12:06:20 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223


看到/tmp下的問題，先看一眼tmpdir：


mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)


        說/tmp下有表需要修複，通過tmpdir可知，是臨時排序表所致。在網上查了一下，說產生這個問題是由於tmpdir目錄下空間不足。
查看一下磁碟空間：
[root@localhost data]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   15G   31G  33% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


/tmp屬於/ 根目錄下，空間還有31G，覺得不可能。


        查看慢日誌，找到一條很可疑的語句，拿50萬的資料來排序，還有的語句拿200多萬的資料來排序，而且從時間上也與開發說的時間很吻合：
# Time: 140904 16:13:50
# User@Host: staff_reader[staff_reader] @  [10.35.15.68]
# Query_time: 133.653564  Lock_time: 0.000186 Rows_sent: 5384178  Rows_examined: 20886933
use hotel_inventory;
SET timestamp=1409818430;
SELECT col1,col2,col3....,col7
  FROM  table1 nolock WHERE
 available_time >= '2014-09-03' AND available_time <=  DATE_ADD('2014-09-03', interval 91 day)
 order by hotel_id,room_type_id,available_time;


看一下該語句訪問的表的具體情況：
 
表行數為20886994：
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 20886994 |
+----------+


表大小為7G： 
 [root@localhost]# du -sch *|grep sum_inv_room.ibd
6.9G    sum_inv_room.ibd


該機器上有4個執行個體：
[root@192.168.97.149 hotel_inventory]# ps -ef|grep mysqld_safe
root     21185     1  0  2013 ?        00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6025/etc/my6025.cnf
root     22525     1  0  2013 ?        00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6027/etc/my6027.cnf
root     24275     1  0  2013 ?        00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6024/etc/my6024.cnf
root     37864     1  0  2013 ?        00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6026/etc/my6026.cnf
root     60463  5879  0 16:18 pts/6    00:00:00 grep mysqld_safe


總大小有30G左右，都是同時抽取資料：
[root@192.168.97.149 hotel_inventory]# du -sch /data/mysql602*/data/hotel_inventory/* |grep sum_inv_room.ibd
6.9G    /data/mysql6024/data/hotel_inventory/sum_inv_room.ibd
7.1G    /data/mysql6025/data/hotel_inventory/sum_inv_room.ibd
7.9G    /data/mysql6026/data/hotel_inventory/sum_inv_room.ibd
6.5G    /data/mysql6027/data/hotel_inventory/sum_inv_room.ibd


        既然看到這些現象，那麼我肯定就是這條語句同時執行所致。這時候給開發反饋，而開發說[staff_reader] @  [10.35.15.68] 這個使用者不是他們那邊的。沒辦法了，繼續找。我就手動那上面那條語句去執行，發現根本沒產生多少臨時檔案。這時候可以斷定不是這個語句造成的了。
所以這個時候只能等問題再次重現。看了一下磁碟空間，正常情況下，/ 根目錄可用空間是31G，然後我就寫了個指令碼一直監測可用空間是否是31G，如果不是，就給我QQ郵箱發郵件。


過了一會，果然收到郵件了，這時候上伺服器看磁碟空間，在持續增長：




查看磁碟空間情況：
[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   30G   17G  65% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   33G   14G  71% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   35G   11G  77% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 tmp]# du -sch *
88K     mha4mysql-node-0.53
2.1G    #sql_5608_0.MYD
4.0K    #sql_5608_0.MYI
3.6G    #sql_5608_1.MYD
4.0K    #sql_5608_1.MYI
1.9G    #sql_5b44_0.MYD
4.0K    #sql_5b44_0.MYI
4.9G    #sql_5b44_1.MYD
4.0K    #sql_5b44_1.MYI
2.0G    #sql_621a_0.MYD
4.0K    #sql_621a_0.MYI
749M    #sql_621a_1.MYD
4.0K    #sql_621a_1.MYI
2.2G    #sql_9753_0.MYD
4.0K    #sql_9753_0.MYI
4.2G    #sql_9753_1.MYD
4.0K    #sql_9753_1.MYI
22G     total


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   37G  9.5G  80% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   41G  5.2G  89% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   46G   29M 100% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data
[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   46G   22M 100% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data




[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   46G     0 100% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


空間滿的同時，看到/data/mysql6025/log/mysqld.err中又出現錯誤：
140904 16:03:59 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it
140904 16:03:59 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223


通過慢日誌來看，又出現了上面那條很可疑的語句。可是之前已經排除了其的可能性，那就只能繼續找，發現一條很簡短的語句，看起來沒什麼，但是執行時間很長：
SELECT MIN(sum_inv_room_id), MAX(sum_inv_room_id) FROM (select s.*, 20140909 as dw_insert_dt_wid from sum_inv_room s WHERE  (1 = 1) ) AS t1;


出錯的這段時間，基本上就這兩條SQL在跑。




報錯之後，磁碟空間逐漸恢複：


........................................................


[root@192.168.97.149 tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   38G  8.6G  82% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


[root@192.168.97.149 log]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              49G   15G   31G  33% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             985M   44M  891M   5% /boot
/dev/sda4             219G  6.5G  201G   4% /opt
/dev/sdb1             1.4T  176G  1.2T  14% /data


最後空間使用率恢複正常。




於是手動拿這個語句執行了一下：
SELECT MIN(col1), MAX(col1) FROM (select s.*, 20140909 as dw_insert_dt_wid from table1 s WHERE  (1 = 1) ) AS t1;


果然看到磁碟空間立馬飆升，找到原因所在。單條語句執行，看到其產生的臨時檔案最大到了10多G。一共31G的空間，4個執行個體，都產生10多G，當然扛不住。


（至於具體為什麼這樣的語句會產生這麼大的臨時檔案，本人對其中的原理不是太理解，先做此記錄。）


經過與開發溝通，開發說那個是定時抽取資料。而且機器上的4個執行個體是同時開始的。


所以給出以下解決辦法：
1.將MySQL中參數tmpdir設定到大的目錄下去，但是這個需要參數是read_only變數，需要重啟資料庫
2.將這4個語句串列執行
3.修改抽取資料的SQL

最後開發選擇了第2種方法，將定點抽取資料的SQL串列執行，問題暫時得到解決，至少半個多月都沒再出現這個問題了。

--我今天也遇到這個問題了：

 Last_Errno: 126

                   Last_Error: Error 'Incorrectkey file for table '/tmp/#sql_1ce3_0.MYI'; try to repair it' on query. Defaultdatabase: 'np044'. Query: 'update t_src_biz_type t,t_biz_type b set b.source_system_id=t.source_system_id,b.system_id=t.system_id

……

我直接start slave，就能正常啟動了，(*^__^*) 嘻嘻……