I am using magento 2.1.13. Website is placed on a server with 20 dedicated cores and db is on a separate server with 28 dedicated cores.

All cache are enabled and are working fine. As soon as a few users start visiting the website, the website gets extremely slow. When you use the following query to check my db process:

show full processlist;

It shows me hundreds of queries and are stuck. The queries are mostly like below:

INSERT INTO `search_tmp_5b181fbcb526a2_18065532` SELECT 
`main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT 
`search_index`.`entity_id`, (((0)) * 1) AS `score` FROM 
`catalogsearch_fulltext_scope3` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON 
 = cea.attribute_id
 INNER JOIN `catalog_category_product_index` AS 
`category_products_index` ON search_index.entity_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_products_index.category_id = 153)) AS `main_select` GROUP BY 
`entity_id` ORDER BY `relevance` DESC
LIMIT 10000

A screenshot is also attached.

enter image description here

Due to these queries, the whole website goes down and even if I restart mysql, these queries are generated again very fast.

I am not sure what is wrong. Can any body help in this issue?

Thank you

Your Answer

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

Browse other questions tagged or ask your own question.