MySQL overloads server

[raul_plana]

Hi, since we launched the website we have some mySQL overload comming from this. The slow-query log is giving me thousands of these:

# Time: 2018-06-22T07:37:34.731523Z
# User@Host: misskits-final[misskits-final] @ localhost []  Id: 599080
# Query_time: 22.042083  Lock_time: 0.000147 Rows_sent: 0  Rows_examined: 12908072
SET timestamp=1529653054;
INSERT INTO `search_tmp_5b2ca728a83356_31372866` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (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 `ovillo_material_filter` ON search_index.entity_id = ovillo_material_filter.entity_id AND ovillo_material_filter.attribute_id = 153 AND ovillo_material_filter.store_id = 1
 LEFT JOIN `catalog_product_index_eav` AS `ovillo_marca_filter` ON search_index.entity_id = ovillo_marca_filter.entity_id AND ovillo_marca_filter.attribute_id = 154 AND ovillo_marca_filter.store_id = 1
 LEFT JOIN `catalog_product_index_eav` AS `ovillo_peso_filter` ON search_index.entity_id = ovillo_peso_filter.entity_id AND ovillo_peso_filter.attribute_id = 156 AND ovillo_peso_filter.store_id = 1 WHERE (category_ids_index.category_id = 10) AND (ovillo_material_filter.value  IN ('69', '76', '70', '66', '65', '75', '73')) AND (ovillo_marca_filter.value  IN ('79', '93')) AND (ovillo_peso_filter.value = '109')) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
 LIMIT 10000;

At first sight we thought that was a reindex problem because it was at the same time that we added a cron reindexing the magento website on the server. What could be the problem? The Magento version is 2.1.9, tell me if you need more information.

Thank you very much.

[Tarandeep Singh]

@raul_planaDid you run this query directly on the database? That's the search query calls when someone search from frontend of the website. If that query runs fine and you have decent hardware then there can be 3 things.

1. Crawlers are hitting your site more often than needed. You can block most of them or slow them down.

2. You have good amount of traffic which is causing the load on the server. Good way to test the same is via stress/load testing.

3. Security attack on your website. You'll need a Firewall in this case.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.