[ale] Convert MyISAM table to InnoDB takes forever

[Richard Bronosky]

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
>

-- 
Sent from my mobile device

.!# RichardBronosky #!.