inventory: MySQL Query stuck on "Sending Data" while retrieving related products

Although I can not tell for sure how to reproduce the issue, as it manifests randomly, I suspect the problem is that MySQL is taking too much time in scanning the tables for the result.

Even tho the issue was discovered on Magento Commerce, except the join related to Catalog Staging the same query is executed on Magento Open Source.

Preconditions (*)

Magento Commerce 2.3.1
MySQL 5.7
Aprox 70k products
Single Store
Add 2 Inventory Stocks
Add ~75 Inventory Sources

Steps to reproduce (*)

Add Simple Product
Assign all inventory sources
Add 5 related products
Enable catalog product flat
Stores > Configuration > Catalog > Inventory > Stock Options > Display Out of Stock Products > NO
Visit product page on frontend and profile the page

Expected result (*)

The resulting query should look similar to this:

SELECT COUNT(DISTINCT e.entity_id)
FROM catalog_product_flat_1 AS e
INNER JOIN inventory_stock_2 AS inventory_in_stock
    ON e.sku = inventory_in_stock.sku
INNER JOIN catalog_product_index_price AS price_index
    ON price_index.entity_id = e.entity_id
    AND price_index.website_id = '1'
    AND price_index.customer_group_id = 0
INNER JOIN catalog_category_product_index_store1 AS cat_index
    ON cat_index.product_id=e.entity_id
    AND cat_index.store_id=1
    AND cat_index.visibility IN(2, 4)
    AND cat_index.category_id=1276
INNER JOIN catalog_product_entity AS product_entity
    ON product_entity.entity_id = e.entity_id
    AND (product_entity.created_in <= '1579039140' AND product_entity.updated_in > '1579039140')
INNER JOIN catalog_product_link AS links
    ON links.linked_product_id = e.entity_id
    AND links.link_type_id = 1
WHERE (inventory_in_stock.is_salable = 1)
AND (e.entity_id NOT IN('124079'))
AND (links.product_id = 124079)
AND (e.row_id != '124079')

Actual result (*)

The resulting query performs too many joins with the same tables, in this case 2 times for inventory_stock_2 and 3 times catalog_product_entity.

SELECT COUNT(DISTINCT e.entity_id)
FROM catalog_product_flat_1 AS e

# \Magento\InventoryCatalog\Model\ResourceModel\AddIsInStockFieldToCollection::execute
INNER JOIN inventory_stock_2 AS inventory_in_stock
    ON e.sku = inventory_in_stock.sku

INNER JOIN catalog_product_index_price AS price_index
    ON price_index.entity_id = e.entity_id
    AND price_index.website_id = '1'
    AND price_index.customer_group_id = 0
INNER JOIN catalog_category_product_index_store1 AS cat_index
    ON cat_index.product_id=e.entity_id
    AND cat_index.store_id=1
    AND cat_index.visibility IN(2, 4)
    AND cat_index.category_id=1276

# \Magento\CatalogStaging\Model\Plugin\ResourceModel\Product\JoinProductsWhenFlatEnabled::beforeLoad
INNER JOIN catalog_product_entity AS product_entity
    ON product_entity.entity_id = e.entity_id
    AND (product_entity.created_in <= '1579039140' AND product_entity.updated_in > '1579039140')

# \Magento\InventoryCatalog\Model\ResourceModel\AddStockDataToCollection::execute
INNER JOIN catalog_product_entity AS product
    ON product.entity_id = e.entity_id
    AND (product.created_in <= '1579039140' AND product.updated_in > '1579039140')

# \Magento\InventoryCatalog\Model\ResourceModel\AddStockDataToCollection::execute
INNER JOIN inventory_stock_2 AS stock_status_index
    ON product.sku = stock_status_index.sku

# \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection::_joinLinks
INNER JOIN catalog_product_link AS links
    ON links.linked_product_id = e.entity_id
    AND links.link_type_id = 1

# \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection::joinProductsToLinks
INNER JOIN catalog_product_entity AS product_entity_table
    ON links.product_id = product_entity_table.row_id
    AND (product_entity_table.created_in <= '1579039140' AND product_entity_table.updated_in > '1579039140')

WHERE (inventory_in_stock.is_salable = 1)
AND (e.entity_id NOT IN('124079'))
AND (stock_status_index.is_salable = 1)
AND (links.product_id = 124079)
AND (e.row_id != '124079')

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 2
Comments: 17 (2 by maintainers)

Most upvoted comments

@TechOS-PL did you resolve the issue?

Adel-Magebinary on Feb 14, 2022

I know the cause of this problem. The problem occurs when the afterExecuteList plugins of configurable products, group products and bundle products are launched after reindex. Delete & insert to the inventory_stock_ * will be send, which freeze the table and next select to this table appears, such a query has the status “Sending data” and nothing it works.

TechOS-PL on Jul 16, 2021

Issue goes away entirely when removing the related products blocks with a layout file.

We don’t even use related products but there you go.

Quazz on Feb 4, 2021

The query comes from: https://github.com/magento/magento2/blob/6dbb7fc48bd05eb2d00c9951b014aea9da7f73d7/app/code/Magento/Catalog/view/frontend/templates/product/list/items.phtml#L39

Method: getSize()

A temporary fix is replacing:

if ($exist = $block->getItems()->getSize())

by if ($exist = count($block->getItems()))

cdfrm on Dec 1, 2022

I turned on explain in slowquerylog, this shows us why this query is so slow, but not why it happens of course:

explain.txt

Attached the explain plan in txt file. As you can see the query that gets stuck uses no KEY for tables inventory_in_stock and stock_status_index, which likely explains why it’s so slow.

But it doesn’t always happen of course, when I run explain on the query now, I get a different plan (they use key PRIMARY in those scenarios), likely because the index tables are different (?)

Quazz on Nov 20, 2020

I have something similar, but on Opensource version (and Magento 2.4 (same thing happened on 2.3.5), Mariadb 10.4 (same thing happened on 10.3), not using flat tables)

In total the server on average uses about 20 out of 64GB RAM so plenty of breathing space.

Example query:

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, cat_index.position AS cat_index_position, stock_status_index.is_salable, links.link_id, links.product_id AS _linked_to_product_id, link_attribute_position_int.value AS position FROM catalog_product_entity AS e
 INNER JOIN inventory_stock_2 AS inventory_in_stock ON e.sku = inventory_in_stock.sku
 INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '5'
 INNER JOIN catalog_category_product_index_store21 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=21 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=16609
 INNER JOIN catalog_product_entity AS product ON product.entity_id = e.entity_id
 INNER JOIN inventory_stock_2 AS stock_status_index ON product.sku = stock_status_index.sku
 INNER JOIN catalog_product_link AS links ON links.linked_product_id = e.entity_id AND links.link_type_id = 1
 LEFT JOIN catalog_product_link_attribute_int AS link_attribute_position_int ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '1'
 INNER JOIN catalog_product_entity AS product_entity_table ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in (4451690)) AND (e.entity_id != '4451690') ORDER BY position;

Explain tells us that the “ORDER BY” forces it into temporary table and such, though I’m not sure if that’s what causes this.

The odd thing is that the query itself doesn’t necessarily always get stuck. When I manually execute it right now, it finishes in 0.002sec (empty result set)

In fact, even though these queries get stuck, I don’t think they themselves are necessarily the cause. (killing one of such queries does not make the others suddenly go through). After an indeterminate amount of time (varies, I’ve seen times as low as 7 minutes to as high as over an hour) they will all finish at basically the same time.

I’ve been trying to hunt down the exact cause or possible solutions. The only improvement I’ve found was to avoid locking tables in my daily database backup. It would almost without fail happen during/after a database backup. But it’s not the only instance where it happens.

Another instance I’ve noticed where this can happen is when making a lot of changes to category assignment for a product. But once again, it doesn’t always happen, it just tends to be more likely.

Other instances seem to happen without anything else going on.

edit: Possibly that’s all just a red herring, seems like it might just occur in specific instances of the Mview Inventory indexation

Quazz on Nov 23, 2020