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
search_index.attribute_id
= cea.attribute_id
INNER JOIN `catalog_category_product_index` AS
`category_products_index` ON search_index.entity_id =
category_products_index.product_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.
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
