2

I'm new in db administration and I've looked for a faster import of a mysqldump.

I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before I used the standard mysqldump command for dumping the db. At import I had a write speed of about 60.000 rows per hour which was extremly slow.

I searched for a way to speed thingsup and found a post here saying to use --single-transaction and --extended-insert on mysqldump. Now the dump is importing in under 30 seconds.

After a quick overview it seems that the data is correctly imported but I'm a little bit leery about the fast import.

Why is this soo fast after adding these two options? Are there any disadvantages on using this options?

Thank you for your help.

1
  • 1
    extended-insert is enabled by default.
    – kasi
    Sep 28, 2016 at 11:04

1 Answer 1

2

this way is faster because

  • Server does not need to perform fsync() multiple times during data import. fsync is the operation of submitting data into disk
  • Extended insert enables sequential write to disk which means disk does not need to seek for a locations for every row.
  • If the table has Auto_Increment values, Server can spare adequate values for the batch insertion at once rather than once for every insertion.
  • The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead (Added By @Rick James in comments)

I know two side effects for this method.

  1. undo log will get too large while performing this operation.
  2. If an error happen during the operation, the entire import will be reverted (which might be an advantage in your case).
10
  • Ah ok! Thank you for your good explaination. I didn't understand the tech-speak at the mysql docs. Jun 5, 2015 at 12:39
  • Correction. The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead. There is no downside of this option unless you happen to had some setting (max_packet_size?) set to low during the load.
    – Rick James
    Jun 9, 2015 at 18:04
  • @RickJames is this a correction or an addition? If it's a correction, do u mean sequential write to disk is not correct?
    – Masoud
    Jun 10, 2015 at 2:03
  • INSERT ... VALUES (...), (...), ... does not change the I/O, it makes the parsing and processing more efficient.
    – Rick James
    Jun 10, 2015 at 4:48
  • 1
    The "sequential" versus "random" depends the order in which the rows are fetched. InnoDB rows are fetched (and written to dump) in PRIMARY KEY order. So the reload will also be in that order, whether or not extended-insert is used. This is very efficient for the PK and the data. Secondary keys are built in bulk (note that the dump disables and enables keys), so they are reasonably efficient.
    – Rick James
    Jun 10, 2015 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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