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.