MySQL: parallel restores at same time from backup files

backupMySQLparallelismrestore

Slightly related to this question, but for MySQL: Would it be possible/wise to execute 2 parallel restores from a single backup?

Is it possible/wise to do a parallel restore of two independent databases from two separate MySQL dump files?

Restoring one of them seems to only take up about 10% of CPU cycles consistently and takes a long time to finish, so I'm thinking that a second restore shouldn't cause any load problems – but might it cause any other MySQL/DB issues?

Best Answer

This should not be a problem as long as the data in the two dump files involves different tables... however, it may or may not be as helpful as you might hope, because if the single restore is only using 10% cpu, that suggests that your disk, as opposed to cpu, may be the more significant determining factor in limiting the speed of the restore.

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup. The approach you want to use (per this blog) uses somewhat different switches. You want to use the -n -t -x switches, and the -o to specify a log file. So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"

Mysql – slow load speed of data from thesqldump

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use 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.
Turn off foreign key checks and turn on auto-commit.
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

Give it a try..

Best Answer

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

Mysql – slow load speed of data from thesqldump

Related Question