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]
