3

I'm experiencing slow performance coming from MSI. New Relic reports something like: 48 runs of SHOW TABLE STATUS LIKE ? that takes in total 5 seconds. There are many calls to this because it's about a configurable product in cart that has many associated simple products. Or it's just big cart with many products.

The issue starts like this:

\Magento\CatalogInventory\Observer\QuantityValidatorObserver::execute
\Magento\CatalogInventory\Model\Quote\Item\QuantityValidator\Initializer\StockItem::initialize()
\Magento\CatalogInventory\Model\StockRegistry\Interceptor::getStockStatus()
\Magento\InventoryCatalog\Plugin\CatalogInventory\Api\StockRegistry\AdaptGetStockStatusPlugin::afterGetStockStatus()
\Magento\InventorySales\Model\GetProductSalableQty::execute()
\Magento\InventoryIndexer\Model\ResourceModel\GetStockItemData::execute()
\Magento\Framework\DB\Adapter\Pdo\Mysql::isTableExists()
\Magento\Framework\DB\Adapter\Pdo\Mysql::showTableStatus()

The URL for above trace is from customer/section/load/. But I see same issue in other URLs.

  • How can I optimize this with code in Magento 2 (not server/software optimization) ?
  • Does SHOW TABLE STATUS LIKE ? MySQL locks on table inventory_stock_123 ?
  • SHOW TABLE STATUS LIKE ? is executed on table(s) inventory_stock_123, correct ? Yes.
  • Is there a Magento 2 / MSI patch for this ?

EDIT:

  • The store uses 10.0.38-MariaDB
  • I believe the big times happen when there are race conditions on the server. This should happen when there locks in progress on table inventory_stock_123.
  • I replaced all calls (five) in inventory modules from if ($connection->isTableExists($tableName) to:
try {
    $connection->query('SELECT 1 FROM ' . $tableName . ' LIMIT 1');
    $exists = true;
} catch (\Exception $e) {
    $exists = false;
}
if ($exists) {
  ..

I can't say that this is the best way to do it but I got very good results. There are other SQL queries to check if a table exists. 3 of the call isTableExists were impacting frontend experience. And 2 of them were making admin product save very slow.

Update:

It's a server issue / webstack configuration. It has something to do with the storage for MySql data.

  • Do you want to use Multi Source Inventory (MSI)? – Dominic Xigen May 29 at 14:25
  • @DominicXigen The store uses MSI and running.SHOW TABLE STATUS LIKE ? adds a bunch of seconds on page load first byte. – obscure May 29 at 16:19
  • What I'm getting at is do you actually want to use the feature though? As in does the store require a multi-source inventory or does it only have a single source inventory? – Dominic Xigen May 29 at 16:38
  • @DominicXigen I am already using the feature. It's on a multistore with 2 stock sources (2 warehouses) and 2 stocks. One stock source for a stock (one to one relation). I got the PHP trace from New Relic calling MSI code (magento/module-inventory*). 'Default' stock is not assigned to products according to Admin > Stores > Stocks > Sales Channel is empty for Default Stock. – obscure May 29 at 16:48
  • @DominicXigen MSI is not Multi-Source feature for Magento, it's a new inventory mechanism. Just consider that in one of the upcoming releases CatalogInventory would be fully eliminated from the codebase. – Igor Minyaylo May 29 at 20:58
3
+100

I haven't seen any similar reports so far, so created a new ticket for MSI: https://github.com/magento-engcom/msi/issues/2282 to track investigation and possibly fix.

Do you use Magento 2.3.0 with MSI 1.0.*? Some performance issues could have been fixed in 1.1.1 and even more fixed in upcoming 1.1.2.

  • I have the latest code of MSI from 1.1.1. I use Magento 2.3.0. The store has the same version of module-inventory* with the inventory modules shipped with Magento 2.3.1. I guess MSI it's build like this. If you run composer update (without changing [magento installation]/composer.json) or maybe just composer install than it will bring latest compatible MSI code. – obscure May 30 at 8:21
  • @obscure Thanks for clarification.This would definitely help. Can you also share more information on the specific table which causes the issue? Existing table inventory_stock_1 is not an actual table but a VIEW, while all other numbers are actual tables. Would be nice to figure out if this is something view-specific. – Eugene Jun 3 at 19:29
  • It's about inventory_stock_3. This is the table for the stock that has associated to it only one source. Beside this I saw that SHOW TABLE STATUS LIKE 'setup_module' can take up to 300ms (executed once in \Magento\Framework\Module\ModuleResource::_loadVersion). We're investigating if this is a MariaDb 10.0.38 bug, miss configuration or something else server related. Or just if there is something coming from code in Magento that could overload the database server. – obscure Jun 4 at 7:03
  • It's a server issue. I updated my answer. Thank you. – obscure Jun 4 at 8:01
1

Decided to put as potential answer rather that comment which can be easily missed. I realise it may not solve your issue but it could be helpful for someone.

It is possible to streamline magento. The guys that did it have seen up to 40% increase in performance. Anyway it may be a potential option for anyone not making full use of MSI in the way in which the magento team intended.

https://www.integer-net.com/make-magento-2-small-again/

Add this to composer.json

    "replace": {
        "magento/module-dhl": "*",
        "magento/module-fedex": "*",
        "magento/module-marketplace": "*",
        "magento/module-multishipping": "*",
        "magento/module-captcha": "*",
        "magento/module-persistent": "*",
        "magento/module-catalog-rule-configurable": "*",
        "magento/module-authorizenet": "*",
        "magento/module-google-adwords": "*",
        "magento/module-sample-data": "*",
        "magento/module-send-friend": "*",
        "magento/module-swagger": "*",
        "magento/module-swagger-webapi": "*",
        "magento/module-swagger-webapi-async": "*",
        "magento/module-swatches": "*",
        "magento/module-swatches-layered-navigation": "*",
        "magento/module-google-optimizer": "*",
        "magento/module-ups": "*",
        "magento/module-usps": "*",
        "magento/module-braintree": "*",
        "magento/module-signifyd": "*",
        "magento/module-release-notification": "*",
        "magento/module-new-relic-reporting": "*",
        "magento/module-version": "*",

        "magento/module-analytics": "*",
        "magento/module-catalog-analytics": "*",
        "magento/module-customer-analytics": "*",
        "magento/module-quote-analytics": "*",
        "magento/module-review-analytics": "*",
        "magento/module-sales-analytics": "*",
        "magento/module-wishlist-analytics": "*",

        "magento/module-bundle-graph-ql": "*",
        "magento/module-catalog-graph-ql": "*",
        "magento/module-catalog-inventory-graph-ql": "*",
        "magento/module-catalog-url-rewrite-graph-ql": "*",
        "magento/module-cms-graph-ql": "*",
        "magento/module-cms-url-rewrite-graph-ql": "*",
        "magento/module-inventory-graph-ql": "*",
        "magento/module-configurable-product-graph-ql": "*",
        "magento/module-customer-graph-ql": "*",
        "magento/module-downloadable-graph-ql": "*",
        "magento/module-eav-graph-ql": "*",
        "magento/module-graph-ql": "*",
        "magento/module-grouped-product-graph-ql": "*",
        "magento/module-quote-graph-ql": "*",
        "magento/module-store-graph-ql": "*",
        "magento/module-swatches-graph-ql": "*",
        "magento/module-tax-graph-ql": "*",
        "magento/module-url-rewrite-graph-ql": "*",
        "magento/module-weee-graph-ql": "*",

        "magento/module-advanced-pricing-import-export": "*",
        "magento/module-bundle-import-export": "*",
        "magento/module-configurable-import-export": "*",
        "magento/module-customer-import-export": "*",
        "magento/module-downloadable-import-export": "*",
        "magento/module-grouped-import-export": "*",
        "magento/module-tax-import-export": "*",

        "magento/module-inventory": "*",
        "magento/module-inventory-admin-ui": "*",
        "magento/module-inventory-api": "*",
        "magento/module-inventory-bundle-product": "*",
        "magento/module-inventory-bundle-product-admin-ui": "*",
        "magento/module-inventory-cache": "*",
        "magento/module-inventory-catalog": "*",
        "magento/module-inventory-catalog-admin-ui": "*",
        "magento/module-inventory-catalog-api": "*",
        "magento/module-inventory-catalog-search": "*",
        "magento/module-inventory-configurable-product": "*",
        "magento/module-inventory-configurable-product-admin-ui": "*",
        "magento/module-inventory-configurable-product-indexer": "*",
        "magento/module-inventory-configuration": "*",
        "magento/module-inventory-configuration-api": "*",

        "magento/module-inventory-elasticsearch": "*",
        "magento/module-inventory-grouped-product": "*",
        "magento/module-inventory-grouped-product-admin-ui": "*",
        "magento/module-inventory-grouped-product-indexer": "*",
        "magento/module-inventory-import-export": "*",
        "magento/module-inventory-indexer": "*",
        "magento/module-inventory-low-quantity-notification": "*",
        "magento/module-inventory-low-quantity-notification-admin-ui": "*",
        "magento/module-inventory-low-quantity-notification-api": "*",
        "magento/module-inventory-multi-dimensional-indexer-api": "*",
        "magento/module-inventory-product-alert": "*",
        "magento/module-inventory-reservations": "*",
        "magento/module-inventory-reservations-api": "*",
        "magento/module-inventory-sales": "*",
        "magento/module-inventory-sales-admin-ui": "*",
        "magento/module-inventory-sales-api": "*",
        "magento/module-inventory-sales-frontend-ui": "*",
        "magento/module-inventory-shipping": "*",
        "magento/module-inventory-shipping-admin-ui": "*",
        "magento/module-inventory-source-deduction-api": "*",
        "magento/module-inventory-source-selection": "*",
        "magento/module-inventory-source-selection-api": "*",
        "magento/module-inventory-distance-based-source-selection": "*",
        "temando/module-shipping-m2": "*",
        "dotmailer/dotmailer-magento2-extension": "*",
        "klarna/module-kp": "*",
        "klarna/module-ordermanagement": "*",
        "klarna/module-core": "*",
        "amzn/amazon-pay-sdk-php": "*",
        "amzn/amazon-pay-and-login-with-amazon-core-module": "*",
        "amzn/login-with-amazon-module": "*",
        "amzn/amazon-pay-module": "*",
        "vertex/module-tax": "*",
        "vertex/sdk": "*"
    },
  • While this solution may be functional, in case with Inventory* modules, it should be noted that at some point in the future it will replace the existing CatalogInventory module, possibly creating an additional challenge in upgrades. – Eugene Jun 3 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.