HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlCritical

mysql dump import incredibly slow on my developer's machine

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dumpslowmysqldevelopermachineincrediblyimport

Problem

I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in 40 minutes)
So this is factor 53 slower.

The specs:

Server A:
   MySQL Version: 5.5.30-1.1 (Debian)
   2 GB RAM
   1 core QEMU Virtual CPU version 1.0 - cpu MHz: 3400.020

Workstation B: 
   MySQL Version: 5.5.41-MariaDB-1ubuntu0.14.04.1
   14 GB RAM
   4 cores Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz - cpu MHz: 1600.000


The mysql/maria config is the stock config.

I switched yesterday to MariaDB on my workstation - but before MariaDB the stats were even worse.

I already removed all databases on my workstation - no difference.

The big question is: How can the performance be factor 53 slower? I can not work like this :-(

My import command:

pv sql/master.sql | mysql -h'localhost' -u'root' -p'root' 'master'


iostat -xm 5

server A:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
      17,43    0,00   30,28   51,85    0,00    0,44

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00   254,03    0,00 1305,45     0,00     6,09     9,56     0,78    0,60    0,00    0,60   0,57  74,25


workstation B:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
       7,32    0,00    3,22    5,03    0,00   84,42

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     1,40    0,80  172,40     0,00     0,56     6,72     1,17    6,75   12,00    6,72   5,40  93,52


dd if=/dev/zero of=tempfile bs=1M count=1024 conv=fdatasync,notrunc

server A:

1073741824 bytes (1,1 GB) copied, 18,6947 s, 57,4 MB/s


workstation B:

1073741824 bytes (1,1 GB) copied, 8,95646 s, 120 MB/s

Solution

This answer speeded up everything a lot:

https://stackoverflow.com/a/2167641/292408

I simply

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;


at the beginning, and

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;


at the end.

Now it took 3 minutes.

(Courtesy of @andreasemer via twitter)

Code Snippets

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

Context

StackExchange Database Administrators Q#98814, answer score: 68

Revisions (0)

No revisions yet.