Magento 2 performance problem profiling and optimization
Paul from MageCloud asked me to help with the performance Optimization of https://modaestile.co/ Magento 2 Web site.
To find performance issues we need to have a good profiler. XHProff is my favorite one.
I have discovered this profiler in 2014 when I worked at Magento and we need a good profiler to discover Magento 2 Core performance issues.
For a long time, we used Zend Xdebug however it has big overhead. Profiling of the single request takes 5–15 minutes and generates a 450MB result file. After you need to read it with the special reader — KCacheGrind tool to analyze your profiling data
Xdebug is not a production profiler and you should never activate it on production. Facebook’s XHProf profiler is good for production server performance measurement.
XHProf, the light-weight, and feature-rich profiler. And can be used for production Magento website profiling with a little overhead. Evan BlackFire has bigger overhead.
However Facebook doesn’t use low-performance legacy PHP language anymore and abundant this project. To yous it with modern PHP version you need to use Tideways XHProf Extension (https://github.com/tideways/php-xhprof-extension). tideways_xhprof is a hierarchical Profiler for PHP, forked from the xhprof Extension originally developed by Facebook. Also you can use the Software-as-a-Service (SaaS) product Tideways.
To Start with profiling you need:
- a Tideway account: https://tideways.com/
- install the Tideways PHP Profiler extension and daemon to start collecting data.
Ubuntu:
echo 'deb http://s3-eu-west-1.amazonaws.com/tideways/packages debian main' | sudo tee /etc/apt/sources.list.d/tideways.list
wget -qO - https://s3-eu-west-1.amazonaws.com/tideways/packages/EEB5E8F4.gpg | sudo apt-key add -
sudo apt-get update
sudo apt-get install tideways-php tideways-daemonCentos :
echo “[tideways] name = Tideways baseurl = https://s3-eu-west-1.amazonaws.com/tideways/rpm" > /etc/yum.repos.d/tideways.repo
rpm — import https://s3-eu-west-1.amazonaws.com/tideways/packages/EEB5E8F4.gpg
yum makecache — disablerepo=* — enablerepo=tideways
yum install tideways-php tideways-cli tideways-daemon3. Set configuration for Tideways PHP Extension. Add to the app/bootstrap.php file these lines:
<?php // MAgento 2 bootstrap.php file
if (class_exists(‘Tideways\Profiler’)) { \Tideways\Profiler::start(
[
‘api_key’ => ‘API_KEY’, //<--- Replace it
‘sample_rate’ => 25,
]
); }4. restart PHP — fpm
5.Install Chrome extension to trigger profiling
https://chrome.google.com/webstore/detail/tideways-profiler/gmffhaalpgfdplaajakbhfklbdfhnlog?hl=en
This extension allows you to control tracing applications with Tideways Profiler.
We are ready to profile the slow Magento 2 core.
To bypass FPC it is better to trigger it by the Query string.
Example of the GET token:
?_tideways%5Bmethod%5D= &_tideways%5Btime%5D=15902&_tideways%5Buser%5D=20393&_tideways%5Bhash%5D=bc523530636e727d304f3bd043eb660ee053b7cadc9b50&_tideways_ref=781ef11&qwertyAlso, you can add a random get parameter like “&qwerty” to the end to profile the same page. Or you can just disable FPC however it is not the best solution.
Ok, let's trigger our Category page Profiling for the category page.
modaestile.co/women/accessories/scarves.htmlFull Profiling Report is here:
https://drive.google.com/file/d/1-TkMAKXnovb1sAHVNJ4122ZlTPvLx4CD/view?usp=sharing
General information
Magento generates 2731 MySQL queries, (40.8 seconds I/O wait).
576 Redis colls however it is normal for the Magento core.
Lets Check whats is the source of this Terrible performance.
- SELECT catalog_category_flat_store_1 executes 1653 times
SELECT
main_table.*
FROM
catalog_category_flat_store_1 AS main_table
WHERE
(is_active = ?)
AND (parent_id = ?)
ORDER BY
position ASCThis N+1 SQL issue produced by low-level Magento mega menu extension
The constructor of the class: Ves/Megamenu/Helper/Editor
triggers Ves/Megamenu/Model/Config/Source/CategoryList::toOptionArray
$this->_categoryList = $categoryList->toOptionArray(false);and this method calls getTreeCategories method which generates N SQL queries in the foreach loop:
public function getTreeCategories($parentId,$level = 0, $caret = ' _ '){
$allCats = $this->_categoryFactory->create()->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter('is_active','1')
->addAttributeToSort('position', 'asc')
->setStoreId(0);
if ($parentId) {
$allCats->addAttributeToFilter('parent_id',array('eq' => $parentId));
}$prefix = "";
if($level) {
$prefix = "|_";
for($i=0;$i < $level; $i++) {
$prefix .= $caret;
}
}
foreach($allCats as $category) ///<<---Issue is there
{
if(!isset(self::$tmp[$category->getId()])) {
self::$tmp[$category->getId()] = $category->getId();
$tmp["value"] = $category->getId();
$tmp["label"] = $prefix."(ID:".$category->getId().") ".addslashes($category->getName());
$arr[] = $tmp;
$subcats = $category->getChildren();
if($subcats != ''){
The simplest way is to cache this getTreeCategories method.
Example of the fix:
public function getTreeCategories($parentId,$level = 0, $caret = ‘ _ ‘){$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$cache = $objectManager
->get('Magento\Framework\Cache\FrontendInterface');$cacheKey = 'menu_fix_'.$parentId.'_'.$level.'_'.$caret;
$data = $cache->load($cacheKey);if ($data){
$tmp = unserialize($data)
if(count($tmp) > 0)
return $data;
}
/// Function Body// public function save($data, $identifier, array $tags = [], $lifeTime = null)
$result = isset($arr)?$arr:array();
$cache->save(serialize($result), $cacheKey);
return $result;
}
Yes I know somebody will tell it is not the best practices you should obfuscate this code in Magento way using DI, Plugins, Cache Poos. You are free to fix it using Magento certified bed practices. It is just an example…
2. SELECT catalog_category_flat_store_1
SELECT
catalog_category_flat_store_1.entity_id
FROM
catalog_category_flat_store_1
WHERE
(path LIKE ?)
AND (
levelThe same issue as the previous however during block Ves\Themesettings\Block\Html\Header generation
it is easy to fix by caching the whole Block using a layout XML file
<block class="Ves\Themesettings\Block\Html\Header" name="ves_header">
<arguments>
<argument name="cache_lifetime" xsi:type="number">3600</argument>
</arguments>
....</block>
3. SELECT url_rewrite executes 324 times
This issue also related to the menu Block generation will be fixed together with the previous issue.
After this Fixes we are having this trace:
Reducing the number of SQL queries from 1657+ 324 to 2 Redis calls.
Notice!
caching of dynamic content is a dirty workaround for the poor performance of a badly designed software. The cache introduces a bunch of new hard problems and it limits the possibilities to optimize the conversion rate. If System doesn’t work without the caches it doesn't work at all.
Magento 2 MSI performance Issue. Slow Queries
However, we also have another issue. Regular Magento 2 website is a Collection of Performance issues.
issues 4,5,6,7,8 are connected with the slow running Mysql query. Merchants Server has 32 Cores and 60GB of RAM quite enough for Mysql.
+----------+----------+-------+----------------+---------------+
| Category | Summary | Count | Total Duration | Self Duration |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 1 | 9 | 6.94 s | 15% | 6.94 s | 15% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 2 | 9 | 6.77 s | 15% | 6.77 s | 15% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 3 | 9 | 6.60 s | 15% | 6.60 s | 15% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 4 | 9 | 6.58 s | 15% | 6.58 s | 15% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 5 | 9 | 6.41 s | 14% | 6.41 s | 14% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 6 | 9 | 5.74 s | 13% | 5.74 s | 13% |
+----------+----------+-------+---------+------+--------+------+
| SQL | SELECT 7 | 2 | 1.28 s | 2.9% | 1.28 s | 2.9% |
+----------+----------+-------+---------+------+--------+------+Let’s try to debug these queries.
One of the queries (3) is like this:
(SELECT `child`.`entity_id` FROM `catalog_product_entity` AS `parent`
INNER JOIN `catalog_product_relation` AS `link` ON link.parent_id = parent.entity_id
INNER JOIN `catalog_product_entity` AS `child` ON child.entity_id = link.child_id
INNER JOIN `catalog_product_index_price` AS `t` ON t.entity_id = child.entity_id
LEFT JOIN `catalog_product_entity_int` AS `status_global_attr` ON status_global_attr.entity_id = child.entity_id AND status_global_attr.attribute_id = 97 AND status_global_attr.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `status_attr` ON status_attr.entity_id = child.entity_id AND status_attr.attribute_id = 97 AND status_attr.store_id = 1
INNER JOIN `inventory_stock_1` AS `stock` ON stock.sku = child.sku WHERE (parent.entity_id = ‘251570’) AND (t.website_id = ‘1’) AND (t.customer_group_id = 0) AND (IFNULL(status_attr.value, status_global_attr.value) = 1) AND (stock.is_salable = 1) ORDER BY `t`.`min_price` ASC, `child`.`entity_id` ASC
LIMIT 1)All of these queries are similar. They are joining the `inventory_stock_1` table. New Magento 2 obfuscation mechanism — MySQL view without indexers.
I thin Magento provides patches for Enterprise/paid customers, however, an Open Source version especially has performance and security holes.
Using views in MySql is not a good idea. Because you can’t create an index for views in MySQL like in Oracle. It is also very dangerous if you assume MySQL would optimize your VIEWs queries the same way as more advanced database systems would. It is difficult to find this issue on a small webshop with 100 products however shops with 100K items suffering from this Magento performance issue. This merchant has 80K SKUs….
Even This Small Query (number 4 in our list) without JOINS:
SELECT inventory_stock_1.quantity, inventory_stock_1.is_salable FROM inventory_stock_1 WHERE (sku = ‘007’)takes 0.6490 seconds. Because it doesn't have an index on the SKU column…
I found 15 queries that have a very low performance. I have checked it
And I have several conclusions:
- All slow queries contain join to inventory_stock_1 SQL view and it can’t be indexed because it’s just view (query without this SQL view works like 10 times faster)
- Some of the queries have unnecessary joins to catalog_product_enity table just for the purpose to provide SKU for join inventory_stock_1. (We don’t need it, once we have product_id field in inventory_stock_1. We have to get rid of this join in spite of this will not solve any performance issue)
- Join by SKU field between inventory_stock_1 view and another table (join by varchar field is anyway slower then join by int, so using product_id from the previous paragraph will solve this issue)
Here are some MySQL performance issues. We need to use some advanced tools for this Query profiling Like Mysql Performance Insight.
Amazon RDS Performance Insights monitors your Amazon RDS DB instance load so that you can analyze and troubleshoot your database performance.
However, this issue is in Magento 2.3.+ in the older versions doesn’t have these issues because they don’t use the VIEW tables ...
We need to disable this Magento MSI.
This command will list and disable all the MSI modules.
php bin/magento module:status | grep Magento_Inventory | grep -v List | grep -v None | grep -v -e '^$' | xargs php bin/magento module:disable
php bin/magento s:upgrade
php bin/magento s:s:deploy -f
php bin/magento c:flush
php bin/magento i:reindexLet's check performance after removing the garbage Magento Core modules.
We have reduced the number of MYSQL queries from 2731 to 272 and page generation time from 45.0 to 2.12 second.
Our Waterfall looks like:
Full report:
All these SQL queries also is a Magento Core N+1 SQL performance issues
Magento 2 legacy ORM select N+1 issue is a data-access performance problem. Any code that iterates through a collection of elements, and executes an additional query for each element, has this problem. As you can expect, doing N+1 queries instead of a single query will flood your database with queries, which is something we can and should avoid.
All modern ORM has an Eager solution to resolve this problem. “Eager load” relationships at the time you query the parent model. Eager loading alleviates the N + 1 query problem. However, legacy Magento 2 ORM doesn't have this tool it has just CRUID and Zend 1 query builder.
Eager loading improves object retrieval performance for large collections of related objects by reducing the number of queries. Counterintuitively, it can be more performant to join everything into one query and let ORM generate the relationships from that.
Even after all my improvements Magento still has 270+ queries per single Category page. For example, Silyus has 40 SQL queries, Shopware 80 per category page
However, we have another Core Magento Performance issue (1)
Block generates to long and mostly it is Magento PHP framework pure performance. Template Magento\Swatches\Block\LayeredNavigation\RenderLayered generate duration : 870ms.
This issue is so critical for this merchant because he is running on cheap VPS like Digital Ocean, Linode, VPS Dime. All these hostings has hard virtualization and cut resources :
model name : Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
cpu MHz : 1201.452 <--Should be 2.4 I have cheapest T3 instance AWS — 6$ month and it has mo power than this calculator:
model name : Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
cpu MHz : 2500.000Use only AWS!
Also, we are having slow Magento core queries :
+-------------------------------------+-------+----------------+
| SQL | Count | Total Duration |
+-------------------------------------+-------+----------------+
| | 3 | 108 ms |
| SELECT eav_attribute_option, | | |
+-------------------------------------+-------+----------------+
| SELECT catalog_product_flat_1, | 2 | 24.4 ms |
+-------------------------------------+-------+----------------+
| SELECT catalog_ca_entity_varchar, | 1 | 22.1 ms |
+-------------------------------------+-------+----------------+
| SELECT catalog_product_super_link, | 50 | 18.4 ms |
+-------------------------------------+-------+----------------+
| SELECT catalog__media_gallery, | 17 | 14.7 ms |
+-------------------------------------+-------+----------------+
| SELECT catalog_product_supe_att | 28 | 13.8 ms |
+-------------------------------------+-------+----------------+This queries and PHP performance can be fixed by moving to the best Magento Open Source AWS infrastructure:
It returns any Mysql queries in the range of 1ms...
