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';

 

Cette réponse était-elle pertinente? 0 Utilisateurs l'ont trouvée utile (0 Votes)