[MySQL友情协助]ERROR3(HY000):Errorwritingfile'/d_MySQL

朋友线上DB执行SQL报错：ERROR 3 (HY000): Error writing file &＃39;/dev/shm/MYHTwgxT&＃39; (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>=&＃39;2013-01-01 00:00:00&＃39; AND item_label.EFFECTIVE_DATE <&＃39;2014-01-14 00:00:00&＃39; and label=&＃39;PROD&＃39; groupby 2) stg on item_label.effective_date=stg.effective_date anditem_label.item_id=stg.item_id and item_label.LABEL=&＃39;PROD&＃39; ) STG_ITEM innerjoin catalog.attribute_set attr_set onattr_set.ATTRIBUTABLE_ENTITY_ID=STG_ITEM.ITEM_ID andattr_set.ATTRIBUTABLE_ENTITY_REVISION=STG_ITEM.REVISION left outer joincatalog.public_attribute attr onattr.ATTRIBUTE_SET_ID=attr_set.ATTRIBUTE_SET_ID whereattr_set.ATTRIBUTABLE_ENTITY_TYPE=&＃39;ITEM&＃39; and attr_set.ATTRIBUTE_SET_TYPE in(&＃39;LOCALE&＃39;,&＃39;ACTION&＃39;,&＃39;COUNTRY&＃39;,&＃39;BASE&＃39;,&＃39;CUSTOM&＃39;,&＃39;EVENT&＃39;,&＃39;FULFILLMENT&＃39;,&＃39;PUBLISHING&＃39;,&＃39;RESTRICTION&＃39;)group by STG_ITEM.ITEM_ID, STG_ITEM.REVISION, attr_set.ATTRIBUTE_SET_ID,attr_set.ATTRIBUTABLE_ENTITY_ID, attr_set.ATTRIBUTABLE_ENTITY_TYPE,attr_set.ATTRIBUTABLE_ENTITY_REVISION, attr_set.ATTRIBUTE_SET_TYPE, attr_set.ATTRIBUTE_SET_KEY,attr.ATTRIBUTE_NAME, attr.ATTRIBUTE_VALUE ) cnt;

ERROR 3 (HY000): Error writing file &＃39;/dev/shm/MYHTwgxT&＃39; (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>=&＃39;2013-01-01 00:00:00&＃39; ” to “item_label.EFFECTIVE_DATE>=&＃39;2013-03-01 00:00:00&＃39; ”, 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>=&＃39;2013-03-01 00:00:00&＃39; AND item_label.EFFECTIVE_DATE <&＃39;2014-01-14 00:00:00&＃39; andlabel=&＃39;PROD&＃39; group by 2) stg on item_label.effective_date=stg.effective_dateand item_label.item_id=stg.item_id and item_label.LABEL=&＃39;PROD&＃39; ) STG_ITEM innerjoin catalog.attribute_set attr_set onattr_set.ATTRIBUTABLE_ENTITY_ID=STG_ITEM.ITEM_ID andattr_set.ATTRIBUTABLE_ENTITY_REVISION=STG_ITEM.REVISION left outer joincatalog.public_attribute attr on attr.ATTRIBUTE_SET_ID=attr_set.ATTRIBUTE_SET_IDwhere attr_set.ATTRIBUTABLE_ENTITY_TYPE=&＃39;ITEM&＃39; and attr_set.ATTRIBUTE_SET_TYPEin(&＃39;LOCALE&＃39;,&＃39;ACTION&＃39;,&＃39;COUNTRY&＃39;,&＃39;BASE&＃39;,&＃39;CUSTOM&＃39;,&＃39;EVENT&＃39;,&＃39;FULFILLMENT&＃39;,&＃39;PUBLISHING&＃39;,&＃39;RESTRICTION&＃39;)group by STG_ITEM.ITEM_ID, STG_ITEM.REVISION, attr_set.ATTRIBUTE_SET_ID,attr_set.ATTRIBUTABLE_ENTITY_ID, attr_set.ATTRIBUTABLE_ENTITY_TYPE,attr_set.ATTRIBUTABLE_ENTITY_REVISION, attr_set.ATTRIBUTE_SET_TYPE,attr_set.ATTRIBUTE_SET_KEY, attr.ATTRIBUTE_NAME, attr.ATTRIBUTE_VALUE ) cnt;

+----------+

| count(*) |

+----------+

| 58739 |

+----------+

1 row in set (7.45 sec)