50

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.

Alex R
  • 11,137
  • 13
  • 86
  • 164
deadprogrammer
  • 11,132
  • 23
  • 72
  • 84

9 Answers9

24
  1. Get a copy of High Performance MySQL. Great book.
  2. Extended inserts in dumps
  3. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile
  4. Import with multiple threads, one for each table.
  5. 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.
  6. 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.
JBB
  • 4,235
  • 3
  • 23
  • 25
15

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.

Alister Bulman
  • 33,278
  • 9
  • 69
  • 109
13

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.

kapex
  • 27,631
  • 6
  • 104
  • 117
Aleksandar Ivanisevic
  • 2,879
  • 3
  • 16
  • 8
  • I wonder, wouldn't setting it to `0` be even faster? – Petr Jun 01 '13 at 07:50
  • really 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). – arun Sep 22 '13 at 01:19
  • 1
    Just curious, does SET autocommit=0 render this unnecessary? – Sian Lerk Lau Jan 02 '14 at 02:50
5

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 to mysqldump.
  • 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).

Ztyx
  • 12,720
  • 14
  • 75
  • 110
  • [Reference](http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_disable-keys) says `--disable-keys` is 'effective only for nonunique indexes of MyISAM tables'. – kapex Mar 02 '13 at 10:57
  • kapep - thanks for the correction. I've updated the answer. – Ztyx Mar 27 '13 at 00:09
4

Using extended inserts in dumps should make imports faster.

che
  • 11,907
  • 7
  • 44
  • 71
  • 1
    if you do that there is a good chance you will not be able to import back if the dump is even moderately big – Jonathan Feb 21 '09 at 20:33
  • How come MySQL client isn't able to process even moderately big dumps with extended inserts? – che Feb 21 '09 at 22:07
  • My guess is that the client has a fixed size buffer for each line it is reading and extended inserts exceeds that limit. – Ztyx Apr 19 '10 at 09:10
  • fwiw I've imported a 10GB dump using extended inserts and it has gone flawlessly. – alxgb May 23 '14 at 14:24
4

turn off foreign key checks and turn on auto-commit.

longneck
  • 287
  • 2
  • 2
1

mysqlhotcopy might be an alternative for you too if you only have MyIsam tables.

Ztyx
  • 12,720
  • 14
  • 75
  • 110
-2

Using indexes but not too much, activate query cache, using sphinx for big database, here is some good tips http://www.keedeo.com/media/1857/26-astuces-pour-accelerer-vos-requetes-mysql (In French)

Peter O.
  • 30,765
  • 14
  • 76
  • 91
paul
  • 1
  • You 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: http://meta.stackexchange.com/q/8259 – Peter O. Nov 14 '12 at 06:10
-2

Another alternative is http://www.mydumper.org - multi-threaded mysql backup/restore which is 3x to 10x times faster than mysqldump and can handle both MyISAM and InnoDB as well as Drizzle http://vbtechsupport.com/1695/

p4guru
  • 1,274
  • 2
  • 17
  • 23