4

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?

  • From what is announced on that extension page, it sounds similar to what EE does. For your use-case you should check with the vendor if stock and price indexes are covered by it as well and if/how it can work together with your (non-manual) product update procedures.My 2 cents. – hakre Jul 26 '15 at 14:34
  • From what I have seen, each indexer is a completely different story. So it might be helpful if you can indicate the average running times of each indexer (or at least indicate which indexers are the slowest). – Aad Mathijssen Jul 28 '15 at 21:04
  • Primarily the problem indexers are catalog_url which takes over 6 hours, I try not to do it because it drives the cpu up to 1600% and the site becomes unresponsive. The catalog_product_price takes 1 hour 36 minutes, 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. – SR_Magento Jul 29 '15 at 9:25
  • i have the same problem with one of store having 1000K products and i sort out it with mirasvit reindex module and its really works.. – Bhupendra Jadeja Jul 31 '15 at 6:10
  • @Bhupendra 100k products or 1000k products? Did all indexing and performance issues get resolved? – SR_Magento Jul 31 '15 at 10:07
5
+50

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.

  • Actually you make a very valid point - I do have a large amount of products that are duplicates being that my suppliers overlap products and they do have identical url keys - (one of the identical products is always disabled the product with the lowest price and stock is enabled) – SR_Magento Jul 30 '15 at 14:10
  • Magento also creates rewrites for disabled and invisible products. See this stackoverflow post for more information: magento.stackexchange.com/questions/17607/… – Aad Mathijssen Jul 30 '15 at 14:36
  • I've tried the Dn'D patch and it did reduce the amount of time, but while indexing the incoming requests eventually drive the CPU for MySQL up to maximum resources until it is completed. Was this your experience? That second module looks like it might do the trick for this index. The thing is that I'm hesitant to start playing with the rewrites because I don't want to hurt the links that are indexed by search engines..... I've read were companies just drop the table cross their fingers and recreate. Any other insight would be greatly appreciated. – SR_Magento Jul 30 '15 at 21:35
  • I didn't use the full Dn'D patch, I only needed the one rewrite above to fix my issues (in most of my projects there are hardly any disabled or invisible products). The fix for the category paths reduces the number of system rewrites and shouldn't have any impact on search engines, as the links will not appear anywhere on the site and are not part of the sitemap XML. For custom rewrites you should be more careful, as rewrites are also created when the url key of a product or category has changed. – Aad Mathijssen Jul 31 '15 at 6:04
  • Where do I drop in that code you provided to give this a try? & can you confirm that in the larger store the processor would drive up to maximum resources during a catalog_url reindex? – SR_Magento Jul 31 '15 at 9:57
2

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

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.

  • The indexes are being invalidated due to stock and pricing updates. Mage RSS was already disabled thanks – SR_Magento Jul 27 '15 at 12:43
0

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
0

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.

-1

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.

  • this is Magento-related community, you're really not helping... – Jonathan Day Dec 22 '15 at 1:54
  • I know, but it might help people who aren't married to Magento find a different solution for this specific case, or it can be seen as background info. – Wouter Dec 27 '15 at 19:03

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.