High CPU load MySQL and Nobody

[psytanium]

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

mysql		8.17	11.65	0.0
nobody		4.76	20.19	0.0

What does it mean ?

Thanks

 

[cPRex]

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]

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

 

[cPRex]

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]

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]

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]

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]

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:

https://support.cpanel.net/hc/en-us/articles/360047752694-How-to-diagnose-high-loads-with-top-command

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

 

[psytanium]

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:

https://support.cpanel.net/hc/en-us/articles/360047752694-How-to-diagnose-high-loads-with-top-command

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]

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.