Issue:
Product save has become incrementally slower over the past 12 months
Background:
- Magento Enterprise 1.14.1 (Issue also present in 1.13.0.2)
- ~15,000 Products, ~700 Categories, 2 Stores
- Solr 3.6
Investigation:
After the slower product save became an issue we investigate the slow query log an found the same query popping up: "UPDATE `catalogsearch_query` SET `is_processed`=0"
coming in at around 2-3 seconds, running the same on local this was upping to 7-10 seconds.
The reason for this slow query is the site is search heavy and they're over 400,000 rows in in the catalogsearch_query table that are all being updated with a 0 which isn't a huge amount of data to store in a MySQL table but is a huge amount to update on such a frequent event.
To make matters worse after xdebugging the process Magento is hitting the Mage_CatalogSearch_Model_Resource_Fulltext::resetSearchResults() method 5 times during each product save off the back of catalog_product_save_commit_after event calling Enterprise_CatalogSearch_Model_Observer::processProductSaveDeleteEvent()
So 5 times * 3 seconds results in 15 seconds being added to each product save. 5 times seems overkill and it seems an oversight that the resetSearchResults() is called so many times at the end of the process triggered by this observer.
Investigating further there seems to be very little use for this is_processed
field in the database when the Solr integration is in place.
- Has anyone ran into this issue?
- What actions did you take?
- Any suggestions for approaching?
My initial thoughts is to restructure the process to remove the query on the catalogsearch_query after fully investigating its impact.
Best Answer
I'm running into this issue while upgrading from EE 1.13.0.2 to 1.14.1.0 right now. We experience this when bulk updating product attributes and stock in cronjobs. In 1.13, the jobs take ~3 seconds and ~90 seconds respectively. In 1.14, it's more like ~10 minutes and I-don't-want-to-know-how-long.
There is an EE patch
PATCH_SUPEE-4945_EE_1.14.0.1_v2.sh
regarding the slow product save. You can request it from the support.Another tip I found was to only update the rows which are not set to 0 already (of course only change the core file temporarily to test if it has any effect for you):
And last, there was a recommendation to add an index to the
is_processed
column:I've tried all of them and while they brought minor performance improvements none of them got me near the performance of EE 1.13.
An easy fix (on the surface) would be to add back
at the beginning of
execute()
for these classes:If I do that, the code following isn't executed because Solr is configured to be used. As the core team deprecated this method for 1.14 it's ugly though and I'll try to stay away from that at all costs.
I'm only investigating this since yesterday so I hope I can follow up with a proper solution.
Update 09.02.2015
I found out by creating a xdebug profile dump that the communication between Magento and Solr takes up most of the time
System > Configuration > Advanced > Index Management > Index Options > Catalog Search Index
is set toUpdate on Save
. Setting the Catalog Search Index toUpdate when scheduled
significantly improves the speed.Update 03.03.2015
In the meanwhile, the Enterprise support explained why
$this->_isFulltextOn()
is deprecated:So the official solution pretty much is to change the Index Mode to
Update when scheduled
. We're using it for a few weeks without problems now. If your cron is running every minute you'll experience only a minor delay until the search is updated.