15

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

migrated from serverfault.com Apr 24 '15 at 17:50

This question came from our site for system and network administrators.

  • Can you describe me how you want to import the database? (what is your concrete mysql statement) What does "show processlist;" say? Have you looked what the process is doing concrete with strace? Can you please also have a look if your machine is maybe swapping? – andreasemer Apr 23 '15 at 11:05
  • I edited my question. – Alex Apr 23 '15 at 12:57
  • InnoDB? What is the value of innodb_buffer_pool_size on each machine? – Rick James Apr 24 '15 at 21:40
38

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)

  • Awesome tricks. Something that I need to know, is there any side effects of doing this? – Dharma Saputra Nov 27 '17 at 7:58
  • In case your data is corrupt, foreign key constraints might not be met after import. – Alex Nov 27 '17 at 12:21
  • Great work around. You have saved me literally hours. – Jafo Jan 23 '18 at 22:49
4

Complementing what I see above... I have my dump file already automatically generated by something like:

mysqldump my_db > db-dump-file.sql

I want to automate this import so I created two files in my computer called default-start-import.sql and default-end-import.sql and their contents are default-start-import.sql:

SET autocommit=0;

and default-end-import.sql:

COMMIT;
SET autocommit=1;

and the script I run is something like this;

cat default-start-import.sql db-dump-file.sql default-end-import.sql | mysql my_other_db

same command but easier to read:

cat default-start-import.sql \
    db-dump-file.sql \
    default-end-import.sql \
| mysql my_other_db

In this case cat is used to concatenate those files before sending them to the pipe. I think it is important that all files end with a new-line character (an empty line in the end of the file if seen from a text editor) so that the cat command doesn't merge lines between files.

The import works fine, I have not tested if it is actually faster because of this improvement to the enable-and-disable-autocommit thing, but if that makes things faster, then this extra steps make things easier.

0

I've tried --compress as well as SET autocommit=0; and they helped a small amount however...

I found that converting multiple INSERT INTO ... statements into one large statement with multiple VALUES(...), (...) improved speed considerably.

I'm using mysql over SSL over WAN. The remote MySQL database is hosted on Amazon.

With 9 columns and 2,100 rows:

  • 2,100 separate INSERT statements: 82s
  • 2 consolidated INSERT statements: < 1s

With 7 columns and 42,000 rows:

  • 42,000 separate INSERT statements: 1,740s
  • 42 consolidated INSERT statement: 105s

So depending on the tool generating the database dump (or more specifically the format of the INSERT statements), the speed can be influenced.

Note: This also decreases the .sql dump file by over 60% in my tests, so it will save on i/o as well.

Warning: There are physical limitations to this technique with mysql and for those needing portability... SQL Server seems to be limited to only 1,000 rows at a time.

Still, doing 1,000 rows at a time for 42,000 rows still yields a 1,657% improvement!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.