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.

Image for post
Image for post

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.

Image for post
Image for post
site traffic after performance fixes

Magento 2 performance fixes:

Magento Category Page Profiling

Image for post
Image for post

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

Image for post
Image for post

Overhead: 783ms

Amasty Preorder issue

Image for post
Image for post

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

Image for post
Image for post

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 EXPLAIN keyword 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.

EXPLAIN is 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.

Image for post
Image for post
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:

Image for post
Image for post

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 for post
Image for post
  1. 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_sizechart was 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

Image for post
Image for post

Profiler waterfall view:

Image for post
Image for post
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);
Image for post
Image for post
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

Image for post
Image for post

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 ASC

To 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 ASC

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

Image for post
Image for post

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 :

Image for post
Image for post
Riskified\Decider\Block\Js::getExtensionVersion
->Riskified\Decider\ApiConfig::getExtensionVersion
->Magento\Framework\Module::FullModuleList::getAll

and 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 this
var 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() execution
var 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!

Image for post
Image for post

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
Image for post
Image for post

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

Written by

Magento/APP Cloud Architect. Melting metal server infrastructure into cloud solutions.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store