Magento CE with 1M Products - dealing with Indexing

Question

I'm operating a reseller website and have combined multiple suppliers which has resulted in a large catalog of a little over 1M product (500k+ products with 2 store views).

I've done all the usual performance tweaks, purchased a dedicated server and the site performs well with the exception of the indexes - they become invalidated daily due to inventory updates, price updates and the addition of new products that come into the feeds.

Regular indexing takes hours upon hours and pricing is not being updated properly resulting in selling a product for a loss.

I'm looking for a permanent solution to deal with this, I know it was addressed in EE 1.13

The introduction of incremental indexing reduces the need to perform a full re-index and most indexing operations are now automated - saving you and your staff time and energy to focus on revenue-generating activities. - See more at: http://magento.com/blog/magento-news/introducing-magento-enterprise-edition-113#sthash.v6XslKCZ.dpuf

@philwinkle mentioned the following here:

Product, URL Rewrite, and Inventory Indexing are the major issues when running a catalog this size.

There is 1 CE Edition module that I'm looking at (mirasvit Fast Asynchronous Re-indexing) but not sure if its the right way to go.

How are companies with large catalogs dealing with indexing in Magento CE? How can this problem be addressed in CE as it was in EE?

Answer 1

So the most problematic index in terms of performance is catalog_url. I have had a similar experience on some larger shops (although not as large as 1M products). The major problem was that the number of records in the core_url_rewrite table was much larger than (#products + #categories) x #stores.

I have found two causes for this, and found a solution/workaround for them.

Hope this helps!

Useless rewrites for products in category paths

Magento creates product rewrites for each category that it is associated with even when the setting catalog/seo/product_use_categories is set to false.

For example, when a product with url 'product-123' is assigned to category 'cat-1/cat-2/cat-3', the following rewrites are created for each store view (taking the default .phtml url suffix):

• /product-123.html
• /cat-1/cat-2/cat-3/product-123.html
• /cat-1/cat-2/product-123.html, when category cat-2 has been configured as a layer navigation category.
• /cat-1/product-123.html, when category cat-1 has been configured as a layer navigation category.

This can be circumvented by rewriting the method Mage_Catalog_Model_Url::_refreshProductRewrite:

/**
 * Refresh product rewrite, but only for non-root categories
 * when the admin setting "Use Categories Path for Product URLs" is enabled
 */
protected function _refreshProductRewrite(Varien_Object $product, Varien_Object $category)
{
    $categoryStoreId = $category->getStoreId();
    if (Mage::getStoreConfigFlag(Mage_Catalog_Helper_Product::XML_PATH_PRODUCT_URL_USE_CATEGORY, $categoryStoreId) ||
        $this->getStoreRootCategory($categoryStoreId)->getId() == $category->getId()
    ) {
        return parent::_refreshProductRewrite($product, $category);
    }
    return $this;
}

After applying this rewrite and setting catalog/seo/product_use_categories to false, the number of system rewrites should drop significantly.

Product URL key clashes

When two products have the same url key, each reindex round adds a new custom rewrite.

For example, for two products with id's 123 and 456 with the same url key foo:

• Reindex round 1:
  • System rewrite /foo-123.phtml > /catalog/product/view/id/123
  • System rewrite /foo.phtml > /catalog/product/view/id/456
• Reindex round 2:
  • Custom rewrite /foo-123.phtml > foo-124.phtml
  • System rewrite /foo-124.phtml > /catalog/product/view/id/123
  • System rewrite /foo.phtml > /catalog/product/view/id/456
• Reindex round 3:
  • Custom rewrite /foo-123.phtml > foo-125.phtml
  • Custom rewrite /foo-124.phtml > foo-125.phtml
  • System rewrite /foo-125.phtml > /catalog/product/view/id/123
  • System rewrite /foo.phtml > /catalog/product/view/id/456

To resolve this, make sure all product url keys are unique, for instance by adding the SKU to the url key.

After that, clear all useless custom rewrites from the core_url_rewrite table.

Answer 2

Disable mage_rss if you are not using it, it fires off lots of invalidation.

• Disabling modules - performance improvement?

Also Fast Asynchronous Re-indexing is a good approach since PHP is single threaded, it allows the indexer to use all the CPU cores in chunks speeding up the process.

I believe EE's approach was change management, only partially indexing what needs to be indexed, instead of fully reindexing the entire catalog.

• http://devdocs.magento.com/guides/m1x/ce18-ee113/EE113-benchmark.html#Section_4

Answer 3

Have you tried reindexing from the command line? Cd into shell folder and type

php indexer.php --info

It will show you all the available commands. For catalog price it is:

php indexer.php --reindex catalog_product_price

Answer 4

Assumptions: 1. Every merchant has each store/store view in magento 2. Every merchant product type they deal in defined for ex. Categories/nature of their products. 3. Attributes pre created in the system according to the nature of business. 4. Attribute set pre created in system. 5. There is abundance of resource in hardware ( CPU, RAM and Disk space) 6. The market place extension already installed like apptha unirgy etc.

I would like to work on most of indexing part r. As this is the one of beast needs to get out of this system. Importing needs to be done through some fast tool to import which completely sits out of the magento and it bootloader processes. I recommend MAGMI as I this is tried and tested tool and I have used with majority of my clients lile C&A marketing USA, Tolexo(India Mart ) India, Bigadda former reliance ecommerce site, shopelect.com ( Marketplace for Electical Goods B2B) India, Xwalker Inc. USA etc.

We are looking at the problem starting from core which is taking maximum turn around time in order to complete 1 merchant process.

Now the problem is: For each product these many process must run.

Product A Product Attributes Product Prices Catalog URL Rewrites Product Flat Data Category Flat Data Category Products Search Index Stock Status Tag Aggregation Data

I am trying here to offload After that, clear all useless custom rewrites from the core_url_rewrite table. Magento also creates rewrites for disabled and invisible products for sure we need to cut lot’s of corner for magento indexing process which is definitely an architectural issue of magento and where Java seems to be a best for these king of marketplace stores. In order to make it light weight we need reconsider the way re indexing works.

Breaking down the problem:

We are looking at majorly two main indexes everytime which needs to be figured out along the process: Product, URL Rewrite, and Inventory Indexing are the major issues when running a catalog everytime. Primarily the problem indexers are catalog_url which takes over 10 mins for 500 products. I try not to do it because it drives the cpu up to 400% and the site becomes unresponsive. The catalog_product_price takes 5 minutes with 500 products, catalog stock is about the same. As you can appreciate it takes close to 12 hours to complete and then 12 hours later they are invalidated and round we go. I can't be the only store owner with this problem. – We will work around this issue with userless core_url rewrites again. Useless rewrites for products in category paths For example, when a product with url 'product-123' is assigned to category 'cat-1/cat-2/cat-3', the following rewrites are created for each store view (taking the default .phtml url suffix): • /product-123.html • /cat-1/cat-2/cat-3/product-123.html • /cat-1/cat-2/product-123.html, when category cat-2 has been configured as a layer navigation category. • /cat-1/product-123.html, when category cat-1 has been configured as a layer navigation category.

After applying this rewrite and setting catalog/seo/product_use_categories to false, the number of system rewrites should drop significantly.

Product URL key clashes When two products have the same url key, each reindex round adds a new custom rewrite. For example, for two products with id's 123 and 456 with the same url key foo:

• Reindex round 1: • System rewrite /foo-123.phtml > /catalog/product/view/id/123 • System rewrite /foo.phtml > /catalog/product/view/id/456 • Reindex round 2: • Custom rewrite /foo-123.phtml > foo-124.phtml • System rewrite /foo-124.phtml > /catalog/product/view/id/123 • System rewrite /foo.phtml > /catalog/product/view/id/456 • Reindex round 3: • Custom rewrite /foo-123.phtml > foo-125.phtml • Custom rewrite /foo-124.phtml > foo-125.phtml • System rewrite /foo-125.phtml > /catalog/product/view/id/123 • System rewrite /foo.phtml > /catalog/product/view/id/456 To resolve this, make sure all product url keys are unique, for instance by adding the SKU to the url key.

After that, clear all useless custom rewrites from the core_url_rewrite table.

We will also look at the introduction of incremental indexing reduces the need to perform a full re-index and most indexing operations are now automated - saving you and your staff time and energy to focus on revenue-generating activities. Re-indexing on these process with fast asynchronous cache. Product Attributes Product Prices

Product Flat Data Category Flat Data

I would like to store and directly import the data from the sources directly into flat tables. I make them them get created on the free load of very first indexing of the process and use the variable structure of different attribute and attribute sets. So we have catalog and category flat tables pre-exists in the system.

The approach will be simpler.. We will going to indexing on the fly at the time of importing the products; We will use magmi for reindxing asynchronously. Never wait merchant to hold for this long. http://wiki.magmi.org/index.php?title=Magmi_Magento_Reindexer

So now using the structure for abundance of products. Product import form MAGMI in to the entity tables and flat tables while RE indexing it on the fly. Reducing search indexing time. Search Index

I would like to have the indexers placed inside the full text search tool. Like sphix or elastic search.

Assuming there would be a state table which is directly storing the merchant sync updates related data. Which will send the notification to merchants about there data syn completion status.

Answer 5

Honestly, from an IT architect's perspective, I believe Magento is not properly equipped to handle this use case. Magento uses indexers to "speed things up", but at the same time also causing new issues. To me, this feels like a design flaw, but at the same time, no easy alternative is available if you stick with PHP/MySQL.

If you take a step back and look at the broader architecture used in Magento, it's just wrong to try and use index tables in MySQL.

A Java developer could, for example, store all data in memory and circumvent all these issues. PHP is limited here, since it is not continuously running and would need to fill it's memory again and again for each request.

In order to solve this, my company (storefront.be) has decided to take a radical new approach by switching backends to a JVM-based solution capable of handling large catalogs in combination with MongoDB storage for handling the EAV side as 1 product = 1 document in storage and no need for 16 MySQL tables.

In the past, others have also moved their catalogs to MongoDB, but I'm not sure what they did about the indexers. For us, this is where Java comes in.

I realise this is probably not the answer you were looking for, but I hope it can help you to better understand the limitation and look for a higher quality, outside the box solution.