Recently viewed items module bug with large catalogs [SOLVED]

[J. Jackson]

I recently started working on a new PrestaShop site for a customer with 3.4 million items in their product catalog. During testing, I increased the number of items 10x for each test, starting at 1000 items. When I got to 1 million items, the site started behaving very poorly, requiring me to up the PHP process limit to 512MB just to display a product page.

After enabling the profiler, I noticed that a query which pulls back the entire list of active items in the product table is executed 3 times. At 1 million items, this is 3 million rows being fetched and loaded into RAM.

The query:

SELECT SQL_NO_CACHE p.id_product FROM ps_product p WHERE p.active = 1

Being executed by:
/modules/ps_viewedproduct/ps_viewedproduct.php:317

This is executed 3 times during the rendering of the product display page. This will never work for large catalogs.

After disabling this module as well as "items in the same category", the page execution time dropped from >4sec to 19ms and the memory usage went from 460M to 5MB. While I would expect load times and memory usage to be higher with these modules enabled, they are both fetching far more data than necessary to display the intended information (the number of items in the database doesn't change the amount of information these modules need to render).

Edited April 30 by J. Jackson
solved (see edit history)

[Prestashop Addict]

Please can you specify PS version

[J. Jackson]

Sure. I tested this with both 8.1.2 and 8.2.1 using the default template. They both exhibit the exact same behavior.

The installs were part of the Turnkey Linux (8.1.2) and Bitnami (8.2.1) appliance templates. 

If the PrestaShop software is settled on as an option for this particular deployment, the intent is to use AWS Lightsail Bitnami appliances or possibly a set of custom built EC2 instances with manual installs of the software.

[Prestashop Addict]

You are right, this is not well coded for a large catalog. If you have php skills you make a pull request to change query by something using IN sql syntax.

    'SELECT p.id_product
    FROM ' . _DB_PREFIX_ . 'product p
    WHERE p.active = 1 AND p.id_product IN ( list of ids from cookie)'

 

[J. Jackson]

I have submitted a pull request with fixes to this module. It is now working properly with large catalogs. See the attached profiler comparisons (these are the results of the product detail page with the Viewed Products module enabled and using a catalog with 1,000,000 products loaded.)

Pull request link: https://github.com/PrestaShop/ps_viewedproduct/pull/37

Edited April 29 by J. Jackson
Add pull request link (see edit history)

[Nickz]

If your shop complains about 512mb up the memory to 4096mb or higher.
I trust you are on a dedicated server.

Is your client aware that he puts all his egg in one basket?
One hack and all is down and to sort out a shop with 3 million products is much harder than one with 10 000.

[J. Jackson]

Thanks for the feedback, but please be aware of what you are suggesting. That number is per PHP process. Allowing a single PHP process/page to consume 4GB of RAM is a recipe for disaster. It is not 512MB for the server as a whole. The web server instance I posted profiler data for has 8GB of RAM allocated to it.

My client has been in business for nearly 30 years (they opened their online shop in 1997). We are migrating the web site off of a very old and largely abandoned product (Interchange) and evaluating different options - PrestaShop being the front-runner for a potential replacement e-commerce product. They sell replacement parts for outdoor equipment and small engines. The sheer number of products is because they have a catalog of every nut, bolt, washer, o-ring, etc for thousands of such products.

The eventual infrastructure for the site will be multiple web servers sitting behind HAProxy based load balancers. MySql will be placed on a dedicated system, with an eventual move to a master/slave configuration.

Everything will be hosted within Amazon Web Services on either custom built EC2 instances or within their Lightsail product offering. Horsepower will not be a problem... assuming the PrestaShop core isn't doing things like what this issue detailed.

[Prestashop Addict]

Il y a 10 heures, J. Jackson a dit :

I have submitted a pull request with fixes to this module. It is now working properly with large catalogs. See the attached profiler comparisons (these are the results of the product detail page with the Viewed Products module enabled and using a catalog with 1,000,000 products loaded.)  Pull request link: https://github.com/PrestaShop/ps_viewedproduct/pull/37

Fantastic time saver 😉 Don't forget to change your title post by adding [SOLVED] and add a Thanks or Like to the good answer, this will help community.

[J. Jackson]

Done. Thanks again.

[Nickz]

12 hours ago, J. Jackson said:

Everything will be hosted within Amazon Web Services on either custom built EC2 instances or within their Lightsail product offering.

I would stay away from amazon,in general huge companies don't have good customer attentions. They also love to raise prices.
This should interest you: https://github.com/PrestaShop/PrestaShop/discussions/32694