7

This is more of a research question - we have a site which is having some real problems with reindexing (30,000 products). It's now at the point where we're experiencing a lot of timeouts and SQLSTATE errors - Lock wait timeout exceeded - so being able to save changes in the admin panel is sporadic at best.

We are trying to ascertain if the indexes which say they are still running are actually doing so. We reindexed via the command line php indexer.php --reindexall. Hours later, in "Index Management" the Search Index still says Processing. All the other indexes are either finished or pending but not running. The main problem seems to be with the search indexes.

The number of rows in the catalogsearch_fulltext DB table are not changing - is this an indication that the Search Index is not actually still running?

In the var/locks folder, we have the following files:

index_process_1.lock
index_process_2.lock
index_process_3.lock
index_process_4.lock
index_process_5.lock
index_process_6.lock
index_process_7.lock
index_process_9.lock

What does the existence of these files actually mean? What effect would deleting them have?

For what it's worth we are using the Mirasvit Advanced Search Pro module which uses Sphinx. https://mirasvit.com/magento-extensions/magento-sphinx-search.html

3

I'm not sure how the Sphinx module works but you may want to go into every attribute you have set up and make sure you are not setting any unnecessary attributes to be included in quick search, and/or visible to the frontend, etc.

*.lock files in their simplistic of form is just a way to keep track of existing running processes so any other attempts to run it while one is in process is prevented. If you know no process is running it's safe to wipe these files when reindexing, any fatal exception could leave them lingering but its normal to see the lock files.

If you are using a 3rd party module then I would suspect the cause why the table never grows in size. You may want to reach out to the owners of the module for more insight as it seems the majority of your problems stem from it.

  • Thanks. There isn't a fundamental problem with the search module as far as we know, in that the Search Index does sometimes finish. Recently we've added more products to the site and it's as if a breaking point has been reached where the site can no longer cope with indexing the search. We've tried removing all of the searchable attributes that we can (we are down to the bare minimum now). – Sarah Jun 30 '15 at 14:49
  • Interestingly when the search index says processing it is not possible to run a search query on the site - we get a Magento "There was an error" page. Are you familiar with this at all? Issue seems to relate to SELECT queries on the catalogsearch_query table not completing. – Sarah Jun 30 '15 at 14:50
  • Perhaps the table needs to be optimized, it is the one of few tables in Magento that are MyISAM. Audit the code base and profile github.com/fbrnc/Aoe_Profiler – B00MER Jun 30 '15 at 15:04
  • More than that. You won't be able to wipe locked files. – user487772 Jul 9 '15 at 14:09
  • 1
    We did indeed optimize the tables, and also truncated the catalogsearch_query table, and this has brought indexing right down (15 minutes for search indexing). We found a transaction UPDATE catalogsearch_query SET is_processed = 0 was stalling during indexing, at least on one attempt! This helped: magento.stackexchange.com/questions/34910/… – Sarah Jul 11 '15 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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