Magento – Magento indexing causing website downtime

awscachedatabaseindexingmysql-indexes

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

Best Answer

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.

Related Topic