Magento 2.3.0 MSI – Improve Performance of isTableExists Calls

magento2.3msiperformance

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.

Best Answer

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.

Related Topic