MySQL Forums
Forum List  »  Replication

Re: Duplicating large database
Posted by: Rick James
Date: May 04, 2013 01:22PM

46 hours for 800GB -- probably reasonable due to disk speeds.

"Replication" is useful for keeping a copy in sync, but not for "replicating" in the sense of "duplicating".

If it is InnoDB, then XtraBackup is pretty handy for duplicating.

If you can set up an LVM snapshot, then you can do the "dump" step in virtually no time. Still reloading elsewhere will be time consuming.

OTOH, If you have lots more space than 800GB on the one machine, then LVM may be very fast this way:
0. Must set up LVM
1. Stop mysqld
2. Take a "snapshot". (This is both the "dump" and "reload" step. It takes virtually zero time, regardless of dataset size.)
3. Start mysqld
4. Start another instance of mysqld in the snapshot.
(So far, the disk consumed is not much more than the original 800GB.) (See below)
5. In the second instance do the ALTERs, etc
(Now the "copy on write" of LVM will start exploding the space requirements, possibly to 2*800GB. And it will take many hours.)

Because step 5 will be big, step 0 probably had to allocate >800GB for the second "logical volume". Hence my comment in step 4 is semi-wrong.

The speed savings in the LVM approach is that "dump" and "reload" steps take virtually no time, regardless of the data size. Adding PARTITIONs and/or INDEXes will still take many hours.

Would you like to discuss the schema and the possible PARTITIONings? There are many _useless_ ways to partition. I may be able to point you at the few 'good' ways.

http://mysql.rjweb.org/doc.php/ricksrots#partitioning

A RAID controller with multiple drives, preferably 15K SAS drives, would help some on the speed. So would SSDs ($$$). In either case, it might be challenging to get enough the 2+ TB to do the clone + ALTER for one extra instance.

Options: ReplyQuote


Subject
Views
Written By
Posted
1396
May 03, 2013 02:15PM
Re: Duplicating large database
1119
May 04, 2013 01:22PM
696
May 04, 2013 02:10PM
723
May 05, 2013 01:12PM
653
May 05, 2013 02:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.