0

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
    extended-insert is enabled by default. – kasi Sep 28 '16 at 11:04
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).
  • Ah ok! Thank you for your good explaination. I didn't understand the tech-speak at the mysql docs. – Jan Zimmermann Jun 5 '15 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 '15 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 '15 at 2:03
  • INSERT ... VALUES (...), (...), ... does not change the I/O, it makes the parsing and processing more efficient. – Rick James Jun 10 '15 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 '15 at 15:21

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.