Converting Tables to InnoDB Takes Forever

I have been familiar with some of the benefits of the InnoDB storage engine for a long time. Of particular interest to me has been row-level locking which should significantly reduce some locking problems that I have on a big table during peak times.

I have made various attempts over the past six months or so to convert this table to InnoDB, but each attempt has always taken a tremendously long time and I have always ended up canceling the query before it completes, because it doesn’t seem to be making any progress.

I finally have had a reason and opportunity to dig into this more and have spent the last day or two learning and experimenting with InnoDB. Of particular use was this set of slides from a 2007 presentation on InnoDB performance. It had some very good information about understanding how InnoDB works.

Slide 9 of the presentation includes this extremely helpful bit:

PRIMARY KEY in random order are costly and lead to table fragmentation (primary key inserts should normally be in an ascending order)
Load data in primary_key order if you can

In the data that I have been attempting to convert from MyISAM, my primary keys are inserted in whatever order that that the data has occurred. When converting that to InnoDB, it would basically run a bunch of insert statements using my unordered data. Each insert would require the storage engine to move a bunch of data in the InnoDB table around to keep the primary keys in order.

In my case, I was converting a MyISAM table containing about 1.9 million rows and occupying 600 MB of disk space. That took over 8 hours using the unordered data. After ordering the data and retrying, it then took about 10 minutes.

Also, I learned the usefulness of the ‘SHOW TABLE STATUS’ command. It is semi-useful in seeing the progress of the import. It is a bit strange to me it shows the number of rows in the table changing by 10% every second, but it is better than nothing:

mysql> SHOW TABLE STATUS WHERE name = 'sometable'

*************************** 1. row ***************************
           Name: books_innodb
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2158501
 Avg_row_length: 286
    Data_length: 617611264
Max_data_length: 0
   Index_length: 70008832
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-08-25 10:03:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
        Comment: InnoDB free: 1799168 kB

Also worth noting is the data inserted in random order used about 30% more space according to the Data_length value.

Leave a Reply

Your email address will not be published. Required fields are marked *