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!

Excessive resource usage: mysql

Discussion in 'Database Discussion' started by Clixer, May 14, 2018.

  1. Clixer

    Clixer Active Member

    Joined:
    Feb 24, 2018
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Hello folks,

    Since I have switched on process monitoring in lfd, I am receiving the following notification from the server on an hourly basis:

    Subject: lfd on server.domain.com: Excessive resource usage: mysql (1093 (Parent PID:1093))

    Email body:

    Time: Mon May 14 20:01:41 2018 +1000
    Account: mysql
    Resource: Process Time
    Exceeded: 165589 > 1800 (seconds)
    Executable: /usr/bin/bash
    Command Line: /bin/sh /usr/bin/mysqld_safe
    PID: 1093 (Parent PID:1093)
    Killed: No

    This notification has led me to believe that the MySQL server is not configured properly and there is a room for improvement.

    Our VPS has 5 cpus and RAM is 5GB,

    Following is the entry to the my.cnf file:

    [mysqld]
    default-storage-engine=innodb
    performance-schema=0
    innodb_file_per_table=1
    innodb_buffer_pool_instances=1
    innodb_buffer_pool_size=134217728
    innodb_log_file_size=512M
    max_connections=300
    max_allowed_packet=268435456
    open_files_limit=10000
    query_cache_type=1
    query_cache_limit=256K
    query_cache_min_res_unit=2k
    query_cache_size=80M
    tmp_table_size=64M
    max_heap_table_size=64M
    slow-query-log=1
    slow-query-log-file=/var/lib/mysql/mysql-slow.log
    long_query_time=1
    local-infile=0
    # MySQL idle Connections
    wait_timeout=60
    # Skip reverse DNS lookup of clients
    skip-name-resolve

    Following is the output of the top command:

    top - 21:51:51 up 3 days, 11:57, 1 user, load average: 0.29, 0.22, 0.27
    Tasks: 178 total, 1 running, 175 sleeping, 0 stopped, 2 zombie
    %Cpu(s): 21.7 us, 1.4 sy, 0.0 ni, 76.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
    KiB Mem : 4931788 total, 143520 free, 1962884 used, 2825384 buff/cache
    KiB Swap: 4980732 total, 4907260 free, 73472 used. 2346676 avail Mem

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    1449 mysql 20 0 1753644 195476 7148 S 100.3 4.0 692:06.16 mysqld
    38569 clubint+ 20 0 455076 21236 13076 S 3.3 0.4 0:00.10 php-fpm
    92384 root 20 0 183832 31240 2180 S 0.7 0.6 1:38.41 lfd - sleeping
    96459 nobody 20 0 2585292 166104 30120 S 0.7 3.4 1:14.02 httpd
    1858 root 20 0 7188 568 460 S 0.3 0.0 0:18.15 strace
    38418 root 20 0 160064 2268 1548 R 0.3 0.0 0:00.17 top
    1 root 20 0 46492 5064 3028 S 0.0 0.1 4:53.80 systemd
    2 root 20 0 0 0 0 S 0.0 0.0 0:00.41 kthreadd

    What should I change in the my.cnf file to optimise the MySQL performance.

    Many thanks for help.
     
  2. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,748
    Likes Received:
    84
    Trophy Points:
    353
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    MySQL runs all the time you need to set your csf firewall to ignore
    add
    exe:/usr/sbin/mysqld_safe
    to your csf pignore
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Clixer

    Clixer Active Member

    Joined:
    Feb 24, 2018
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Thanks Dalem.

    So you dont see any issues with the MySQL performance?
     
  4. dalem

    dalem Well-Known Member
    PartnerNOC

    Joined:
    Oct 24, 2003
    Messages:
    2,748
    Likes Received:
    84
    Trophy Points:
    353
    Location:
    SLC
    cPanel Access Level:
    DataCenter Provider
    Not from the info you posted
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,451
    Likes Received:
    246
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Thanks for the response on this @dalem! @Clixer please let us know if you have any other issues with this.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. Clixer

    Clixer Active Member

    Joined:
    Feb 24, 2018
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Hello @dalem and @cPanelLauren.

    I have found that the following two entries are already in the csf.pignore file

    exe:/usr/sbin/mysqld
    exe:/usr/sbin/mysqld_safe

    This file is at location: /etc/csf/csf.pignore.

    Any ideas as to why the lfd is still sending the email notifications?

    Thanks for help.
     
  7. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,451
    Likes Received:
    246
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clixer

    The exe you're referencing is not the same one you received the email about. It looks like the exe should be

    Code:
    Executable: /usr/bin/bash
    
    Though as a whole I don't think it's a good idea to add /usr/bin/bash to that list.

    The primary reason you're getting that notification is that the runtime of the process exceeded the threshold set by LFD's PT_USERTIME setting. Basically, CSF says the process ran too long or abnormally long.

    If the length of time a process runs is inconsequential to you can modify the PT_USERTIME setting to ignore this, or you can increase it, though you may risk hitting it again if it starts complaining about bash.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. Clixer

    Clixer Active Member

    Joined:
    Feb 24, 2018
    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Hi @cPanelLauren. Thank you for further advice.

    My computing knowledge is limited but mysqld is a process which should be running all the time. The websites hosted on the VPS are database driven, calls to the database will be made whenever a visitor requests a page which is not cached. I am at a loss as to why mysqld process should have a time limit.

    Technically, unless the VPS is rebooted, myslqd would continue to run, hence I am not sure what maximum time I could use? I have used the maximum allowed of 86400. I am assuming lfd will continue to send me email notification every 24 hours.
     
  9. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,451
    Likes Received:
    246
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @Clixer

    I agree, though in this case it wasn't mysql that it was actually complaining about, it was bash. The same predicament stands though, the only way to stop this is either set /usr/bin/bash to be ignored, disable the PT_USERTIME setting, or modify it though in this instance I don't think modifying PT_USERTIME to a higher threshold would stop you from receiving the email. You could also leave it as is and self-verify the process' legitimacy (processes like httpd, mysql, etc are processes that normally run for extended amounts of time.

    You could also check over at the CSF forums for further advice on this, they may be able to provide you with some advice on customizations that will work for you. You can find the CSF forum here: ConfigServer Community Forum - Index page
     
    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