2.3.2 - Product Flat Data index taking long time to reindex?

[cscd98]

Preconditions (*)

1. Magento 2.3.2
2. PHP 7.2.x

Steps to reproduce (*)

1. bin/magento index:reindex
2. Product Flat/Categories enabled in back end.

Expected result (*)

Re-index to be completed in timely fashion.

Actual result (*)

Design Config Grid index has been rebuilt successfully in 00:00:00
Customer Grid index has been rebuilt successfully in 00:00:00
Product Flat Data index has been rebuilt successfully in 00:28:37
Category Flat Data index has been rebuilt successfully in 00:00:04
Category Products index has been rebuilt successfully in 00:00:15
Product Categories index has been rebuilt successfully in 00:00:00
Catalog Rule Product index has been rebuilt successfully in 00:00:00
Product EAV index has been rebuilt successfully in 00:00:13
Stock index has been rebuilt successfully in 00:00:03
Product Price index has been rebuilt successfully in 00:00:06
Catalog Product Rule index has been rebuilt successfully in 00:00:00
Product/Target Rule index has been rebuilt successfully in 00:00:00
Target Rule/Product index has been rebuilt successfully in 00:00:00
Sales Rule index has been rebuilt successfully in 00:00:04
Catalog Search index has been rebuilt successfully in 00:01:24
Aheadworks Advanced Reports index has been rebuilt successfully in 00:00:01
Search Score Rules index has been rebuilt successfully in 00:00:00
Bazaarvoice Product Feed Index index has been rebuilt successfully in 00:00:52

[m2-assistant]

Hi @craigcarnell. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.3-develop instance - upcoming 2.3.x release

For more details, please, review the Magento Contributor Assistant documentation.

@craigcarnell do you confirm that you were able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

[engcom-Charlie]

Hello @craigcarnell ! Thank you for your report !
It is hard to reproduce this issue due your steps , can you please provide some additional steps how to reproduce it ?

[cscd98]

@Engcom We have 58 attributes with 'Used in Product Listing' set to Yes. We are going to try and reduce them and see if it increases the speed.

However, on 2.3.1 this was not an issue.

[engcom-Charlie]

@craigcarnell i will try to reproduce it with sample data

[cscd98]

@engcom-Charlie Might be relevant but in vendor/magento/module-catalog/Model/Indexer/Product/Flat/Action/Eraser.php

removeDisabledProducts

foreach ($result->fetchAll() as $product) {
            //echo $product['entity_id'] . PHP_EOL;
            $disabledProducts[] = $product['entity_id'];
        }

This is an infinite loop.

$select is:

SELECT `product_table`.*, `product_table`.`entity_id` FROM `catalog_product_entity` AS `product_table` LEFT JOIN `catalog_product_entity_int` AS `status_global_attr` ON status_global_attr.attribute_id = 87 AND status_global_attr.store_id = 0 LEFT JOIN `catalog_product_entity_int` AS `status_attr` ON status_attr.attribute_id = 87 AND status_attr.store_id = 3 WHERE ((product_table.entity_id IN('11261')) AND (IFNULL(status_attr.value, status_global_attr.value) = 2)) AND (product_table.created_in <= 1) AND (product_table.updated_in > 1)

Returns 20,000+ results for the same entity_id. (which is not disabled)

[cscd98]

@engcom-Charlie Nevermind, the last one was fixed with 2.3.3 patch 0a11ce5

[engcom-Charlie]

@craigcarnell even with sample data i was not able to reproduce this issue .

[cscd98]

@engcom-Charlie

SELECT attribute_code
FROM eav_attribute
INNER JOIN catalog_eav_attribute ON eav_attribute.attribute_id = catalog_eav_attribute.attribute_id
WHERE used_in_product_listing = 1;

We have 97 attributes used in product listing. By removing just 6 of them, we reduce time to 6 minutes.

Why is it taking so long? They are simply varchars..

[adam-inco]

Hi, I am also seeing this since going from 2.3.1 to 2.3.2, but my flat data wont re-index it just hangs and eventually crashes the system, Left it running overnight for 12hrs and it was still trying
it starts at INSERT INTO catalog_product_entity_int_tmp_indexer but never moves, on 2.31 you would see it run thought all the store views as it processes

[xi-ao]

Hi,
Same issue here. Flat catalog used to take 2-3 minutes max. on 2.3.1 (for ~50 000 products), now it requires 2 hours.
There must be some regression here to explain such difference.

Edit: new duration is not 30+ minutes but 2 hours.

[adam-inco]

I have downgraded back to 2.3.1 and now its back to 1 -2 mins to process again