Adobe Magento Commerce Core performance issue any Magento website has. Fix included.

Magento 2 is the worst by architecture and the slowest by performance eCommerce platform on the modern software market. Every merchant struggling with uncountable Magento bug, issues, terrible slow performance, and Magento service providers that only know how to sell Magento development hours but not how to build fast modern cloud-native Microservices driven eCommerce solutions.

There is an example of the Magento Core(now Adobe Commerce) issues any Magento 2 Store has:

Each Magento page, especially created using terrible enterprise PageBuild contains several CMS blocks. Header, footer, top, custom, contacts, links, map, etc.

Let's imaging we have 10 different CMS blocks. Each book has its own SQL query to fetch the data. The SQL query is :

SELECT `cms_block`.`block_id` FROM `cms_block` INNER JOIN `cms_block_store` AS `cbs` ON cms_block.block_id = cbs.block_id WHERE (`cms_block`.`identifier`='magento2_sucks') AND (is_active = 1) AND (cbs.store_id in (22, 0)) ORDER BY `store_id` DESC LIMIT 1;

So, You will have 10 SQL queries with a different `cms_block`.`identifier` filter. It is a classic N+1 SQL issue. However, it is not the main issue. Magento has too many issues like that due to its architecture. The main issue is hidden behind its bad code deeply in the database.

As you can see from the AWS RDS Performance insight report, each query examines 400 rows when it should select only a single row by ID(identifier)!!! Period…

So, if you have a store with 400 cms blocks in the database and 10 CMS blocks per page, you will examine 400 * 10 = 4000 rows per page view, and it will produce redundant load on a server. I have saw sites with thousand of CMS blocks. In that case, the performance was just terrible.

Thanks to us, it is easy to fix this issue by fixing the bad Magento 2 core Database design and adding index by `identifier` filed without rewritten any purely designed Magento 2 coder PHP code.

There is a fix :

ALTER TABLE cms_block ADD INDEX (identifier);

Now when Magento queries Cms block, it fetches a single line per query:

However, it is not the only issue for this query:

Using index condition; Using where; Using temporary; Using filesort

It is stupid to sort the result with one element. So we need to rewrite Query and remove sort. And save valuable resources for necessary calculation, not for redundant Magento design issues.

explain SELECT `cms_block`.`block_id` FROM `cms_block` INNER JOIN `cms_block_store` AS `cbs` ON cms_block.block_id = cbs.block_id WHERE (`cms_block`.`identifier`=’magento_sucks’) AND (is_active = 1) AND (cbs.store_id in (22, 0)) LIMIT 1;

After the fix, we can see MySQL RDS CPU utilization dropped from 40% to 20%.

Magento Core Performance issue

We can also see linear relation beating Row_read/Examined and CPU utilization. So to improve Magento performance, we should reduce the number of rows read and numbers (N)of queries like:

select * from cms_block (or whatever) where something = value. 

Each SQL query has a latency of ~ 1ms if the Query is super fast. If the query is not optimized like CMS page, it can be slower 2–3ms, or if rows examined are around 1000+, it is approximately 10 ms.

1ms x 1000 Queries = minimum 1 second to the page load time. When the website receives 1000 page visits per second during peak hours, N+1 SQL Magento 2 core design issue just killing the database.

The fix for that is to rewrite the junky Magento 2 core code — collect a list of IDs and run the query, using where SKU IN (…) or use JOIN, which loads all needed information via a single query instead of loading each record one-by-one and make code more efficient. However, nobody is doing this because it requires thousand of developer’s hours to rewrite Magento code. Magento 2 is too broken to be fixed. So, the better solution is Magento-less microservices with efficient code.

Happy Magento 2 struggling.