OOM/Timeouts when saving a category with large catalog as all product URLs are regenerated

[devonto]

With a catalog of many thousands of products loaded, saving a category causes long execution times and high memory usage, when it isn't necessarily required. In a production environment, the execution fails with max_execution_time or memory_limit errors.

It appears that the system is trying to regenerate all URLs of products contained within the category and subcategories; even if the url_key is not modified.

Preconditions

1. Magento 2.1.1
2. max_execution_time set to 300
3. memory_limit set to 2G
4. Catalog of 140,000 products
5. Top category which contains subcategories and 40,000+ products

Steps to reproduce

1. Simply "Save" the top category without even making any changes

Expected result

1. No errors
2. A notice of You saved the category should appear

Actual result

1. PHP errors for max_execution_time and/or memory_limit

A workaround is to modify /vendor/magento/module-catalog-url-rewrite/Observer/CategoryProcessUrlRewriteSavingObserver.php temporarily to return; on line 1 of the execute() function to prevent URL rewrite changes.

A suggested performance improvement would be to check if the url_key was modified before running the regeneration of URLs.

This, though, cannot be used if the url_key is updated. At that point, the process should be handled by some background task.

[erikhansen]

One of my 2.1.4 merchants is experiencing this exact issue. They have a category that contains 538 children/descendent categories and those child categories contain a total of 5k+ products. Upon saving the category, the 768M native Magento memory limit is hit. Magento's memory limit should not need to be increased to accommodate saving this category.

When the category is saved, the \Magento\CatalogUrlRewrite\Observer\CategoryUrlPathAutogeneratorObserver::updateUrlPathForChildren observer calls \Magento\CatalogUrlRewrite\Observer\CategoryUrlPathAutogeneratorObserver::updateUrlPathForChildren for all ~583 child categories. This behavior happens of regardless of whether "By Category" is set to "Anchor == Yes" or "Anchor == No".

I profiled the category save using Xdebug and loaded the data in MacCallGrind and have included some screenshots below demonstrating how much of the processing time was used by the method mentioned above:

[orlangur]

@devonto,

A suggested performance improvement would be to check if the url_key was modified before running the regeneration of URLs.

Yeah, AFAIR it worked this way in initial implementation:
https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogUrlRewrite/Observer/CategoryUrlPathAutogeneratorObserver.php#L61

Maybe, this logic was broken in one of further fixes, like 6c0ac05#diff-33a66f964a57f4cb0f7f47ee7358ed35R106 where some force changes to url_path are introduced.

@erikhansen,

This behavior happens of regardless of whether "By Category" is set to "Anchor == Yes" or "Anchor == No".

Anchor option is responsible for products attachment to category I believe. Another option is responsible for whether URLs of child categories contain parent category url_path or not. The latter can be used to avoid updates of what is not necessary to be updated.

[quisse]

Any update on this?

[dan-advantec]

Is there an update on this please, still an issue in 2.1.7

[woutersamaey]

If I remember correctly, the fix is in the latest develop branch, but Magento is terribly slow to release it to the general public. We have solved this ourselves in a custom built module for our client.

…

On 2 Aug 2017, at 13:15, dan-advantec ***@***.***> wrote: Is there an update on this please, still an issue in 2.1.7 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#7874 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAEVa5zq427edlpqtqi5N8Jb4MfJISp3ks5sUFpngaJpZM4LQqWu>.

[dan-advantec]

Excellent, thanks @woutersamaey I assume this module isn't released to the general public?

[woutersamaey]

No, this is closed source, but you can search for the solution in the develop branch or my company can do the work for you and re-package the fix in a stand-alone module if you don’t have the means.

…

On 2 Aug 2017, at 13:23, dan-advantec ***@***.***> wrote: Excellent, thanks @woutersamaey <https://github.com/woutersamaey> I assume this module isn't released to the general public? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#7874 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAEVa5wBPK1PMPP_UisHFJCLcp1uwBZrks5sUFwwgaJpZM4LQqWu>.

[dan-advantec]

No worries @woutersamaey I'll have a look in the dev branch, but thank you for the offer.

[orlangur]

@dan-advantec @woutersamaey please check https://community.magento.com/t5/Magento-DevBlog/Pull-Requests-for-2-1-x-Patch-Releases/ba-p/65630 out. So, having knowledge where a bugfix resides in develop branch the best approach is to create a backport PR to 2.1-develop branch (it will be included in next 2.1.x release, until then you can apply a well-tested fix manually to your codebase).

[mdmujassam]

@dan-advantec you did find any solution ?

[magento-engcom-team]

@devonto, thank you for your report.
The issue is already fixed in 2.2.1

[nishantbkw]

Our Upgraded version is 2.2.2 still having the same issue removing one product from the category and then saving category with 4k products taking 5mins to save and some time fails.
can't find this fix in new version too. checked in the file "CategoryProcessUrlRewriteSavingObserver.php" your code fix is not there.
can you please verify?

[OlgaBurtyka]

This issue appear when we save product as well. When we un-assigned category with big qty of products and press save product. Because this event also called "catalog_category_save_after". Will be good move logic from this Observer to separate reindex. I had this problem on magento 2.2.3 and with category qty of products > 2-3k

[pixiemediaweb]

Stumbled across this thread as a client site is experiencing the same issue. After some more digging, discovered a third party elastic search module was listening for the category save and product save events and rebuilding elastic search which is what was causing the time out. With the event disabled (commented out), saving is back to much more manageable times. Install has 1,700 categories, 50,000 products and 6 store views. Categories save in approx 20 seconds. Worth a quick check to see if you have modules listening to the catalog_category_save event.

[woutersamaey]

@pixiemediaweb which Elasticsearch module is this? Can you give a bit more detail?

[pixiemediaweb]

hi @woutersamaey yes sure - it was the Wyomind module.

[quisse]

@pixiemediaweb Can you please specify what Wyomind module? Wyomind is a vendor which has multiple modules.

[pixiemediaweb]

Hi @quisse - as I said above - its their Elastic Search module

[quisse]

Oh k, sorry. Forget what I've said.

[pixiemediaweb]

no problem

[harrigo]

I feel like this is an issue for me on 2.2.3 and 2.2.5 still. Saving a category can sometimes take like 4 mins. Other categories can save in 10 seconds. Not quite sure why some are effected and not others doesn't seem related to size particularly as root category had 11,000 products and was saving in 10-20 seconds where as a category with 800 products took 4 mins.

[farhadfrz]

Having the same problem on Magento 2.2.6 !
If we save a large category without any modifications, everything works fine. But If we make any change to the category (ex. unassign just one product) and save it, it regenerates all the url keys.

[KulwinderKohli]

This is really a huge disaster when we are live with lots products and categories and can't unassign products from categories.

If anybody have solution, please share. 🙏

[AbdulKadir-Agoliya]

Using Magento 2.2.5 Same issue.

when remove one category from product and then save Product it will throw me error like :

Could not save product "55" with position 0 to category 4 {"exception":"[object] (Magento\Framework\Exception\CouldNotSaveException(code: 0): Could not save product "55" with position 0 to category 4 at /home/luxmadein/public_html/vendor/magento/module-catalog/Model/CategoryLinkRepository.php:90, Magento\UrlRewrite\Model\Exception\UrlAlreadyExistsException(code: 0): URL key for specified store already exists. at /home/luxmadein/public_html/vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php:220, Magento\Framework\Exception\AlreadyExistsException(code: 0): URL key for specified store already exists. at /home/luxmadein/public_html/vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php:250, Magento\Framework\DB\Adapter\DuplicateException(code: 1062): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'hot-chick-6.html-1' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID', query was: INSERT INTO url_rewrite (redirect_type,is_autogenerated,metadata,description,entity_type,entity_id,request_path,target_path,store_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?) at public_html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:571, Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'hot-chick-6.html-1' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID', query was: INSERT INTO url_rewrite (redirect_type,is_autogenerated,metadata,description,entity_type,entity_id,request_path,target_path,store_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?) at /public_html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'hot-chick-6.html-1' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID' at /public_html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)

is anybody had found solution ?

[sjb9774]

Reopening as I have recently seen this happen on 2.2.7. There are a number of problems involved, one of them being that \Magento\UrlRewrite\Model\Storage\DbStorage::deleteOldUrls does not properly remove existing rewrites and allows clashes. It assumes that if there is not entity_id conflict in url_rewrite then there will be no conflict on the table but that's not always true (as evidenced by the integrity constraint errors reported). Another is that when removing a product from a category it seems that Magento goes and updates all the other products in that category to reflect their new positions with that product removed, but then when those products are saved all of their URL rewrites are regenerated because the products are considered "changed" even though the url_key has not changed.

[m2-assistant]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 6. Add label Issue: Confirmed once verification is complete.

• 7. Make sure that automatic system confirms that report has been added to the backlog.

[ghost]

Hi @hostep Hi @sjb9774 this may be fixed by this commit -> 83204d2

[sjb9774]

@engcom-backlog-nazar that commit does look promising, at least as far as not regenerating rewrites for products that don't need them regenerated. Thank you for adding that information here. It also looks like it was ported into 2.2.8. I will try generating a patch for this and testing.

[ghost]

@sjb9774 did you check related commit, with your environment ? is issue still exists ? or we can close this one issue ?

[sjb9774]

@engcom-backlog-nazar I just checked with the dev involved with testing that and he has pushed out a patch generated from that commit and has not had any further issues. I would recommend anyone facing this problem upgrade to 2.2.8 or 2.3, or apply a patch generated from that commit. Closing this issue.