High CPU load MySQL and Nobody

psytanium

Well-Known Member
Jun 6, 2014
322
20
68
Lebanon
cPanel Access Level
Root Administrator
Hi,

I'm trying to learn how to identify a frequent high CPU load every 30 to 60 minutes. I opened Daily Process Log, the first 2 records are

mysql8.1711.650.0
nobody4.7620.190.0

What does it mean ?

Thanks
 
Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
12,499
1,971
363
cPanel Access Level
Root Administrator
Hey there! The "nobody" user would be Apache, so that is all web content being served. For MySQL, you can use the "mysqladmin proc status" command to see what the MySQL server is doing in real-time and that should help you isolate the issue there.
 

psytanium

Well-Known Member
Jun 6, 2014
322
20
68
Lebanon
cPanel Access Level
Root Administrator
My VPS server have 4 CPUs, I'm willing to upgrade, but the maximum available is 8 CPUs. I'm afraid I upgrade to 8 CPUs but still having the same problems.

In "WHM > Service Status", the " Server Load " is marked in red if its over 4. Currently its reaching between 20 and 80. (Screen attached)

In "WHM > Daily Process Log", only the nobody is 4.5 and the MySQL is 6.8 (Screen attached)

I guess if I upgrade to 8 CPUs its also not enough. Right ? and there is something wrong I should fix before I consider any upgrade, right ?

Running "mysqladmin proc status" give this result:

[[email protected] ~]# mysqladmin proc status
+--------+------------------+-----------+------------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------------------+-----------+------------------+---------+------+----------+------------------+
| 141213 | account1 | localhost | account1 | Sleep | 0 | | |
| 141215 | account2 | localhost | account2 | Sleep | 0 | | |
| 141224 | account3 | localhost | account3 | Sleep | 0 | | |
| 141225 | account3 | localhost | account3 | Sleep | 0 | | |
| 141226 | root | localhost | | Query | 0 | starting | show processlist |
+--------+------------------+-----------+------------------+---------+------+----------+------------------+
Uptime: 7917 Threads: 5 Questions: 8418173 Slow queries: 0 Opens: 241043 Flush tables: 1 Open tables: 2000 Queries per second avg: 1063.303
 

Attachments

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
12,499
1,971
363
cPanel Access Level
Root Administrator
That's a lot of queries per second for sure. And yes, upgrading without fixing the underlying issue may give better results, or it may not.

The CPU usage in that last screenshot doesn't seem bad at all, but you really want to be watching things from an SSH connection in real-time to track down load issues.
 

psytanium

Well-Known Member
Jun 6, 2014
322
20
68
Lebanon
cPanel Access Level
Root Administrator
The highest load is from mysql and nobody, even 8 CPUs can't be enough for those 2 only.
So the problem is Queries per second avg: 1063.303, how can I know which account is running most of those queries per seconds ?
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
12,499
1,971
363
cPanel Access Level
Root Administrator
I think one of the problems could be related to the number of queries per second, but the CPU usage for MySQL seems low on the machine. 6% of a 4-core system would not be enough alone to cause load.

Do you see any other processes contributing to load when you check "top -c" ?
 

psytanium

Well-Known Member
Jun 6, 2014
322
20
68
Lebanon
cPanel Access Level
Root Administrator
Runing "top -c" gave me this result:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1758 mysql 20 0 2527540 762900 7632 S 41.4 9.5 91:34.68 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
....

mysql ranging from 20 to 60
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
12,499
1,971
363
cPanel Access Level
Root Administrator
So there you go - if it's ranging from 20-60 that would be worth investigating. It's still worth nothing that percentage is only one CPU core, so it may not be the only issue on the machine causing load, but it's still worth investigating.

Checking that MySQL command I provided during times of higher CPU usage may give better results, or you could check the MySQL logs to see if there are specific accounts. Unfortunately there isn't going to be an easy solution, so I'd recommend reviewing our guides here:


or a more general article here: https://support.cpanel.net/hc/en-us/articles/360056001894-How-to-diagnose-high-server-loads
 

psytanium

Well-Known Member
Jun 6, 2014
322
20
68
Lebanon
cPanel Access Level
Root Administrator
So there you go - if it's ranging from 20-60 that would be worth investigating. It's still worth nothing that percentage is only one CPU core, so it may not be the only issue on the machine causing load, but it's still worth investigating.

Checking that MySQL command I provided during times of higher CPU usage may give better results, or you could check the MySQL logs to see if there are specific accounts. Unfortunately there isn't going to be an easy solution, so I'd recommend reviewing our guides here:


or a more general article here: https://support.cpanel.net/hc/en-us/articles/360056001894-How-to-diagnose-high-server-loads
I was checking apache error log, sometimes i notice :
[Sat Apr 30 07:33:01.578932 2022] [mpm_prefork:error] [pid 1609] AH00161: server reached MaxRequestWorkers setting, consider raising the MaxRequestWorkers setting

Could it be a DDoS if this error appears once every few minutes ?
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
12,499
1,971
363
cPanel Access Level
Root Administrator
That's a possibility for sure, especially if your sites typically don't cause those issues.

Running "apachectl status" or "apachectl fullstatus" will show you what traffic Apache is handling in real-time, and you can see if there are certain IPs making too many connections.