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)