Catalog Permissions Indexer Crashing Magento 2
Published: June 10, 2019
I recently investigated an issue where running a full reindex would cause a Magento 2 site to crash. Of course it’s not best practice to run full reindexes, however, at the same time, it should be possible to do them without crashing the entire website. In this post we’ll explore the issue.
Capturing SHOW FULL PROCESSLIST
We knew from New Relic that MySQL response time would become sluggish leading up to the crash. In order to determine why this was happening we took snapshots of output of the MySQL SHOW FULL PROCESSLIST statement while the reindex was in process.
In these snapshots we saw many long running queries in the following states:
- Opening tables
- exit open_tables()
- closing tables
Then at the beginning of it all we saw a REPLACE INTO magento_catalogpermissions_index_product_replica query. During the last SHOW FULL PROCESSLIST captured the query had been running for 10.5 minutes and was still in an “Opening tables state”.
Examining the REPLACE INTO Query
Upon closer inspection, it quickly became apparent that REPLACE INTO query was highly problematic. The query statement itself was 14MB (!!!) and resulted in 23,220 JOINs across 1,934 UNIONs.
We were able to deduce that the query was causing high contention for the table cache, causing table open / close operations to slow to a crawl.
The Fix
We provided our findings to Magento support who were able to reproduce the issue and provided us with patch MDVA-16669. The patch modifies the internals of the Catalog Permissions indexer. We deployed the patch to production and confirmed it resolved the issue.
Hi, I'm Max!