Catalog Permissions Indexer Crashing Magento 2

Published: June 10, 2019

Tags:

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.

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but loves dabbling in other languages like Go and Ruby. Technical topics that interest me are monitoring, security and performance. I'm also a stickler for good documentation and clear technical writing.

During the day I lead a team of developers and solve challenging technical problems at Rightpoint where I mainly work with the Magento platform. I've also spoken at a number of events.

In my spare time I blog about tech, work on open source and participate in bug bounty programs.

If you'd like to get in contact, you can find me on Twitter and LinkedIn.