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

[craigcarnell]

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[bot]]

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 ?

[craigcarnell]

@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

[craigcarnell]

@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)

[craigcarnell]

@engcom-Charlie Nevermind, the last one was fixed with 2.3.3 patch https://github.com/magento/magento2/commit/0a11ce53c9c404266e4badc604dbc9c7c84652aa

[engcom-Charlie]

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

[craigcarnell]

@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

[magento-engcom-team]

:white_check_mark: Confirmed by @engcom-Charlie
Thank you for verifying the issue. Based on the provided information internal tickets MC-17929 were created

Issue Available: @engcom-Charlie, _You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself._

[xi-ao]

In my case, the query that takes a really long time to execute (90+ minutes) is the one of that form:

INSERT INTO `catalog_product_entity_int_tmp_indexer_value` (`entity_id`, `attr1`, `attr2`, ...)
    SELECT `e`.`entity_id`, `t1`.`value` AS `attr1`, `t2`.`value` AS `attr2`, ...
    FROM `catalog_product_entity_int_tmp_indexer` AS `e`
     LEFT JOIN `eav_attribute_option_value` AS `t1` ON e.attr1 = t1.option_id AND (t1.store_id = 1 OR t1.store_id = 0)
     LEFT JOIN `eav_attribute_option_value` AS `t2` ON e.attr2 = t2.option_id AND (t2.store_id = 1 OR t2.store_id = 0)
     ...
        ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`),
            `attr1` = VALUES(`attr1`),
            `attr2` = VALUES(`attr2`),
            ...

[matthewonassis]

Same issue here.
The Product Flat Data index will crash the whole server and I have to restart the server to back up for a while before it crashes again. I manually made the status of the indexers to be INVALID in the database and reindexed again, but none of the solutions works.
I hope someone can find the bug.

[SamB-GB]

I can confirm I am also seeing the same issue with the same query as posted by @xi-ao posted. I can't get the reindex to finish, let it run for 12 hrs with no result.

Had to disable flat products to get these reindexed.

[SamB-GB]

When can we expect a fix for this as its preventing us from indexing our products?

[ameysar]

@craigcarnell, @xi-ao, @SamB-GB could you please provide more details about your environment:

• How many products do you have on your site
• What kind of products do you have (i.e. simple, configurable, etc.)
• How many attributes (used in the product listing) per product ( average )

[xi-ao]

@craigcarnell, @xi-ao, @SamB-GB could you please provide more details about your environment:

• How many products do you have on your site
• What kind of products do you have (i.e. simple, configurable, etc.)
• How many attributes (used in the product listing) per product ( average )

• Approx. 250.000 products at the time of the tests
• Virtual only
• 48 attributes with used_in_product_listing = 1

[ameysar]

@xi-ao I was trying to reproduce the issue with next data:

• 1 store
• 250000 simple products ( I don't think there is some difference if I use simple instead of virtual )
• 48 additional dropdown attributes with _used_in_product_listing = 1_ added to default dataset (each product has all 48 attributes filled with random value).
• Product Flat Indexer: On

Got next results on different branches:

2.3-develop:

andrew@andrew-VirtualBox:~/sites/magento/2_3$ bin/magento indexer:reindex catalog_product_flat
Product Flat Data index has been rebuilt successfully in 00:30:39

2.3.1-release:

andrew@andrew-VirtualBox:~/sites/magento/2_3$ bin/magento indexer:reindex catalog_product_flat
Product Flat Data index has been rebuilt successfully in 00:31:38

2.3.2-develop:

andrew@andrew-VirtualBox:~/sites/magento/2_3$ bin/magento indexer:reindex catalog_product_flat
Product Flat Data index has been rebuilt successfully in 00:32:01

[adam-inco]

Hi, I have the following

8 Stores
6000 Products mixed config/simple
20 - 30 attributes used in the product listing

on 2.3.2 My indexes never finished, and just hung for 24hrs before i killed the process, I have done this on 3 servers with different setup and OS's

on 2.3.1 this process finishes in 4 mins

[xi-ao]

Hi @ameysar,
I don't know what to say. There might be some other things to consider besides those 3 elements.

Can you try again using multi-selects instead of dropdown for those attributes?

[ameysar]

Hi @xi-ao,
Could you please tell how many websites/stores (per website) do you have on your site, what type of attributes are you using in product listing (i.e. text, dropdown, multiselect, etc) and in case multi-store environment - do you have store specific values for those attributes with _used_in_product_listing = 1_

[ameysar]

Performance degradation is caused by https://github.com/magento/magento2/issues/21747. I prepared a fast fix. Could you please try it and check if the problem still exists.
flat_composer.patch.txt
flat.patch.txt

[matthewonassis]

I just found a problem in my Magento, and it might have something to do with this index issue.
I had MSI and SSM (single store more) both enabled at the same time. Also, I had two sources and two stocks. I haven't tried the upgrade again because I don't have a staging site, but if we all have the same configuration, that might be the root cause of this issue, and I would suggest somebody tries this out (remove additional stocks, or turn single store mode), and let us know if the indexer works on their staging site after upgrade to 2.3.2.

[adam-inco]

Hi, I have just installed 2.3.2 on our test site, and installed flat_composer.patch and the indexes are now running as expected

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:01:35
Category Flat Data index has been rebuilt successfully in 00:00:02
Category Products index has been rebuilt successfully in 00:00:27
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:38
Stock index has been rebuilt successfully in 00:00:00
Product Price index has been rebuilt successfully in 00:00:12
Catalog Product Rule index has been rebuilt successfully in 00:00:00

[matthewonassis]

@adam-inco @ameysar
Can you please explain how to install these two patches?

[ameysar]

@matthewonassis navigate to site root folder and use next command from terminal:
git apply flat_composer.patch
You need only one of the files. flat_composer.patch is for Composer version, flat.patch - for Git version

[craigcarnell]

@ameysar Do you have a link to the commit / PR please as well?

[ameysar]

@craigcarnell
PR is not created yet. I think the commit / PR link will be posted as soon as internal tickets MC-17929 will be delivered.

[SamB-GB]

Performance degradation is caused by #21747. I prepared a fast fix. Could you please try it and check if the problem still exists.
flat_composer.patch.txt
flat.patch.txt

This seemed to fix it for me as well. Thanks @ameysar

[magento-engcom-team]

Hi @craigcarnell.

Thank you for your report and collaboration!

The issue was fixed by Magento team. The fix was delivered into magento/magento2:2.2-develop branch(es).
Related commit(s):

• ac6512c2dbdf5df87df88f9d528c512f8e099ac0
• 1172c2ea2aab95677ab2fd7508e1ab12aac3aada
• a9f7246a5fc6957339b8a9af40f309e550b10d0f

The fix will be available with the upcoming 2.2.10 release.

[magento-engcom-team]

Hi @craigcarnell.

Thank you for your report and collaboration!

The issue was fixed by Magento team. The fix was delivered into magento/magento2:2.3-develop branch(es).
Related commit(s):

• 959437a67b3b412d1f8ba0250110b000205eb9e6
• adfa33f91c9ecb6bdfd913a6a9296531bd8d278b
• d73f49ee5a0eaaca9fee22bfc39091b4a0eef853
• 01af315e8227dfa814125a9a796d91c930b571af
• 7d9d3495be63463cf405be3a5eb2eb8869443170
• f73ddf13b5a993fe2d55d6579dd80dfce8229ff9
• 719df103ab7ef5b61133338ccd77dfe60096fec8

The fix will be available with the upcoming 2.3.3 release.

[kilis]

Hey Guys issue after the fix for the problem (https://github.com/magento/magento2/pull/22581) still persists after this improvement. Okay i know it degrades performance, but this fix reverts the same problem back.
Lets say i have an attribute set for all store views, but on one store view it is empty, then it still sets it to default store view in indexed table.
Can anybody suggest an improvement on this issue @ameysar?

[chris-pook]

Just want to throw this in here as it seems related and needs some community discussion..

https://docs.magento.com/m2/ce/user_guide/catalog/catalog-flat.html

This official documentation from Sept 2019 states that "Starting with Magento 2.3.0+, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues. A detailed description and solution is available in the Help Center."

Unfortunately this links to a Help Centre page which contradicts the information saying this affects "Magento Commerce (On-Premise) 2.1.x and above", despite the page being dated October 09, 2019. There are also other pages in the documentation that still recommend using flat catalog.

So which is it? This is a pretty key configuration setting from my experience and there needs to be a clear line of when to use it and when not to and more importantly why, what are the implications?

Perhaps the discussion here of the performance issues when running the flat indexers is a mute point if they're no longer meant to run at all?

[m2-assistant[bot]]

Hi @engcom-Hotel. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-Hotel]

Hello @chris-pook
Please refer to Magento devdocs repository, for advice or general discussion about your issue.

Сould you close this issue when you decide everything in the DevDocs repository?

[Naeemakhter1999]

How to reindex the indexer in every 5 Minutes in MagentoVersion 2.1.16 ?