Speeding up mysql dumps and imports
Solution 1
http://www.maatkit.org/ has a mk-parallel-dump and mk-parallel-restore
If you’ve been wishing for multi-threaded mysqldump, wish no more. This tool dumps MySQL tables in parallel. It is a much smarter mysqldump that can either act as a wrapper for mysqldump (with sensible default behavior) or as a wrapper around SELECT INTO OUTFILE. It is designed for high-performance applications on very large data sizes, where speed matters a lot. It takes advantage of multiple CPUs and disks to dump your data much faster.
There are also various potential options in mysqldump such as not making indexes while the dump is being imported - but instead doing them en-mass on the completion.
Solution 2
- Get a copy of High Performance MySQL. Great book.
- Extended inserts in dumps
- Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile
- Import with multiple threads, one for each table.
- Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
- Look at the table compare script in the Maakit toolkit and see if you can update your tables rather than dumping them and importing them. But you're probably talking about backups/restores.
Solution 3
If you are importing to InnoDB the single most effective thing you can do is to put
innodb_flush_log_at_trx_commit = 2
in your my.cnf
, temporarily while the import is running. You can put it back to 1
if you need ACID.
Solution 4
I guess your question also depends on where the bottleneck is:
- If your network is a bottleneck you could also have a look at the
-C
/--compress
flag tomysqldump
. - If your computer runs out of memory (ie. starts swapping) you should buy more memory.
Also, have a look at the --quick
flag for mysqldump
(and --disable-keys
if you are using MyIsam).
Solution 5
Using extended inserts in dumps should make imports faster.
deadprogrammer
Web developer, blogger, content management specialist. Major switches in life: Soviet Union to the US, single to married, PC to Mac, ASP to PHP, Sharepoint to Drupal. My blog is actually readable, people tell me. Go check it out at http://www.deadprogrammer.com
Updated on July 08, 2020Comments
-
deadprogrammer almost 4 years
Are there any documented techniques for speeding up mySQL dumps and imports?
This would include my.cnf settings, using ramdisks, etc.
Looking only for documented techniques, preferably with benchmarks showing potential speed-up.
-
Jonathan over 15 yearsif you do that there is a good chance you will not be able to import back if the dump is even moderately big
-
che over 15 yearsHow come MySQL client isn't able to process even moderately big dumps with extended inserts?
-
Ztyx about 14 yearsAccording to the mk-parallel-dump man page (maatkit.org/doc/mk-parallel-dump.html) it should not be used for backup. Beware!
-
Ztyx about 14 yearsMy guess is that the client has a fixed size buffer for each line it is reading and extended inserts exceeds that limit.
-
aldrinleal about 13 yearsActually thats what mysqlhotcopy does
-
drewish over 12 yearsMaatkit is now part of Percona Toolkit (launchpad.net/percona-toolkit)
-
Peter O. over 11 yearsYou shouldn't just give a link to another site as an answer, since the site may go out of date in the future. Instead, click the "edit" link on this answer and include the essential parts of the solution from that page here. See: meta.stackexchange.com/q/8259
-
Bogdan Gusiev over 11 yearsWhat is the name of the command in percona-toolkit?
-
Alister Bulman over 11 yearsAlas, it does not appear to be in percona-toolkit now.
-
kapex over 11 yearsReference says
--disable-keys
is 'effective only for nonunique indexes of MyISAM tables'. -
Ztyx about 11 yearskapep - thanks for the correction. I've updated the answer.
-
Petr about 11 yearsI wonder, wouldn't setting it to
0
be even faster? -
arun almost 11 yearsreally didn't understand what this does, but it sped up the restore of my large innoDB database dump. for MyISAM dbs increasing
key_buffer_size
helped (to 30% of available memory). -
Sian Lerk Lau over 10 yearsJust curious, does SET autocommit=0 render this unnecessary?
-
alxgb about 10 yearsfwiw I've imported a 10GB dump using extended inserts and it has gone flawlessly.
-
lepe over 9 yearsThis is the second time I use this method. In both cases (different databases), it reduced the import time from hours into few minutes. Thanks!
-
Nate about 9 yearsSadly, I only have one up-vote to give. Changing the way I was performing mysqldump, using the --opt option as you suggested, shaved 5 hours off of my import!
-
Adil almost 9 yearsI recently had to do this for a simple table of ~8 columns and mostly int data. Before applying these, i was getting about ~30 inserts/s (indexes disabled). After the change i was getting ~600 inserts/s. The biggest win comes from setting innodb_flush_log_at_trx_commit from '1' (default) to '2', which flushes writes to log every sec, instead of on every transaction (which is after each insert when a autocommit is true. It is true by default)
-
dr_ over 7 years@Nate There must be other reasons why your import is now faster, because
--opt
is enabled by default in mysqldump. It has been so at least since v5.5 (2010). -
reignsly almost 7 yearsCan you explain what happen when editing this values? For us to understand. I just used this and the speed is awesome.