Magento indexing causing website downtime

Question

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

Answer 1

First of all if you have Flat Category enabled, indexation is going to put your website down. You must create a custom solution for aliasing the tables like described in my Indexation talk at Magento Developers Paradise:

https://ivanchepurnyi.github.io/slides/indexation-techiques/index.html

Apart from that, it seems like you are using a kind of custom module that left joins to a flat table of categories for just RETRIEVE URL rewrites. This is entirely redundant as there is no filter applied on URL in the current situation.

I highly recommend separating this query into 2 simpler ones (for MySQL they are simpler).

The first one to retrieve categories:

SELECT 
    main_table.entity_id, 
    main_table.name,
    main_table.path, 
    main_table.is_active,
    main_table.is_anchor
FROM catalog_category_flat_store_1 AS main_table 
    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

And the second one for URL rewrites retrieval

 SELECT url_rewrite.category_id, url_rewrite.request_path 
    FROM (
       SELECT CONCAT('category/', entity_id) as id_path
          FROM catalog_category_flat_store_1 
          WHERE is_active = '1' 
             AND main_table.include_in_menu = '1' 
             AND main_table.path like '1/2/%'
    ) as id_path
    INNER JOIN core_url_rewrite AS url_rewrite 
       ON url_rewrite.store_id='1' AND url_rewrite.id_path = id_path.id_path

The difference in approaches: The second query can use indexes for looking up URL rewrites. So instead of doing table scan on multi-million URL rewrite table it will do it only on a small fraction of data.

Answer 2

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 clear.

Cache aspect
That's "normal" behavior depending on your current traffic. That's why you should not flush entire cache every time you make a simple change on the website. Choose cache flushing moment wisely; do it only in low traffic moments, or you can just flush affected areas. There are many strategies which you can build around this limitation, but it depends on your server(s), infrastructure, traffic, etc.

Index aspect If you're doing full reindex every time during high traffic, it will kill the most powerful servers too. Partial async reindex can help you as a first aid. If you have Magento Enterprise, you already have it. Otherwise, you can check Mirasvit Async Reindex extension. Again, this is not an ultimate solution, it just helps with majority small/mid businesses. It demands more work and details in order to create a solution tailored to your needs.

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

This SQL query apparently represents data retrieval for your navigation. I saw many navigations which are heavy consumers of resources; it can be just weak solution masked under block cache. If you want to skip this kind of issue: do not clear the cache during high traffic hours. At some point, you can try to revisit navigation code to find an optimal solution.