Fixing Magento 2 Core MySQL performance issues using Percona Monitoring and Management (PMM)

CPU Magento MySQL issue

SELECT `e`.`entity_id` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ? AND price_index.customer_group_id = ? INNER JOIN `catalog_category_product_index_store?` 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=?)) WHERE ((( e.row_id IN (SELECT `t`.* FROM (SELECT IFNULL(relation.parent_id, table.row_id) FROM `catalog_product_entity_varchar` AS `table` INNER JOIN `catalog_product_relation` AS `relation` ON table.row_id=relation.child_id WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?) UNION SELECT `table`.`row_id` FROM `catalog_product_entity_varchar` AS `table` WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?)) AS `t`) AND (`e`.`attribute_set_id`=?) AND e.row_id IN (SELECT `t`.* FROM (SELECT IFNULL(relation.parent_id, table.row_id) FROM `catalog_product_entity_int` AS `table` INNER JOIN `catalog_product_relation` AS `relation` ON table.row_id=relation.child_id WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?) UNION SELECT `table`.`row_id` FROM `catalog_product_entity_int` AS `table` WHERE (table.attribute_id=?) AND (table.store_id=?) AND (`table`.`value`=:targetrule_bind_?)) AS `t`) AND (SELECT COUNT(*) FROM `catalog_category_product` WHERE (product_id=e.entity_id) AND (FIND_IN_SET(`category_id`, :targetrule_bind_?))) > ?)) AND (`e`.`entity_id` NOT IN(?))) AND (e.created_in <= ?) AND (e.updated_in > ?) LIMIT ?

Results

--

--

--

True Stories about Magento 2. Melting metal server infrastructure into cloud solutions. Donate: https://paypal.me/magentasoftware?country.x=US&locale.x=en_US

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Flutter & Flame: Physic-based collisions

What did I learn in switching to a new development team?

Continuous Integration (CI) with Jenkins

Local Kubernetes development with Tilt.dev

Reasons why your Compliance/EQMS cloud platform should be multitenant

VLOOKUP and MATCH combo function in excel: How to create a dynamic VLOOKUP in excel ||DataWitzz

VLOOKUP and MATCH combo function in excel

Exceptions in Java

Atom Packages Revisited

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
Yegor Shytikov

Yegor Shytikov

True Stories about Magento 2. Melting metal server infrastructure into cloud solutions. Donate: https://paypal.me/magentasoftware?country.x=US&locale.x=en_US

More from Medium

Adobe Commerce Cloud architecture under the hood

On Testing — Part 1

What Is The CQRS Pattern?