0

I'm working on a Magento 2.3.0 store with 790k SKUs and 7k Categories. Category pages and Single product page open load time is more than 10s. I enabled db query log and I could see below query that takes more than 3 seconds.

## 2019-01-19 19:32:07
## 9478 ## QUERY
SQL: SELECT `inventory_stock_1`.`quantity`, `inventory_stock_1`.`is_salable` FROM `inventory_stock_1` WHERE (sku = 'P00AA40')
AFF: 1

TIME: 3.0955  
TRACE: #1 Magento\Framework\DB\Logger\File[Magento\Framework\DB\Logger\LoggerAbstract]#000000006e030af800000000689c3fe1#->getStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Logger/File.php:67]

#2 Magento\Framework\DB\Logger\File#000000006e030af800000000689c3fe1#->logStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Logger/LoggerProxy.php:152]

#3 Magento\Framework\DB\Logger\LoggerProxy#000000006e030aef00000000689c3fe1#->logStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:542]

#4 Magento\Framework\DB\Adapter\Pdo\Mysql#000000006e030ae800000000689c3fe1#->_query('SELECT `inventor...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:615]

#5 Magento\Framework\DB\Adapter\Pdo\Mysql#000000006e030ae800000000689c3fe1#->query(&Magento\Framework\DB\Select#000000006e03020b00000000689c3fe1#, array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:756]

#6 Magento\Framework\DB\Adapter\Pdo\Mysql[Zend_Db_Adapter_Abstract]#000000006e030ae800000000689c3fe1#->fetchRow(&Magento\Framework\DB\Select#000000006e03020b00000000689c3fe1#) called at [vendor/magento/module-inventory-indexer/Model/ResourceModel/GetStockItemData.php:63]

#7 Magento\InventoryIndexer\Model\ResourceModel\GetStockItemData#000000006e030f3a00000000689c3fe1#->execute('P00AA40', 1) called at [vendor/magento/module-inventory-sales/Model/GetProductSalableQty.php:75]

#8 Magento\InventorySales\Model\GetProductSalableQty#000000006e03021800000000689c3fe1#->execute('P00AA40', 1) called at [vendor/magento/module-inventory-sales-frontend-ui/Plugin/Block/Stockqty/AbstractStockqtyPlugin.php:80]

#9 Magento\InventorySalesFrontendUi\Plugin\Block\Stockqty\AbstractStockqtyPlugin#000000006e03020a00000000689c3fe1#->aroundIsMsgVisible(&Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#, &Closure#000000006e03021e00000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:135]

#10 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]

#11 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->___callPlugins('isMsgVisible', array(), NULL) called at [generated/code/Magento/CatalogInventory/Block/Stockqty/DefaultStockqty/Interceptor.php:26]

#12 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->isMsgVisible() called at [vendor/magento/module-catalog-inventory/Block/Stockqty/DefaultStockqty.php:28]

#13 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor[Magento\CatalogInventory\Block\Stockqty\DefaultStockqty]#000000006e030ce500000000689c3fe1#->_toHtml() called at [vendor/magento/framework/View/Element/AbstractBlock.php:668]

#14 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor[Magento\Framework\View\Element\AbstractBlock]#000000006e030ce500000000689c3fe1#->toHtml() called at [vendor/magento/framework/View/Layout.php:557]

#15 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderBlock('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:533]

#16 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:488]

#17 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:585]

#18 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:535]

#19 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:488]

#20 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:585]

#21 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.typ...', false) called at [vendor/magento/framework/View/Layout.php:535]

#22 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.typ...') called at [vendor/magento/framework/View/Layout.php:488]

#23 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.typ...', false) called at [vendor/magento/framework/View/Layout.php:585]

#24 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.sto...', false) called at [vendor/magento/framework/View/Layout.php:535]

#25 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sto...') called at [vendor/magento/framework/View/Layout.php:488]

#26 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sto...', false) called at [vendor/magento/framework/View/Layout.php:585]

#27 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.pri...', false) called at [vendor/magento/framework/View/Layout.php:535]

#28 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.pri...') called at [vendor/magento/framework/View/Layout.php:488]

#29 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.pri...', false) called at [vendor/magento/framework/View/Layout.php:585]

#30 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.mai...', false) called at [vendor/magento/framework/View/Layout.php:535]

#31 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.mai...') called at [vendor/magento/framework/View/Layout.php:488]

#32 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.mai...', false) called at [vendor/magento/framework/View/Layout.php:585]

#33 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('content', false) called at [vendor/magento/framework/View/Layout.php:535]

#34 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('content') called at [vendor/magento/framework/View/Layout.php:488]

#35 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('content', false) called at [vendor/magento/framework/View/Layout.php:585]
#36 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('main', false) called at [vendor/magento/framework/View/Layout.php:535]
#37 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('main') called at [vendor/magento/framework/View/Layout.php:488]
#38 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('main', false) called at [vendor/magento/framework/View/Layout.php:585]
#39 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('columns', false) called at [vendor/magento/framework/View/Layout.php:535]
#40 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('columns') called at [vendor/magento/framework/View/Layout.php:488]
#41 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('columns', false) called at [vendor/magento/framework/View/Layout.php:585]
#42 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('main.content', false) called at [vendor/magento/framework/View/Layout.php:535]
#43 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('main.content') called at [vendor/magento/framework/View/Layout.php:488]
#44 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('main.content', false) called at [vendor/magento/framework/View/Layout.php:585]
#45 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('page.wrapper', false) called at [vendor/magento/framework/View/Layout.php:535]
#46 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('page.wrapper') called at [vendor/magento/framework/View/Layout.php:488]
#47 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('page.wrapper', false) called at [vendor/magento/framework/View/Layout.php:585]
#48 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('root', false) called at [vendor/magento/framework/View/Layout.php:535]
#49 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('root') called at [vendor/magento/framework/View/Layout.php:488]
#50 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('root') called at [vendor/magento/framework/View/Layout.php:954]
#51 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->getOutput() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#52 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->___callParent('getOutput', array()) called at [vendor/magento/framework/Interception/Interceptor.php:138]
#53 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#54 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->___callPlugins('getOutput', array(), array(array('layout-model-cac...'))) called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:39]
#55 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->getOutput() called at [vendor/magento/framework/View/Result/Page.php:258]
#56 Magento\Framework\View\Result\Page\Interceptor[Magento\Framework\View\Result\Page]#000000006e03098000000000689c3fe1#->render(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/View/Result/Layout.php:171]
#57 Magento\Framework\View\Result\Page\Interceptor[Magento\Framework\View\Result\Layout]#000000006e03098000000000689c3fe1#->renderResult(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:58]
#58 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->___callParent('renderResult', array(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#)) called at [vendor/magento/framework/Interception/Interceptor.php:138]
#59 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->Magento\Framework\Interception\{closure}(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:153]
#60 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->___callPlugins('renderResult', array(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#), array(array('result-messages', 'result-builtin-c...', 'result-varnish-c...'))) called at [generated/code/Magento/Framework/View/Result/Page/Interceptor.php:26]
#61 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->renderResult(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/App/Http.php:139]
#62 Magento\Framework\App\Http\Interceptor[Magento\Framework\App\Http]#000000006e030aa200000000689c3fe1#->launch() called at [vendor/magento/framework/App/Bootstrap.php:258]
#63 Magento\Framework\App\Bootstrap#000000006e030a0d00000000689c3fe1#->run(&Magento\Framework\App\Http\Interceptor#000000006e030aa200000000689c3fe1#) called at [index.php:39]

What could be the reason for this? I'm using Luma theme and disabled all 3rd part modules.

1
  • Did you find a solution to this in the end?. I'm having some issues relating to that db call SELECT `inventory_stock_1 there is a recent dev update I just found coming in 2.3.1 github.com/magento-engcom/msi/pull/1950 but I've not tried this
    – SWD
    Mar 17, 2019 at 15:34

1 Answer 1

2

Try these some possible basic steps for Optimization:

  1. High Performance Dedicated Server. Ex: Amazon EC2 cloud
  2. Swap Apache for NGINX
  3. Minimize Javascript use
  4. Minify and Compressed CSS files
  5. ‘Combine CSS’ seeks to reduce the number of HTTP requests made by a browser
  6. Optimize images
  7. Use lazyload for images
  8. Specify Image dimensions
  9. Combine images into CSS sprites
  10. Use a Content Delivery Network (CDN) for delivering static files like JS, CSS and Images to offload your server
  11. Disable modules/extension which are not required
  12. Enable all Magento Caches
  13. Use a Full Page Cache / Varnish Cache / Memcache / RedisCache
  14. Don’t use layered navigation if you don’t really need it, it needs a lot of resources
  15. Enable Compilation
  16. Limit the number of products on a product overview page.
  17. Set only those attribute frontend properties to ‘Yes’ that you’re actually going to use. Set all other to ‘No’.
  18. Don’t use in quick search, advanced search compare, etc. : Catalog -> Attributes -> Manage Atributes -> Frontend Properties.
  19. Install Google Page Speed Module
  20. Minimize redirects – Minimizing HTTP redirects from one URL to another cuts out wait time for users.
  21. Prefer asynchronous resources – Fetching resources asynchronously prevents those resources from blocking the page load.
1
  • This is a nice list of general M2 optimization tips but does not address the OPs question directly relating to db call inventory_stock_1 might want to check out github.com/magento-engcom/msi/pull/1950
    – SWD
    Mar 17, 2019 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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