Large product numbers (~ 1 million)

[murdocmk]

Is there anyone using PrestaShop that has imported large numbers of products (anywhere near 1 million) that could share their performance experiences with us?

 

We're working to load around a million products and have bumped into a number of challenges.  It seems that we're pushing the out-of-the-box design limits of the PrestaShop application and we're just looking for someone to share information with in terms of choosing/disabling modules, tuning PHP/MySQL, comparing server hardware, and organizing data.

 

We've seen most of the "common" resources for performance/optimization tips.  We're looking for people/organizations that have gone the distance and worked with very large PrestaShop stores.  At this stage I think we're beyond the typical tuning steps and would like to get some specifics from others that have made it happen (if indeed anyone is out there with 500,000+ products).

 

Thanks,

Mark

 

[Dh42]

What specific issues are you having? I have worked with several large shops and have experienced a few different issues before. 

[murdocmk]

Dh42,

 

Thanks for responding.  I could probably carry this thread on for a bit, but let me catch you up a bit on what we're currently doing:

 

• We have a hosted server solution with 2 x E5520 2.27 GHz CPU, 4 GB of RAM
• We have been using eMagicOne's Store Manager to import products, in batches of around 50K products, because we found that larger imports had a higher probability of failing (getting interrupted, generating errors on import, etc.)
• The product CSV's we're importing are fairly large, with 58 fields and a rather long description field in some cases.
• We are not using any product attributes.
• We have 43 features per product (not all of which exist for every product).
• We have 7 categories, and products can be members of more than one category.  Therefore, a given category can contain a large number of associated products.
• We have been importing images using PrestaShop's native import feature, which has been faster in our testing than using the Store Manager image import options.  It appears to be roughly twice as fast.

 

Some of the issues we've encountered:

• Importing products is fairly slow.  Store Manager is *significantly* better than the default PrestaShop import process, but we're still seeing around 5-10 products/second (more during initial loads, less as we proceeded) and around 2 images/second.  It's doable, but it's a long road to get a million products and images in there, and making a data mistake or change means a *lot* of time.
• At around 400K products, the site is already pretty sluggish.  Searching a product can take quite a few seconds and clicking on a category can even take a matter of minutes.  We've noticed that adding some modules, such as Advanced Search4, slowed the site to a crawl.
• We had to make a PHP memory adjustment just to get the product search indexing working.  It seems that we have to go back and run it multiple times to get it to cover all the products, and we've only managed to get about 50% of our imported products indexed so far.  This is a pretty important issue for us - we're trying to understand a good strategy for getting indexes updated as we add new products, in such a way that we feel confident that all of our products will be searchable.
• With the performance issues we've seen so far, we're certainly concerned about doubling the number of products and adding users.  If a single test user clicking a category can result in 5-10 minutes of MySQL running at 100% CPU, we're not going to be able to support any users on the site.
• We've seen a number of warnings in our MySQL stats that suggests that some additional tunings may be needed.  Things such as "Created tmp disk tables", "Handler read rnd", "Handler read rnd next", "Innodb buffer pool reads", "Opened tables", "Select full join", "Slow queries" and "Sort merge passes".  We've made some adjustments to variables in MySQL and have seen some improvements, but some of those stats refer to potential problems with table indexes and we have not felt comfortable/knowledgeable enough to modify them.  We have some concerns whether the PrestaShop out-of-the-box indexes are really tuned for large sites.

Any thoughts, comments or guidance would be much appreciated!  Be glad to answer any questions too.

 

Thanks,

Mark