We have a couple of MySQL 5.5 servers running on Ubuntu 14.04 for our QA and DEV environments and every so often we restore a backup SQL file we get from our production OPS team so we are testing with the same data. The file they give me is about 37GB uncompressed.
We import it directly on the MySQL server via the command-line client by doing:
set unique_checks=0;
set foreign_key_checks=0;
set autocommit=0;
source backup.sql
set unique_checks=1;
set foreign_key_checks=1;
set autocommit=1;
However the import takes more than 24 hours. It is running on a VM on ESXi 6.0. The VM specs are:
4 CPUs
8 GB RAM
750 GB disk - Thick Provisioned/Local RAID5 Storage(8x10KSAS)/Unlimited IOPS
The VM performance graphs from vCenter show:
- CPU utilization: 25%
- Memory Usage: 5GB
- Disk I/O: ~17.5 MB/s current
Initially CPU peaked at 90% and DiskIO was at 200MB/s then bounced up/down from around the 75-80MB/s and has settled to a fairly consistent 17.5MB/s with a slight downward trend. Here is a link to a PNG of the full graph:
https://goo.gl/photos/siGU1E8yge6CWzgP6
What else could I do to speed this up, if anything?