Product price reindex takes too long with Magento 2.4.1 + MySQL 8.x + PHP 7.4 + nginx

[davidandersson1]

Hello,

We are facing issue on catalog_product_price reindex. reindex takes too long to complete or never completes.

Preconditions (*)

1. Magento version 2.4.0 or 2.4.1
2. MySQL version 8.x
3. PHP version 7.4
4. Total number of products = 400k (400000)
5. Total number of bundle products = 1000

Steps to reproduce (*)

1. Create 1000 bundled products with 15 options.
2. Create 1 bundled product with 30 options.
3. Set price_type equal to Dynamic, sku_type equal to Fixed, weight_type equal to Fixed on all bundle products.
4. Run price reindex from CLI: bin/magento indexer:reindex catalog_product_price

Expected result (*)

1. Product price reindex takes 1+ hours or more to complete.

Actual result (*)

1. Product price reindex takes a short time (2-3 minutes) to complete.

---

If we disable bundle products and perform price index then it completed within only 3 minutes but with enabled bundle products, it will take around 1+ hr.

We also tried to set higher values on some MySQL variables (i.e tmp_table_size,max_heap_table_size,innodb_buffer_pool_size) as per standard guideline as per below link but its not help us. https://devdocs.magento.com/guides/v2.4/install-gde/prereq/mysql.html

We also done some more investigation for this issue and found one Insert query (attached screenshot) which is taking too long while price reindex running.

Look like same issue as per below patch but here we have magento community edition + 2.4 versions https://support.magento.com/hc/en-us/articles/360051183691-MDVA-31224-Magento-patch-Product-price-reindex-takes-too-long

Please note: Above same configuration + if we use MySQL 5.7 then price index working fine without any extra time and completed within only 5 mins.

[m2-assistant[bot]]

Hi @davidandersson1. 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.4-develop instance - upcoming 2.4.x release

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

Please, add a comment to assign the issue: @magento I am working on this

---

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

  :warning: According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

:clock10: You can find the schedule on the Magento Community Calendar page.

:telephone_receiver: The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, please join the Community Contributions Triage session to discuss the appropriate ticket.

:movie_camera: You can find the recording of the previous Community Contributions Triage on the Magento Youtube Channel

:pencil2: Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[romainruaud]

@davidandersson1 I do have the exact same issue with MariaDB 10.1 (I don't want to update on latest since there are other known issues with recent MariaDB).

[romainruaud]

That being said it appears that the MDVA-31224 patch is available here : https://github.com/magento/quality-patches/blob/master/patches/os/MDVA-31224__improves_performance_of_catalog_product_price_reindex_operation_for_bundle_products__2.3.3.patch

I'll have a look at it to see if this improves things for me.

Regards

[romainruaud]

Okay, after a quick test :

• patch MDVA has no effect, it's already what is included in Magento 2.4.1

However as stated in #29264 I switched these lines :

$query = $select->insertFromSelect($this->getBundleSelectionTable());
//$query = $select->crossUpdateFromSelect($this->getBundleSelectionTable());

And the index did run in 2 minutes instead of hanging forever. Prices in the index table seems to be accurate.

Maybe you could give this a try on your project also. I'm curious if that does also fix your situation.

Regards

[davidandersson1]

Hi @romainruaud, Thanks for your help! I have checked with switches lines as you mentioned above but somehow its not help.

And yes, you are right that MDVA-31224 patch is already included in Magento 2.4.1

However, as per my previous investigation, method calculateDynamicBundleSelectionPrice (line no 588 in vendor/magento/module-bundle/Model/ResourceModel/Indexer/Price.php file + Magento version 2.4.1) tooks too longs to finish that's why price index time much increased instead of normal time to complete.

Please help us if anybody faced issue and got some solution for the same.

Thanks in advance, David Andersson

[davidandersson1]

Hello,

May I know, is there any update for us?

We are really eager to resolve this issue and at this time we have no possibility to upgrade the latest version on production server without resolve this index issue.

I request you to all, If anybody faced same kind of index issue and found any solution for same than please share with us.

Thanks in advance, David Andersson

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed after 14 days if no further activity occurs. Is this issue still relevant? If so, what is blocking it? Is there anything you can do to help move it forward? Thank you for your contributions!

[davidandersson1]

Hello,

Is there any update for us? really waiting from long time to resolve the issue!

Anybody please help!

[cdekkers]

Just noticed this problem in our similar setup. Price index takes way too long to complete. With ~130k products, the process takes about a minute for Magento 2.3, and around 2 to 3 hours on Magento 2.4.

[m2-assistant[bot]]

Hi @engcom-Alfa. 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-Alfa]

Hi @davidandersson1 , Well, Yes there is a delay in re indexing when we have large number of data (ex-50k-100k products as such). So, what is the expected criteria about the re indexing performance delay in such cases

[engcom-Alfa]

As there is no response to my last comment in last ~14 days, closing this current issue.

Kindly upgrade to the latest version to re test. In case of any Magento related issues, you may please report a new bug with all the information like Preconditions, detailed steps to reproduce, expected and actual Results along with screenshots/ screen recordings possibly.

For any Magento related references, you may refer to the Magento user guide.

[sunilit42]

@engcom-Alfa any solution for that

For me price reindex not complete any more

@cdekkers have you did any change to fix it?

[cdekkers]

@cdekkers have you did any change to fix it?

@sunilit42 We took some time to identify the cause of the issue, but were unable to pinpoint anything specific. At one point we just decided to live with the issue and schedule the indexing processes during the night. I just pulled up the logs and they show that the price index took 4 hours and 13 minutes last night.

@engcom-Alfa this issue really should not be closed due to inactivity.

[sunilit42]

@cdekkers for we work some time with 4-5 hours but some time give us temp table issue

any idea on that?

[cdekkers]

@sunilit42 Can you share the details of the temp table issue?

[sunilit42]

Hey @cdekkers Child process failed with message: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'catalog_product_index_price_temp' doesn't exist, query was: UPDATEcatalog_product_index_price_tempASi INNER JOINcatalog_product_index_price_cfg_opt_tempASioON i.entity_id = io.entity_id AND i.customer_group_id = io.customer_group_id AND i.website_id = io.website_id SETi.min_price= i.min_price - i.price + io.min_price,i.max_price= i.max_price - i.price + io.max_price,i.tier_price=io.tier_price{"exception":"[object] (Magento\\Framework\\DB\\Adapter\\TableNotFoundException(code: 1146): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'catalog_product_index_price_temp' doesn't exist, query was: UPDATEcatalog_product_index_price_tempASi INNER JOINcatalog_product_index_price_cfg_opt_tempASioON i.entity_id = io.entity_id AND i.customer_group_id = io.customer_group_id AND i.website_id = io.website_id SETi.min_price= i.min_price - i.price + io.min_price,i.max_price= i.max_price - i.price + io.max_price,i.tier_price=io.tier_priceat /srv/public_html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:599, Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'catalog_product_index_price_temp' doesn't exist, query was: UPDATEcatalog_product_index_price_tempASi INNER JOINcatalog_product_index_price_cfg_opt_tempASioON i.entity_id = io.entity_id AND i.customer_group_id = io.customer_group_id AND i.website_id = io.website_id SETi.min_price= i.min_price - i.price + io.min_price,i.max_price= i.max_price - i.price + io.max_price,i.tier_price=io.tier_priceat /srv/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110

[sunilit42]

One more thing while debuging i see

Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\CustomOptionPriceModifier=>85.012804031372 Taking more time

i change simple product price bunch only 3000 product then debuging time

Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\BasePriceModifier.php public function modifyPrice(IndexTableStructure $priceTable, array $entityIds = []) : void { foreach ($this->priceModifiers as $priceModifier) { echo get_class($priceModifier); $start = microtime(true); $priceModifier->modifyPrice($priceTable, $entityIds); $time_elapsed_secs = microtime(true) - $start; echo "=>".$time_elapsed_secs; echo "\n"; } ///exit; }

[cdekkers]

@sunilit42 Thanks for the update. You might want to redact your table name and the full path to your Magento installation for security reasons.

I cannot pinpoint the cause of the Base table or view not found issue, but I suspect that the abnormally long running time of the indexer process causes abnormal database resource usage that, depending on your database settings in your setup, will at some point cause the temporary table to be removed, which in turn results in this error.

Issue #18229 relates to this error, and just like you, some contributors mention that there's something in the customizable product options logic that is triggering this behavior—specifically, the Magento\Catalog\Model\ResourceModel\Product\Indexer\Price\CustomOptionPriceModifier class is pointed to.

In my setup, we have over 150k products and use many customizable options. I would not be surprised if the root cause of the abnormal indexing times is related to customizable options somehow. @sunilit42 can you share anything regarding the usage of customizable product options in your setup?

[sunilit42]

@cdekkers as for debugging i just doing reindex for simple product and it is taking aroung 1 hour and 30 min and max time into custom option price

then i move to mysql version 5.7 then i see my price reindex taking only 30 mins

[cdekkers]

@sunilit42 Thank you. Whenever I find the time I can delve deeper into this issue, but this might take a while.

[sunilit42]

@cdekkers finally i found with mysql 8 , that is issue with /!50100 TABLESPACE innodb_system / ENGINE=InnoDB DEFAULT CHARSET=utf8

Table space should be :- innodb_file_per_table

If i convert table space with innodb_file_per_table then works fine and price reindex is also quick

Hope it will help you

[davidandersson1]

Hello @sunilit42

Can you please help me, I want to change table space should be "innodb_file_per_table" on mysql 8 as you said above but I do not getting exactly how and where I have to change and perform again price indexer to check the index timing.

So if possible can you please provide me steps to do it.

Thanks in advance! David Andersson

[davidandersson1]

Hi @davidandersson1 , Well, Yes there is a delay in re indexing when we have large number of data (ex-50k-100k products as such). So, what is the expected criteria about the re indexing performance delay in such cases

For example, if we have 350k products where only 600-700 products are bundle products where dynamic price = Enabled then price reindex taking around 17-18 mins with MySQL 8.x.x, if we just disabled or remove those 600-700 bundle products then price reindex taking only 3-4 mins to complete it. so main issue is as i described above like Dynamic price Bundle products with MySQL 8.x.x is taking lots of time somehow.

[sunilit42]

@davidandersson1

ALTER TABLE catalog_category_product_index_store1 TABLESPACE = innodb_file_per_table;

• i have same query again for each store

[davidandersson1]

Hi @sunilit42 thanks a lot for your quick reply.

However before I run your suggested query, in my case issue below query taking much time during price index run. ( dynamic price type bundle products is original cause)

INSERT INTO catalog_product_index_price_bundle_sel_temp SELECT i.entity_id, i.customer_group_id, i.website_id, bo.option_id, bs.selection_id, IF(bo.type = 'select' OR bo.type = 'radio', 0, 1) AS group_type, bo.required AS is_required, LEAST(IF(i.special_price > 0 AND i.special_price < 100, ROUND(idx.min_price bs.selection_qty (i.special_price / 100), 4), idx.min_price bs.selection_qty), IFNULL((IF(i.tier_percent IS NOT NULL, ROUND((1 - i.tier_percent / 100) idx.min_price bs.selection_qty, 4), NULL)), idx.min_price bs.selection_qty)) AS price, IF(i.tier_percent IS NOT NULL, ROUND((1 - i.tier_percent / 100) idx.min_price bs.selection_qty, 4), NULL) AS tier_price FROM catalog_product_index_price_bundle_temp AS i INNER JOIN catalog_product_entity AS parent_product ON parent_product.entity_id = i.entity_id INNER JOIN catalog_product_bundle_option AS bo ON bo.parent_id = parent_product.entity_id INNER JOIN catalog_product_bundle_selection AS bs ON bs.option_id = bo.option_id INNER JOIN catalog_product_index_price_replica AS idx ON bs.product_id = idx.entity_id AND i.customer_group_id = idx.customer_group_id AND i.website_id = idx.website_id INNER JOIN cataloginventory_stock_status AS si ON si.product_id = bs.product_id WHERE (i.price_type=0) AND (si.stock_status = 1) ON DUPLICATE KEY UPDATE group_type = VALUES(group_type), is_required = VALUES(is_required), price = VALUES(price), tier_price = VALUES(tier_price)

So we both are on same page? if Yes then I will go ahead with your suggested query. so please let me know your view.

Thanks, David Andersson

[sunilit42]

@davidandersson1 ya for price reindex taking for more than 2-3 hours after i did it is taking 30 min

[davidandersson1]

Thanks a lot @sunilit42 for your quick response. I will try with it!

[davidandersson1]

@sunilit42 now i have tried with suggested query, but somehow its throw error on "catalog_category_product" indexer i.e SQLSTATE[HY000]: General error: 1478 InnoDB: TABLESPACE=innodb_file_per_table option is disallowed for temporary tables with INNODB_STRICT_MODE=ON. So is there any other changes we need to do or how? any idea? please share if you aware.

[amouri82]

@sunilit42 now i have tried with suggested query, but somehow its throw error on "catalog_category_product" indexer i.e SQLSTATE[HY000]: General error: 1478 InnoDB: TABLESPACE=innodb_file_per_table option is disallowed for temporary tables with INNODB_STRICT_MODE=ON. So is there any other changes we need to do or how? any idea? please share if you aware.

Hello, @davidandersson1 did you find a solution to rollback the Alter table ? I am facing the same problem! Thank you

[davidandersson1]

@amouri82 i just set INNODB_STRICT_MODE = OFF in order to resolve above index issue, except than this, I haven't found found any other solution.

[amouri82]

Hi @davidandersson1, Thank you for your replay. Do you know how to return to default tablespace value without turning off INNODB_STRICT_MODE ?

[sky-hub]

The problem here it's way deeper than that. It is caused by the index selection when query is running from PHP. I did not get to check what is happening in PHP that could cause a different index selection but here are the results:

PHP: `

[0] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => i
        [type] => ALL
        [possible_keys] => PRIMARY
        [key] =>
        [key_len] =>
        [ref] =>
        [rows] => 64
        [filtered] => 100
        [Extra] => Using where
    )

[1] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => parent_product
        [type] => ref
        [possible_keys] => PRIMARY,CATALOG_PRODUCT_ENTITY_CREATED_IN,CATALOG_PRODUCT_ENTITY_UPDATED_IN,CATALOG_PRODUCT_ENTITY_ENTITY_ID_CREATED_IN_UPDATED_IN
        [key] => CATALOG_PRODUCT_ENTITY_ENTITY_ID_CREATED_IN_UPDATED_IN
        [key_len] => 4
        [ref] => magento.i.entity_id
        [rows] => 1
        [filtered] => 100
        [Extra] => Using where; Using index
    )
[2] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => idx
        [type] => ref
        [possible_keys] => PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
        [key] => CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
        [key_len] => 6
        [ref] => magento.i.website_id,magento.i.customer_group_id
        [rows] => 1
        [filtered] => 100
        [Extra] => Using where; Using index
    )
[3] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => si
        [type] => ref
        [possible_keys] => PRIMARY,CATALOGINVENTORY_STOCK_STATUS_STOCK_STATUS
        [key] => CATALOGINVENTORY_STOCK_STATUS_STOCK_STATUS
        [key_len] => 6
        [ref] => const,magento.idx.entity_id
        [rows] => 1
        [filtered] => 100
        [Extra] =>  
    )
[4] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => bo
        [type] => ref
        [possible_keys] => PRIMARY,CATALOG_PRODUCT_BUNDLE_OPTION_PARENT_ID
        [key] => CATALOG_PRODUCT_BUNDLE_OPTION_PARENT_ID
        [key_len] => 4
        [ref] => magento.parent_product.row_id
        [rows] => 2
        [filtered] => 100
        [Extra] =>
    )
[5] => Array
    (
        [id] => 1
        [select_type] => SIMPLE
        [table] => bs
        [type] => ref
        [possible_keys] => CATALOG_PRODUCT_BUNDLE_SELECTION_OPTION_ID,CATALOG_PRODUCT_BUNDLE_SELECTION_PRODUCT_ID
        [key] => CATALOG_PRODUCT_BUNDLE_SELECTION_OPTION_ID
        [key_len] => 4
        [ref] => magento.bo.option_id
        [rows] => 2
        [filtered] => 100
        [Extra] => Using where
    )

`

CLI:

` 1. row id: 1 select_type: SIMPLE table: i type: ALL possible_keys: PRIMARY,[CATALOG_PRODUCT_INDEX_PRICE_BUNDLE_TMP_PRICE_TYPE] key: NULL key_len: NULL ref: NULL rows: 64 filtered: 75.00 Extra: Using where

2. row id: 1 select_type: SIMPLE table: parent_product type: ref possible_keys: PRIMARY,CATALOG_PRODUCT_ENTITY_CREATED_IN,CATALOG_PRODUCT_ENTITY_UPDATED_IN,CATALOG_PRODUCT_ENTITY_ENTITY_ID_CREATED_IN_UPDATED_IN key: CATALOG_PRODUCT_ENTITY_ENTITY_ID_CREATED_IN_UPDATED_IN key_len: 4 ref: magento.i.entity_id rows: 1 filtered: 100.00 Extra: Using where; Using index 3. row id: 1 select_type: SIMPLE table: bo type: ref possible_keys: PRIMARY,CATALOG_PRODUCT_BUNDLE_OPTION_PARENT_ID key: CATALOG_PRODUCT_BUNDLE_OPTION_PARENT_ID key_len: 4 ref: magento.parent_product.row_id rows: 1 filtered: 100.00 Extra: 4. row id: 1 select_type: SIMPLE table: bs type: ref possible_keys: CATALOG_PRODUCT_BUNDLE_SELECTION_OPTION_ID,CATALOG_PRODUCT_BUNDLE_SELECTION_PRODUCT_ID key: CATALOG_PRODUCT_BUNDLE_SELECTION_OPTION_ID key_len: 4 ref: magento.bo.option_id rows: 3 filtered: 100.00 Extra: 5. row id: 1 select_type: SIMPLE table: idx type: eq_ref possible_keys: PRIMARY,CATALOGINVENTORY_STOCK_STATUS_STOCK_STATUS key: CATALOGINVENTORY_STOCK_STATUS_STOCK_STATUS key_len: 6 ref: const,magento.bs.product_id rows: 1 filtered: 100.00 Extra: Using index 6. row id: 1 select_type: SIMPLE table: idx type: eq_ref possible_keys: PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE key: PRIMARY key_len: 10 ref: magento.bs.product_id,magento.i.customer_group_id,magento.i.website_id rows: 1 filtered: 100.00 Extra: `

Long story short, the index selected for price replica table is CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE when it should be using PRIMARY.

At this point the solution would be to use INDEX HINTING:

INSERT INTOcatalog_product_index_price_bundle_sel_temp(entity_id,customer_group_id,website_id,option_id,selection_id,group_type,is_required,price,tier_price) SELECTi.entity_id,i.customer_group_id,i.website_id,bo.option_id,bs.selection_id, IF(bo.type = 'select' OR bo.type = 'radio', 0, 1) ASgroup_type,bo.requiredASis_required, LEAST(IF(i.special_price > 0 AND i.special_price < 100, ROUND(idx.min_price * bs.selection_qty * (i.special_price / 100), 4), idx.min_price * bs.selection_qty), IFNULL((IF(i.tier_percent IS NOT NULL, ROUND((1 - i.tier_percent / 100) * idx.min_price * bs.selection_qty, 4), NULL)), idx.min_price * bs.selection_qty)) ASprice, IF(i.tier_percent IS NOT NULL, ROUND((1 - i.tier_percent / 100) * idx.min_price * bs.selection_qty, 4), NULL) AStier_priceFROMcatalog_product_index_price_bundle_tempASi INNER JOINcatalog_product_entityASparent_productON parent_product.entity_id = i.entity_id AND (parent_product.created_in <= '1669586340' AND parent_product.updated_in > '1669586340') INNER JOINcatalog_product_bundle_optionASboON bo.parent_id = parent_product.row_id INNER JOINcatalog_product_bundle_selectionASbsON bs.option_id = bo.option_id INNER JOINcatalog_product_index_price_replicaASidxUSE INDEX (PRIMARY) ON idx.entity_id = bs.product_id AND idx.customer_group_id = i.customer_group_id AND idx.website_id = i.website_id INNER JOINcataloginventory_stock_statusASsiON si.product_id = bs.product_id WHERE (i.price_type=0) AND (si.stock_status = 1) ON DUPLICATE KEY UPDATEentity_id= VALUES(entity_id),customer_group_id= VALUES(customer_group_id),website_id= VALUES(website_id),option_id= VALUES(option_id),selection_id= VALUES(selection_id),group_type= VALUES(group_type),is_required= VALUES(is_required),price= VALUES(price),tier_price= VALUES(tier_price)

See: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

Note: I will get back with updates once I have them. Enjoy.