MySQL high CPU load, nothing releavant on processlist

[manoaratefy]

Good morning,

I'm facing high CPU load on one of my customer's cPanel server. After checking, I see that MySQL is the most using CPU (122% CPU usage on htop, 12 cores servers, 20+ load average). I checked running queries by "show processlist", there is almost nothing here.

How could I find which website is overloading the server?

Thank you in advance.

 

[GOT]

What is the output of

mysqladmin stat

?

Finding the specific user if the running queries are not obvious is tricky.

 

[manoaratefy]

What is the output of

mysqladmin stat

?

Finding the specific user if the running queries are not obvious is tricky.

[root@web46 ~]# mysqladmin stat
Uptime: 78960 Threads: 223 Questions: 166174857 Slow queries: 3651 Opens: 1296424 Flush tables: 1 Open tables: 5000 Queries per second avg: 2104.544

 

[GOT]

2100 qps is pretty high I'm not surprised you are seeing load issues. I would start with looking at which sites are using the most bandwidth. It would not surprise me if one or two are using a lot more than the others.

 

[cPanelLauren]

Let us know if you have any further questions after checking into the advice provided by @GOT

 

[manoaratefy]

At this time, the concerned server load average is fine (8-10 on 12 CPU) without any action from my side. I'll wait if it happens again and I'll surely try to handle users which run a lot of queries.

However, in another server, I have same issue, high load average (17 on 16 CPU) but just 800 qps on mysqladmin. Should I still limit queries on some users? (I see users having 400 000+ selects commands in just a few minutes of users stats activation on MySQL)

 

[cPanelLauren]

If you'd like to lower the usage and impose limits on your users then I would say yes you should.

 

[manoaratefy]

In other words, the DB Governor shipped with CloudLinux is not enough reliable to keep everyone inside their LVE limits? I had to write a BASH script to detect these overloading users and to limit manually their MySQL users.

 

[cPanelLauren]

What exactly was not sufficient? Did you look at the documentation? Understanding how CloudLinux's features work might save you some frustration of not only writing scripts but also understanding what to expect CloudLinux OS components