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

[sky-hub]

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 (*)

1. Magento Commerce 2.3.1
2. MySQL 5.7
3. Aprox 70k products
4. Single Store
5. Add 2 Inventory Stocks
6. Add ~75 Inventory Sources

Steps to reproduce (*)

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

Expected result (*)

1. 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 (*)

1. 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')

[sky-hub]

An issue was opened in the Magento Repository

[Quazz]

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]

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 (?)

[Melleschepers]

I am having the same kind of issue. I'm having magento MSI enabled and therefore also have the table inventory_stock_2. So I assume you are also using MSI?

It turns out that all the mysql queries that are getting stuck are using this table. So I think it is related to a lock on the inventory_stock_2 table. In my situation we are updating the stock every hour, and that's when this issue sometime occur (once/twice a day.. so not always).

Will let you know when I find something.

[Quazz]

Was able to catch one as it was happening. I changed the categories assigned to a product and saved the product. On another tab I was browsing on our site when it got stuck on loading.

I dove into mysql and saw a bunch of queries stuck (oldest one on 'Sending data', rest on 'Waiting for metadata lock', probably waiting on the 'Sending data' one? Though it's a select, so why??)

Then I invoked 'SHOW ENGINE INNODB STATUS', which wasn't very illuminating to me personally. The query stuck on 'Sending data' was listed in the transactions.

Other than that, the only thing I found was '1 read views open inside InnoDB'

[Quazz]

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.

[michalbednarzcsm]

Hi. I have exactly the same problem, after turn on Magento_Inventory (MSI), before everything was ok. It's really critical issue in my opinion - storefront is unreachable when this problem occurs. I am trying to debug this problem right now. Maybe someone have other solution than remove related products block?

[fredden]

Is this related to https://github.com/magento/magento2/issues/25199, https://github.com/magento/magento2/pull/27129, https://jira.mariadb.org/browse/MDEV-20900?

Check the your database engine is running a supported version and that it's up-to-date. https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html

[michalbednarzcsm]

@fredden Thanks for answer. Database is running on Percona especially this version: https://www.percona.com/doc/percona-server/5.7/release-notes/Percona-Server-5.7.32-35.html

I was tried to debug this problem, but only solution for now is to cut off related products (crossell upsell) functionality. What i know for now is SQL query form related products makes database tables lock and whole Magento aplication dies.

I must find another solution for this. My issue is same as @Quazz issue.

[TechOS-PL]

@sky-hub & @Quazz You tried add index in inventory_stock_2 (inventory_stock_*) table on the is_salable column ?

[TechOS-PL]

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.

[WinstonN]

Reduce your batch sizes, down to 500 as this article state https://dave-baker.com/2019/07/28/fixing-slow-reindexes-in-magento-2-mariadb/ It does make things a lot better

[Adel-Magebinary]

@TechOS-PL did you resolve the issue?

[cdfrm]

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

[Quazz]

Have to agree with TechOS-PL. It's definitely related to indexation of that table. Every query that uses this table is vulnerable to this problem. Generally speaking small enough queries can get away with it due to their speed, but once you try to pull in multiple items (such as in related products) the odds of this problem occuring go up significantly.

https://github.com/magento/inventory/blob/develop/InventoryMultiDimensionalIndexerApi/Model/IndexTableSwitcher.php

This is how magento does its table switching usually.

However, other Magento indexers only does this for full reindexation process. Inventory switches tables around during every single update whether it's 1 item, 10, or full reindex. In practical terms this means it can occur thousands of times per day as opposed to maybe once or twice. Keep in mind that each rename has to lock the table.

Obviously larger stocks will be far more likely to experience this problem since the queries start to become somewhat slower on larger sets.

So, imo, the solution is for Inventory to update the indexation to process updates for non-full directly onto the table the way that Magento does for other indexers.

[Quazz]

Disabling the Magento\CatalogInventory\Model\AddStockStatusToCollection plugin significantly reduces this issue (or even removes it entirely). Added benefit is that pages that have any kind of product collection load much faster.

Even though that's a "CatalogInventory" thing, it's related to MSI in that MSI has a plugin on that plugin which changes it to inventory style data. However disabling the MSI plugin alone will cause issues since then you get incorrect old style inventory data instead.

I do have quite a few modules installed; so test if carefully first, but personally I don't really see any practical use for it.

[Quazz]

An alternative way to remove the double join is this:

https://github.com/magento/magento2/issues/36667#issuecomment-1790246996

Also there's an entire MariaDB discussion about this (unresolved): https://jira.mariadb.org/browse/MDEV-32033?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&showAll=true

Unfortunately even though these actions improve the situation a lot; I found that they don't necessarily resolve it entirely.