MyISAM to InnoDB Conversion: Converting almost a billion records

So my challenge last week was to convert almost a billion records in 37 databases and 74 tables. Oh, and I wanted to do it only during the night (between 12:30AM and 6AM) and in the least number of nights.

This was not my first massive MyISAM to InnoDB migration, nonetheless, I still manged to learn a few things. We're at a stage where MyISAM just isn't the solution any more. Our plans are to go almost 100% InnoDB and this migration project completes yet another massive step towards that direction.

The first night was depressing. I applied every trick up my sleeve but only ended up getting 9.48% complete. Second night was only half depressing allowing me to complete ~ 33%. Third night was a charm and I got ~42.5% done. I would have finished the project that night but there was some storage issue and so I completed the last ~15% on the fourth day. Of course, optimizations and the method of conversion I selected really saved the day in the end.

Now that this has been accomplished, the next step will be to cover another 700-900 million records in a different cluster. Oh well, I will start doing that this weekend.

As far as performance is concerned, I couldn't be more happier. Load average on all servers in this cluster is well under 0.30 and each server is happily doing upto 1800 queries per second.

Note: Indexes were not dropped. Primary key did not consist of an integer. There was a composite primary key on all tables. The servers continued to handle production traffic on all databases on which migration was not being performed.

Update: What I did in a nutshell, for very large tables, was:

1. set innodb_flush_log_at_trx_commit=0 in my.cnf and restart
2. create new table tbl_innodb
3. set sort_buffer_size and read_rnd_buffer_size

SET SESSION sort_buffer_size=512*1024*1024; 
SET SESSION read_rnd_buffer_size=512*1024*1024;

4. then insert into tbl_innodb selecting from tbl_myisam and sorting by primary key
5. rename tbl_innodb to tbl

Of course, you need to have sufficient memory or adjust the buffers accordingly. But doing this ended up working great for me.

Update2: Regarding read_rnd_buffer_size, MySQL Manual says,

"If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations."

Peter Zaitsev has also written about read_rnd_buffer_size.

Update3: My system (Solaris 10) had 10G of memory and read/writes were done to mounts residing on a SAN.

Update4: Best case scenario was 9600 records / second on average. Worst case was 500 records / second on average.