I caught the following query via mysql's SHOW FULL PROCESSLIST; which was eating the whole CPU / RAM on my server (32GB Ram, i7 hexacore CPU) rendering the entire server unusable for more than 3 hours. Once I killed the query below, the server was acting normal again.
INSERT INTO `search_tmp_5beff7364e8e28_10011726` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (0) + (0)) * 1) AS `score` FROM `catalogsearch_fulltext_scope1` AS `search_index`
LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id
LEFT JOIN `catalog_product_index_eav` AS `schuh_groesse_filter` ON search_index.entity_id = schuh_groesse_filter.entity_id AND schuh_groesse_filter.attribute_id = 127 AND schuh_groesse_filter.store_id = 1
LEFT JOIN `cataloginventory_stock_status` AS `schuh_groesse_filter_stock` ON schuh_groesse_filter_stock.product_id = schuh_groesse_filter.source_id
LEFT JOIN `catalog_product_index_eav` AS `farbe_filter` ON search_index.entity_id = farbe_filter.entity_id AND farbe_filter.attribute_id = 163 AND farbe_filter.store_id = 1
LEFT JOIN `cataloginventory_stock_status` AS `farbe_filter_stock` ON farbe_filter_stock.product_id = farbe_filter.source_id
LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 40) AND (schuh_groesse_filter.value = '26' AND schuh_groesse_filter_stock.stock_status = 1) AND (farbe_filter.value = '166' AND farbe_filter_stock.stock_status = 1)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
LIMIT 10000
EXPLAIN of the query:
+----+-------------+------------------------------------+------------+--------+-------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------------------+------------+--------+-------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
| 1 | INSERT | search_tmp_5beff7364e8e28_10011726 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | farbe_filter | NULL | ref | PRIMARY,CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,CATALOG_PRODUCT_INDEX_EAV_STORE_ID,CATALOG_PRODUCT_INDEX_EAV_VALUE | CATALOG_PRODUCT_INDEX_EAV_VALUE | 4 | const | 316 | 25.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | farbe_filter_stock | NULL | ref | PRIMARY | PRIMARY | 4 | neu3.farbe_filter.source_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | stock_index | NULL | ref | PRIMARY,CATALOGINVENTORY_STOCK_STATUS_WEBSITE_ID | PRIMARY | 6 | neu3.farbe_filter.entity_id,const | 1 | 10.00 | Using where |
| 1 | SIMPLE | category_ids_index | NULL | ref | PRIMARY,CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY | PRIMARY | 8 | const,neu3.farbe_filter.entity_id | 1 | 100.00 | Using index |
| 1 | SIMPLE | schuh_groesse_filter | NULL | ref | PRIMARY,CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,CATALOG_PRODUCT_INDEX_EAV_STORE_ID,CATALOG_PRODUCT_INDEX_EAV_VALUE | CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID | 12 | const,neu3.farbe_filter.entity_id,const,const | 2 | 100.00 | Using index |
| 1 | SIMPLE | schuh_groesse_filter_stock | NULL | ref | PRIMARY | PRIMARY | 4 | neu3.schuh_groesse_filter.source_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | search_index | NULL | ref | PRIMARY,FTI_FULLTEXT_DATA_INDEX | PRIMARY | 4 | neu3.farbe_filter.entity_id | 6 | 100.00 | Using index |
| 1 | SIMPLE | cea | NULL | eq_ref | PRIMARY | PRIMARY | 2 | neu3.search_index.attribute_id | 1 | 100.00 | Using where; Using index |
+----+-------------+------------------------------------+------------+--------+-------------------------------------------------------------------------------------------------------------------+----------------------------------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
9 rows in set (0.01 sec)
I modified the query to show me the results (which then getting inserted into search_tmp_...):
SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (0) + (0)) * 1) AS `score` FROM `catalogsearch_fulltext_scope1` AS `search_index`
LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id
LEFT JOIN `catalog_product_index_eav` AS `schuh_groesse_filter` ON search_index.entity_id = schuh_groesse_filter.entity_id AND schuh_groesse_filter.attribute_id = 127 AND schuh_groesse_filter.store_id = 1
LEFT JOIN `cataloginventory_stock_status` AS `schuh_groesse_filter_stock` ON schuh_groesse_filter_stock.product_id = schuh_groesse_filter.source_id
LEFT JOIN `catalog_product_index_eav` AS `farbe_filter` ON search_index.entity_id = farbe_filter.entity_id AND farbe_filter.attribute_id = 163 AND farbe_filter.store_id = 1
LEFT JOIN `cataloginventory_stock_status` AS `farbe_filter_stock` ON farbe_filter_stock.product_id = farbe_filter.source_id
LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 40) AND (schuh_groesse_filter.value = '26' AND schuh_groesse_filter_stock.stock_status = 1) AND (farbe_filter.value = '166' AND farbe_filter_stock.stock_status = 1)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
LIMIT 10000
Result:
+-----------+-----------+
| entity_id | relevance |
+-----------+-----------+
| 21112 | 0 |
+-----------+-----------+
1 row in set (0.02 sec)
Some more information:
farbe is a custom product attribute of my shop (color),
schuh_groesse => shoe size. The tables were all generated by Magento itself - nothing special here.
What the hell is happening here... I am running Magento 2.1.9. Everything was fine the last 6 months till yesterday. What does this query do? How can I prevent this query from happening / optimize it? Can I configure MySQL in a way, that it does not block the server entirely if the query happens again?
