drop the indexes for the conversion then rebuild the indexes after the tables are converted.
On Jan 29, 2008 4:08 PM, David Schneider-Joseph <[EMAIL PROTECTED]> wrote: > Hi all, > > I am attempting to convert a very large table (~23 million rows) from > MyISAM to InnoDB. If I do it in chunks of one million at a time, the > first million are very fast (approx. 3 minutes or so), and then it > gets progressively worse, until by the time I get even to the fourth > chunk, it's taking 15-20 minutes, and continuing to worsen. This is > much worse degradation than the O*log(N) that you would expect. > > I have determined that this is in part due to the indexes on the > table. I have an index on two columns, call them A and B. There is > no relationship between the ordering in A and the ordering in B. If I > create the InnoDB table with only index A, and insert the data into it > in the order of A, then almost no gradual degradation in performance > can be observed between chunks. Similarly, if I create the InnoDB > table with only index B, and insert the data ordered by B, it also > maintains its performance. > > However, I have not been able find a way to insert the data with > *both* indexes, A and B, without suffering this gradual degradation in > performance. I have tried all sorts of methods to convert the table, > such as dumping to a file and importing back into an InnoDB table, > using "ALTER TABLE big_table ENGINE=InnoDB;", importing the data > without the second index and adding it after the fact, and in all > cases it is just a game of whackamole, with the overhead being moved > elsewhere. (Note: my primary key is an integer column, so that is not > the issue here.) > > This problem can even be reproduced in a very simple test case, where > I continuously insert approximately 1 million rows into a table, with > random data. `big_table` can be any table with approximately one > million rows in id range 1 through 1000000 (we're not actually using > any data from it): > > > mysql> create table test (id int NOT NULL auto_increment, x int NOT > > NULL, primary key (id), key (x)) ENGINE=InnoDB; > > Query OK, 0 rows affected (0.08 sec) > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (22.23 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (37.03 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (56.41 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (1 min 8.47 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (1 min 27.67 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > > > mysql> insert into test (x) select cast(rand()*10000000 as unsigned) > > from big_table where id between 1 and 1000000; > > Query OK, 981734 rows affected (1 min 57.93 sec) > > Records: 981734 Duplicates: 0 Warnings: 0 > > Any ideas, anyone? > > Thanks, > David > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- We are all slave to our own paradigm. -- Joshua Williams If the letters PhD appear after a person's name, that person will remain outdoors even after it's started raining. -- Jeff Kay
