5

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 where 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?

  • Additional information request. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; helpful AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Dec 30 '18 at 22:20
  • After you post the requested information, some relevant Suggestions for your my.cnf will be provided after analysis of the data from your server. – Wilson Hauck Mar 2 at 1:24
0

There is some custom module that makes it difficult to answer.

I suggest you truncate search related tables if it has lots of entries.

  • What do you mean by "custom module"? I know there are some german fields which are queried (they are my attributes). F.e. farbe => color, schuh_groesse => shoe_size. – mfuesslin Nov 19 '18 at 8:37
0

Magento search can be slow, to boost it, we may think of the work that it is doing when the web user hits the button search:

  • each product attribute setup in the backend as searchable will be searched for when the search is triggered. So right away, you can check how many product attributes are searchable
  • then, you have the second option to go a bit deeper by identifying whether this is the query that is slow or whether it is a red-herring. I would enabled slow log (see enable slow log in magento) . If you have confirmation that this query is slow then your last resort is to go right in the middle of mysql: run 'explain select * from ...' will give you understanding of what takes the time in the query.

--> once you spotted the culprit, you will see with point 1 how to make your search faster but also more relevant. (for instance, no point to have description searchable as it will give make your results quality worse)

--> if the second point gives you some ideas, then it may be time to see whether your database is correctly optimised and whether any indexes is missing too.

  • Which line / part of the query does you make guess that there is a custom module? How can I identify the module causing this? The german words inside the query are my attributes (f.e. farbe => color, etc.) – mfuesslin Nov 19 '18 at 8:39
  • Hi, sorry for late reply. Yes, I don't read german often these ddays and I have been caught as associating these joins to custom joins. In your case, the point 2 is not relevant indeed. thanks for pointing this out – Herve Tribouilloy Nov 20 '18 at 11:18
  • I found 2 attributes which does not have to be searchable - I disabled them for search. However, this does not solve the real problem with the sql query killing the server... – mfuesslin Nov 22 '18 at 9:40
  • thanks for th feedback, I have updated my answer.. good luck – Herve Tribouilloy Nov 22 '18 at 10:16
  • @mfuesslin Please provide additional information requested on Dec 30, 18. – Wilson Hauck Mar 2 at 1:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.