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?