2

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?

Improve this question
3
  • You need more RAM. You don't have nearly enough. For 70k products, you should have at least 32GB. I would be inclined to question your definition of "tuned" (running tuningprimer/mysqltuner is not how you tune a DB). Proper server config and bottleneck identification (ie. read your instrumentation) is the key - any good host could point this out in an instant. I would imagine the other half your issue would be browser rendering of the 10,000+ DOM elements.
    – Ben Lessani
    Commented Nov 14, 2014 at 0:36
  • Could you point me where 32GB is required for this amount of products? Everything on my site is loading in sub second load times, this the one and only element where I'm having issues.
    – SR_Magento
    Commented Nov 14, 2014 at 13:33
  • Points to self. I know from experience (Sonassi hosts thousands of Magento stores). 12GB RAM is barely enough to run a small catalogue Magento store, let alone one with 70k SKUs. Have a look here - I could well imagine your MySQL tablespace is around ~8-12GB alone - which means MySQL needs to be allocated at least TWICE that figure for it to perform properly (not even factoring in the memory requirements of the rest of the server).
    – Ben Lessani
    Commented Nov 14, 2014 at 13:56

4 Answers 4

Reset to default
3

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?

Improve this answer
6
  • t would make sense to stick with defaults if I had 5k products, but I have much more. Manipulating large amounts of products by limits of 200 per page is just not an option. I'm sure there is a way to speed this up, thanks for your insight.
    – SR_Magento
    Commented Oct 15, 2014 at 0:44
  • Ah right, well if it's mass product editing you are after you should be using the export/import process to do this - far more efficient than working with vast product collections in admin.
    – Jonathan Hussey
    Commented Oct 15, 2014 at 6:40
  • Going through csv files isn't time efficient either. BTW Whats the largest database you've worked with?
    – SR_Magento
    Commented Oct 15, 2014 at 10:45
  • Well it's of course completely up to you how you do things, I'm just offering my thoughts on your best, most efficient course of action. Reading/writing directly to the database which is close to what the export/import process does it always going to be countless times quicker than doing the same through PHP methods. The largest database I have worked with is around 115K products, 800K orders.
    – Jonathan Hussey
    Commented Oct 15, 2014 at 13:56
  • I appreciate your thoughts, with those large databases have any of your clients not had the requirement to update the products? Maybe my question isn't worded right. Basically I have a script that updates the products names, descriptions and images with a 3rd party. The developer I hired made it so that I have a drop down to update them. So when I need to update products, I dont want to go by pages of 200. I go 5k, update and walk away. Should I include this in my question?
    – SR_Magento
    Commented Oct 15, 2014 at 14:40
1

As Jonathan Hussey has said, that's just how fast, or slow, your page will be loading with 5K- 10K products at once in the admin product grid.

To get more insight, take a look at Mage_Adminhtml_Block_Catalog_Product_Grid::_prepareCollection(). It already looks pretty optimized to me at a glance. If you can further optimize it, you could see some improvement, but I doubt it will be that much faster.

Improve this answer
1

I have a different approach to this problem.

I import the products into magento and then I have overriden the magento classes for product etc. So that they pull the product details / description (and layered navigation) directly from an external database.

Improve this answer
0

It seems to me like the whole question is based on a misunderstanding.

Manipulating large amounts of products by limits of 200 per page is just not an option

If you filter the Grid and "Select All", it will not only select the visible entries but all entries that match the filter.

Real world example:

screenshot

Side note: No merchant that I know would use the Magento backend to manage 70k products because it is horribly inefficient (as you noticed). A PIM that can export to Magento would be the better solution in the long run.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.