[ale] Convert MyISAM table to InnoDB takes forever - Solved

[Brandon Checketts]

I tried dumping the table out to disk and importing that, which cut the time
down to about 8 hours.  That still seems unreasonably slow though.

While waiting for it to import, I did some more reading on InnoDB, and found
that it places a lot of emphasis on the primary key.  I'd recommend anybody
interested in InnoDB read through the slides at
http://www.scribd.com/doc/2085411/MySQL-UC-2007-Innodb-Performance-Optimization
which were very informative.

Of particular use to me is the slide about inserting data in primary key order.
 Once I reordered my data and populated the table using that, it took only about
10 minutes (down from 8 hours).


Thanks,
Brandon Checketts

Brandon Checketts wrote:
> 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
>>>
> 
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale