22

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.

  • I have wasted a year in Magento and its extensions and its extremely inefficient and idiotic data architecture that makes an ecommerce site with mere 10K plus products crap out. All these warnings should have been given to any one beginning to see Magento CE. Magento onwers should be taken to court for wasting thousands of man hours. Just let a database do indexing, do not do a database's job. I advice that instead of wasting money on a dedicated server and then tonnes of overnight sleepless work hours, better to move to a hosted ecommerce platform or an open source that uses MS SQL server. – semiprecious.com Oct 21 '13 at 5:21
  • Did you ever think that maybe you didn't find the right extension, or the right server configuration? If some software does not fit your needs doesn't necessarily mean that it's useless. I've been earning my bread (and beer) for the past 5+ years from Magento and I had a lot of satisfied customers also. Some with more than 10k catalog. – Marius Oct 21 '13 at 6:39
  • They are correct, due to the way CE works data maintenance is a problem with 10s to 100s thousands skus. EE is better due to the indexing updates they have made but that is for $mulit-million revenue companies. You can throw hosting at it but you will turn your ROI negative. The solution we use is very specialist & delta processes uploads similar to solutions such as SAP & Walmart use, combined with a special pricing solution (ATG-esque) which bypasses the indexing issue (fx & inline margin/attribute recalcs), combined with cluster hosting. Simple answer no, Magento wasn't designed optimally. – user2935 Jul 25 '14 at 20:11
30

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.

7

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:

enter image description here

  • We appreciate the props, but re-indexing has nothing to do with front-end caching; its entirely a back-end operation. Alleviating front-end load will prevent a re-index taking longer, but certainly won't make it faster. – Ben Lessani - Sonassi Apr 17 '13 at 17:32
  • What I'm getting at is reducing traffic coming to the box. The ultimate concern here is the site becoming unavailable during index or being locked for an unknown period of time while jobs run. At the end of the day if indexing had no negative effect on the frontend it wouldn't matter how long the job runs. There is no fix or improvement to indexing load times. Nobody wants a "Upgrade to the paid version" answer - so my suggestion is improving your frontend availability and schedule the index to run off-peak. – philwinkle Apr 17 '13 at 17:48
  • Absolutely, I understood that - but whilst availability is important for a website; its not enough for an e-commerce site. If you can't actually make a purchase due to indexes being locked up, then the site might as well be off-line. – Ben Lessani - Sonassi Apr 17 '13 at 18:06
  • we only have a few hundred products and it still takes several minutes to save a simple product on Magento 1.7, and I pay over $500 a month for a dedicated Rackspace server. I'm not sure where to start, but I suspect some index is perhaps corrupt maybe. Can anyone recommend a good magento consultant? – Max Hodges May 8 '13 at 8:37
5

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.

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 ;)

  • We are generally facing issue with product flat all other indexes are fine. – ravisoni Apr 16 '13 at 18:11
3

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/

1

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

  • 2
    But most client prefer to community version. – ravisoni Apr 16 '13 at 13:17
  • 1
    Agreed. 1.8 will be out in a couple of weeks but it will most likely not include the indexer optimizations. I don't like it either, but this is the easiest, safest and maybe cheapest way to get your indexers perform. – Paul Grigoruta Apr 16 '13 at 13:34
  • is this is impossible to find a permanent solution. – ravisoni Apr 16 '13 at 13:49
  • In most cases, where someone has so many SKUs that they are really running into a brick wall with the existing CE 1.7 indexers, then they should go with EE 1.13. There are plenty of smoothly running sites out there with these CE 1.7 and EE 1.12 indexers having 10-25k SKUs. The key is managing them right on a workflow level mostly and having the right infrastructure. – davidalger Apr 16 '13 at 21:30
  • CE is a perfectly adequate choice. The features in EE 1.13 are bug fixes - that the community have driven into CE anyway. Irrespective of that and regardless of whether you use CE or EE - indexing time will always be entirely dependent on catalogue complexity, server configuration, visitor concurrency and re-index frequency. EE is no magic bullet, and certainly not an appropriate solution for any architecture related issue. – Ben Lessani - Sonassi Apr 17 '13 at 17:28

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.