Tips to Speed Up Handling of Large Product Collections in Backend

backendgridperformanceproduct-collectionproducts-management

I'm dealing with a large database (roughly 70k products) to help me manage my products more efficiently it was necessary to modify the admin panel to allow larger collection of products. In my case 5000 products and 10,000 products for the View drop down under Manage Products.

The problem is that "Please wait" appears for a period ranging between 5 to 15 minutes before the products load and sometimes they don't load at all.

My store is running on a dedicated server [dual quad core Xeons, 12GB memory] so there is no reason why it cannot handle this, so I'm trying to track down where I can tweak some settings to make it more responsive.

The front end is lightning fast, the back end is fast with the exception of the large collection of products.

Mysql has been tuned, using Redis Cache and APC as a backend, not sure what else to do to speed this specific issue up. I'm sure those with large databases can benefit from this.

What settings can be tweaked in order to make handling of large collections of products more efficient in the Magento back end?

Best Answer

For this amount of data in one collection you aren't really ever going to get this to be as fast as you probably want it to be - no matter what spec hardware you are running on. Not only are you pulling a vast amount of data from the database in one call, you are also then processing that data with PHP to display it in the grid which is always going to be slow.

With the grid taking 5-15 minutes to load surely sticking with default behaviour makes the most sense?

You don't have nearly enough RAM

We have about 240k products
Available ram: 6GB
Threads: 32

You do not have nearly enough RAM for the amount of products you have. As a rule of thumb, we recommend at least 2-4GB RAM per logical core.

If you map out your possible memory usage:

64 PHP Threads with a max_memory of ~768MB = 24GB
240,000 Products will likely mean about 15GB of InnoDB table space
64 PHP Threads will warrant around of 128 MySQL connections, typically this comes at a cost of about 200MB per connection minimum
Backend storage for 240,000 products in Redis AND lzf compressed - will still consume about 6GB of RAM

So the total so far is 70GB of committed RAM - we've not even mentioned the OS etc.

Your hardware is dreadfully underspecified. I would suggest reading over this Magento server set up article for a bit of a feeler on how to progress.

Memcached doesn't support cache tags

If you're using Memcached (not a problem, its very high performance), then you are either storing cache tags or not. If you don't have a slow_backend defined - then you're not storing tags, which basically means your cache cannot discriminate between any of the different cache types - so you won't be able to flush them independently.

Have a read over this, http://www.sonassi.com/knowledge-base/magento-kb/what-is-memcache-actually-caching-in-magento/

We would strongly suggest switching over to Redis. It has its quirks and does need significant fine-tuning for larger stores. But as a whole will perform slightly better than Memcached with the real benefit of cache-tag support.

404's and FPC

FPC has a real problem, in-fact, all caching engines have a problem with 404s. The reason being, any old URLs still being crawled or linked to will land on a page that has to iterate through the entire core_url_rewrite table, try and find a match against all defined routers and namespaces before finally giving up and loading a 404.

Then caching a resource which has no value and will consume space in your cache storage. You'll probably find a huge proportion of your Memcached storage is actually being eaten by 404 content.

With large catalogues (240k products), you're certainly going to have your fair share of product turnover, and thus, changes in URLs and subsequently, many 404's.

FPC Invalidate vs. Clean

At the moment - and by default - FPC's behaviour is to clean the cache on changes, rather than merely invalidate the cache entry. We wrote an extension to alter this behaviour for an EE store to do exactly what you required.

Here's a quick patch to give you an idea of how to solve your issue.

app/code/core/Enterprise/PageCache/etc/config.xml
index 6a56a80..85ebc92 100644
--- app/code/core/Enterprise/PageCache/etc/config.xml
+++ app/code/core/Enterprise/PageCache/etc/config.xml
@@ -139,7 +139,7 @@
             <observers>
                 <enterprise_pagecache>
                     <class>enterprise_pagecache/observer</class>
-                    <method>cleanCache</method>
+                    <method>invalidateCache</method>
                 </enterprise_pagecache>
             </observers>
         </catalogrule_after_apply>

Don't run a crawler

If you've got decent enough footfall - we don't advise running the crawl tool, it generates unnecessary load. People/bots/crawlers browsing the site should keep the cache primed.

But to answer your question, if you look in the configuration file mentioned above - you see the cron schedule that has been defined for the crawl browsing window.

If you can afford stale content

And ultimately, if you have enough RAM. You could well benefit from increasing the TTL of content stored in FPC - to keep your cached data alive for longer.

In the <full_page_cache> tag in your ./app/etc/local.xml just define

<lifetimelimit>86400</lifetimelimit>

The lifetime is defined in seconds. You need to strike a balance between content freshness, performance and the amount of storage space you actually have available.

Why are you using a 3rd party caching extension with EE

You're paying a premium for FPC - which pains me to say, is very good. So why are you running 3rd party alternatives over the top. Remove it.

Put it this way. If your car was running badly - would you just add another engine in the boot to compensate; or just fix the engine already in there?

How to Add Filterable ‘Manage Stock’ Column to Catalog Admin Grid

Try replacing the line

 $collection->addAttributeToSelect('manage_stock');

with

        $collection->joinField('manage_stock',
            'cataloginventory/stock_item',
            'manage_stock',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left');

Your _prepareColumns function should look like this

protected function _prepareColumns(){
    $this->addColumn('manage_stock', array(
           'header'=> Mage::helper('catalog')->__('Manage Stock'),
           'index' => 'manage_stock',
           'type'  => 'options',
           'options' => array (
                           '0' =>  Mage::helper('core')->__('No'), 
                           '1' =>  Mage::helper('core')->__('Yes'),
                        )
    ));
    return parent::_prepareColumns();
}