3

I have latest magento 1.9 shop with 2000 configurable products and each product have approximately 30 associated products (apparel store with sizes and colors as configurable options). Total 60.000 products. I have two store views. My VPS server has 4 cores, 2GB ram, PHP 7 and Percona 5.6 MySQL server optimized with mysqltuner.

If I truncate core_url_rewrite table, URL reindex is finished in few minutes. But if URL Rewrite reindex is started again, without truncate, it can take up to few hours or it does not finish at all due to PHP fatal error, memory exhausted (1GM of ram in limit for php).

A strange thing is, that I see a mysql process doing this:

SELECT MAX(CAST(SUBSTRING(`request_path`, 24, LENGTH(`request_path`) - 23 - 5) AS SIGNED)) FROM `core_url_rewrite` WHERE (store_id = '1') AND (request_path LIKE 'james-amp-nicholson-jn-%.html') AND (request_path REGEXP '^james\\-amp\\-nicholson\\-jn\\-[0-9]*\\.html$')

almost all the time of url reindex process. The mysql transaction is filling very slowly and I assume that this is taking a lot of ram. I have around 20.000 (1000 configurable products with 20 associated products) products named like this: James & Nicholson | JN XYZ

Any suggestions on speeding or solving this problem?

Improve this question

1 Answer 1

Reset to default
4

Make sure, the url_keys are different. If they are (all) the same, magento adds a increment number which is calculated each time and takes a lot of resources.

Improve this answer
2
  • 3
    You pointed me to the right way. All my associated products had the same url key as configurable product. This was causing the mess when doing url reindex and causing high memory usage and core_url_rewrite table growing without the limit. Now I have set url key with corresponding product SKU in it and thus making it unique for every associated product. Full reindex is done in 3 min now. :) Thank you!
    – Iztok
    Mar 31, 2016 at 15:43
  • @Fabian Blechschmidt - If I change all of my associated url keys to be unique, what would this do to the rows that are already in the url_core_rewrite table? Cause additional reindexing and redirects?
    – NotJay
    Jul 24, 2017 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.