magento2: Randomly getting flooded with queries from related / upsell / crosssell blocks and price indexing

Second day the query didn’t appear in the slow log. I think it’s safe to say that the issue is in two areas

Performance of Upsell / Related / Cross-sells queries

1. Missing indexes for is_salable and sku column in the inventory_stock_# tables, causing the query to do full table scans
2. addInStockFilterToCollection not checking whether the is_salable condition is already added and the table inventory_stock_# is joined already
3. The plugin vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php causing the double join on the inventory_stock_# tables
4. The order of the joins
5. All of the above cause the database optimizer to choose different query plan alltogether leading to the wrong plan for execution forcing billions of rows to be examined.

Architectural flaw

The root cause of the downtime is the architectural flaw of renaming the index tables without calculating that long running queries will prevent the rename table action from taking place while it still tries to get an exclusive lock on the table.

@engcom-Dash since you were initially assigned to the task, could you re-examine it now after all information is here?

Steps to simulate the issue

1. Run a long running select query on the price index table (make it run for a long period).
2. Run a full reindex of prices indexer
3. Use systems that hold a single database node and do not do read-write splitting
4. Try to access the website while the rename tables action is in place and watch magento go down

For actual replication, you’d need a high traffic live environment with cross-sells / upsells and related products spamming queries all over the database and reindex to hit while those queries don’t run optimally on any mariadb version 10.4 -> 10.6

I’ve added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php	
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php	(date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventory_stock_# table

Running steady for 24 hours now and no occurrences of the slow query, this looks like it might be the culprit.

2 weeks later, 0 downtimes after applying the patch. 0 records of the query in the slow log.

No noticeable issues.

I think I was spot on with this one. Can the engcom team please acknowledge this issue on both accounts?

1. Arcitectural flaw in the RENAME_TABLE procedure of index tables, blocks all other queries
2. Duplicate joins of IsInStockFilter can make the MariaDB optimizer to cause infinite blocking on the RENAME TABLE queries coming from Crosssell / Upsell and Related products blocks depending on the plan to execute the joins

Please this would be vital, so that we can brainstorm and get suggestions for possible solutions to this if possible.

cc @engcom-Dash @engcom-Alfa @engcom-Bravo @engcom-Charlie @engcom-Delta

I’ve added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php	
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php	(date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventory_stock_# table

Running steady for 24 hours now and no occurrences of the slow query, this looks like it might be the culprit.

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.

e.g. this message

If it is RENAME TABLE indeed, then this won’t be fixed anytime soon. RENAME changes table metadata, it has to take an exclusive metadata lock for a very short time. But if you have a long-running SELECT, RENAME won’t be able to take a metadata lock, so it’ll wait. And all later statements will wait for RENAME. When your long-running SELECT finishes, everything will start working again.

It points to a flaw in the design of the indexing process in Magento 2 as it relies on the core of renaming index tables from the replicas to the normal name and the normal name to the normal name _old. This one seems to be spot on.

There is an opreation for a RENAME table almost 90% of the time I check the issue occuring. On top of it there are some price indexing queries which slow down and below the RENAME table there are other random queries either for price indexer or stock indexer or different other things.

I’m thinking that different indexers are trying to rename the tables while other processes are reading data from the index tables, thus causing this deadlock situation.

@engcom-Dash could you check with some core developer about this use case? It seems like a valid architectural flaw as described in MariaDB jira bug tracker because the RENAME table action can cause a deadlock when used in this context. If we could get some input from both ends we could fix this issue

It seems that those two functions are written in the following helper

\Magento\CatalogInventory\Helper\Stock

    /**
     * Adds filtering for collection to return only in stock products
     *
     * @param \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection $collection
     * @return void
     */
    public function addInStockFilterToCollection($collection)
    {
        $manageStock = $this->scopeConfig->getValue(
            \Magento\CatalogInventory\Model\Configuration::XML_PATH_MANAGE_STOCK,
            \Magento\Store\Model\ScopeInterface::SCOPE_STORE
        );
        $cond = [
            '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=1 AND {{table}}.is_in_stock=1',
            '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=0'
        ];

        if ($manageStock) {
            $cond[] = '{{table}}.use_config_manage_stock = 1 AND {{table}}.is_in_stock=1';
        } else {
            $cond[] = '{{table}}.use_config_manage_stock = 1';
        }

        $collection->joinField(
            'inventory_in_stock',
            'cataloginventory_stock_item',
            'is_in_stock',
            'product_id=entity_id',
            '(' . join(') OR (', $cond) . ')'
        );
    }

    /**
     * Add only is in stock products filter to product collection
     *
     * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
     * @return void
     */
    public function addIsInStockFilterToCollection($collection)
    {
        $stockFlag = 'has_stock_status_filter';
        if (!$collection->hasFlag($stockFlag)) {
            $isShowOutOfStock = $this->scopeConfig->getValue(
                \Magento\CatalogInventory\Model\Configuration::XML_PATH_SHOW_OUT_OF_STOCK,
                \Magento\Store\Model\ScopeInterface::SCOPE_STORE
            );
            $resource = $this->getStockStatusResource();
            $resource->addStockDataToCollection(
                $collection,
                !$isShowOutOfStock
            );
            $collection->setFlag($stockFlag, true);
        }
    }

The second is modified by a plugin

\Magento\InventoryCatalog\Plugin\CatalogInventory\Model\ResourceModel\Stock\Status\AdaptAddIsInStockFilterToCollectionPlugin::aroundAddIsInStockFilterToCollection

\Magento\InventoryCatalog\Model\ResourceModel\AddIsInStockFilterToCollection::execute

To give pretty much the same result, but doesn’t prevent double joining the table due to the missing flag.

If they’re doing the same thing, why aren’t they unified?

Broken down further it’s these two culprits for the double join

\Magento\CatalogInventory\Model\Plugin\ProductLinks::afterGetProductCollection leads to

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1)

\Magento\CatalogInventory\Helper\Stock::addIsInStockFilterToCollection leads to

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`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` 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 = '2'
         INNER JOIN `catalog_category_product_index_store5` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 5 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = 2
         INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id
         INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku
WHERE (inventory_in_stock.is_salable = 1)
  AND (stock_status_index.is_salable = 1)
ORDER BY `position` ASC

Because the first one doesn’t set the has_stock_status_filter flag. There’s no need for the double join

I’ve managed to get to the double join for inventory stock status

Here’s the execution path that leads to the double join first of all

STEP 1: \Magento\Catalog\Block\Product\ProductList\Upsell::_prepareData

null collection

STEP 2: \Magento\Catalog\Model\Product::getUpSellProductCollection

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1);

STEP 3: \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection::setPositionOrder

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 4: \Magento\Catalog\Model\ResourceModel\Product\Collection::addStoreFilter

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         INNER JOIN `catalog_product_website` AS `product_website`
                    ON product_website.product_id = e.entity_id AND product_website.website_id = 2
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 5: \Magento\Catalog\Block\Product\AbstractProduct::_addProductAttributesAndPrices

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`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         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_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 = '2'
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 6: \Magento\CatalogInventory\Helper\Stock::addIsInStockFilterToCollection

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`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` 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 = '2'
         INNER JOIN `catalog_category_product_index_store5` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 5 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = 2
         INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id
         INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku
WHERE (inventory_in_stock.is_salable = 1)
  AND (stock_status_index.is_salable = 1)
ORDER BY `position` ASC

On step 2 the flag is not set for the stock status filter so it’s joined twice.

I’m beginning to think it’s the optimizer switching the execution plan order of tables.

What I notice is on the slow log explain looks like this examining 199043934 rows

# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  inventory_in_stock      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       5822.00 100.00  100.00  Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  stock_status_index      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       17093.62        100.00  100.00  Using index
# explain: 1    SIMPLE  e       ref     PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      CATALOG_PRODUCT_ENTITY_SKU      195     db_edomainnlive.inventory_in_stock.sku  1       1.00    100.00  100.00  Using index condition
# explain: 1    SIMPLE  links   eq_ref  CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID   CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID   10      const,const,db_edomainnlive.e.entity_id 1       0.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60       PRIMARY 10      const,db_edomainnlive.e.entity_id,const 1       NULL    100.00   NULL    Using where
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE   PRIMARY 10      db_edomainnlive.e.entity_id,const,const 1NULL    100.00  NULL
# explain: 1    SIMPLE  product eq_ref  PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      PRIMARY 4       db_edomainnlive.e.entity_id     1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  product_entity_table    eq_ref  PRIMARY PRIMARY 4       db_edomainnlive.links.product_id        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  link_attribute_position_int     eq_ref  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID     7       const,db_edomainnlive.links.link_id      1       NULL    100.00  NULL
# explain: 1    SIMPLE  link_attribute_qty_decimal      eq_ref  CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID      CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID     7const,db_edomainnlive.links.link_id     1       NULL    100.00  NULL

When I execute it the order differs

+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|id|select_type|table                      |type  |possible_keys                                                                                                                        |key                                                           |key_len|ref                                                |rows|filtered|Extra                                       |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|1 |SIMPLE     |links                      |ref   |CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID|6      |const,const                                        |2   |100     |Using index; Using temporary; Using filesort|
|1 |SIMPLE     |cat_index                  |eq_ref|PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60                                                    |PRIMARY                                                       |10     |const,db_edomainnlive.links.linked_product_id,const|1   |100     |Using where                                 |
|1 |SIMPLE     |price_index                |eq_ref|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE                                |PRIMARY                                                       |10     |db_edomainnlive.links.linked_product_id,const,const|1   |100     |                                            |
|1 |SIMPLE     |e                          |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |inventory_in_stock         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.e.sku                              |1   |100     |Using where                                 |
|1 |SIMPLE     |product                    |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |stock_status_index         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.product.sku                        |1   |100     |Using where                                 |
|1 |SIMPLE     |product_entity_table       |eq_ref|PRIMARY                                                                                                                              |PRIMARY                                                       |4      |db_edomainnlive.links.product_id                   |1   |100     |Using index                                 |
|1 |SIMPLE     |link_attribute_position_int|eq_ref|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID                                               |CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
|1 |SIMPLE     |link_attribute_qty_decimal |eq_ref|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID                                           |CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+

I guess it’s possible to replicate this issue with the new information

1. Let’s create a working set of 200k products (the more the better)
2. with 30 customer group catalog rules and tier prices and group prices etc etc to make the price indexer run longer.
3. Create a long running query that uses catalog_product_index_price table to export a product feed for example
4. Try to reindex catalog_product_price
5. Watch the RENAME query get blocked trying to acquire an exclusive lock on the catalog_product_index_price table to rename it.

What do you think @engcom-Dash ?

Could you guys take a look at this thread as well?

https://jira.mariadb.org/browse/MDEV-32033

I’ve opened it as a bug, and any input in the internal process of reindexing and why this might be happening is welcome.

A user hinted that the rename action on the indexing tables may be creating the metadata lock on the whole table, while other queries are pending leading to this.

Hi @engcom-Dash,

I’m not sure why I’m pinged here. However, that makes me think of an issue of mine : https://github.com/magento/magento2/issues/35685

As you can see in mview.xml :

    <view id="catalog_product_price" class="Magento\Catalog\Model\Indexer\Product\Price" group="indexer">
        <subscriptions>
            <table name="catalog_product_entity" entity_column="entity_id" />
            <table name="catalog_product_entity_datetime" entity_column="entity_id" />
            <table name="catalog_product_entity_decimal" entity_column="entity_id" />
            <table name="catalog_product_entity_int" entity_column="entity_id" />
            <table name="catalog_product_entity_tier_price" entity_column="entity_id" />
            <table name="catalog_product_link" entity_column="product_id" />
        </subscriptions>
    </view>

Price index gets reindexed each time a product is updated. So if we update the color, the manufacturer or any int/decimal/date/static attribute, the price gets reindexed. On website with high amounts of product data writing, that could produce a huge quantity of useless reindex sql requests.

Hope it’ll help.