Magento 2.3.0 Product page load time

[chathuramk]

 I'm working on a Magento 2.3.0 store with 790k products and 7k Categories.

product page loading time is more than 10s. 

Website is running on AWS t3.xlarge (4 CPU and 16 GB Memory).

I Could find below db query fromm db log.

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

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]

[Stuart M]

@chathuramk have you done any tuning on the MySQL database server? If not that is likely where your bottleneck exists as the defaults out the box would struggled with what you are saying is close to 800K products.

If you manually run the slow query on your database command line does it really take 3s+

SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = 'P00AA40') AFF: 1

[Tarandeep Singh]

@chathuramkIs your Magento is on default/developer mode? If yes then switch it to Product mode, clear cache and deploy static content. That should resolve your problem.

Are you using same server for DB and Web? Separating them will also help in improving the performance as the size of catalog is really huge. Do also check consumption of the memory as everything is hosted on same server then you may be running out of resources to keep the performance of your Magento.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[chathuramk]

@Stuart M , I ran below query manually in phpmyadmin. Query took 7.6490 seconds.

SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = 'P00AA50')

[chathuramk]

@Tarandeep Singh ,

I am using aws mysql RDS for the database. (db.t2.medium)

[Tarandeep Singh]

@chathuramkStill have issue? Not sure if you already in production mode or switched to production mode to see if issue resolved?

Another thing you can look into it that make sure if you are using Flat Tables for Categories and Products. Make sure to reindex. Sometime re-deploying the static content also helps. Let know how thing works for you.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[SeoulWebDesign]

any progress with this, i'm thinking i could have the same problem, I have around 300k products....

[Tarandeep Singh]

@SeoulWebDesignAre you running into any issue?

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[SeoulWebDesign]

looks like somehting to do with : https://github.com/magento-engcom/msi/pull/1950 which looks like might of been fixed and coming out for 2.3.1 --

[szsc]

We have the same issue with 1.6 M skus on magento 2.3.0. Has anybody any experience if it was solved with magento 2.3.1? Does it have to do with MSI, as we are thinking of disabling some modules that we just don't use. We are in fact not using magento to manage stock and have therefore disabled it already. Even with the option Display Products Availability in Stock on Storefront disabled the stock count still shows up and the query still gets executed.

Production Mode, Flat Tables have all been enabled with no change, as the query itself on the database runs slow. As has already been said the query SELECT `inventory_stock_1`.`quantity`, `inventory_stock_1`.`is_salable` FROM `inventory_stock_1` WHERE (sku = 'XXXX') which accesses a view is the culprit.