Guide to fix MySQL hangup problem in WHM/cPanel Server

Facing issues like the crashing of MySQL server and unable to restart due to connection timed out when restarting? This problem can occur when your website’s database is large and have high traffic. Due to this when you or WHM wants to restart the MySQL, it sticks there and throws the timeout error.

Above stated problems could happen due to any of the following –

  • Low max_open_files limits on both MySQL server and Linux Server.
  • Corrupted Database of the website.
  • Faulty plugins creating numerous database connections.
  • WHM conflicting while changing open file limits.

We will fix this problem using a series of changes that will be discussed in below sections.

Log Inspection

We will check the logs on your server and implement the fixes accordingly.

Log File Location – /var/log/chkservd.log

  • Open this log file and search for “system failed”
    You can find the similar log as below after searching the above keywords

     

    Cpanel::RestartSrv::Lock::new(“Cpanel::RestartSrv::Lock”, “mysql”) called at /usr/local/cpanel/Cpanel/ServiceManager/Base.pm line 183
    Cpanel::SafeFile::safeopen(undef, “>>”, “/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/RestartSrv/Lock.pm line 25
    Cpanel::SafeFile::_safe_open(undef, “>>”, “/var/run/restartsrv_mysql”, CODE(0xb8ee48), “safeopen”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 115
    Cpanel::SafeFile::_safelock(“/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 558
    Cpanel::SafeFile::_lock_wait(“/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 355
    Cpanel::SafeFile::_die_if_file_is_flocked_cuz_already_waited_a_while(“/var/run/restartsrv_mysql”, 197) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 829
    Cpanel::SafeFile::_timeout_exception(“/var/run/restartsrv_mysql”, 197) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 761
    Cpanel::Exception::__ANON__(__CPANEL_HIDDEN__, __CPANEL_HIDDEN__…, ARRAY(0x14cce58)) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 729
    Cpanel::Exception::create(“Timeout”, “The system failed to lock the file \x{e2}\x{80}\x{9c}[_1]\x{e2}\x{80}\x{9d} after [quant,_2″…, ARRAY(0x14cce58)) called at /usr/local/cpanel/Cpanel/Exception.pm line 61
    at /usr/local/cpanel/Cpanel/Exception/CORE.pm line 336.

    This means that WHM wants to restart MySQL server but its state is hanged and due to its state SQL server is unable to save the changes and restart safely. Hence it cannot create a lock file.

    For User-Friendly Logs for above problem you can fetch systemd log by running following command
    systemctl --no-pager -l status mysql > systemctl.log
    This saves the log file with name systemctl.log

    You can search for “operation timed out” after opening the saved log file
    You can find the similar log as below after searching the above keywords

    Jun 23 14:23:45 wp mysqld[31554]: 2018-06-23 14:23:45 139794412255424 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB-log) starting as process 31554 …
    Jun 23 14:23:45 wp systemd[1]: Starting MariaDB 10.2.15 database server…
    Jun 23 14:20:55 wp systemd[1]: mariadb.service failed.
    Jun 23 14:20:55 wp systemd[1]: Unit mariadb.service entered failed state.
    Jun 23 14:20:55 wp systemd[1]: Failed to start MariaDB 10.2.15 database server.
    Jun 23 14:20:55 wp systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
    Jun 23 14:20:14 wp systemd[1]: mariadb.service start operation timed out. Terminating.
    Jun 23 14:18:44 wp mysqld[30610]: 2018-06-23 14:18:44 140586726332608 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB) starting as process 30610 …
    Jun 23 14:18:44 wp systemd[1]: Starting MariaDB 10.2.15 database server…

  • Check for the open_limits warning on systemctl logs saved in the last section. You can search for “max_open_files” after opening the saved log file
    You can find the similar log as below after searching the above keywords

     

    Jun 23 14:33:47 wp mysqld[32608]: 2018-06-23 14:33:47 140276754827456 [Warning] Could not increase number of max_open_files to more than 10000 (request: 11031)
    Jun 23 14:33:47 wp mysqld[32608]: 2018-06-23 14:33:47 140276754827456 [Warning] Changed limits: max_open_files: 10000 max_connections: 1000 (was 1000) table_cache: 4485 (was 5000)
    Jun 23 14:35:17 wp systemd[1]: mariadb.service start operation timed out. Terminating.
    Jun 23 14:35:58 wp systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
    Jun 23 14:35:58 wp systemd[1]: Failed to start MariaDB 10.2.15 database server.
    Jun 23 14:35:58 wp systemd[1]: Unit mariadb.service entered failed state.
    Jun 23 14:35:58 wp systemd[1]: mariadb.service failed.
    Jun 23 14:36:28 wp systemd[1]: Starting MariaDB 10.2.15 database server…
    Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB-log) starting as process 962 …
    Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Warning] Could not increase number of max_open_files to more than 10000 (request: 11031)
    Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Warning] Changed limits: max_open_files: 10000 max_connections: 1000 (was 1000) table_cache: 4485 (was 5000)

So with this, we finish the logs inspections. The further section of this guide will show how we can fix the above-found MySQL Server Problems

 

Solution Section

Dis-Allowing cPanel & WHM to change MySQL open_files_limit configuration

  • Login to WHM and Search for Tweak Settings open it.
  • Inside Tweak Settings goto SQL Tab and look toggle for Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?
    We will turn it off and press the save button below.tweak whm open limit settings

Increasing Linux open limits in CentOS

  • Log in to the root of your Web Server.
  • Edit File – /etc/security/limits.conf
    At the very bottom of this file, add the following lines and save it

     

    # Performance Tuning
    *       soft    nproc   32768
    *       hard    nproc   65535
    *       soft    nofile  32768
    *       hard    nofile  65535
    root    soft    nproc   32768
    root    hard    nproc   65535
    root    soft    nofile  32768
    root    hard    nofile  65535
  • Now edit the file: /etc/sysctl.conf
    At the very bottom of this file, add the following lines and save it

     

    # Performance Tuning
    fs.file-max = 2097152
    net.core.netdev_max_backlog = 131070
    net.core.somaxconn = 131070
    net.ipv4.tcp_max_syn_backlog = 3240000
    net.ipv4.tcp_max_tw_buckets = 1440000
    net.ipv4.tcp_window_scaling = 1
    vm.swappiness = 30
  • To apply these changes, simply type this command as root user via SSH:
    sysctl -p

    Note – If you error with “net.core.somaxconn” not being merged, change its value to “65535” and then re-merge with commandsysctl -p.

    The changes are now active and your system is able to handle more concurrent network connections.

  • Finally, reboot your server.

Increasing MySQL open limits

  • Log in to the root of your Web Server.
  • Check whether you actually exceed the open_files limit by executing the following SQL queriesSHOW GLOBAL STATUS LIKE 'Open_files';
    The above command will give you the number of currently open files.

     

    Also, confirm the maximum value set for the open_files

    SHOW VARIABLES LIKE 'open_files_limit';

  • Check if your server uses MariaDB or MySQL. Run the following command.mysql --version

    mysql version
    MySQL Output

    mariadb version
    MariaDB Output

  • Now we need to edit my.cnf setting config file and change LimitNOFILE value.
    We will change value from 10000 to 30000For MySQL Server – /etc/systemd/system/mysqld.service.d/limits.conf

     

    open limits mysql

    For MariaDB Server – /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

    open limits mariadb

  • Run the following command to apply the changes.systemctl daemon-reload && /scripts/restartsrv_mysql 

    The first part of that reloads the new limit data, and the second restart the service to start with the new limit data.

  • Reboot your server.
  • After the successful reboot of the server, we will again run below SQL Queries.SHOW GLOBAL STATUS LIKE 'Open_files';
    SHOW VARIABLES LIKE 'open_files_limit';

     

    mysql open limits values

With the solution above discussed your problems stated initially in this guide will be fixed and you will get a clean chkservd.log file without any MySQL failure Log Spam.

  • mysql, mysql server optimization, mysql performance check, mysql tuning
  • 0 משתמשים שמצאו מאמר זה מועיל
?האם התשובה שקיבלתם הייתה מועילה

מאמרים קשורים

How to access server via Remote Desktop (RDP)?

Remote Desktop Protocol (RDP) is a proprietary protocol developed by Microsoft, which provides a...

How to install Let’s Encrypt on CentOS 7 with Apache?

Today, we will show you, How to install Let’s Encrypt on CentOS 7 with Apache. Let’s Encrypt is a...

Domains are not showing in WHM -> List accounts

We were unable to see any domains listed under WHM -> List Accounts. However, we can see the...

HOW TO INSTALL LITESPEED WEB SERVER IN LINUX SERVER?

Litespeed Cache is useful to increase high performance for your Wordpress Website. Following are...

Remove ONLY_FULL_GROUP_BY in my.cnf file

Remove ONLY_FULL_GROUP_BY in my.cnf file First you need to find my.cnf file location and then...