Magento catalogsearch_fulltext reindex

Question

I am trying to renidex (through CLI) magento catalog but the routine is hangs (or executing very slow) on catalogsearch_fulltext. I am tried to wait more 8 hours, but it didn't finish. There are 18k SKUs in catalog. Magento installed on a DigitalOcean VPS which has a 4Gb of RAM. I am tried to make truncate catalogsearch_fulltext - but that didn't help.

UPDATE: I am using Magento 1.9

UPDATE 2: I am deleted French and German stores, an reindexing finally finished: "Catalog Search Index index was rebuilt successfully in 03:03:16" It really should work so long?

Answer 1

The problem with Magento CatalogSearch Fulltext, apart the whole indexation takes place on PHP side, that when you search on the frontend, related database tables are locked, that slow downs it dramatically. If you have a possibility to avoid standard fulltext search in Magento, you should do, as it is not developed for huge datasets.

There are different options available like Sphinx, Solr, ElasticSearch that you can implement with less effort and better search results.

Answer 2

Your question is not so simple because there are many fix that you could check to increment speed of your magento.

1) If you are running a simple apache2 and mysql in same machine with 4gb of ram, your resource are too low: bottleneck can't be identified without a strong analysis and anyone here can't solve your problem without a full strong optimization. Anyway I'll give some tips helping you in increase performance:

• use php-fpm

• optimize your mysql:

-- increase IOPS to use SSD in a right way

-- check your swap usage

-- considerate to use a different server for your mysql server

• pay attation that digital ocean use vCPU with some limits in operations, mysql use a lot of CPU during this kind of operations

• use memcached

• I think that you are testing everything in a website that actually don't sell: be carefull because traffic will increase your memory usage

• tune your innodb (very important)
• update your php version to 5.4 at least My finally answer is: there is not a specific problem in your magento, it's a common problem that this instrument use a lot of resources.