Memory issue when updating category name

[george-vlahakis]

Memory allocation failure

Preconditions

Magento 2.1.6 CE
MySQL 5.6.35
PHP 7.0.15 (with mem limit = -1)

Two store (two languages) website.
~16.000 products
~160.000 customers
~800 categories

Steps to reproduce

Go to category (2nd store) and change the name.
Do Save.

Expected result

Save to work.

Actual result

Memory drained completely

Looking at the mysql general it seems that it is going through all the products under the category changed and looks to see if there is a url_rewrite needed.

This doesn't make much sense as the category name was changed only and the url_key was left the same (the one assigned in store 1).

But even so, why the memory consumption (and crash) ?

From what I can see and as far as I got, no UPDATE/INSERT statements executed.

This is the query that seems to look (product_id increments)

		  569 Query	SELECT `e`.*, `at_product_id`.`product_id` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `at_product_id` ON (at_product_id.`category_id`=e.entity_id) WHERE (at_product_id.product_id = '13367')
		  569 Query	SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value` FROM `catalog_category_entity_varchar` AS `t_d`
 INNER JOIN `catalog_category_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_category_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1 WHERE (e.entity_id IN (88, 130, 701, 991)) AND (t_d.attribute_id IN ('124', '125')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))
		  569 Query	SELECT `url_rewrite`.* FROM `url_rewrite` WHERE (`store_id` IN (1)) AND (`entity_id` IN ('13367')) AND (`entity_type` IN ('product'))
		  569 Query	SELECT `ce`.`entity_id`, `ci`.`value` FROM `catalog_category_entity` AS `ce`
 LEFT JOIN `catalog_category_entity_int` AS `ci` ON ci.entity_id = ce.entity_id AND attribute_id = '54' WHERE (ci.value = '1') AND (ce.entity_id IN ('1', '2', '3', '7', '83'))
		  569 Query	SELECT `ce`.`entity_id`, `ci`.`value` FROM `catalog_category_entity` AS `ce`
 LEFT JOIN `catalog_category_entity_int` AS `ci` ON ci.entity_id = ce.entity_id AND attribute_id = '54' WHERE (ci.value = '1') AND (ce.entity_id IN ('1', '2', '3', '7', '129'))
		  569 Query	SELECT `ce`.`entity_id`, `ci`.`value` FROM `catalog_category_entity` AS `ce`
 LEFT JOIN `catalog_category_entity_int` AS `ci` ON ci.entity_id = ce.entity_id AND attribute_id = '54' WHERE (ci.value = '1') AND (ce.entity_id IN ('1', '2', '3', '686', '687'))
		  569 Query	SELECT `ce`.`entity_id`, `ci`.`value` FROM `catalog_category_entity` AS `ce`
 LEFT JOIN `catalog_category_entity_int` AS `ci` ON ci.entity_id = ce.entity_id AND attribute_id = '54' WHERE (ci.value = '1') AND (ce.entity_id IN ('1', '2', '4'))

[george-vlahakis]

UPDATE:

Switched to PHP 5.6.30 and the memory issue seems to have gone away.

Now need to figure out why the db thrashing for a name change. This particular task took over 10 mins to complete at the root category. It has two sub-categories with 15.000 products each (one for the product tree and one for the brands tree).

[george-vlahakis]

UPDATE:

Still causing a memory drain even with 5.6.30. Previous success was due to making a change at different level on the category tree.

[george-vlahakis]

From what I have traced this far.

UrlRewriteHandler:getCategoryProductsUrlRewrites

This guy for a category with 350 products selects and builds 5079 productUrls.
The reasoning behind this is that those 350 products belong to multiple categories and a new url needs to be built for each category per product (and per store I think).

If I was to make the assumption the rest of the store categories and products are distributed similarly, then this would mean Magento is building a ~4 Million string array.

Without going into specifics in terms of how the products are distributed in the store it is clear (to me) that the logic in this module suffers greatly as it is attempting to build the array in memory and I assume later dump it in an single UPDATE statement.

This is a complete show stopper for us.

[george-vlahakis]

Solution (for now) is to pass the UrlPersistInterface instance from CategoryProcessUrlRewriteSavingObserver to UrlRewriteHandler so that he can use it to persist to database after each category call. This avoids merging arrays to rollup to do one call. Not sharing my code as it is pretty simple solution and I don't think my coding standards will be appreciated. Similar issue found in reindex namespace Batch file which goes and fetches and loads all records in memory before passing them in batches for the insert. Would have been better to process them in batches to start with.

[magento-engcom-team]

@georgios-2317, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.
We tested the issue on 2.3.0-dev, 2.2.0, 2.1.9