Fortune 500 Magento 2 Performance Fixing
The Fortune 500 Huge International Company has a website on Magento 2. Before they have a custom website an after migration to the modern Magento website lost 80% of the revenue because of the core architectural performance issue and 3-d party module issues. Moved from The Magento Cloud Hosting to self-hosted AWS. Magento Cloud doesn’t have even tools to check, found and fix performance issues. You need to create your own proper development Magento environment with proper tooling using Digital Ocean or AWS and after check end fix performance and after deploy to Magento Cloudy hosting.
The site has high traffic 1000 requests per minute because of its popularity in Taiwan and other countries.
If you Magento Performance is bad Varnish will not help you. Varnish performance is not Magento 2 performance! Magento companies like to sell varnish integration with page cache warmup crawling however this client already has this “solution” and undersends that it is just scam and he needs better performance, not the cache warmer and FPC. None of that should be necessary.
Illustration how Magento FPC works:
It second My performance review previously page load time was around 40 seconds. Now it is around 3–6 seconds. Was fixed Mega Menu performance, and several modules were fixed, and couple modules removed. After the fixes, traffic increased 3 times. More organic traffic and less bounce rate.
Magento 2 performance fixes:
Magento Category Page Profiling
PHP execution 4.13s
Magento\Catalog\Controller\Category\View
Database calls (2 646): 2.12 s
Redis calls (608): 33.3 ms
SQL queries statistic:
SELECT eav_attribute_option, eav_attribute_option_value + 1 table ->678 -> 473ms
SELECT inventory_stock_1 -> 2 -> 518 ms -> just 2 queries took 500ms
SELECT cataloginventory_stock_item, catalog_product_entity -> 882 ->250 ms
diffrent SELECTs -> 73 -> 416 ms
SELECT inventory_stock_sales_channel -> 349 -> 116 ms
SELECT catalog_product_entity -> 96 -> 26.9 ms
SELECT catalog_product_super_link, catalog_product_entity + 1 table -> 77 -> 21.6 ms
Category page Amasty SeoRichData issue
Overhead: 783ms
Amasty Preorder issue
Overhead: 127ms.
Small overhead, however, this issue is typical to any Magento modules. They are so purely designed with a lot of SQL N+1 issues (Load in the loop ). Even Amasty couldn't make a good extension for M2, not because Amasty is bad I love Amasty Team and their extensions especially for Magento 1 ( good product). Amasty M1 FPC works better than Magento 2 Varnish implementation. Everything Because Magento 2 framework is beast in the hell.
Welt Pixel Google Tag manager performance issue
Overhead: 220ms
Fix: remove the extension.
Magento MSI (Multi-Source Inventory)
Magento Core code has too many bugs and performance issues. Multi-Source Inventory is an example.
Total MSI SQL Querys execution time 255 + 264 + 253 = 772ms
#1 PDOStatement::execute#2 Magento\Framework\DB\Statement\Pdo\Mysql::Magento\Framework\DB\Statement\Pdo\{closure}#3 Magento\Framework\DB\Statement\Pdo\Mysql::tryExecute#4 Magento\Framework\DB\Statement\Pdo\Mysql::_execute#5 Zend_Db_Statement::execute#6 Zend_Db_Adapter_Abstract::query#7 Zend_Db_Adapter_Pdo_Abstract::query#8 Magento\Framework\DB\Adapter\Pdo\Mysql::_query#9 Magento\Framework\DB\Adapter\Pdo\Mysql::query#10 Zend_Db_Adapter_Abstract::fetchRow#11 Magento\InventoryIndexer\Model\ResourceModel\GetStockItemData::execute#12 Magento\InventorySales\Model\IsProductSalableCondition\IsSalableWithReservationsCondition::execute#13 Magento\InventorySales\Model\IsProductSalableCondition\IsProductSalableConditionChain::execute#14 Magento\InventoryCatalog\Plugin\CatalogInventory\Api\StockRegistry\AdaptGetProductStockStatusPlugin::aroundGetProductStockStatus#15 Magento\CatalogInventory\Model\StockRegistry\Interceptor::Magento\Framework\Interception\{closure}#16 Magento\CatalogInventory\Model\StockRegistry\Interceptor::___callPlugins#17 Magento\CatalogInventory\Model\StockRegistry\Interceptor::getProductStockStatus
The best and the cheapest solution is Magento MSI less solution — remove MSI however this client has Requirements and wants to use it.
MSI SQL is pretty simple and looks like this
SELECT
inventory_stock_1.quantity,
inventory_stock_1.is_salable
FROM
inventory_stock_1
WHERE
(sku = ?)Let's profile/explain this SQL query.
The
EXPLAINkeyword is used throughout various SQL databases and provides information about how your SQL database executes a query. Instead of the usual result output, MySQL would then show its statement execution plan by explaining which processes take place in which order when executing the statement.
EXPLAINis a great tool to quickly remedy slow queries. While it can certainly help you, it will not take away the need for structural thinking and a good overview of the data models in place. Often, the simplest fix and quickest advice is to add an index to a specific table’s columns in question if they are used in many queries with performance issues. Beware, though, do not use too many indices as that might be counter-productive. Reading the index and the table only makes sense if the table has a significant amount of rows and you need only a few data points. If you are retrieving a huge result set from a table and querying different columns often, an index on every column does not make sense and hinders performance more than it helps.
MySQL [MgentoMSISucks]> EXPLAIN SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = 45545)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 46103
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: legacy_stock_status
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 46103
filtered: 100.00
Extra: Using temporary
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: product
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: MgentoMSISucks.legacy_stock_status.product_id
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)Describe the Magento table:
Indexes not found.
However, the issue is in the View
MySQL [MgentoMSISucks]> SHOW FULL TABLES
-> WHERE table_type = 'VIEW';
+---------------------+------------+
| Tables_in_magecloud | Table_type |
+---------------------+------------+
| inventory_stock_1 | VIEW |
+---------------------+------------+
1 row in set (0.75 sec)The only View in Magento2 and so many issues.
SHOW CREATE VIEW inventory_stock_1;ordernoCREATE ALGORITHM=UNDEFINED DEFINER=`MgentoMSISucks`@`%` SQL SECURITY INVOKER VIEW `inventory_stock_1` AS select distinct `legacy_stock_status`.`product_id` AS `product_id`,`legacy_stock_status`.`website_id` AS `website_id`,`legacy_stock_status`.`stock_id` AS `stock_id`,`legacy_stock_status`.`qty` AS `quantity`,`legacy_stock_status`.`stock_status` AS `is_salable`,`product`.`sku` AS `sku` from (`cataloginventory_stock_status` `legacy_stock_status` join `catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)))
Distinct always sets off alarm bells to me — it usually signifies a bad table design or a developer who’s unsure of themselves. It is used to remove duplicate rows, but if the joins are correct, it should rarely be needed. And yes there is a large cost to using it.
What’s the primary key of the table? Assuming it’s stock no then that should be sufficient to guarantee no duplicates. If it’s something else, then you may need to do a bit more with the query, but you should make it a goal to remove those distinct!
Ok the best way to fix remove distinct by recreating the view
drop view inventory_stock_1CREATE VIEW inventory_stock_1 AS select `legacy_stock_status`.`product_id` AS `product_id`,`legacy_stock_status`.`website_id` AS `website_id`,`legacy_stock_status`.`stock_id` AS `stock_id`,`legacy_stock_status`.`qty` AS `quantity`,`legacy_stock_status`.`stock_status` AS `is_salable`,`product`.`sku` AS `sku` from (`cataloginventory_stock_status` `legacy_stock_status` join `catalog_product_entity` `product` on((`legacy_stock_status`.`product_id` = `product`.`entity_id`)))
Now we are having next explain
EXPLAIN SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = 45545)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: product
partitions: NULL
type: index
possible_keys: PRIMARY,CATALOG_PRODUCT_ENTITY_SKU
key: CATALOG_PRODUCT_ENTITY_SKU
key_len: 195
ref: NULL
rows: 69680
filtered: 10.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: legacy_stock_status
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: MgentoMSISucks.product.entity_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 3 warnings (0.00 sec)Product page issues profiling
- Image regenerates every request
The plugin has code which resizes instead of reuse existed URL from the cache each request
if ($images instanceof \Magento\Framework\Data\Collection) {
foreach ($images as $image) {
$image->setData('small_image_url', $this->_imagehelper->init($product, 'product_page_image_small')
->setImageFile($image->getFile())
->keepAspectRatio(true)
->keepFrame(false)
->resize($thump_image_width)
->getUrl());Magento 2 cached Images path in Redis looks like this. No needs resize images if the image file already in the cache
1594663491.598094 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_CF95B13FDACF067A48892E4A133AED78_S_7_S710XX19_32_7__JPG” “d”
1594663491.609179 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_FDDD8E0A6F2DA919A33E59AF781E4F5C_S_7_S710XX__JPG” “d”
1594663491.609396 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_307D49A744834A19686949FDF069D9A1_S_7_S710XX__JPG” “d”
1594663491.609585 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_CF95B13FDACF067A48892E4A133AED78_S_7_S710XX__JPG” “d”
1594663491.614012 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_FDDD8E0A6F2DA919A33E59AF781E4F5C_I_M_IMG_0920__JPG” “d”
1594663491.614224 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_307D49A744834A19686949FDF069D9A1_I_M_IMG_0920__JPG” “d”
1594663491.614409 [1 127.0.0.1:37570] “HGET” “zc:k:def_IMG_INFO_VAR_WWW_HTML_MAGENTO_PUB_MEDIA_CATALOG_PRODUCT_CACHE_CF95B13FDACF067A48892E4A133AED78_I_M_IMG_0920__JPG” “d”In this case, the problem is in the good plugin and to fix it by commenting out resize line.
foreach ($images as $image) {
$image->setData(‘small_image_url’, $this->_imagehelper->init($product, ‘product_page_image_small’)
->setImageFile($image->getFile())
->keepAspectRatio(true)
->keepFrame(false)
// ->resize($thump_image_width)
->getUrl());2. Magento Mysql N+1 performance problems usually occur when you execute SQL queries or HTTP calls in a loop. The following queries are candidates for this problem:
SELECT plumrocket_sizechartwas executed 182 times and took 1.72 s.
SELECT
plumrocket_sizechart.*
FROM
plumrocket_sizechart
WHERE
(plumrocket_sizechart.id = ?)The best way to fix it is to disable that module or rewrite to use IN however is always better to remove 3-d party modules.
Also, we can see smaller issues :
Magedelight GeoIP Location SQL issue
Profiler waterfall view:
SELECT
main_table.*
FROM
magedelight_geoip_location AS main_table
WHERE
(start_ip_num = ?)Explain the query :
MySQL [MagentoPerformanceUgly]> explain SELECT main_table.* FROM magedelight_geoip_location AS main_table WHERE (start_ip_num = 192)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 330514 <--!!
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)A single query is a MySQL loop of 330K rows …
Describe magedelight_geoip_location MySQL Table and indexes
MySQL> describe magedelight_geoip_location;
+--------------+------------------+------+-----+---------+----------
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------
| entity_id | int(10) unsigned | NO | PRI | NULL |
| start_ip_num | bigint(20) | YES | | NULL | |
| end_ip_num | bigint(20) | YES | | NULL | |
| country_code | varchar(100) | YES | | NULL | |
| country | varchar(255) | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------start_ip_num doesn’t have indexers at all
CREATE INDEX start_ip_num_index ON magedelight_geoip_location (start_ip_num);
explain SELECT main_table.* FROM magedelight_geoip_location AS main_table WHERE (start_ip_num = 192)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ref
possible_keys: start_ip_num_index
key: start_ip_num_index
key_len: 9
ref: const
rows: 1 <--
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)After these fixes, we are having 330 times faster SQL queries without a performance bottleneck.
Mage Delight according to its web site has 81 Magento Certified developers and they cand add an index to MySQL. Magento Certification is totally boolshit because it requires Magento XML modification skills and understanding Magento 2 legacy gotchas, however, doesn't require software development skills. Build software, not Magento!
Product\View\Attributes issue
The next issues are smaller however any Magento installation has it because it is Magento core issues. Attributes interceptor takes 32 ms to render something. Modern e-commerce platforms need 50ms to render whole pages without FPC when Magento needs 32ms just t render several attributes
Magento 2 generates 10 the same SQL Queries
SELECT
main_table.*,
tdv.value AS default_value,
[..]
FROM
eav_attribute_option AS main_table
INNER JOIN eav_attribute_option_value AS tdv ON tdv.option_id = main_table.option_id
LEFT JOIN eav_attribute_option_value AS tsv ON tsv.option_id = main_table.option_id
AND tsv.store_id = ?
WHERE
(main_table.attribute_id = ?)
AND (main_table.option_id IN ([..]))
AND (tdv.store_id = ?)
ORDER BY
main_table.sort_order ASC,
value ASCTo fix we need rewrite code to generate something like this and save 30ms TTFB and reduce SQL serverload
SELECT
main_table.*,
tdv.value AS default_value,
[..]
FROM
eav_attribute_option AS main_table
INNER JOIN eav_attribute_option_value AS tdv ON tdv.option_id = main_table.option_id
LEFT JOIN eav_attribute_option_value AS tsv ON tsv.option_id = main_table.option_id
AND tsv.store_id = ?
WHERE
(main_table.attribute_id IN ([..]))
AND (main_table.option_id IN ([..]))
AND (tdv.store_id = ?)
ORDER BY
main_table.sort_order ASC,
value ASCWe will not fix this core issue. Lets Adobe and Magento co. fix it. It is an enterprise platform, isn’t it?
Next issue:
Amasty\ShopbyBrand\Block\Catalog\Product\ProductList\MoreFrom\
it takes 88 ms to render something because of two slow SQL queries.
Slow SQL:
SELECT
COUNT(DISTINCT e.entity_id)
FROM
catalog_product_flat_1 AS e
INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id = e.entity_id
AND cat_index.store_id = ?
AND cat_index.visibility IN ([..])
AND cat_index.category_id = ?
INNER JOIN cataloginventory_stock_item AS at_inventory_in_stock ON (at_inventory_in_stock.`product_id` = e.entity_id)
AND (
(
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.manage_stock = ?
AND at_inventory_in_stock.is_in_stock = ?
)
OR (
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.manage_stock = ?
)
OR (
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.is_in_stock = ?
)
)
INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id
WHERE
(e.manufacturer IN ([..]))
AND (e.entity_id != ?)
AND (stock_status_index.stock_status = ?)Duration: 36.8 ms (2.2%)
AND
SELECT
? AS status,
e.entity_id,
[..]
FROM
catalog_product_flat_1 AS e
INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id = e.entity_id
AND cat_index.store_id = ?
AND cat_index.visibility IN ([..])
AND cat_index.category_id = ?
INNER JOIN cataloginventory_stock_item AS at_inventory_in_stock ON (at_inventory_in_stock.`product_id` = e.entity_id)
AND (
(
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.manage_stock = ?
AND at_inventory_in_stock.is_in_stock = ?
)
OR (
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.manage_stock = ?
)
OR (
at_inventory_in_stock.use_config_manage_stock = ?
AND at_inventory_in_stock.is_in_stock = ?
)
)
INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id
WHERE
(e.manufacturer IN ([..]))
AND (e.entity_id != ?)
AND (stock_status_index.stock_status = ?)
ORDER BY
RAND()
LIMIT
?Duration: 46.1ms
The best solution is to remove this module or add a hardcoded version without call getExtensionVersion method.
Next issue block Riskified\Decider\Block\JS takes 69.3 ms
Add this block to the cache is nice ide or just remove or fix.
To fix we could see the issue in :
Riskified\Decider\Block\Js::getExtensionVersion
->Riskified\Decider\ApiConfig::getExtensionVersion
->Magento\Framework\Module::FullModuleList::getAlland it reads slow file system to check module version
This issue is there:
$ grep -r ‘getExtensionVersion’ vendor/riskified/decider/
vendor/riskified/decider/Api/Api.php: $this->version = $this->_apiConfig->getExtensionVersion();
vendor/riskified/decider/Api/Config.php: public function getExtensionVersion()
vendor/riskified/decider/Block/Js.php: public function getExtensionVersion()
vendor/riskified/decider/Block/Js.php: return $this->apiConfig->getExtensionVersion();
vendor/riskified/decider/Observer/SaveRiskifiedConfig.php: $extensionVersion = $helper->getExtensionVersion();
vendor/riskified/decider/view/frontend/templates/riskified_js.phtml: var version = “<?php echo $block->getExtensionVersion() ?>”;vendor/riskified/decider/view/frontend/templates/riskified_js.phtml content :
var store_domain = "<?php echo $block->getShopDomain() ?>";
var version = "<?php echo $block->getExtensionVersion() ?>"; <-- commented out thisvar url = ('https:' == document.location.protocol ? 'https://' : 'http://')
+ "<?php echo $block->getConfigBeaconUrl()?>?shop=" + store_domain
+ "&sid=" + session_id
+ "&v=" + version; <-- Harcode version there instead of $block->getExtensionVersion() executionvar s = document.createElement('script');
s.type = 'text/javascript';
s.async = true;However it is up to you remove or don’t remove.
RDS Performance insight monitoring Magento 2 MySQL
Amazon RDS Performance Insights is a database performance tuning and monitoring feature that helps you quickly assess the load on your database, and determine when and where to take action. Performance Insights allows non-experts to detect performance problems with an easy-to-understand dashboard that visualizes database load.
Performance Insights uses lightweight data collection methods that don’t impact the performance of your applications and makes it easy to see which SQL statements are causing the load, and why. It requires no configuration or maintenance. Magento Cloud doesn’t have this awesome tool!
What we can see there. We improved Magento frontend performance a lot however Magento Crons and Checkout still sucks. Magento C and Admin also kill frontend performance by abusing MySQL performance. If you are having more than 50 Rows examined value it is bad.
Let’s check
Explain SELECT `order_items`.`qty_ordered` AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`sku` AS `order_items_sku` FROM `sales_order_item` AS `order_items` INNER JOIN `sales_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> ‘canceled’ WHERE (order_items.parent_item_id IS NULL) AND (`product_id` = ‘54404’) HAVING (order_items.qty_ordered > 0) ORDER BY `ordered_qty` desc
it examinates 159K rows Using where; Using filesort each order items invocation. Because no index on
Maybe that’s it for today after all these fixes will be deployed to production we can test it again or I will post tests other projects. If you would like to test you Magento problems? Send me an email: yegorshytikov@gmail.com
