High load coming from /usr/sbin/mysqld --daemonize?

[Benjamin D.]

I recently installed a new server and migrated all sites (and databases) from another server having the same exact (100% identical) hardware configuration. We're talking about a Xeon E3-1270v6 (3.8ghz - 4.2ghz) so I'm not used to seeing such high loads, but yet, from the first hour post migration, I'm seeing this process sticking at the top of the process manager, consuming always around 27%

/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Average loads are often reaching 1.0 which was quite unusual for me to see on the previous server hosting exactly the same databases on the same hardware. Something is going on, what has this process been doing for 2 days in a row now? Is this normal that it's constantly eating 27% of the CPU non stop? (i'll answer my own question: no it's not, now what can I do about this?)

SHOW FULL PROCESSLIST; shows NOTHING going on, if I refresh this command every second, sometimes I see a SELECT from one of the sites on the machine, but basically it's sleeping 99% of the time. No query hanging, no optimization or index creation going on, at least not more than a fraction of a second from time to time, like normal.

swapon --summary : 0KB used out of 4GB

Contents of /var/log/mysql-slow.log ? Empty.

Any MYSQL error log? find /var/lib/mysql/ | grep [.]err : not a single one.

Repair every single table of every DB on this machine? mysqlcheck -A --auto-repair : Done. Waited 2 minutes, still exactly the same, 27% CPU load.

After systemctl restart mysqld : Same thing.

 

[cPanelMichael]

Hello @Benjamin D.,

You noted the server is using the exact same hardware. Can you confirm if there's any differences in the installed operating system, cPanel, or MySQL versions? Also, are you using the same configuration values in the /etc/my.cnf file?

Note that generally the first step in solving MySQL performance issues is run a MySQL tuning script after MySQL has been running for at least 24 hours:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[Benjamin D.]

No, sorry, CentOS 6.1 on server-A and CentOS 7.5 on server-B. I thought I mentioned this, but I just realized I had mentioned this on another forum post I'm watching for another migration related issue.

OK VERY GOOD POINT about checking /etc/my.cnf THANKS!
I don't know what made me overlook this. Oh yeah, probably because I finished my work shift at 5:30am (started at 9am the day before) thinking I was done migrating the server only to wake up in panic at 8:45am listening to upset customers voice mails.

server-A :

[mysqld]
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
local-infile=0

performance-schema=0
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-----------------------

server-B :

[mysqld]
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
local-infile=0

performance-schema=0
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-----------------------

So they look identical to me. I never messed with it over the 4 years period that server-A was in operation precisely for this reason: to avoid migration problems.

As for your other question, yes it's the same exact version: Server version: 5.7.22 - MySQL Community Server (GPL)

OK, now I have done mysqladmin variables > some.txt on both servers and after diffing the two files, here are the differences in the whole variable list:

- hostname (it's different on server-A and server-B)
- server_uuid (serial number)
- general_log_file and other log filenames, because they contain the hostname
- have open ssl and openssl is DISABLED on server-A but is on YES on server-B
- ssl_ca, ssl_cert, ssl_key are all unset on server-B and set on server-B

THE WHOLE file is absolutely identical besides the small details listed above which most of them are irrelevant in our scenario.

And while we're at it, a mysqlmymonlite report is worth a thousand words ;-)

- Removed -

 

[cPanelLauren]

Hi @Benjamin D.


The configuration appears to be similar but I am curious if there isn't some difference between the handling of MySQL threads on CentOS 6 as opposed to CentOS 7 - In this instance so that we can look further into the issue/configuration can you please open a ticket using the link in my signature? Once open please reply with the Ticket ID here so that we can update this thread with the resolution once the ticket is resolved.


Thanks!

 

[Benjamin D.]

I will at some point if I cannot fix the issue by myself.

I read many forum posts throughout the Internet about this high CPU issue with current versions of /usr/sbin/mysqld so I'm trying their resolution solutions.

I added these to /etc/my.cnf ...
innodb_buffer_pool_size=16M
slow_query_log=1
slow_query_log_file="/var/log/mysql-slow-queries.log"

...and touched, chmoded and chowned /var/log/mysql-slow-queries.log then restarted MySQL from WHM and the only thing it wrote in the log in 1 hour is:

/usr/sbin/mysqld, Version: 5.7.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument

The file is literally empty. As mentioned before, there is absolutely no hanging or long query on this server, everything is running perfectly fine. So why is mysqld using SO MUCH CPU? 20% to 35% at ALL TIME 24/7 NON STOP, every single minute since the server was installed a week ago. If it were a bad query or an indexing issue, it would tell, plus I've migrated the sites AS IS from another server which never, ever had such an issue for 4 years.

 

[Benjamin D.]

EDIT: NOPE. Came back to 27% CPU half an hour after this.

I ran this command 20 minutes ago, based on this SO post: MySQL high CPU usage

service ntpd stop
date -s "`date`"
service ntpd start

...and then I restarted MySQL again.

To my surprise, loads have just been lower and lower over the past 15-20 minutes! 0.27 0.34 0.48 and before load was constantly never lower than like 1.10 (could go up to 1.30 sometimes)
/usr/sbin/mysqld now uses NO MORE than 10% (which IMO is still high-ish) but at least 200% to 300% LESS than initially. For the first time monitoring this server, /usr/sbin/mysqld is not always the top process consuming CPU.

I don't believe the issue is completely resolved tough, but it's a damn good improvement already.

 

[cPanelLauren]

Hi @Benjamin D.

Wow, just read that post - that's something I absolutely have not heard of before and I'm actually pretty surprised it helped. The issue doesn't appear to be slow queries or errors either. The only other thing that I notice which seems unusual is there are occasional IO spikes (not too bad) which may have been contributing. You can see them in this output:

09:56:42 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
09:56:44 PM all 10.51 0.00 4.45 0.49 0.00 1.42 0.00 0.00 0.00 83.13
09:56:44 PM 0 11.06 0.00 4.81 0.48 0.00 4.81 0.00 0.00 0.00 78.85
09:56:44 PM 1 6.19 0.00 3.81 0.00 0.00 3.81 0.00 0.00 0.00 86.19
09:56:44 PM 2 11.44 0.00 4.98 0.00 0.00 0.50 0.00 0.00 0.00 83.08
09:56:44 PM 3 3.48 0.00 2.99 0.50 0.00 1.00 0.00 0.00 0.00 92.04
09:56:44 PM 4 14.85 0.00 6.44 1.49 0.00 0.99 0.00 0.00 0.00 76.24
09:56:44 PM 5 15.92 0.00 5.97 1.00 0.00 0.00 0.00 0.00 0.00 77.11
09:56:44 PM 6 10.61 0.00 2.53 0.00 0.00 0.00 0.00 0.00 0.00 86.87
09:56:44 PM 7 10.89 0.00 4.95 0.00 0.00 0.99 0.00 0.00 0.00 83.17

You can get this output from sysstat by running the following:

sar -p

 

[Benjamin D.]

Not really, no.


09:50:01 AM CPU %user %nice %system %iowait %steal %idle
10:00:01 AM all 3.87 0.01 1.64 0.53 0.00 93.95
10:10:01 AM all 5.06 0.00 2.16 0.61 0.00 92.17
10:20:01 AM all 4.71 0.00 1.97 0.54 0.00 92.78
10:30:01 AM all 4.40 0.00 1.99 0.68 0.00 92.93
Average: all 5.40 0.09 2.25 1.62 0.00 90.64

 

[cPanelLauren]

The average being

Average: all 5.40 0.09 2.25 1.62 0.00 90.64

is a bit high though I'm not sure it would cause noticeable effects. Often this is due to traffic on the node on VM servers. I still believe a ticket would be the best way to investigate this issue though.


Thanks!

 

[Benjamin D.]

I do not have a VM server. As mentioned on the 2nd sentence of the very first thread post, I've got a dedicated server with a Xeon E3-1270v6 (3.8ghz - 4.2ghz) CPU.

I still believe your goddess powers will help me solve the issue without opening a ticket. Plus, it's good for the community to read/reply/solve the issues together. I'm sure I'm not the only one with a WHM 72.0 setup having this issue. Some sysadmins might not even have realized their server suffers this behavior yet and it could help them realize and possibly fix this issue on their server if they're in the same boat as I am. They might not have noticed, because at first, I did not realize it, since the websites are smooth and fast still. It's just using electricity for nothing it seems.

My biggest fear is that this translates into a lot of disk writes and that it basically destroys my RAID disks. I'm sorry to ask, but I'm not used to monitor this as in the past years I've never had to deal with such an issue, but do you know how to check if this high CPU issue also translates into premature/heavy disk deterioration?

This should not happen as it was not happening with my previous (now unplugged) server, which had the EXACT SAME sites, databases, cPanel accounts, settings and hardware-wise, the same CPU.

This is most probably caused solely by CentOS 7.5 as it's the ONLY different factor from my two servers. As mentioned in first post, the previous server was also on WHM 72.0 but CentOS 6.1 and none of this high CPU usage ever occured.

 

[Benjamin D.]

3 weeks later, any updates on a possible resolution route for this issue? It's still happening 24/7... still around 28% alllll day long. Is this considered normal behavior under Centos 7.5 or what?

 

[cPanelLauren]

Hi @Benjamin D.


I had asked that you open a ticket did you have a ticket ID?

 

[Benjamin D.]

Not yet, altough everything you can do remotely on my server, you can tell me and I can do it, so that the whole community sees the steps to fix it.

 

[cPanelLauren]

Hi @Benjamin D.


I can't do that but I can promise to post the resolution to the ticket (if you open one) here.


Thanks!

 

[gizmo80]

did you ever find the cause of this problem, having the same one.

 

[cPanelMichael]

did you ever find the cause of this problem, having the same one.

Hello @gizmo80,

Can you provide some more details about the specific issue you are facing? For instance, did you also notice the same issue after migrating to new server hardware, or is it just an overall load/resource usage issue you are facing with MySQL?

Thank you.

 

[gizmo80]

Hello @gizmo80,

Can you provide some more details about the specific issue you are facing? For instance, did you also notice the same issue after migrating to new server hardware, or is it just an overall load/resource usage issue you are facing with MySQL?

Thank you.

I did change servers, but mysql was often high on previous one too.

BUT I noticed that while CPU% in Process Manager is high at 64%, in Service Status the Server Load is only 0.792969 (24 CPUs)
so based on Process Manager CPU % is it possible that Process Manager adds up the loads on all 24 cores as if it's the load on 1 CPU, hence making it seem like total CPU useage is high while in effect it's low. That would make the max CPU% in Process Manager not 100%, but 2400% in my case.

 

[tomidjx]

Hi everyone, I have the same problem, Centos 7 cPanel is showing high CPU with the following /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid.
Any idea/solution for this?
thanks

 

[AlexisValera]

Hi everyone here you masters I run this

• CENTOS 7.8 kvm [vps]
• v88.0.13
• Load Averages: 24.36 23.67 25.12

Have this process

/home/criptomo/public_html/wp-admin/wp-update -B+
usr/sbin/mysqld --daemonize --pid-file=/var/run+

and the sites goes nut

Please help us here

 

[joshi123]

The setup gives off an impression of being comparative yet I am interested if there isn't some distinction between the treatment of MySQL strings on CentOS 6 rather than CentOS 7 - In this occasion so we can look further into the issue/arrangement would you be able to please open a ticket utilizing the connection in my mark? When open please answer with the Ticket ID here so we can refresh this string with the goal once the ticket is settled.