List:General Discussion« Previous MessageNext Message »
From:Karen Abgarian Date:December 2 2011 1:28am
Subject:Re: best way to copy a innodb table
View as plain text  
Hi, 

I have a support case with MySQL opened on this subject.   Here is what we were able to
come up with.  

1.  Create the table with the primary key and unique key constraints defined but no
secondary indexes. 
2.  Bump up InnoDB logs to 2M and especially memory to the highest there can be. 
3.  Disable binary logging, InnoDB sync if running that. 
4.  Split the job into several insert..select statements using some criteria dependent on
the table. 
5.  Run the load in parallel. 
6.  Create secondary indexes via fast create option. 

My experience is that the inserts run at blazing speed until the table roughly becomes as
big as there size of the Innodb pool. 
The inserts then slow down gradually to like 100 inserts/second.  The net result is that
the 25M of records can, say, be loaded 
in an hour, and the remaining, say 25M of records would be loaded in, like, 24 hours.  

If anyone can shed some light on it that would be great.   I observe no bottlenecks
anywhere on the OS.  CPU is low, no paging,
no significant disk activity.   

Tx
Karen.




On Dec 1, 2011, at 2:16 PM, Angela liu wrote:

> Hi, folks:
> 
> 
> I have a situation:
> 
> A large innodb table t1 with 45 million rows, need to have a new table t2 exactly the
> same as t1, to copy the data from t1 to t2, I have the following query:
> 
> create table t2 like t1;
> 
> insert into t2 select * from t1;
> 
> 
> but the above insert may run long time , that can interface with performance, is
> there a way to chunk the insert into the new table? like breaking it down into chunks of
> 100,000 rows ?
> 
> 
> Thanks

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