core developers poor performance of product listing with large amount of data

[riu]

Hi there,

on sunday we are launching a prestashop with around 50.000 products. We will grade up to around 150.000 products within the next 12 weeks. All products have around 5-15 attributes. We have heaps of variants, but do not use the variant functionality of prestashop. It is disabled. We have them in a seperate system and only rendering a variant configurator via elasticsearch on the product page that then redirects to the proper product details page.

Everything works pretty fine, except the performance of the product category/ listing pages. Especially when the category has lots of products. Product detail page performance is awesome! One category has around 40.000 products, and the page loading time is around 3.5 s which is way too slow. I gave everything I found so far a try. It's not related to php (7.2), debug mode, profiler, caching or something else.

Also when I disable the category tree and the faceted search module I do not get much better performance. I see that even if I load 24 products per page, there is around 120 queries against product page. When I enable faceted search module, there is around 4000 queries per page load. probably related due to our amount of attributes.

Does someone have a idea what to do? The only chance I see so far is really overwriting the core code with my custom queries or use elasticsearch to render products on client side. Maybe someone of the presta core team if there is a chance to fix this in prestashop?

Would really appreciate your help!

Thanks a lot,

riu

 

 

Edited May 8, 2020 by riu (see edit history)

[tomerg3]

What version of PS are you using?

Are you having issues with smaller categories?

What's the purpose of having so many products in one category?

[riu]

I am using prestashop version 1.7.6.5. With smaller categories everything works fine and page load times are well. I upgraded my server today to 16gig and 8 cores. I gave mysqld process 4 gig pool buffer size and raised all other cache sizes. My php-fpm process has 3gb of ram.

Just the category page with the around 40.000 products (level 1) is still slow, around 3-5 seconds load time. I have mapped those 40.000 products to one parent category (level 1) and divided them again to subcategories (level 2). The reasons for having so many products mapped to one category is that those are all variants of one product. Prestashop's combination/ variant feature is not enough for us, because it does not allow to have the following:

• important: delivery time per variant
• important: dimensions per variant
• own product description
• product features
• works with basePrice and baseWeight which gets really annoying on price updates of a single variant

That's why we decided to store each variant as a product which empowers the full features of a product and all those problems above described are gone. We then just visualize the products as variant with a configurator, querying data from elasticsearch that stores the grouping of variants.

The queries that get fired from the facetted navigation module and also from prestashop core (ProductLazyArray) are simply redundant and not well optimized for performance. 

My idea now is to write a custom rest api (maybe nodejs) that allows me to render the facets via elasticsearch and queries the products from mysql and skip the whole php code when opening a product listing page.

Would be great if you have any hint what option I could give a try else than writing custom code. Especially when I think about pushing more products into my shopsystem I a am scared that it won't be usable anymore.

[tomerg3]

I'm going to take a step back and say that I don't think that a solution to a problem with one product, should be creating 40,000 products.

Yes, PrestaShop's combination system only works well if you have a very small number of attributes per product, but it can be modified.

If you invested too much into your solution, I think there must be a better way than to have all 40,000 products in one category, instead I would try to break them down to smaller categories, and modify the search module if needed to search all the smaller categories.

To be honest, I think you need to reconsider the approach, and instead try to make the product work the way you need.

We created a module many years ago, which helps address the problem of having too many combinations, by changing the combination structure, and adding many more customization options for the module.

It will not do everything you need or want, but it could provide a much better starting point, for you to create the customizations you need.

You can find Attribute Wizard Pro at https://www.presto-changeo.com/prestashop-17-modules/139-prestashop-attribute-wizard-pro-module.html

[Henrik41]

I have the same issue. Too many product (30K in my case) and 400 categories using  Nginx, php 7.3 and mysql 8 on Ubuntu only one CPU. I noticed that my sites had a very high time to first byte. Obviously I have caching everywhere including database.

after reviewing the profiler there was tons of unecessary queries in my front page.

You can get your profile by typing this in the terminal:

curl -s -w 'Testing Website Response Time for :%{url_effective}\n\nLookup Time:\t\t%{time_namelookup} seconds\nConnect Time:\t\t%{time_connect} seconds\nAppCon Time:\t\t%{time_appconnect} seconds\nRedirect Time:\t\t%{time_redirect} seconds\nPre-transfer Time:\t%{time_pretransfer} seconds\nStart-transfer Time:\t%{time_starttransfer} seconds\n\nTotal Time:\t\t%{time_total} seconds\n' -o /dev/null https://xxxxx.xx

 

Tested with 30K products via curl command. Gtmetrix performance 30%

With a  complex template I bough on themeforest:

Lookup Time: 0.028560 seconds

Connect Time: 0.028866 seconds

AppCon Time: 0.040392 seconds

Redirect Time: 0.000000 seconds

Pre-transfer Time: 0.044713 seconds

Start-transfer Time: 0.088862 seconds

Total Time: 0.088902 seconds

 

With the basic template provided by Prestashop I get a Gtmetrix score of 75%

Lookup Time: 0.004222 seconds

Connect Time: 0.004419 seconds

AppCon Time: 0.013796 seconds

Redirect Time: 0.000000 seconds

Pre-transfer Time: 0.017617 seconds

Start-transfer Time: 0.048474 seconds

Total Time: 0.048512 seconds

 

reducing the number of categories from 400 to 10 didn't change much the profile and I only gained 3% in Gtmetrix performance.

reducing the number of products from 30K to 10 did another difference. Gtmetrix @ 84%

 

Lookup Time: 0.004250 seconds

Connect Time: 0.004432 seconds

AppCon Time: 0.014142 seconds

Redirect Time: 0.000000 seconds

Pre-transfer Time: 0.017972 seconds

Start-transfer Time: 0.040663 seconds

Total Time: 0.040694 seconds

Therefore the variables that truly matter:

1. The template that you use can destroy your time to first byte (use only tested template - I learned the hard way)

2. The number of products and category makes a difference (Mostly  products)

Makes me wonder if Prestashop is design for large product number.

Edited February 18, 2021 by Henrik41 (see edit history)