Mysql process high CPU usage

[fraizor]

I had been using Prestashop since 2017 for my startup company since PS 1.6

Serious usage was from PS 1.7.4 till now using 1.7.7

on all of these releases i had been facing performance issues that occurs when we have a spike with visitors.

when we have less than 20 visitor's everything is ok, however when they are above 30 the issue start happening... 

The issue is that mysql process is eating too much CPU , which is causing the website to be very slow 

as you can see in screenshot below Mysql process is using 175% CPU , followed by FPM processes 

 

i had consulted multiple developers, sysadmin, web agencies, all of them failed to solve the issue or square it in a specific module.

one agency said that this is related to the core structure of prestashop and due to the unoptimized nature of the code, mystore have 150 category and 80k products .. simply PS cannot handle them in an efficient way 

 

Any advice / suggestion is highly appreciated , i am really stuck here and this issue is deeply hurting our business 

 

AWS EC2 x.large instance ( 4 cpu, 16GB RAM) VPS

CentOS v7.9.2009 kvm

Prestashop 1.7.7.8

 

 

 

[ComGrafPL]

Do you use any module for optimizing db, page speed etc? If issues are caused from PS side, try it out. I have used couple modules on clients stores and the are working a lot better. ( not so huge stores as yours ).

[fraizor]

Thank you for your response @ComGrafPL
actually i had tried 3 of the best seller cache modules on the marketplace ..
unfortunately they did not fixed the issue 
while i felt the website is a little faster when it is enabled , still the CPU issue is exactly the same
that is why i uninstalled them  

[SmartDataSoft]

@fraizor

Cache module can't fix your this issue. Suppose you have huge visitor , those visitor log's saved in db. so you need to clean your store db frequently, using cron is good.

 

Secondly, you need to find which module cause this issue. if they call the db frequently without cache. Optimize this two your mysql request will reduce

 

Thank you

[fraizor]

Hello @SmartDataSoft

Actually I have All the stats module disabled ... 

 

regarding finding the bad module , how can I do this ? Debug profiling is not showing the perfoamce of each module on a Spesific page ...

[Toulousain]

Hello fraizor, Have you found a solution to your problem ?

Thank you

[PrestaHeroes USA]

using phpmyadmin, review your mysql config, we have never seen a hosting that was had more than default values.  

we do this first as part of our performance or new server migration, this is one of the most important performance works that can be done but is overlooked 

also there are more affordable nvme hardware hosting options, highly recommended....ovh has some vps nvme, we offer inhouse clients vps nvme on our dedicated....

 

[fraizor]

9 hours ago, Toulousain said:

Hello fraizor, Have you found a solution to your problem ?

Thank you

Yes, use litespeed instead of apache

[Toulousain]

On 6/1/2022 at 4:11 PM, El Patron said:

using phpmyadmin, review your mysql config, we have never seen a hosting that was had more than default values.  

we do this first as part of our performance or new server migration, this is one of the most important performance works that can be done but is overlooked 

also there are more affordable nvme hardware hosting options, highly recommended....ovh has some vps nvme, we offer inhouse clients vps nvme on our dedicated....

 

Thank you very much EL PATRON, your answer helped me a lot

[Toulousain]

On 6/1/2022 at 9:33 PM, fraizor said:

Yes, use litespeed instead of apache

Thank you very much Fraizor for your answer.
Switching from Apache to litespeed seems a bit complicated to me.

[PrestaHeroes USA]

20 hours ago, Toulousain said:

Thank you very much Fraizor for your answer.
Switching from Apache to litespeed seems a bit complicated to me.

Ngnix with php fpm's is faster but if u do not already have I highly recommend nvme to ssd.  Solves most issues with performance all around.

You say you contacted dev and agencies, we solve these issues for clients often and quickly.

[metacreo]

THE BEST OF THE  BEST ANSWER: Roll back or transfer your project to Prestashop 1.6 or run 1.7 on real server.
Because compared to 1.6 presta 1.7 its garbage. It has long since become marketing.
I tell you as a developer who has been working with PS engine for over 10 years and has a lot of prestashop projects at the top of google.

Implementation of a serious large project is impossible at PS 1.7 on VPS.

Facts:
Version 1.7 requires many times more server resources.
1.7 сannot be used without maintenance, and should be maintained by an experienced programmer with skills.
1.7 with curves and huge database queries require serious powerful database servers.
Throw out your VPS, VDS and all other services that hosting companies offer, because PS 1.7 almost normally works only on a real server with a powerful CPU and a lot of RAM for the database.

One of shop I have running on VDS / Debian / 32gb ram / 8 cores cpu / 240 SSD
Second shop REAL / Freebsd / 64 ram / 4 cores / SSD
PS on real server works without performance problems and even calmly holds DDOS attacks.
VDS starts to lag already at 10-20 one-time connections

On real server mysql running with default config and on vds mysql tunned for max perfs.

Do not forget that hosting companies sell the same resource to dozens of users. What stable performance are we talking about?