[MySQL友情协助]ERROR3(HY000):Errorwritingfile'/d_MySQL
朋友线上DB执行SQL报错：ERROR 3 (HY000): Error writing file '/dev/shm/MYHTwgxT' (Errcode: 28)
让朋友取得dev组run 过的sql，执行explain，如下：
mysql> explainselect count(*) from ( select STG_ITEM.ITEM_ID from (selectitem_label.item_id,item_label.revision from catalog.item_label item_label innerjoin (select max(item_label.effective_date) aseffective_date,item_label.item_id from catalog.item_label inner joincatalog.item item on item.item_id=item_label.item_id anditem.revision=item_label.revision whereitem_label.EFFECTIVE_DATE>='2013-01-01 00:00:00' AND item_label.EFFECTIVE_DATE
ERROR 3 (HY000): Error writing file '/dev/shm/MYHTwgxT' (Errcode: 28)
让朋友马上去检查tmp目录，发现磁盘tmp磁盘目录满了:
[xx@xx01]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/Sys-root1008M 235M 723M 25% /
tmpfs 16G 16G 139M 100%/dev/shm
/dev/vda1 194M 32M 153M 18% /boot
/dev/mapper/Sys-home 4.0G 145M 3.6G 4% /home
看到此情况，我给了2个建议：
1, increase the size of/dev/shm, maybe 200G is enough.
2, this sql istoo complex to run, ptimize the sql, and run the more concise sql in theint map database, for example:
When I change the where clause “item_label.EFFECTIVE_DATE>='2013-01-01 00:00:00' ” to “item_label.EFFECTIVE_DATE>='2013-03-01 00:00:00' ”, this sql scriptcan run and get a result, as follows:
mysql> select count(*) from ( selectSTG_ITEM.ITEM_ID from (select item_label.item_id,item_label.revision fromcatalog.item_label item_label inner join (select max(item_label.effective_date)as effective_date,item_label.item_id from catalog.item_label inner joincatalog.item item on item.item_id=item_label.item_id anditem.revision=item_label.revision where item_label.EFFECTIVE_DATE>='2013-03-01 00:00:00' AND item_label.EFFECTIVE_DATE
+----------+
| count(*) |
+----------+
| 58739 |
+----------+
1 row in set (7.45 sec)
bitsCN.com