Issue High CPU usage and poor performance (MYSQL)

[aromero]

Server operating system version

Ubuntu 20.04.2 LTS

Plesk version and microupdate number

18.0.58 #2

Hello, we're having some problems with the performance of the server. Right now, it looks like the CPU usage is almost at 100% all the time, and the process that is taking most of the usage is mysql:

As of today, we haven't figured out what to do in this case. We can't give more resources to the server since is already at it's maximum. Of course, this translates in poor performance on mysql databases aswell.

I've recently added this on the config file of mysql, but It didn't do much.

We need some help with this.

 

[MartinT]

Mysql? check the domain log, maybe some kind of a sqlinjection tryout

 

[aromero]

What is the domain log, the mysql.log? We had to disable the mysql.log because it would grow way too fast.

 

[Peter Debik]

# plesk db
> show processlist;

What does it reveal?

 

[MartinT]

e.G. Backend Plesk - Domains - Logs: you see time, ip, executed file - status of execution (200, 404..) and there you can look for strage execution with sql commands like create, select... and u can judge is it normal or not (likley php files are ued to exploit with adding "?BAD-COMMAND")
Or look at the different jounals at cli what Peter mentioned

 

[aromero]

This is what I get with the "show processlist"

 

[aromero]

One thing I've noticed is that after I enabled the mysql.log momentarily it increased 5GB in a matter of seconds, and checking the log the server is doing massive queries with thousands of IDs (Mostly PrestaShop queries). I'm inclined to think we (or some client) is getting attacked via PrestaShop queries.
(Multiply that by infinite, basically)

 

[Peter Debik]

I'm inclined to think we (or some client) is getting attacked via PrestaShop queries.

Yes, that seems to be just right. You'll probably also see something like frequently repeated requests in the access_ssl_log of the site with changing parameters (...?<parameter>=...).
Quick fix: plesk bin ip_ban --ban <offending ip address here>,recidive

Or, if the attacks come from different sources, finde the top requestors:
cat access_ssl_log | awk '{print $1}' | sort -n | uniq -c | sort -n
Then ban them (but watchout that you do not ban your own server IP).

 

[mow]

Might also a search engine crawler going through all your listing options. In that case you should probably not ban them if you still want to be found, but instead adjust the robots.txt and declare links to search pages as nofollow.