[MySQL friendly assistance] ERROR3 (HY000): Errorwritingfile & amp; #39;/d_MySQL

[MySQL friendly assistance] ERROR3 (HY000): Errorwritingfileamp; amp; #39; devshmMYHTwgxTamp; amp; #39; (Errcode: 28) bitsCN.com

ERROR 3 (HY000): Error writing file '/dev/shm/MYHTwgxT' (Errcode: 28)

Ask a friend to obtain the SQL statement run by the dev group and execute explain as follows:

Mysql> explainselect count (*) from (select STG_ITEM.ITEM_ID from (selectitem_label.item_id, item_label.revision from catalog. item_label innerjoin (select max (item_label.effective_date) aseffective_date, item_label.item_id from catalog. item_label inner joincatalog. item on item. item_id = item_label.item_id anditem. revision = item_label.revision placement> = '2017-01-01 00:00:00 'AND item_label.1_tive_date <'2017-01-14 00:00:00' and label = 'prod' groupby 2) stg on placement = stg. required tive_date anditem_label.item_id = stg. item_id and item_label.LABEL = 'prod') 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 identifier = 'ITEM' and attributes in ('locale', 'Action', 'Country ', 'base', 'custom', 'event', 'fulfillment ', 'publishing', 'restriction ') group by STG_ITEM.ITEM_ID, STG_ITEM.REVISION, attr_set.ATTRIBUTE_SET_ID, attr_set.ATTRIBUTABLE_ENTITY_ID, identifier, attr. ATTRIBUTE_NAME, attr. ATTRIBUTE_VALUE) cnt;

ERROR 3 (HY000): Error writing file '/dev/shm/MYHTwgxT' (Errcode: 28)

Ask a friend to check the tmp directory immediately and find that the disk tmp disk directory is full:

[Xx @ xx01] $ df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/mapper/Sys-root1008M 235 M 723 M 25%/

Tmpfs 16G 16G 139 M 100%/dev/shm

/Dev/vda1 194 M 32 M 153 M 18%/boot

/Dev/mapper/Sys-home 4.0G 145 M 3.6G 4%/home

In this case, I gave two suggestions:

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.inclutive_date> = '2017-01-01 00:00:00 '" to "item_label.inclutive_date> = '2017-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 inner join (select max (item_label.inclutive_date) as inclutive_date, item_label.item_id from catalog. item_label inner joincatalog. item on item. item_id = item_label.item_id anditem. revision = item_label.revision whereItem_label.1_tive_date> = '2017-03-01 00:00:00'AND item_label.1_tive_date <'1970-01-14 00:00:00 'andlabel = 'prod' group by 2) stg on item_label.1_tive_date = stg. required tive_dateand item_label.item_id = stg. item_id and item_label.LABEL = 'prod') 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 = 'ITEM' and values ('locale', 'Action', 'Country ', 'base', 'custom', 'event', 'fulfillment ', 'publishing', 'restriction ') group by STG_ITEM.ITEM_ID, STG_ITEM.REVISION, attr_set.ATTRIBUTE_SET_ID, attr_set.ATTRIBUTABLE_ENTITY_ID, identifier, attr. ATTRIBUTE_NAME, attr. ATTRIBUTE_VALUE) cnt;

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

| Count (*) |

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

| 1, 58739 |

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

1 row in set (7.45 sec)

BitsCN.com