Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

Discussion in 'Database Discussion' started by Benjamin D., Jul 26, 2018.

  1. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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.
     

    Attached Files:

    #1 Benjamin D., Jul 26, 2018
    Last edited: Jul 26, 2018
    rarod likes this.
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,803
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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 -
     
    #3 Benjamin D., Jul 27, 2018
    Last edited by a moderator: Aug 1, 2018
  4. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,137
    Likes Received:
    222
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    cPanelMichael likes this.
  5. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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.
     
  6. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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.
     
    #6 Benjamin D., Aug 1, 2018
    Last edited: Aug 1, 2018
  7. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,137
    Likes Received:
    222
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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:

    Code:
    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:
    Code:
    sar -p
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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
     
  9. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,137
    Likes Received:
    222
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    The average being

    Code:
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  10. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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.
     
    #10 Benjamin D., Aug 1, 2018
    Last edited: Aug 1, 2018
  11. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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?
     
  12. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,137
    Likes Received:
    222
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Benjamin D.


    I had asked that you open a ticket did you have a ticket ID?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. Benjamin D.

    Benjamin D. Well-Known Member

    Joined:
    Jan 28, 2016
    Messages:
    116
    Likes Received:
    13
    Trophy Points:
    18
    Location:
    Canada
    cPanel Access Level:
    Root Administrator
    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.
     
  14. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,137
    Likes Received:
    222
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice