本文主要描述 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序列執行，問題暫時得到解決，至少半個多月都沒再出現這個問題了。

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

相關文章