magento / commerce-data-export Goto Github PK

We have a catalog of 150k products, 12k of product attributes and 300 attribute sets. We notice memory limit issues during the reindexation process for catalog_data_exporter_products.

I've noticed that the query used in Magento\CatalogDataExporter\Model\Provider\Product\AttributeMetadata.php in method getRawOptionsSelect is performing a query like the following:

SELECT 
    `a`.*
    `o`.`option_id` AS `optionId`,
    `v`.`value` AS `optionValue`,
    `s`.`code` AS `storeViewCode`
FROM
    `eav_attribute` AS `a`
        INNER JOIN `eav_entity_type` AS `t` ON t.entity_type_id = a.entity_type_id
        INNER JOIN `eav_attribute_option` AS `o` ON o.attribute_id
        INNER JOIN `eav_attribute_option_value` AS `v` ON o.option_id = v.option_id
        INNER JOIN `store` AS `s` ON v.store_id = s.store_id
WHERE
    (t.entity_table = 'catalog_product_entity')
        AND (a.attribute_code = 'manufacturer_filter');

When you notice closely, the INNER JOIN for eav_attribute_option is lacking a comparison with the eav_attribute table.attribute_id, this results in a huge dataset to be loaded. We also don't need to retrieve all the columns used in eav_attribute. I've created the following patch for my project, but would like to verify this issue with you as well:

Index: Model/Provider/Product/AttributeMetadata.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/Model/Provider/Product/AttributeMetadata.php b/Model/Provider/Product/AttributeMetadata.php
--- a/Model/Provider/Product/AttributeMetadata.php
+++ b/Model/Provider/Product/AttributeMetadata.php	(date 1641919694877)
@@ -65,9 +65,11 @@
     private function getRawOptionsSelect(string $attributeCode) : Select
     {
         return $this->getAttributesSelect($attributeCode)
+            ->reset('columns')
+            ->columns(['source_model', 'attribute_code'], 'a')
             ->join(
                 ['o' => $this->resourceConnection->getTableName('eav_attribute_option')],
-                'o.attribute_id',
+                'a.attribute_id = o.attribute_id',
                 [
                     'optionId' => 'o.option_id',
                 ]
                 ```

There's a performance issue with the module magento/module-catalog-data-exporter, it takes more than 1 hour on processing catalog_data_exporter_products indexer, when the reindex happen through cron, the indexer takes so long that it puts in backlog other indexers scheduled after this one, see below image:

The issue seems to be related to out of memory issues when a few crons are scheduled at similar time to catalog_data_exporter_products indexer

Preconditions (*)

1. Magento Cloud 2.4.3-p2 (it happens also in version 2.4.6, but we have widely tested in 2.4.3-p2)
2. 40000 skus, 20 stores
3. Install Adobe Product Recommendations (https://experienceleague.adobe.com/docs/commerce-merchant-services/live-search/guide-overview.html) and/or Adobe Live Search (https://experienceleague.adobe.com/docs/commerce-merchant-services/live-search/guide-overview.html)

Steps to reproduce (*)

1. Have all indexers in mode: Update by schedule
2. Having cron running indexers, from time to time there'll be memory issues and catalog_data_exporter_products will keep status = Working and multiple items in backlog (the issue specially could happen after massive products changes, eg. adding a catalog price rule which affects multiple products for multiple stores)

Expected result (*)

1. Indexers should be processed correctly by cron and in a not escessive amount of time (at least catalog_data_exporter_products should be executed in much less than 1 hour in order not to backlog next scheduled indexers)

Actual result (*)

1. catalog_data_exporter_products will keep status = Working and multiple items in backlog, see below screenshot

Both simple products and configurable products fixtures generation goes on error during CLI command : The entity ID field for the catalog_data_exporter_products_cl table wasn't found. Verify the field and try again.

Preconditions (*)

1. Vanilla Magento Enterprise on Prem. 2.4.6-p4
2. Catalog Service module installed

   composer require magento/catalog-service=^3.0.1

Steps to reproduce (*)

1. Run CLI command setup:perf:generate-fixtures with existing profile file path:

   bin/magento setup:perf:generate-fixtures setup/performance-toolkit/profiles/ee/medium.xml

Expected result (*)

1. For simple products: Generating simple products... done
   For configurable products: Generating configurable products... done

Actual result (*)

1. For simple products: Generating simple products... The entity ID field for the "catalog_data_exporter_products_cl" table wasn't found. Verify the field and try again.
   For configurable products: Generating configurable products... The entity ID field for the "catalog_data_exporter_products_cl" table wasn't found. Verify the field and try again.