Randomly getting flooded with queries from related / upsell / crosssell blocks and price indexing #36667
Comments
|
Hi @ioweb-gr. Thank you for your report.
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:
For more details, review the Magento Contributor Assistant documentation. Add a comment to assign the issue: To learn more about issue processing workflow, refer to the Code Contributions.
🕙 You can find the schedule on the Magento Community Calendar page. 📞 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. ✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel |
|
There might be a dead lock created. Use flock to separate your index thread for cron. |
|
See here #25987 (comment) |
|
Hi @engcom-Dash. Thank you for working on this issue.
|
|
Hi @ioweb-gr , We are trying to reproduce the issue in 2.4.4 magento instance...But We are not able to reproduce the issue. Let us know if we missing anything!!! |
engcom-Dash
added
the
Issue: needs update
|
Hi, it's not like it's dropping constantly when reindexing. The problem is that we get this situation randomly while the site is under normal load 40-60 users. I think @Adel-Magebinary may be right about this one and the original issue is the same as mine. I experimented with changing the lock-mechanism from DB to File as a test, and the load average on the server decreased significantly (it was averaging at 9-14) and now it's constantly on 2-4. I'm waiting a bit to see if the issue reoccurs with the file lock mechanism and if it does I'll experiment with flock as it was suggested in the thread. It's entirely possible the cause is that same issue #25987 Let's put this issue on hold until I can verify this behavior has changed by changing the lock mechanism. |
m2-community-project
bot
removed
the
Issue: needs update
|
Hi @ioweb-gr , We are closing this issue as there has been no latest update on the same. Kindly reopen / create new issue if you are still facing any issues. |
|
HI @engcom-Dash I was trying to figure out what else might be the difference between our systems, in my case I have the indexers set to schedule mode. Today I tried to reset the following indexers
Which in turn tried to trigger a reindex of
After doing this, I got the same picture as above. Would you be willing to retry it once with the indexers set in scheduled mode? |
|
Hi @ioweb-gr , Thanks for your Information... But we got expected result as " The site is still working and queries are much faster and won't bring the site down." Thanks. |
engcom-Dash
added
the
Issue: needs update
|
This is a weekly occurence for us but I still need any ideas you can throw on how to reproduce the issue in a stable manner |
|
Hi @Adel-Magebinary , @ilnytskyi , @Nuranto , @hostep , cc: @ioweb-gr , Thank you all for your kind cooperation, I am writing a request to all of you that here we are trying to reproduce the issue,But as neither reporter nor i am able to reproduce the issue. Thanks. |
engcom-Bravo
added
the
Reported on 2.4.1
m2-community-project
bot
added
the
Priority: P2
|
Hello @ioweb-gr, Thanks for the detailed explanation here in this comment #36667 (comment). We are trying to reproduce this issue meanwhile can you please elaborate on the below point:
Thanks |
engcom-Hotel
added
Issue: needs update
As explained this main issue is coming when a select query is executing that uses the price index table that is normally renamed by the indexer. If the rename action occurs while the select query is running, Magento will drop In my case the queries from related, crosssells and upsells took ages to finish due to improper joins so they were the culprits but also other queries like product export queries can block it as well as they run for a prolonged period |
|
@engcom-Hotel Try inserting 2m products with 10 MSI sources with upsell and related products as dummy data. Then, run an export from the command line. Then go to catalogue price rule and create a rule with a 20% discount. You should see the freeze. Make sure cron is changed to schedule. This is a significant performance and stability issue for large sites. It should be looked into urgently. I'm sure most of the large sites are experiencing this issue and getting random downtime. They might not be able to look as deep as @ioweb-gr did. But this is definitely should be on the top of the top of the backlog P0. |
I can also confirm that this patch worked for me on Open Source 2.4.5-p4 with ~100k products with some catalog price rules. |
|
Hello @Adel-Magebinary, Thanks for the steps! We have tried to reproduce the issue with the vanilla 2.4-develop instance with the MSI package installed. But still the issue is not reproducible for us. Please have a look at the below screenshot for reference: Product Page Cron setup on schedule mode Catalog Price Rule The only difference is, that we have started the export from the admin panel but not from CLI, can you please let us know which module you have used to run the export from CLI? Thanks |
|
@engcom-Hotel it's not so easy to reproduce the issue. Keep in mind we were seeing this happen 4-5 times / day on a moderate traffic website where the mariadb optimizer would be confused by the query and choosing randomly a different plan. The underlying issue that the RENAME table queries break the website is reproducible by any type of select that needs to run for a long time e.g. here's a sample query, __SELECT__ `e`.*,
IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`,
IF(at_visibility.value_id > 0, at_visibility.value,
at_visibility_default.value) AS `visibility`,
`at_brand_logo`.`value` AS `brand_logo`,
IF(at_image.value_id > 0, at_image.value, at_image_default.value) AS `image`,
`at_manufacturer`.`value` AS `manufacturer`,
IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,
IF(at_season.value_id > 0, at_season.value, at_season_default.value) AS `season`,
`at_sex`.`value` AS `sex`,
IF(at_short_description.value_id > 0, at_short_description.value,
at_short_description_default.value) AS `short_description`,
IF(at_small_image.value_id > 0, at_small_image.value,
at_small_image_default.value) AS `small_image`,
IF(at_thumbnail.value_id > 0, at_thumbnail.value,
at_thumbnail_default.value) AS `thumbnail`,
IF(at_url_key.value_id > 0, at_url_key.value,
at_url_key_default.value) AS `url_key`,
IF(at_special_price.value_id > 0, at_special_price.value,
at_special_price_default.value) AS `special_price`,
IF(at_special_from_date.value_id > 0, at_special_from_date.value,
at_special_from_date_default.value) AS `special_from_date`,
IF(at_special_to_date.value_id > 0, at_special_to_date.value,
at_special_to_date_default.value) AS `special_to_date`,
`at_price_type`.`value` AS `price_type`,
IF(at_price.value_id > 0, at_price.value, at_price_default.value) AS `price`,
`at_tax_class_id`.`value` AS `tax_class_id`,
`stock`.`qty`,
`stock`.`is_in_stock`,
`stock`.`manage_stock`,
`stock`.`use_config_manage_stock`,
`stock`.`backorders`,
`stock`.`use_config_backorders`,
MAX(DISTINCT request_path) AS `request_path`,
`curpc`.*,
`cpsl`.`parent_id`,
GROUP_CONCAT(DISTINCT categories.category_id) AS `categories_ids`,
`price_index`.`min_price`,
`price_index`.`max_price`,
`price_index`.`final_price`,
`price_index`.`price` AS `base_price`,
COUNT(DISTINCT e.entity_id) AS `total`
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `product_website`
ON product_website.product_id = e.entity_id AND product_website.website_id = 2
INNER JOIN `catalog_product_entity_int` AS `at_status_default`
ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND
(`at_status_default`.`attribute_id` = 84) AND `at_status_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_status`
ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = 84) AND
(`at_status`.`store_id` = 5)
INNER JOIN `catalog_product_entity_int` AS `at_visibility_default`
ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND
(`at_visibility_default`.`attribute_id` = 91) AND `at_visibility_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_visibility`
ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = 91) AND
(`at_visibility`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_int` AS `at_brand_logo`
ON (`at_brand_logo`.`entity_id` = `e`.`entity_id`) AND (`at_brand_logo`.`attribute_id` = 273) AND
(`at_brand_logo`.`store_id` = 0)
LEFT JOIN `catalog_product_entity_varchar` AS `at_image_default`
ON (`at_image_default`.`entity_id` = `e`.`entity_id`) AND
(`at_image_default`.`attribute_id` = 74) AND `at_image_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_image`
ON (`at_image`.`entity_id` = `e`.`entity_id`) AND (`at_image`.`attribute_id` = 74) AND
(`at_image`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_int` AS `at_manufacturer`
ON (`at_manufacturer`.`entity_id` = `e`.`entity_id`) AND (`at_manufacturer`.`attribute_id` = 70) AND
(`at_manufacturer`.`store_id` = 0)
LEFT JOIN `catalog_product_entity_varchar` AS `at_name_default`
ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = 60) AND
`at_name_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_name`
ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = 60) AND
(`at_name`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_varchar` AS `at_season_default`
ON (`at_season_default`.`entity_id` = `e`.`entity_id`) AND
(`at_season_default`.`attribute_id` = 319) AND `at_season_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_season`
ON (`at_season`.`entity_id` = `e`.`entity_id`) AND (`at_season`.`attribute_id` = 319) AND
(`at_season`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_int` AS `at_sex`
ON (`at_sex`.`entity_id` = `e`.`entity_id`) AND (`at_sex`.`attribute_id` = 343) AND
(`at_sex`.`store_id` = 0)
LEFT JOIN `catalog_product_entity_text` AS `at_short_description_default`
ON (`at_short_description_default`.`entity_id` = `e`.`entity_id`) AND
(`at_short_description_default`.`attribute_id` = 62) AND
`at_short_description_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_text` AS `at_short_description`
ON (`at_short_description`.`entity_id` = `e`.`entity_id`) AND
(`at_short_description`.`attribute_id` = 62) AND (`at_short_description`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_varchar` AS `at_small_image_default`
ON (`at_small_image_default`.`entity_id` = `e`.`entity_id`) AND
(`at_small_image_default`.`attribute_id` = 75) AND `at_small_image_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_small_image`
ON (`at_small_image`.`entity_id` = `e`.`entity_id`) AND (`at_small_image`.`attribute_id` = 75) AND
(`at_small_image`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_varchar` AS `at_thumbnail_default`
ON (`at_thumbnail_default`.`entity_id` = `e`.`entity_id`) AND
(`at_thumbnail_default`.`attribute_id` = 76) AND `at_thumbnail_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_thumbnail`
ON (`at_thumbnail`.`entity_id` = `e`.`entity_id`) AND (`at_thumbnail`.`attribute_id` = 76) AND
(`at_thumbnail`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key_default`
ON (`at_url_key_default`.`entity_id` = `e`.`entity_id`) AND
(`at_url_key_default`.`attribute_id` = 86) AND `at_url_key_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key`
ON (`at_url_key`.`entity_id` = `e`.`entity_id`) AND (`at_url_key`.`attribute_id` = 86) AND
(`at_url_key`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_decimal` AS `at_special_price_default`
ON (`at_special_price_default`.`entity_id` = `e`.`entity_id`) AND
(`at_special_price_default`.`attribute_id` = 65) AND `at_special_price_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_decimal` AS `at_special_price`
ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND
(`at_special_price`.`attribute_id` = 65) AND (`at_special_price`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date_default`
ON (`at_special_from_date_default`.`entity_id` = `e`.`entity_id`) AND
(`at_special_from_date_default`.`attribute_id` = 66) AND
`at_special_from_date_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date`
ON (`at_special_from_date`.`entity_id` = `e`.`entity_id`) AND
(`at_special_from_date`.`attribute_id` = 66) AND (`at_special_from_date`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date_default`
ON (`at_special_to_date_default`.`entity_id` = `e`.`entity_id`) AND
(`at_special_to_date_default`.`attribute_id` = 67) AND `at_special_to_date_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date`
ON (`at_special_to_date`.`entity_id` = `e`.`entity_id`) AND
(`at_special_to_date`.`attribute_id` = 67) AND (`at_special_to_date`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_int` AS `at_price_type`
ON (`at_price_type`.`entity_id` = `e`.`entity_id`) AND (`at_price_type`.`attribute_id` = 109) AND
(`at_price_type`.`store_id` = 0)
LEFT JOIN `catalog_product_entity_decimal` AS `at_price_default`
ON (`at_price_default`.`entity_id` = `e`.`entity_id`) AND
(`at_price_default`.`attribute_id` = 64) AND `at_price_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_decimal` AS `at_price`
ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = 64) AND
(`at_price`.`store_id` = 5)
LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id`
ON (`at_tax_class_id`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id`.`attribute_id` = 85) AND
(`at_tax_class_id`.`store_id` = 0)
LEFT JOIN `cataloginventory_stock_item` AS `stock` ON stock.product_id = e.entity_id
LEFT JOIN `url_rewrite` AS `url` ON url.entity_id = e.entity_id AND url.target_path NOT LIKE '%category%' and
is_autogenerated = '1' AND url.entity_type = 'product' AND url.store_id = 5
LEFT JOIN `catalog_url_rewrite_product_category` AS `curpc` ON url.url_rewrite_id = curpc.url_rewrite_id
LEFT JOIN `catalog_product_super_link` AS `cpsl` ON cpsl.product_id = e.entity_id
LEFT JOIN `catalog_product_entity` AS `cpslcpe` ON cpsl.parent_id = cpslcpe.entity_id
INNER JOIN `catalog_category_product` AS `categories`
ON (categories.product_id = e.entity_id OR categories.product_id = cpslcpe.entity_id) AND
categories.category_id IN
(4, 5, 6, 8, 10, 11, 21, 29, 30, 34, 35, 43, 44, 48, 92, 93, 95, 96, 116, 156, 157, 159, 160,
161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 190, 191, 193, 194, 195, 196, 197, 198, 208,
209, 210, 213, 214, 216, 217, 237, 240, 254, 261, 262, 269, 272, 275, 284, 290, 293, 294, 295,
298, 299, 317, 318, 321, 326, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388,
389, 390, 391, 392, 393, 394, 511, 512, 513, 514, 524, 525, 534, 540, 541, 542, 543, 544, 551,
556, 557, 683, 710, 761, 770, 772, 774, 775, 776, 777, 778, 780, 781, 782, 784, 830, 839, 854,
889, 901, 981, 982, 1032, 1033, 1034, 1035, 1036, 1055, 1056, 1058, 1060, 1127, 1128, 1129,
1130, 1131, 1132, 1133, 1134, 1135, 1136, 1137, 1138, 1139, 1140, 1141, 1142, 1143, 1144, 1145,
1167, 1177)
INNER JOIN `catalog_category_entity` AS `cce`
ON categories.category_id = cce.entity_id AND cce.path LIKE '1/2/%'
LEFT JOIN `catalog_product_index_price` AS `price_index`
ON price_index.entity_id = e.entity_id AND customer_group_id = 0 AND price_index.website_id = 2
WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')
AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN ('4'))
AND ((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1'))
LIMIT 1You may need to create the mentioned attributes / categories etc and populate them with lots of sample data. If you have enough records in the price index table due to products and catalog rules and customer groups, this query should take more than 10 minutes to finish. Then during that time, try to reindex prices so that the rename operation on the price index table hits while the select is still running. Moreover, it has to be such a large dataset that the select query will be stuck in the sending data phase where it cannot release the lock for the RENAME operation because it's actively fetching the data. Then you will see the RENAME operation take long time to finish and finally the site will drop. Similar to my early image
|
|
I think you are missing multiple stock sources & stock assigned to the products. @engcom-Hotel Please also assign related/upsell products, then trigger the index with the catalog price rule. |
engcom-Hotel
added
Issue: Confirmed
|
✅ Jira issue https://jira.corp.adobe.com/browse/AC-10991 is successfully created for this GitHub issue. |
|
✅ Confirmed by @engcom-Hotel. Thank you for verifying the issue. |
I can confirm this appears to have fixed an issue we were having with frequent lock-ups, Magento CE 2.4.6-p2 on PHP 8.1. |
m2-community-project
bot
added
Progress: done
and removed
Progress: PR in progress
labels
Preconditions and environment
Steps to reproduce
I don't have the exact steps, other than the fact that reindexing the price rules takes too long. However in my case I see hundreds of queries like this
Which take too long to finish and drop our website
Expected result
The site is still working and queries are much faster and won't bring the site down.
Actual result
The website is down with a lot of queries in queue. Over 2k siilar to this
Additional information
No response
Release note
No response
Triage and priority
The text was updated successfully, but these errors were encountered: