From: Rick James
Date: July 2 2013 7:35pm
Subject: RE: best way to copy a innodb table
List-Archive: http://lists.mysql.com/mysql/229630
Message-Id: <582AFBFC517D194489EF570FE21694CF141CF12D@GQ1-MB04-02.y.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
The particular example given here is unsafe and slow.
* Without an ORDER BY, you are not guaranteed that the chunks will be disti=
nct.
* 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 t=
he table, this takes longer. That is, you have an ORDER(N**2) operation, n=
ot 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 AN=
D id <=3D 2000000 is a better approach -- Order(N), and chunks guaranteed t=
o 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
>=20
> Well, the easy way to chunk the inserts is by use of limit. Here is what =
I
> used for one of my projects:
>=20
> Insert ignore into t1 (f1, f2, f3)
> Select f1, f2, f3 from t2 limit 1000000, 1000000
>=20
> 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.
>=20
> Enjoy!
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql