Slow performance due to full reindexing

This article provides a fix for poor performance due to full reindexing (where data in the indexing-related database tables is updating).

Affected versions and products

  • Adobe Commerce on cloud infrastructure 2.x.x
  • Adobe Commerce on-premises 2.x.x

Issue

Constant flushing and index rebuilding are some of the reasons for performance degradation. Additionally, constant full reindexing adds locks on tables making the website work much slower than expected.

Cause

Actions that can produce full reindexing were performed from admin including:

  • Product attribute save
  • Website/store/store view save
  • Store configuration
NOTE
These actions should be run outside of business hours to make sure these actions do not affect performance during business hours.

Third-party extensions can also cause full reindex. Full reindex may also be manually run from CLI. To find out if you have indexes being reindexed and potentially causing performance downgrade:

  1. Perform this query to find the indexers that were fully reindexed in the last 15 minutes:

    code language-none
    SELECT * FROM indexer_state WHERE updated > NOW() - INTERVAL 15 MINUTE;
    

    An indexer name in the output means that the indexer has been reindexed at least once during the last 15 minutes.

  2. If you found frequent full reindexation, investigate the following:

    • Who might be doing this manually from the CLI
    • What third-party module is doing the reindexation
    • What third-party module is marking indexers as Invalid

Solution

Run reindexing only when necessary. For steps, review Configure Indexers in our developer documentation. A general recommendation and best practice is to allow the partial reindexation mechanism to take care of data reindexation with no manual action required from a merchant. All reindexation should be done using native Adobe Commerce functionality (Mview). Mview performs partial reindexation, which is the most efficient way to reindex data. To learn about Mview, refer to Indexing overview: Mview in our developer documentation.

recommendation-more-help
8bd06ef0-b3d5-4137-b74e-d7b00485808a