Category Save with 3000+ products doesn't complete and shows blank page

[sayan-gitkid]

Preconditions

1. Magento CE 2.1.7
2. PHP php5.6.25 (php.ini configs memory_limit = 7000M, mysql.connect_timeout = -1)
3. mysql5.7.14

Steps to reproduce

1. Clean install of Magento CE 2.1.7
2. Create 4000+simple products and assign them to categories with 4-5 level deep categories randomly. A product belongs to 5-15 categories.
3. Import products successfully.
4. Save a category below Default Category containing more than 4000+ produts

Expected result

1. Category saved successfully.

Actual result

1. [Screenshot, logs] Category takes long time to save and request timeouts.
2. php error_log
   [21-Sep-2017 07:39:20 UTC] PHP Fatal error: Allowed memory size of 805306368 bytes exhausted (tried to allocate 64 bytes)

[orlangur]

This is due to poor URL Rewrites performance, tracked as MAGETWO-69145 internally according to #10242 (comment).

Not sure if it works better already on Magento 2.2.0 branch.

[sayan-gitkid]

@orlangur Thanks for the info. Yes, it seems the issue is UrlRewrites. Just checked the mysql query log and found that the db rolls back after query to url_rewrites. Will try the same sample data in magento 2.2.0 and check it.

[sayan-gitkid]

@orlangur Tested the same product, category files in Magento 2.1.9, and Magento 2.2.0 rc-3 and the url_rewrite performance has improved. The category save is working fine.

[orlangur]

@sayan-gitkid thanks for testing! Closing as the fix for this issue is already available on Magento 2.2.0 branch which will be released really soon.

Not sure if needed changes could be backported at all due to BC constraints.