2.4.3-p1 + MySQL version: 8.x.x with catalog price index taken too long

[davidandersson1]

Preconditions and environment

• 2.4.3-p1
• MySQL version: 8.x.x
• PHP version 7.4

Steps to reproduce

1) create around 350k~ products with 500-600 products where type_id = bundle and Dynamic Price = Yes with min 5-6 options with each product 2) Run catalog_product_price indexer

Expected result

it's take only around 5-6 mins.

Actual result

its took 18-20 mins instead of 5-6 mins.

Additional information

Magento 2.4.0 + PHP 7.4 + MySQL 5.7 + nginx server performance is much better than MySQL 8.0

Release note

No response

Triage and priority

• [X] Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• [X] Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• [X] Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• [X] Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• [X] Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant[bot]]

Hi @davidandersson1. Thank you for your report. To speed up processing of this issue, make sure that you provided the following information:

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

Make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

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

For more details, review the Magento Contributor Assistant documentation.

Add a comment to assign the issue: @magento I am working on this

To learn more about issue processing workflow, refer to the Code Contributions.

---

• 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, join the Community Contributions Triage session to discuss the appropriate ticket.

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

[engcom-Delta]

Hi @davidandersson1 , Thank you for raising an issue. We tried to reproduce the issue on magento 2.4 develop instance. Could you please confirm that do we need to create 350000 products to reproduce the issue ? please confirm.

Regards,

[m2-assistant[bot]]

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

[davidandersson1]

• PHP version 7.4

In order to confirm that 350k requires or not, we have one more test instance with same configuration (2.4.3-p1 + MySQL version: 8.x.x + PHP version 7.4), till time there we have 132k~ only simple products but now we have imported there 75~ around bundle dynamic products and tried to perform the catalog product index.

Below are the difference. so now I can confirm that those 350k products does not matter here, please try with only dynamic bundle products, here in my case only 75~ around bundle but like if we will increase the number of it, price reindex time should also increase that I am sure. 1) Without dynamic bundle products Product Price index has been rebuilt successfully in 00:01:05 2) with dynamic bundle products: Product Price index has been rebuilt successfully in 00:12:10

[davidandersson1]

tried to perform the catalog product index

Here I meant tried to perform the catalog product price reindex (catalog_product_price).

[davidandersson1]

Hi @davidandersson1 , Thank you for raising an issue. We tried to reproduce the issue on magento 2.4 develop instance. Could you please confirm that do we need to create 350000 products to reproduce the issue ? please confirm.

Regards,

Hello @engcom-Delta , I have respond above, hop you noticed.

[engcom-Delta]

Hi @davidandersson1 , Thank you for the update , we tried to replicate the issue on Magento 2.4 develop instance. issue is not reproducible.

Below steps followed:

1. Small. xml

2. Run the command

bin/magento setup:perf:generate-fixtures setup/performance-toolkit/profiles/ce/small.xml

3. Run the command bin/magento indexer:reindex

Approximately Product price index has been taken 00:01:01

Hence , request to review the steps followed and confirm if i am wrong.

Thanks

[davidandersson1]

@engcom-Delta now I done with another fresh setup and I run below command because we have 2 store views in our current live site.

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

Now bundle products creating successfully but after looking on admin, it has Dynamic Price = No (see attached screenshot) on all 75 bundle products + it's assigned with only main Website (see attached screenshot) but as I informed above at "Steps to reproduce" we need Dynamic Price = Yes to reproduce this issue. so please change Dynamic Price = Yes + assigned on all three websites and then try to preform the catalog_product_price command.

Hope you getting me here!

Thanks, David Andersson

[davidandersson1]

@engcom-Delta now I have created more 425 bundle products and set Dynamic price = Yes and assigned on all three websites and ran catalog_product_price reindex, you can see the index time difference in attached screenshots.

Total number of products: 55500 Where only 500 bundle products with dynamic price = Yes and assigned all three websites.

however during price index running, I also checked "show all processlist" and below query I found which is stuck and most probably a reason why its taking so long.

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 issue is completely reproducible on magento 2.4 develop instance + MySQL 8.x.x + PHP 7.4

Thanks, David Andersson

[engcom-Delta]

Hi @davidandersson1 , Thank you for confirming once again. Could you please help me how you were able to make 'set Dynamic price = Yes ' . in fact after we ran the command "bin/magento setup:perf:generate-fixtures setup/performance-toolkit/profiles/ce/small.xml" for generating bundle products. We could not find out Dynamic price for those products.

So please guide us to replicate it .

Regards,

[davidandersson1]

Hi @engcom-Delta, We are doing it via rapidflow module, however without rapidflow module it's also possible. as we know, Dynamic price is product attribute with code = "price_type" with backend_type = "int" so entity table is catalog_product_entity_int so we can do it directly within below MySQL update query. first you can check with SELECT query and then update. if we do not do it via database then manually we have to create bundle products from admin, there magento give us option to set Dynamic Price = Yes/No.

SELECT * from catalog_product_entity_int WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price_type') and value = 1

UPDATE catalog_product_entity_int set value = 0 WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price_type') and value = 1

After run above update query, if you check product edit level on admin it will be Dynamic Price = Yes and then you can run reindex but it would be good first if you will some more bundle products (i.e 500~) as i did above so issue should replicate well.

Please let me know if you need any other help from my end.

Thanks, David Andersson

[engcom-Delta]

Hi @davidandersson1 , Thanks for providing the update. We are able to reproduce Actual Results.Hence the issue has been confirmed after Verifying on Magento 2.4 develop branch.please refer the screenshots.

1. Medium.xml

2. Creating Bundle Products

3. Reindexing without Dynamic price set to NO

4. After setting Dynamic Price to Yes

5. Reindexing with Dynamic Price set to Yes

DB commands: SELECT * from catalog_product_entity_int WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price_type') and value = 1 UPDATE catalog_product_entity_int set value = 0 WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price_type') and value = 1

[davidandersson1]

Hi @engcom-Delta thanks for providing all screenshots and all steps you followed correctly. in short, just adding 500 dynamic price bundle products increase price index time around 20~ mins.

[github-jira-sync-bot]

:white_check_mark: Jira issue https://jira.corp.adobe.com/browse/AC-6061 is successfully created for this GitHub issue.

[m2-assistant[bot]]

:white_check_mark: Confirmed by @engcom-Delta. Thank you for verifying the issue.
Issue Available: @engcom-Delta, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[davidandersson1]

Hello @engcom-Delta please let us know if there any update on this issue or patch released.

Regards,

[davidandersson1]

HI @engcom-Delta please let us know if there any update on this issue or patch released.

Regards, David Andersson

[davidandersson1]

Hello @engcom-Delta please let me know if there any update on this issue or patch released.

Regards, David Andersson

[davidandersson1]

HI @engcom-Delta please let us know if there any update on this issue or patch released.

Regards, David Andersson

[ahsan-horani-folio]

I believe this issue might have been resolved in 2.4.4, 2.4.5 or in later releases @davidandersson1 Did you try with the Magento 2.4.5-p1 version?

[sky-hub]

@davidandersson1 , you may want to check this: https://github.com/magento/magento2/issues/31219#issuecomment-1541639283

[nd1996]

Any patch for same - this issue is happened on 2.4.5-p1 EE version also

Take too much time for reindexing the price

[ahsan-horani-folio]

We resolved this problem by forcefully adding the indexes. Indexes on temp tables are not properly applied. This worked for us.

Recently done for 2.4.5-p1 with MySQL 8

[TzviCons]

@ahsan-horani-folio I'd like to try your approach with our server. What did you do for adding table indexes?

[davidandersson1]

Hi @ahsan-horani-folio can you please share your view how exactly you have resolved this issue? or what do you mean by "forcefully adding the indexes".

It would be good if you share here. I will be very thankful to you!

[davidandersson1]

Hello @engcom-Delta do we have any update or any patch for this core magento bug?

[davidandersson1]

Now, I can confirm that with magento latest version 2.4.6-p2, this issue is not fixed

[ahsan-horani-folio]

@davidandersson1 very unfortunate to hear that. I am sharing the solution that worked for us in Magento 2.4.5-p1 Please must share your feedback @TzviCons @nd1996 FYA

Folio3_Mysql8-1.0.0.zip

[brideo]

For me this command is the one taking the most amount of time:

UPDATE
    `catalog_product_index_price_temp` AS `i`
    INNER JOIN (
        SELECT
            `catalog_product_index_price_bundle_opt_temp`.`entity_id`,
            `catalog_product_index_price_bundle_opt_temp`.`customer_group_id`,
            `catalog_product_index_price_bundle_opt_temp`.`website_id`,
            SUM(min_price) AS `min_price`,
            MIN(alt_price) AS `alt_price`,
            SUM(max_price) AS `max_price`,
            SUM(tier_price) AS `tier_price`,
            MIN(alt_tier_price) AS `alt_tier_price`
        FROM
            `catalog_product_index_price_bundle_opt_temp`
        GROUP BY
            `entity_id`,
            `customer_group_id`,
            `website_id`) AS `io`
            ON i.entity_id = io.entity_id
    AND i.customer_group_id = io.customer_group_id
    AND i.website_id = io.website_id
    SET `i`.`min_price` = IF(i.min_price + IFNULL(io.min_price, 0) = 0, io.alt_price, i.min_price + IFNULL(io.min_price, 0)), `i`.`max_price` = io.max_price + i.max_price, `i`.`tier_price` = IF(i.tier_price + IFNULL(io.tier_price, 0) = 0, io.alt_tier_price, i.tier_price + IFNULL(io.tier_price, 0));

None of the tables seemed to have any indexes, this has made the index faster for me:

ALTER TABLE catalog_product_index_price_tmp ADD INDEX (entity_id);
ALTER TABLE catalog_product_index_price_tmp ADD INDEX (customer_group_id);
ALTER TABLE catalog_product_index_price_tmp ADD INDEX (website_id);

You can see there are no indexes in the schema definition:

    <table name="catalog_product_index_price_tmp" resource="default" engine="innodb"
           comment="Catalog Product Price Indexer Temp Table">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="false"
                comment="Product ID"/>
        <column xsi:type="int" name="customer_group_id" unsigned="true" nullable="false" identity="false"
                default="0" comment="Customer Group ID"/>
        <column xsi:type="smallint" name="website_id" unsigned="true" nullable="false" identity="false"
                comment="Website ID"/>
        <column xsi:type="smallint" name="tax_class_id" unsigned="true" nullable="true" identity="false"
                default="0" comment="Tax Class ID"/>
        <column xsi:type="decimal" name="price" scale="6" precision="20" unsigned="false" nullable="true"
                comment="Price"/>
        <column xsi:type="decimal" name="final_price" scale="6" precision="20" unsigned="false" nullable="true"
                comment="Final Price"/>
        <column xsi:type="decimal" name="min_price" scale="6" precision="20" unsigned="false" nullable="true"
                comment="Min Price"/>
        <column xsi:type="decimal" name="max_price" scale="6" precision="20" unsigned="false" nullable="true"
                comment="Max Price"/>
        <column xsi:type="decimal" name="tier_price" scale="6" precision="20" unsigned="false" nullable="true"
                comment="Tier Price"/>
        <column xsi:type="int" name="id" unsigned="true" nullable="false" identity="true"
                comment="ID"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>
    </table>

Magento\Bundle\Model\ResourceModel\Indexer\Price::getBundleOptionTable()

    private function getBundleOptionTable()
    {
        if ($this->tmpBundleOptionTable === null) {
            $this->tmpBundleOptionTable = $this->getTable('catalog_product_index_price_bundle_opt_temp');
            $this->getConnection()->createTemporaryTableLike(
                $this->tmpBundleOptionTable,
                $this->getTable('catalog_product_index_price_bundle_opt_tmp'),
                true
            );

            $this->getConnection()->addIndex('catalog_product_index_price_bundle_opt_tmp', 'some_index_name_entity_id', 'entity_id');
            $this->getConnection()->addIndex('catalog_product_index_price_bundle_opt_tmp', 'some_index_customer_group_id', 'customer_group_id');
            $this->getConnection()->addIndex('catalog_product_index_price_bundle_opt_tmp', 'some_index_website_id', 'website_id');
        }

        return $this->tmpBundleOptionTable;
    }

It still took 7 minutes, which is unacceptable:

Product Price index has been rebuilt successfully in 00:07:32

But it's more acceptable than what I was getting before:

Product Price index has been rebuilt successfully in 02:47:13

[ahsan-horani-folio]

@brideo try the solution I have provided above

[epson121]

@ahsan-horani-folio I've tried your approach and it's working fine, Magento 2.4.6-p1. Thanks!

Do you have more information about why this query is using wrong index, so a 'PRIMARY' has to be forced?

When running re-indexing process, I always get this issue - query using the wrong index, however, when running this query directly either via PHPStorm's DB console, or mysql client, correct index is always used without forcing anything. This is telling me that maybe Magento is doing something that is causing this issue.

[ahsan-horani-folio]

It's a Magento bug. Magento 2.4 is not fully supported with MySQL 8.0

Use MariaDB 10.6 instead

On Thu, 14 Sep 2023 at 4:04 PM, Luka Rajčević @.***> wrote:

@ahsan-horani-folio https://github.com/ahsan-horani-folio I've tried your approach and it's working fine, Magento 2.4.6-p1. Thanks!

Do you have more information about why this query is using wrong index, so a 'PRIMARY' has to be forced?

When running re-indexing process, I always get this issue - query using the wrong index, however, when running this query directly either via PHPStorm's DB console, or mysql client, correct index is always used without forcing anything. This is telling me that maybe Magento is doing something that is causing this issue.

— Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/35721#issuecomment-1719238859, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKKTGFOZHP7P7JMWSG3TUM3X2LQCRANCNFSM52TFJE5A . You are receiving this because you were mentioned.Message ID: @.***>

[davidandersson1]

@ahsan-horani-folio I have tried your provided solution in magento 2.4.6-p2 and it's works really great!

Thanks a lot for your help!

[ahsan-horani-folio]

@ahsan-horani-folio I have tried your provided solution in magento 2.4.6-p2 and it's works really great!

Thanks a lot for your help!

I am glad to hear this. I hope Adobe addresses this issue soon

[pkarsai]

It looks like this chain of commits is the official solution to this problem: https://github.com/search?q=repo%3Amagento%2Fmagento2+acp2e-2033+author%3Aaplapana&type=commits&s=committer-date&o=desc