Magento 2.3.0 Performance issue

Question

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.

Answer 1

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.