Magento is creating 700 + connections leading to database breakdown whenever cache is flushed or indexing is triggered. Production site remain down for 20 mins till all connections clears. All connections firing same query. And remain in state creating sort index. Using very high database configuration. DB on Amazon rds.Any help is appreciated. This is breaking our production site.
Show full pprocesslist getting below query being fired 600 times and is struck at creating sort index.
SELECT main_table.entity_id, main_table.name, main_table.path, main_table.is_active, main_table.is_anchor, url_rewrite.request_path FROM catalog_category_flat_store_1 AS main_table LEFT JOIN core_url_rewrite AS url_rewrite ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.product_id IS NULL AND url_rewrite.store_id='1' AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/%') ORDER BY main_table.position ASC