Magento 2 MSI (multi-source inventory) performance issue fix

Multi-Source Inventory the new Magento store feature with a bad performance

Inventory Management modules provide all inventory features and options for Single and Multi Source merchants to manage product quantities and stock for sales channels. These features are available in 2.3.x Magento Open Source, Magento Commerce, and Magento Commerce Cloud.

Multi Source merchants have one website or a multisite implementation and manage on-hand inventory and fulfilling orders through multiple locations. This is called multi-sourcing.

Good advertisement but bad performance a lot of merchants struggling with these features even if they don’t have multi-source inventory. Basically Magento team forgot to add Indexers to the table. They are using View it doesn’t have indexes.

Basically, in general, it is a bad idea to manage inventory in Magento. If you really need it better to write microservice to manage stocks without using Magento’s slow PHP Monolithic backend.

Performance With and Without MS:

Magento 2 performance with MSI and without MSI

The quick fix is to disable all the MSI modules:

php bin/magento module:status | grep Magento_Inventory | grep -v List | grep -v None | grep -v -e '^$' | xargs php bin/magento module:disable
php bin/magento s:upgrade --keep-generated
php bin/magento i:reindex

Fix The issue

All MSI slow queries are similar. They are joining the `inventory_stock_1` table. New Magento 2 obfuscation mechanism — MySQL view without indexers.

I thin Magento provides patches for Enterprise/paid customers, however, an Open Source version especially has performance and security holes.

Using views in MySql is not a good idea. Because you can’t create an index for views in MySQL like in Oracle. It is also very dangerous if you assume MySQL would optimize your VIEWs queries the same way as more advanced database systems would. It is difficult to find this issue on a small webshop with 100 products however shops with 100K items suffering from this Magento performance issue. This merchant has 80K SKUs….

Even This Small Query (number 4 in our list) without JOINS:

SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = ‘007’)

takes 0.6490 seconds. Because it doesn’t have an index on the SKU column…

I found 15 queries that have a very low performance. I have checked it
And I have several conclusions:

• All slow queries contain join to inventory_stock_1 SQL view and it can’t be indexed because it’s just view (query without this SQL view works like 10 times faster)
• Some of the queries have unnecessary joins to catalog_product_enity table just for the purpose to provide SKU for join inventory_stock_1. (We don’t need it, once we have product_id field in inventory_stock_1. We have to get rid of this join in spite of this will not solve any performance issue)
• Join by SKU field between inventory_stock_1 view and another table (join by varchar field is anyway slower then join by int, so using product_id from the previous paragraph will solve this issue)

The only View (inventory_stock_1) in Magento2 and so many issues.

Check The View declaration.

SHOW CREATE VIEW inventory_stock_1;
CREATE ALGORITHM=UNDEFINED DEFINER=`MgentoMSISucks`@`%` SQL SECURITY INVOKER VIEW `inventory_stock_1` AS select distinct `legacy_stock_status`.`product_id` AS `product_id`,`legacy_stock_status`.`website_id` AS `website_id`,`legacy_stock_status`.`stock_id` AS `stock_id`,`legacy_stock_status`.`qty` AS `quantity`,`legacy_stock_status`.`stock_status` AS `is_salable`,`product`.`sku` AS `sku` from (`cataloginventory_stock_status` `legacy_stock_status` join `catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)))

The query has distinct.

While you should generally select fewer columns, you’re better off selecting additional columns instead of using SELECT DISTINCT to ensure each row is distinct. SELECT DISTINCT can use a lot of processing and slow down the query. You can usually ensure each row is distinct by selecting an additional column and check it in PHP or just ignore duplicated rows.

Distinct always sets off alarm bells to me — it usually signifies a bad table design or a developer who’s unsure of themselves. It is used to remove duplicate rows, but if the joins are correct, it should rarely be needed. And yes there is a large cost to using it.

Looking at ratio or Rows Examined Per Row Sent is great way to spot inefficient queries which are candidates for optimization or elimination.

If we check out EXPLAIN, we can see it is using FULL table scan because index is missing.

Explain the query:

MySQL [MgentoMSISucks]> EXPLAIN SELECT   inventory_stock_1.quantity,   inventory_stock_1.is_salable FROM   inventory_stock_1 WHERE   (sku = 45545)\G
...
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: legacy_stock_status
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 46103
     filtered: 100.00
        Extra: Using temporary

Describe the Magento table:

Indexes not found. However, this is a view it uses parent table indexes.

Fix by removing MySQL Select Distinct:

The best way to fix remove distinct by recreating the view

drop view inventory_stock_1
CREATE VIEW inventory_stock_1  AS select `legacy_stock_status`.`product_id` AS `product_id`,`legacy_stock_status`.`website_id` AS `website_id`,`legacy_stock_status`.`stock_id` AS `stock_id`,`legacy_stock_status`.`qty` AS `quantity`,`legacy_stock_status`.`stock_status` AS `is_salable`,`product`.`sku` AS `sku` from (`cataloginventory_stock_status` `legacy_stock_status` join `catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)))

Now our query has a much faster filter 1 row examinated vs 46 000 rows

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: legacy_stock_status
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: MgentoMSISucks.product.entity_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 3 warnings (0.00 sec)

Now problem queries take 10ms max vs 600ms+ Magento core performance.

The same you should repeat for another stock Views — inventory_stock_1,2,3...N