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

[El Patron]

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.

[El Patron]

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?

[JaredK]

I use PS 8.1 and was having increasingly more frequent 500 Errors, both on front and admin of my website. Lots of calls to 'upgrade the plan' and spend more dollars without getting to the bottom of it. I see this is an ancient problem with Prestashop, some of these very similar posts going back to 2011.

Probably everyone had their issues resolved, but I want to leave this here, in case some other unfortunate soul has the same issue and is frantically searching the internet... and perhaps they'll arrive here.

For most of the day I was struggling to try and get my site back up. Hours passed. Restore of both file system and database to previously working versions. This was the worst it had ever been. Nothing helped to rsolve the problem, and I was completely locked out of the admin side too, so couldn't even make any changes to the settings. I thought my coder had somehow messed something up when he made a modification. He thought so too, but couldn't find the reason for the 500 Error and finally gave up on the project.

I thought it was off that my CPU usage and memory would still spike even during times that both the website and admin side were down. Also at night. By fluke I looked at the analytics, the host support being not much help in figuring out the cause. I saw that there was huge traffic from Brazil, Banghladesh and other countries far away, and I proceeded to block access for any country I have no dealings with at all. Within minutes of doing so, CPU and Memory usage normalized. It's now been a full 6 hours since everything is calm and working perfectly.

Moral of the story: before you dig into mods and try to find the problem in the data structure or settings of Prestashop, check out what traffic you're subjected to and if you see extremely heavy traffic from countries or IP ranges that should not be on your site, block their access. On a small site, that's essentially the equivalent of a DDoS attack. Chances are, your problems will clear up.

II hope someone is going to be helped with this. Good luck

[El Patron]

MySQL configuration in hosting environments is frequently not optimized for performance and efficiency. In many cases, essential components, such as MySQL caching mechanisms, are not enabled or configured properly. This can lead to suboptimal database performance, resulting in slower query response times and increased load on the server. 

To enhance MySQL performance, it's critical to ensure that key features like query caching, connection pooling, and proper memory allocation are utilized. By implementing these optimizations, hosting providers can significantly improve the overall speed and reliability of their database services, providing a better experience for users and applications that rely on MySQL for data management.

Ensuring that MySQL is properly configured not only enhances performance but also contributes to more efficient resource utilization, reducing both server load and operational costs. Thus, it's essential for hosting services to prioritize optimizing MySQL settings and enabling all relevant features to support robust database performance.

[JaredK]

Am I correct in assuming that this is something the hosting company needs to do and that it is not something the user can implement?

2 minutes ago, El Patron said:

MySQL configuration in hosting environments is frequently not optimized for performance and efficiency. In many cases, essential components, such as MySQL caching mechanisms, are not enabled or configured properly. This can lead to suboptimal database performance, resulting in slower query response times and increased load on the server. 

To enhance MySQL performance, it's critical to ensure that key features like query caching, connection pooling, and proper memory allocation are utilized. By implementing these optimizations, hosting providers can significantly improve the overall speed and reliability of their database services, providing a better experience for users and applications that rely on MySQL for data management.

Ensuring that MySQL is properly configured not only enhances performance but also contributes to more efficient resource utilization, reducing both server load and operational costs. Thus, it's essential for hosting services to prioritize optimizing MySQL settings and enabling all relevant features to support robust database performance.

 

[El Patron]

Hi, generally speaking and from experience, no the hosting company will not support tuning of mysql unless the hosting package is fully managed and may not even then.

 

Login to phpMyAdmin
Go to your hosting control panel and launch phpMyAdmin.

Go to the "Status" or "Server Status" tab
This gives you live metrics. Look for values highlighted in red—these indicate areas where performance may be suboptimal.

Look for these key stats:

• Key buffer size – Important if using MyISAM tables.
• Query cache efficiency – If too low, consider disabling or tuning query_cache_size (note: query cache is deprecated in MySQL 5.7+).
• Temporary tables created on disk – If this number is high, increase tmp_table_size and max_heap_table_size.
• Sorts using disk – If high, consider increasing sort_buffer_size.
• Table locks / Slow queries – May need innodb_buffer_pool_size tuning or indexing.

Edited May 5 by El Patron (see edit history)

[Behc98]

On 5/5/2025 at 6:47 AM, JaredK said:

I use PS 8.1 and was having increasingly more frequent 500 Errors, both on front and admin of my website. Lots of calls to 'upgrade the plan' and spend more dollars without getting to the bottom of it. I see this is an ancient problem with Prestashop, some of these very similar posts going back to 2011.

Probably everyone had their issues resolved, but I want to leave this here, in case some other unfortunate soul has the same issue and is frantically searching the internet... and perhaps they'll arrive here.

For most of the day I was struggling to try and get my site back up. Hours passed. Restore of both file system and database to previously working versions. This was the worst it had ever been. Nothing helped to rsolve the problem, and I was completely locked out of the admin side too, so couldn't even make any changes to the settings. I thought my coder had somehow messed something up when he made a modification. He thought so too, but couldn't find the reason for the 500 Error and finally gave up on the project.

I thought it was off that my CPU usage and memory would still spike even during times that both the website and admin side were down. Also at night. By fluke I looked at the analytics, the host support being not much help in figuring out the cause. I saw that there was huge traffic from Brazil, Banghladesh and other countries far away, and I proceeded to block access for any country I have no dealings with at all. Within minutes of doing so, CPU and Memory usage normalized. It's now been a full 6 hours since everything is calm and working perfectly.

Moral of the story: before you dig into mods and try to find the problem in the data structure or settings of Prestashop, check out what traffic you're subjected to and if you see extremely heavy traffic from countries or IP ranges that should not be on your site, block their access. On a small site, that's essentially the equivalent of a DDoS attack. Chances are, your problems will clear up.

II hope someone is going to be helped with this. Good luck

I encountered a 500 error. It was resolved by changing the ownership of the files owned by root under the var/dev folder to the user. I don't know why it became root.