List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 2 2013 7:35pm
Subject:RE: best way to copy a innodb table
View as plain text  
The particular example given here is unsafe and slow.

* Without an ORDER BY, you are not guaranteed that the chunks will be distinct.

* If there are any INSERTs/DELETEs between chunk copies, you will get dups/missing rows
for two reasons:  the inserted/deleted rows, and the OFFSET is not quite right.

* OFFSET requires walking over the skipped rows.  As you get farther into the table, this
takes longer.  That is, you have an ORDER(N**2) operation, not what could be ORDER(N).

* If replication is involved, 1M rows is a lot -- there will be noticeable delays where
other replication activity is stalled.

If you have an AUTO_INCREMENT PRIMARY KEY, then using WHERE id > 1000000 AND id <=
2000000 is a better approach -- Order(N), and chunks guaranteed to be distinct.  Still,
it is not immune from INSERTs/DELETEs.  Replication is fixed by decreasing chunk size
(and by avoiding OFFSET).

> -----Original Message-----
> From: Arjun [mailto:nagav@stripped]
> Sent: Tuesday, July 02, 2013 12:48 AM
> To: mysql@stripped
> Subject: Re: best way to copy a innodb table
> 
> Well, the easy way to chunk the inserts is by use of limit. Here is what I
> used for one of my projects:
> 
> Insert ignore into t1 (f1, f2, f3)
> Select f1, f2, f3 from t2 limit 1000000, 1000000
> 
> Inserts 1M records at a time starting from 1M th record in t2 and you can
> keep incrementing this offset as you progress. This will help in
> monitoring the table inserts and at the same time move chunks of records
> from source table.
> 
> Enjoy!
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

Thread
best way to copy a innodb tableAngela liu1 Dec
  • Re: best way to copy a innodb tableClaudio Nanni1 Dec
    • Re: best way to copy a innodb tableAngela liu1 Dec
  • Re: best way to copy a innodb tableMiguel Angel Nieto1 Dec
    • Re: best way to copy a innodb tablePerrin Harkins2 Jul
  • Re: best way to copy a innodb tableKaren Abgarian2 Dec
  • Re: best way to copy a innodb tableArjun2 Jul
    • Re: best way to copy a innodb tableMichael Dykman2 Jul
    • RE: best way to copy a innodb tableRick James2 Jul