ACSD-53347: Price indexing performance gradually degrades overtime
The ACSD-53347 patch fixes the issue where performance gradually degrades when reindexing prices for a large product catalog. This patch is available when the Quality Patches Tool (QPT) 1.1.38 is installed. The patch ID is ACSD-53347. Please note that the issue is scheduled to be fixed in Adobe Commerce 2.4.7.
Affected products and versions
The patch is created for Adobe Commerce version:
- Adobe Commerce (all deployment methods) 2.4.6
Compatible with Adobe Commerce versions:
- Adobe Commerce (all deployment methods) 2.3.7 - 2.4.6-p2
magento/quality-patches
package to the latest version and check the compatibility on the Quality Patches Tool: Search for patches page. Use the patch ID as a search keyword to locate the patch.Issue
When reindexing prices for a large product catalog, the performance of the queries executed during the indexing process gradually degrades.
Steps to reproduce:
- Create a large simple catalog and assign custom options to these products (custom options use a temporary table during indexing).
- Create at least 200 or more customer groups to increase visibility of the issue.
- Create ten or more websites and assign all products to each of them.
- Note that the imported products are almost identical, differing only by SKU and name.
- Enable DB Logging.
- Execute the
bin/magento index:reindex catalog_product_price
command. - Check for DELETE FROM
catalog_product_index_price_opt_agr_temp
in thedb.log
.
Expected results:
The execution of the DB queries runs efficiently.
Actual results:
The performance of the DB queries on temporary tables become slow overtime, hence price indexing table takes a lot of time to complete.
Apply the patch
To apply individual patches, use the following links depending on your deployment method:
- Adobe Commerce or Magento Open Source on-premises: Quality Patches Tool > Usage in the Quality Patches Tool guide
- Adobe Commerce on cloud infrastructure: Upgrades and Patches > Apply Patches in the Commerce on Cloud Infrastructure guide
Related reading
- Quality Patches Tool released: a new tool to self-serve quality patches in the support knowledge base
- Check if patch is available for your Adobe Commerce issue using Quality Patches Tool in the Quality Patches Tool guide
- Best practices for modifying database tables in the Commerce Implementation Playbook
For info about other patches available in QPT, refer to Quality Patches Tool: Search for patches in the Quality Patches Tool guide.