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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
