Home » Blog » Knowledge Base » Magento » Catalog Search Index refresh running slow or halting/freezing

Catalog Search Index refresh running slow or halting/freezing

Posted on April 29, 2010 by Benjamin

You no longer need to follow the instructions below, you can use the new tool on Magento Connect instead.

Please note. This article applies to Magento 1.4 and greater only.

A lot of people are starting to find that with large catalogues that the new indexing manager in Magento 1.4 may be starting to time out when generating Catalog Search Index – Rebuild Catalog product fulltext search index. This usually results in a blank screen when refreshing indexes or a index timeout error

This is largely due to a number of factors:

the number of products in a catalogue
the number of store views
the apache/lighttpd timeout setting
the php.ini maximum execution time & script input time
an already inflated catalogsearch_fulltext MySQL table
the lock file is still place from the previous time it ran preventing it running again

To remedy this issue, you’ll need to know a few facts. The catalogsearch_fulltext table is essentially re-built entirely when running this command. BUT, if you once had multiple store views (or forgot the delete the default French/German views), the table will contact a record for each product PER store view. So it is always best to truncate catalogsearch_fulltext before starting. Either log in via MySQL command line and run

truncate catalogsearch_fulltext;

or log into phpMyAdmin and hit the empty link.

For 1 store view with a dedicated server (2.5GHz Quad core), it takes an average of 0.003 minutes per product to insert the fulltext field.

So in our example, we have a store with 200,000 SKUs and a single store view, so

200,000 (skus) * 0.003 (time p/p) * 1 (store view) * 60 = 36000 seconds

Which means it would be silly to execute the command via the web server, it would be preferred to run a command line executable. The new index manager assigns a “process ID” to each type of index:

Product Attributes Index product attributes for layered navigation building
Product Prices Index product prices
Catalog Url Rewrites Index product and categories url rewrites
Product Flat Data Reorganize EAV product structure to flat structure
Category Flat Data Reorganize EAV category structure to flat structure
Category Products Indexed category/products association
Catalog Search Index Rebuild Catalog product fulltext search index
Stock status Index product stock status

In our case, number 7 is the offending (read: slow) command. So we can run this manually using a command line script instead. Change your php.ini for your php-cli installation to suit the maximum time out shown above (36000 seconds for us).

Then create a file in the root directory of your Magento installation, we’ll call it fulltext.php:

<?php

require_once 'app/Mage.php';

umask( 0 );

Mage :: app( "default" );

Mage::log(â

Explore

Explore our world and see what we can do for you

Magento

Recent Blogs

Sjoerd

Hi, I tried your script. But when using ssh i am getting this error.

Warning: require_once(app/Mage.php): failed to open stream: Inappropriate ioctl for device in /var/www/vhosts/xxx.xxx/httpdocs/web/fulltext.php on line 3
PHP Fatal error: require_once(): Failed opening required ‘app/Mage.php’ (include_path=’.:’) in /var/www/vhosts/xxx.xxx/httpdocs/web/fulltext.php on line 3
http://www.sonassi.com ben@sonassi.com

HI Sjoerd,

It sounds like you didn’t put the file in your root Magento directory.

“Then create a file in the root directory of your Magento installation, we’ll call it fulltext.php:”

By root directory, we mean the one in which the Magento installation is in (ie. the one which contains app,var etc.)
Sjoerd

Hi Ben,

The fulltext.php is in the root folder.
http://www.sonassi.com ben@sonassi.com

Hi Sjoerd,

Is it in the root MAGENTO directory?
Sjoerd

Yes. I build over 80 Magento shops. I know what you are talking about.
http://www.sonassi.com ben@sonassi.com

Hi Sjoerd,

This isn’t an issue with our script, but more with your PHP-CLI installation by the looks of things.

Compare both your Apache php.ini with your PHP-CLI php.ini – specifically the “include_path” setting.
Sjoerd

Hi Ben,

I know a thing or 2 about Magento but this is new to me. I can find my php.ini. It is in the etc folder. But I have no idea where to find my PHP-CLI php.ini. Can it be I have PHP-CGI and not CLI?
Anyway in the php.ini I found it says,

;;;;;;;;;;;;;;;;;;;;;;;;;
; Paths and Directories ;
;;;;;;;;;;;;;;;;;;;;;;;;;

; UNIX: “/path1:/path2″
;include_path = “.:/php/includes”
;
; Windows: “\path1;\path2″
;include_path = “.;c:\php\includes”

include_path = “.:”
http://www.sonassi.com ben@sonassi.com
Hi Sjoerd,

If you run this command:
```
php -i | egrep --color "include_path|php.ini"
```
It should identify both the php.ini location and the setting for include_path.
http://www.shoesx.cn jerry

I got a error :

Fatal error: Uncaught exception ‘Varien_Exception’ with message ‘Invalid method Mage_Index_Model_Process::_getLockFile(Array ( ) )’ in /home/geesangc/public_html/lib/Varien/Object.php:542 Stack trace: #0 [internal function]: Varien_Object->__call(‘_getLockFile’, Array) #1 /home/geesangc/public_html/fulltext.php(11): Mage_Index_Model_Process->_getLockFile() #2 {main} thrown in /home/geesangc/public_html/lib/Varien/Object.php on line 542

something wrong at there?
Philip

When I try and run the script on the command line (with fulltext.php in the magento root directory) I get the following exception:

# php fulltext.php
07/05/10 12:23:16
Fatal error: Uncaught exception ‘Varien_Exception’ with message ‘Invalid method Mage_Index_Model_Process::_getLockFile(Array
(
)
)’ in /var/www/storesdirect.co.uk/lib/Varien/Object.php:542
Stack trace:
#0 [internal function]: Varien_Object->__call(‘_getLockFile’, Array)
#1 /var/www/storesdirect.co.uk/fulltext.php(11): Mage_Index_Model_Process->_getLockFile()
#2 {main}
thrown in /var/www/storesdirect.co.uk/lib/Varien/Object.php on line 542

Any ideas why that might be? I am using Magento version1.4.0.1.
http://www.sonassi.com ben@sonassi.com

Hi Philip,

It could be a number of different things. Could you see if the lock file exists in:

./var/lock/

For the index you are running the script on
Philip

Hi Ben,

There was a lock file in ./var/lock/ for the index but removing it makes no difference; the same exception is shown when running the script.
depa82

Hi I created the script and launched
http://www.mywebsite.com/fulltext.php but it show me only the start date (and don’t trunc the table)….
What’s wrong?
Thanks
Stefy
http://classyllama.com David Alger
The problem here is that Ben’s code is calling a protected class method, and will throw this error. Also, the calls to _getLockFile and flock are actually uneeded, as reindexAll creates the lock file for the indexer when it’s called.

I would also recommend calling “php -f fulltext.php&” to start the script, which will put it immediately in the background and list it’s job number and PID. Then, (assuming you’re using the bash shell) calling “disown ” so you won’t have to worry about your SSH connection terminating and thus killing the script execution before it’s had time to complete.

Here is a modified and working version of Ben’s code. You should notice that I changed the calls to echo to Mage::log calls, that’s because the echo commands won’t print anything in a disowned process, just make sure you have logging enabled in your Magento config if you need this info.
```
getConnection(’core_write’);
$mysqli->query($sql);
$process = Mage::getModel(’index/process’)->load(7);
$process->reindexAll();
Mage::log(”Finished Rebuilding Search Index At: ” . date(”d/m/y h:i:s”));
```
http://www.sonassi.com ben@sonassi.com

Hi David,

Thanks for the comments. Sorry, we we’re being a little oblivious as we have our own index module – with public, rather than protected functions for those mentioned above (hence our compatibility). The fUNLock was purely to override any “floating” files left over.

I’ll give your code a test and update the post
Stefy

Can you post the complete code modified please??
Thanks
http://www.onesecurity.co.uk Richard

Can anyone confirm full working code?

Thanks!

Rich
http://www.sonassi.com ben@sonassi.com

Hi Stefy, it has been posted for you
http://www.sonassi.com ben@sonassi.com

Hi Rich, it has been posted for you
http://www.onesecurity.co.uk Richard

Hi Ben, many thanks, but I get:

Parse error: syntax error, unexpected T_STRING in /var/sites/o/xxxx/public_html/fulltext_search_index_cli.php on line 5

When I run this from CLI…

Kind regards

Rich
Stefy

Something work but i have this error :

PHP Fatal error: Uncaught exception ‘Exception’ with message ‘Warning: Division by zero in /home/magento/public_html/fulltext.php on line 8′ in /home/magento/public_html/app/code/core/Mage/Core/functions.php:245

My line 8 is:
$process = Mage::getModel(’index/process’)->load(7);

Please help help help!!!
dan

is there anyway to solve this problem without ssh access? the problem is that the Catalog Search Index is stuck on ‘processing’. any help is appreachiated.
http://dynagirl.co.uk Jason

Hi Ben – can you post the code here so the rest of us can get to it?

Thx
http://www.onesecurity.co.uk Richard

Chaps, I’m now getting:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘is_searchable’ in ‘where clause’

Anyone seen something like this before?

Rich
http://ims dave

Ran #php fulltext.php with no error but I can’t see that it did anything.

How do I know if it is working?
G

This is the most beautiful peace of code, that worked as miracle. I really thank ben for putting this online.

Needed to a little bit of tweak, but worked amazingly after that.
http://www.medicaldelivered.com Nick

Thanks! I created the file as suggested and first ran from the browser to make sure it was running correctly. It worked but of course timed out. I then logged in via SSH and ran the file. I had to cd to my public_html folder and then just run “php fulltext.php” no “#”.

The script worked and my catalog search fulltext index actually finished! I have 17,000 skus on a shared server.

Thanks again!
Nick Cron
Medical Delivered (www.medicaldelivered.com)
RichPC

Hi Ben,
Thanks for the article. I’d just done a 46,000 sku import and this index was taking forever to refresh.

To speed it up I went to the database table you mentioned ‘catalogsearch_fulltext’ and dropped the fulltext index from the ‘data_index’ field. Then ran magneto’s refresh on the index (via admin panel not cli). Then put the fulltext index back on the ‘data_index’ field. Doing this made it hundreds of times faster.

Cheers,
RichPC
RichPC

Sorry, had a false success from that dropping of the fulltext index, it was running faster because there was less data to process when i tested without the index – doh, what a shame, back to slow magento.
http://www.sonassi.com ben@sonassi.com

Hi Rich,

I’m not sure why it would be slower, this method is much more reliable.