Uploading sql dump via phpmyadmin takes long time and causes high load on a server

Created:

2016-11-16 13:15:32 UTC

Modified:

2017-04-24 11:41:32 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Uploading sql dump via phpmyadmin takes long time and causes high load on a server

Applicable to:

  • Plesk 12.5 for Linux
  • Plesk 12.0 for Linux

Symptoms

It takes about 5 minutes to upload a 10Mb sql dump via phpMyadmin. Process sw-engine-fpm drives the CPU to nearly 100%:

# top
top - 00:56:53 up 2 days, 15 min, 1 user, load average: 0.09, 0.03, 0.00
Tasks: 146 total, 1 running, 145 sleeping, 0 stopped, 0 zombie
Cpu(s): 22.9%us, 5.9%sy, 0.0%ni, 70.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.5%st
Mem: 5991176k total, 4449924k used, 1541252k free, 252972k buffers
Swap: 6127612k total, 0k used, 6127612k free, 3418196k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30123 psaadm 20 0 447m 33m 16m S 20.6 0.6 0:02.02 sw-engine-fpm
1789 mysql 20 0 1186m 301m 7984 S 10.6 5.2 5:31.19 mysqld
30126 psaadm 20 0 446m 33m 16m S 8.6 0.6 0:01.79 sw-engine-fpm
30125 psaadm 20 0 445m 32m 16m S 6.0 0.6 0:02.01 sw-engine-fpm
30127 psaadm 20 0 446m 33m 17m S 4.7 0.6 0:01.48 sw-engine-fpm
30121 psaadm 20 0 443m 30m 17m S 4.0 0.5 0:01.97 sw-engine-fpm
1428 sw-cp-se 20 0 46044 4168 1792 S 0.7 0.1 0:04.77 sw-cp-serverd

Cause

phpMyadmin sql importing performance issues.

Resolution

In general, phpMyAdmin has performance issues with importing SQL dumps. In this case high CPU consumption by the sw-engine-fpm process is expected behavior.

In some cases, depending on dump structure, it is possible to speed up imports by delaying index generation via setting the following parameter in the my.cnf file and restarting the mysql server:

# vi /etc/my.cnf
[mysqld]
delay_key_write=ALL

Note that this setting should be removed after importing.

It is found that phpMyadmin4.4 is much slower than phpMyadmin3.5 for most operations.

The work on improvements already started on phpMyadmin side so its results might be observed soon. Please contact phpMyadmin for more details.

Have more questions? Submit a request
Please sign in to leave a comment.