[ale] Convert MyISAM table to InnoDB takes forever

[Brandon Checketts]

Thanks Richard.  Trying that now.

Brandon

Richard Bronosky wrote:
> The performance of the insert...select process isn't great. Try
> mysqldumping the table to a file. Edit the file to modify the table
> create statement. Then redirect to file in to the mysql cli client.
> This should cut your disk i/o in half.
> 
> On 8/24/09, Brandon Checketts <brandon at brandonchecketts.com> wrote:
>> 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
>> _______________________________________________
>> Ale mailing list
>> Ale at ale.org
>> http://mail.ale.org/mailman/listinfo/ale
>>
>