'Add to cart' extremly slow and high TTFB - Profiling shows 2000 queries, 1000 table stress

[hitin]

We have a shop that runs on Prestashop v1.7.5.1 and the site becomes extremely slow once we add a product to the cart. Also, when we apply a coupon it takes about 10-20 seconds to apply it.

What we tried
We enabled CDN (cloudflare) , we also enabled server side caching. In the Prestashop the CCC is enabled for HTML & JS & CSS and we now have enabled the CacheMemcache. However, none of this seems to fix the slow checkout process. Ajax cart is enabled but still customers are reporting 10-25 seconds to complete each step.

We then tried to enabled the profiler and found strange values. So, we are guessing it is something wrong with the theme itself.

Since the log is huge. We are sharing few value and file is attached. We need help understanding what does these values mean.

Profiling numbers from the site are below

Querying Time 6119 ms
Queries 1821
Memory Peak Usage 47.0 Mb
Included Files 625 files - 8.66 Mb
PrestaShop Cache 0.01 Mb
Global vars 0.38 Mb

Top 5 Table Stress

912 category_lang
900 category
673 category_shop
193 creativepage_meta
124 cart_product

ObjectModel instances

Name Instance Source
Category 1554 /modules/posmegamenu/posmegamenu.php
CartRule 50 /classes/Cart.php
Carrier 22 /classes/Cart.php

Load Time.docx

Edited June 10, 2020 by hitin (see edit history)

[hitin]

40 views 0 replies!
Am I the only one who is facing this issue?

[Razi]

Kindly if possible send the site link. Thanks

[hitin]

@Razi the website link is https://www.craftingcrow.com

[Razi]

Kindly disable the ajax cart option and check the working of add to card.

if not effect the speed then enable the debug mode and check is there any error.

[hitin]

Thanks. I have tried it a few things including disabling ajax cart. I have cache enabled, Cloudflare CDN and also CCC enabled. Also, I use Prestashop cleanup tool to clear the junk data. But none of this has solved the issue.

I am guessing its to do with theme. Not sure though.

[hitin]

Here is something I found in the error_log

[11-Jul-2019 15:05:16 Asia/Calcutta] PHP Notice:  Undefined index: theme_store_id in /home/u153454032/domains/xxxxxx.com/public_html/var/cache/prod/smarty/compile/d4/ce/7a/d4ce7abe28cde0e6d26a41a8a40500a7c8_2.file.menu.tpl.php on line 36
[11-Jul-2019 15:05:16 Asia/Calcutta] PHP Notice:  Trying to get property 'value' of non-object in /home/u153454032/domains/xxxxxx.com/public_html/var/cache/prod/smarty/compile/d4/ce/7a/d4ce7abe28cde0e6d26a41a8a40500a7c8_2.file.menu.tpl.php on line 36

[Razi]

@razaro - @vekia kindly help in this case

[razaro]

Hi @hitin

sorry to hear you have those issues. It is really slow.

I am not sure if I can help but please provide some more information.

How many products/categories do you have ?

What hosting you are on and what kind of server it is ?

Do you have some voucher module or just lot of discounts/vouchers created ?

Please check your database directly, like with phpMyAdmin and see what are largest size tables?

 

My guess you have 30-50 000 products which is good amount, but what could be an issue is cart rules like  log shows.

For example I saw few times cart_rule_combination tables that are over 10GB and that can slow down a website to halt.

 

[hitin]

Hello @razaro , I really appreciate your response.

Quote

How many products/categories do you have ?

37K products and 243 categories

Quote

What hosting you are on and what kind of server it is ?

Hostinger's professional plan with 140 GB SSD, 6 GB RAM, 4 CPU Cores, 2X Speed Boost, Cloudflare Integration

https://www.hostinger.in/cloud-hosting

Quote

Do you have some voucher module or just lot of discounts/vouchers created ?

I have 88772 cart rules and no module

Quote

Please check your database directly, like with phpMyAdmin and see what are largest size tables?

Top 5 tables with size

Quote

I saw few times cart_rule_combination tables that are over 10GB

As per phpmyadmin cart_rule_combination is 6.5 MB in size

Edited June 17, 2020 by hitin
removed duplicate image (see edit history)

[rdy4ever]

On 6/17/2020 at 1:55 AM, hitin said:

Here is something I found in the error_log

[11-Jul-2019 15:05:16 Asia/Calcutta] PHP Notice:  Undefined index: theme_store_id in /home/u153454032/domains/xxxxxx.com/public_html/var/cache/prod/smarty/compile/d4/ce/7a/d4ce7abe28cde0e6d26a41a8a40500a7c8_2.file.menu.tpl.php on line 36
[11-Jul-2019 15:05:16 Asia/Calcutta] PHP Notice:  Trying to get property 'value' of non-object in /home/u153454032/domains/xxxxxx.com/public_html/var/cache/prod/smarty/compile/d4/ce/7a/d4ce7abe28cde0e6d26a41a8a40500a7c8_2.file.menu.tpl.php on line 36

Your theme uses an undefined variable

theme_store_id

This could cause the slow down. Try to switch to the default theme and see if your store is still slow. If it is, then the problem is you have too many cart rules to be able to run your store on shared hosting. Why do you need so many cart rules?

[hitin]

Thank you for your time and reply @rdy4ever

Quote

Your theme uses an undefined variable

Do you suggest I define them in the code?

Quote

This could cause the slow down. Try to switch to the default theme and see if your store is still slow. If it is, then the problem is you have too many cart rules to be able to run your store on shared hosting.

I switched to classic (default) theme but it almost same. So I guess we are now left with cart rules.

Quote

Why do you need so many cart rules?

We have a few partners who promote us and sometimes sell our discount code to their customers. On a average for each deal, we make around 10K cart rules. We can't remove these codes because some of the customers use these codes after 2-3 months from the data of purchase. And for this reason our codes are valid for 6 months.

Is there something that I can do differently to reduce issuing thousands of codes?

[J.Sahu]

What have you done to increase the add to cart speed? It must be adding more power to hosting I gues.