[ale] Convert MyISAM table to InnoDB takes forever

[Brandon Checketts]

I've historically used MyISAM tables for almost everything because they seem to
work well in most cases.  However, I now have a problem where the table-level
locking is causing some delays and converting the table to InnoDB with row-level
locking seems like the most straightforward solution.

I've made various attempts at converting the table to InnoDB, and each time it
takes seemingly forever to insert data into the table.   I've let it run for
half a day before and I eventually give up figuring that it will never finish.
The MyISAM table is not terribly huge.  Its about 600 MB with around 1.9 million
rows.

I've tried 'ALTER TABLE...ENGINE=InnoDB', a few months ago without success.
Yesterday & today I've been attempting a 'INSERT INTO newtable SELECT * FROM
oldtable', but have the same results.

I don't have anything special with regards to InnoDB in my.cnf, which might be
part of the problem.  I seem to remember trying different values for
innodb_flush_log_at_trx_commit, but I can't remember for sure, and I don't know
that it would make that much difference.

Some details about the software and hardware:

[root at rimu1 ~]# cat /etc/issue
CentOS release 5.3 (Final)
Kernel \r on an \m


mysql> show table status where Name = 'books'\G;
*************************** 1. row ***************************
           Name: books
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1884858
 Avg_row_length: 286
    Data_length: 539451700
Max_data_length: 281474976710655
   Index_length: 88403968
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-06-04 02:10:23
    Update_time: 2009-08-24 14:24:21
     Check_time: 2009-01-22 09:45:21
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

CREATE TABLE `books` (
  `isbn13` varchar(13) NOT NULL default '',
  `isbn10` varchar(10) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `author` varchar(255) NOT NULL default '',
  `binding` varchar(20) NOT NULL default '',
  `edition` varchar(20) NOT NULL default '',
  `msrp` decimal(6,2) NOT NULL default '0.00',
  `pages` smallint(5) unsigned NOT NULL default '0',
  `published_date` date default NULL,
  `publisher` varchar(255) NOT NULL default '',
  `rank` int(10) unsigned NOT NULL default '0',
  `rating` decimal(3,1) NOT NULL default '0.0',
  `images_ser_gz` blob NOT NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`isbn13`),
  KEY `isbn10` (`isbn10`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.45-log |
+------------+


I'm trying to test the process on a backup server, so hardware is not terribly
beefy.  It is a Dual Core AMD 4200 processor with 4 GB of Ram and dual 500 GB
SATA drives in a software raid array.


Any thoughts on what I might do to speed this up?  Or does this seem like normal
InnoDB performance?

Thanks,
Brandon Checketts