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 tableinventory_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.