Prestashop 1.7.8.11 | Prestashop slowed down randomly

[PatoPest0]

Configuration and informations : 

 

Prestashop 1.7.8.11

Mariadb 10.11

 

We have some traffic on our website (~4000 visitors/day) + back office activity from our employees (10 employees)

 

What we tried so far : 

- Upgrading from prestashop 1.7.8.7 -> 1.7.8.11

- Enable Cache

- Empty Cache

- Enable CDN

- Enable LiteSpeed Cache (turns out not compatible with our theme)

- Clean all running process from our hosting panel (Quite effective but not a viable option)

- Checked performances (CPU Usage, RAM, HTTP Queries...)

- Checked PHP extensions and options 

- Maintenance Mode 

- Removed some permissions from our employees in the Back Office.

- Set up a local environment without facing the same problem

 

Problem :

We're facing an issue were our website is running smoothly and then is randomly slowed down by something and we were not able to identify what could be the root cause. It's to the point where our frontend is unreachable by our customers and our back office is really slow. This problem happens arround 5 times a day without recurrence nor in time or actions done from our end.

After quite a lote of research we noticed that our SQL Queries stack up when we run SHOW PROCESSLIST command but we're not able to tell if there's some process stuck as our hosting provider does not allow to check the logs.

 

Do you have any idea what could be the problem or any tips as to how to locate the problem.

Let me know if you need more informations.

Kind Regards.

[ComGrafPL]

Have you checked your database? Maybe it need to be cleaned? 4000 visitors/day, so a lot of connections, empty cart ect.

[PatoPest0]

3 hours ago, ComGrafPL said:

Have you checked your database? Maybe it need to be cleaned? 4000 visitors/day, so a lot of connections, empty cart ect.

Hi,

Thanks a lot for your reply,

Unfortunately we already truncated several times connection and guest tables (one of them had over a million rows) the only one that we did not touch yet is the cart table because a truncate is far less ressource consuming than a delete with a where statement. 

I'm currently trying to set up lite speed cache on development environment to see if it would fit on our production one but i'm not very succesfull with it at the moment.

Another lead that might be relevant is our current SQL engine which is MariaDB, I saw yesterday that Prestashop recommend using MySQL but my knowledge of the differences between the two of them is very limited.

 

I would take in consideration any other advices as I can test relatively quickly on our dev environment.

 

Kind Regards

 

[Prestashop Addict]

Le 18/04/2024 à 4:57 PM, PatoPest0 a dit :

Unfortunately we already truncated several times connection and guest tables (one of them had over a million rows) the only one that we did not touch yet is the cart table because a truncate is far less ressource consuming than a delete with a where statement.

1/ If you use Google Analytics or same external statistics, I recommend you to disable module statistics (IP, guest...) It generate a lot of queries and grow up database for nothing. Better than truncate periodically.

2/ Did you check on log files (apache and php) to see if you have errors, and/or ddos attacks?

[PatoPest0]

On 4/21/2024 at 5:36 PM, Prestashop Addict said:

1/ If you use Google Analytics or same external statistics, I recommend you to disable module statistics (IP, guest...) It generate a lot of queries and grow up database for nothing. Better than truncate periodically.

2/ Did you check on log files (apache and php) to see if you have errors, and/or ddos attacks?

Hi, Thanks for your reply. We will try to disable the statistics.

Last week the shop ran fine but it started to be slow again on friday afternoon and kept going up and down all week-end.

Unfortunately since we use a Cloud Entreprise on Hostinger we don't have access to a lot of logs from PHP and the SQL logs (For exemple we can't enable the slow query logs via phpmyadmin)

I will keep this topic updated if we find any solution.

Any suggestion is still welcomed.

 

Kind Regards

[Prestashop Addict]

Il y a 18 heures, PatoPest0 a dit :

Unfortunately since we use a Cloud Entreprise on Hostinger we don't have access to a lot of logs from PHP and the SQL logs (For exemple we can't enable the slow query logs via phpmyadmin)

Open a ticket at Hostinger and explain your issue, and ask us to check to ddos attacks.

[PatoPest0]

On 4/30/2024 at 7:24 AM, Prestashop Addict said:

Open a ticket at Hostinger and explain your issue, and ask us to check to ddos attacks.

Hi,

Thanks for your reply.

Already talked to the support multiple times, they are unable or not willing to help unless we upgrade our plan to a VPS. I asked for elevated privilegies on the database just to check the logs they refused, asked them again for elevated privilegies on the server again to check the logs and locate the problem, they refused.

Asked if there was ANYTHING they could do to help us and they gave us a GTMetrix report stating that we should optimize our SQL requests.......

 

We would like to get a VPS but we can't afford someone to take care of the server for many reasons (Security, Perfomances, Updates....) and our current plan seems enough to handle the traffic :

300 GB disk storage 

12 GB RAM

6 Core

Unlimited bandwidth

 

I'll keep this feed updated.

Kind Regards

[PatoPest0]

Hi,

Here is some update :

After looking for another issue on our website I found the profiling feature of prestashop that allows you to get a lot of data about page loading time, hook loading time, module loading time, number AND logs of requests that are called sorted by loading time.

It allowed us to identify that our theme (AngarTheme) generated a lot of heavy request that one of our module that came with the theme generated arround 1000 SQL queries when you get to the homepage and took arround 2000ms to load. Safe to say we removed this module immediatly as it was only for display purpose.

 

We are still monitoring this issue, I don't think it resolved the problem but it definitely helped the performances.

If you want to enable the profiling feature, you can go to config/defines.inc.php, scroll to line 46 and set profiling to true :

if (!defined('_PS_DEBUG_PROFILING_')) {
    define('_PS_DEBUG_PROFILING_', true);
}

Refresh the homepage and scroll down to the bottom of your website, you should be able to see the profiler.

I advise to do that on a development server first and in maintenance mode on your live server.

You can follow this tutorial if you need more informations (Sorry the only good and recent one i've found is in french) : 

 

I don't know why this feature isn't mentionned more often on the forums or in the official documentation but it is really useful in my opinion.

I'll keep this feed updated if I find anything else relevant.

 

Kind Regards