MySQL Forums
Forum List  »  Performance

How can I improve the import speed of a large SQL file?
Posted by: Jeff Vincent
Date: May 06, 2016 09:46AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
How can I improve the import speed of a large SQL file?
20825
May 06, 2016 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.