Permanent solution for the common indexing issue

Question

We have developed some magento project with large inventory record and always face the indexing issue we have tried every thing found on the internet for solving day-to-day indexing issue like truncating the flat tables and re-index using CLI, setting the cron for indexing but this is our day-to-day headache facing with indexing issue.

We are looking for Permanent solution for this problem while we work on projects there are different scenarios like updating the products on daily basis or importing the products from some other feed daily.

Anyone having some best practices with this or some workaround please share them that will be much appreciated.

Answer 1

Its important to understand what indexes are slow and why

Catalogue complexity and ultimately store architecture will dictate how long a re-index will take - combined with the underlying infrastructure.

• If you've got 50,000 products and 10 store views, you can guarantee the few million rows in catalog_url_rewrite will take time to process.

• If you've got 100 products, but 5,000 attributes, you can guarantee the catalog_attributes or catalog_product_flat table will take an age to rebuild, or fall flat on its face

• If you have 1,000 products, but 500 searchable attributes, then catalog_fulltext_search will again take an age to complete

The solution to each and any problem you face isn't 1 size fits all, its about architecting your store properly; having the right infrastructure in place to support it and using a re-index frequency/strategy that both supports content recency and performance.

• Adding front-end caching won't help at all
• Throwing more hardware at the situation might
• Addressing the catalogue size/complexity will help
• Using third party indexing tools will help
• Externalising certain indexes (eg. search > SOLR) will help

There's also the case of evaluating whether certain indexes are even required. Using flat product/category doesn't always make all stores faster; we've seen it make stores much slower. So you might find that after testing performance before/after - they're not even a consideration.

Answer 2

tl;dr

There is no silver bullet solution. There are some workarounds, I suggest Sonassi_Fastsearchindex - but that's specifically for catalog search.

Perhaps disabling index updates on save - scheduling to run overnight - will provide some relief? Combined with adding more caching - memcached, Redis, APC - and a full-page-cache like Varnish (if you're running CE) may get you started. If you plan on using Varnish, look at Nexcess_Turpentine on github for a quick-start.

More information

The indexing issues - specifically catalog_url_rewrites - are well known and documented in the community. Magento has handled these in the Enterprise version because these are the customers who are most adversely affected. Many EE customers have 10k+ products and multiple store views, websites, etc.

However, if you have a large catalog and a large number of attributes you may find yourself in the position that indexing will take a long period of time - specifically catalog_url_rewrite, product_flat -- in that case my suggestion is not to fix the index run time length but rather to offload some processing to allow the box to spend CPU cycles indexing rather than serving content.

The questions to ask yourself:

• Am I losing business due to indexing issues?
• Am I losing productivity due to indexing issues?
• Am I at risk of losing conversions or is my conversion rate suffering?
• Are my customers at risk of purchasing items out of stock that are a direct result of indexes being out of sync (inventory, etc.)
• Are my catalog pricing rules part of my core business and
• Is my on-site-search conversion rate higher than norm (8-10%), thus benefitting from better indexing?

There is no silver bullet solution to this particular issue - as a solution provider you should help your customer make the decision that will best improve sales and the business while keeping overhead costs low.

Alternatives

Offload catalog search and layered nav to Solr.

Scale horizontally. Add more Apache/nginx servers. More servers = more concurrent throughput. This isn't 1:1. Nexcess has a great whitepaper on performance and Apache configuration here: http://www.nexcess.net/magento-best-practices-whitepaper

And, if you opt to go with Varnish - remember:

Answer 3

In most of the heavy Magento webshops it's mostly been so hard to get the Magento backend Index Management working. I have had this issue often. Running the shell script all the time by the developer is often hectic. Usually I do fix this issue permanently like this.

I create a new copy of shell/indexer.php > shell/myindexer.php

Customize shell/myindexer.php some around line 154

} else if ($this->getArg('reindex') || $this->getArg('reindexall')) {

To

} else if ($this->getArg('reindex') || $this->getArg('reindexall')  || $this->getArg('reindexallrequired') ) {

and, add this check around line 166

//reindex only if required
if( $this->getArg('reindexallrequired') && $process->getStatus() == Mage_Index_Model_Process::STATUS_PENDING )
    continue;

before

$startTime = microtime(true);
$process->reindexEverything();
$resultTime = microtime(true) - $startTime;
Mage::dispatchEvent($process->getIndexerCode() . '_shell_reindex_after');

And then I add the new shell script to cpanel cron to run in every 5 minutes

/home/public_html/shell/indexer.php --reindexallrequired >/dev/null

As above shell script runs every 5 minutes and it reindexes only the processes that require reindexing, it reduces risk of the heavy load to the server cpu as well as the whole process of reindexing is very fast. If no process require reindexing, it will simply not run the reindexing process. Also, remember to put the reindexing mode to "Update on Save" in Index Management page. If you don't know, you can get this option in Actions > Change index mode beside Submit button.

Answer 4

It would be easier to say if you could give some more data (inventory size, visitors, machine), but here is a possibility:

• we use Sonassi_Fastsearchindex Extension for Catalog Search index. Although it just indexes title, description and sku (I think I have noticed), it works great and reduces catalogsearch indexer time.
• there will most likely be some indexers that you don't have to run, i.e. for tags or for product attributes. It is sometimes enough if you do only price, product flat, category products and catalogsearch regularly, and the others maybe daily.
• we synchronize products with an external system every two hours, and in the meantime, we index with php-scripts. So, we have a cronjob for each indexer we want to run to a certain time, and let this cron execute the script. This appears to be the best middleway between what the server can do and up-to-date product data.

This is running on Magento CE 1.7.0.2; still a pain, though ;)

Answer 5

using Dnd_Patchindexurl I was able to cut catalog_url_rewrite reindex time to nearly 70%

I think it's a good solution to exclude disabled products or not visible products to have their URL created for nothing!

$ php ./shell/indexer.php -reindexall
Product Attributes index was rebuilt successfully in 00:00:11
Product Prices index was rebuilt successfully in 00:00:22
Catalog URL Rewrites index was rebuilt successfully in 00:08:49
Product Flat Data index was rebuilt successfully in 00:00:51
Category Products index was rebuilt successfully in 00:00:19
Catalog Search Index index was rebuilt successfully in 00:00:12
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00

After:

$ php ./shell/indexer.php -reindexall
Product Attributes index was rebuilt successfully in 00:00:12
Product Prices index was rebuilt successfully in 00:00:24
Catalog URL Rewrites index was rebuilt successfully in 00:02:52
Product Flat Data index was rebuilt successfully in 00:00:57
Category Products index was rebuilt successfully in 00:00:25
Catalog Search Index index was rebuilt successfully in 00:00:13
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00

I installed it on 1.9.1.1 and working very fine!

Can be installed through Connect too http://www.magentocommerce.com/magento-connect/catalog/product/view/id/15074/s/dn-d-patch-index-url-1364/category/12863/

Answer 6

Upgrade to EE 1.13. The indexers were heavily improved in this version.