In some cases, the database import in Plesk hangs at 100%. Users often notice errors even while importing a comparatively small database.
In this context, we shall look into methods to solve Plesk usage problems.
Causes for Database import to hanging in Plesk
Recently we received a request from one of our customers that the database import task from Plesk was getting hang. This was even the case with considerably smaller databases.
Mysql error log at "/var/log/mysql/error.log" was reporting the error as shown below:
show full processlist;
| 255327 | word_user1 | localhost | word_name1 | Query | 42625 | Waiting for table metadata lock | SELECT COUNT(*) FROM `word_name1`.`wp_wfConfig` |
| 255334 | word_user1 | localhost | word_name1 | Query | 42621 | Waiting for table metadata lock | SELECT COUNT(*) FROM `word_name1`.`wp_wfConfig` |
| 255512 | word_user1 | localhost | word_name1 | Query | 42288 | Waiting for table metadata lock | SELECT COUNT(*) FROM `word_name1`.`wp_wfConfig` |
While we cross-checked the Mysql CPU usage, we noticed that the usage has spiked to a great extend. A typical usage that we noticed looked like the one below:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16164 mysql 20 0 2654656 748200 8044 S 201.0 18.3 2698:08 mysqld
This error generally triggers due to non-optimized client databases.
How to fix Database import hangs in Plesk ?
As the database import tasks normally hang at 100% due to non-optimized databases, we need to optimize the databases to fix this error permanently.
One solution here would be to allocate RAM to the MySQL server.
For this, we need to connect to the Plesk server via SSH. Then, we need to open the MySQL configuration file my.cnf or my.ini with any text editor. Locations of these files vary for different Linux distros.
Then, we need to add the following directives under the [mysqld] section or increase the values if these directives are already defined:
Once the changes are made, save the changes and close the file. Also, note to restart the MySQL service with the appropriate command:
# systemctl restart mariadb
# systemctl restart mysqld
Now, we need to monitor CPU usage. If CPU consumption increases again, our Support Engineers consider reviewing the SQL code of a database that has slow queries.
During a high level of CPU usage, we can find queries that are currently running and taking a lot of time with the command below:
plesk db "SHOW FULL PROCESSLIST"
This would help us to note down the queries that are getting stuck up. We also note to check the MySQL error log file /var/log/mysqld.log for errors.
Another possible reason could be low RAM and disk space available in the server. We can check RAM and free disk space with the command below:
# free -h
# df -h