Month: August 2009

Not allowing Firefox to install plugins from previous versions is kindof annoying. Especially when the developer doesn’t maintain something. The No-Referer plugin is very simple and handy, but hasn’t been updated to allow it to be installed in Firefox 3.5 or above.

In many cases, all that needs to be done is a version number needs to be incremented in the install.rdf file. I’ve posed a new version of this that works for FireFox 3.7 here

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
 Create_options:
        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.