Might have come across the 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
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
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
1. Login to WHM and Search for Tweak Settings open it.
2. 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.
Increasing Linux open limits in CentOS
1. Log in to the root of your Web Server.
2. 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
3. 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
4. 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.
5. Finally, reboot your server.
Increasing MySQL open limits
1. Log in to the root of your Web Server.
2. Check whether you actually exceed the open_files limit by executing the following SQL queries
SHOW 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';
3. Check if your server uses MariaDB or MySQL. Run the following command.
mysql --version
4. Now we need to edit my.cnf setting config file and change the LimitNOFILE value. We will change value from 10000 to 30000.
Note:
For MySQL Server – /etc/systemd/system/mysqld.service.d/limits.conf
For MariaDB Server – /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
5. 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.
6. Reboot your server.
7. 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';
