I have a client, who still uses MyISAM. They asked me about this "InnoDB" thing and wondering if they should change their tables to InnoDB. I looked at their MySQL instances and they were running MySQL 5.1 and didn't even have the InnoDB plug-in installed. They have a sharded setup, so what we decided to do was convert one shard (all the tables on a single instance) to InnoDB. We got the InnoDB plugin installed and enabled and then created a migration to convert all the tables on the server to InnoDB (actually wrote a store procedure to do this).
Before the migration, we collected "slow query logs" with the threshold set to 0, in order to collect all running queries. Ran this through pt-query digest to get a baseline on how the server was operating under MyISAM. Started converting tables to InnoDB "online". It took a number of days but we slowly got everything converted over. This was tricky because we had to allocate memory to both the key_buffer_cache and the innodb_buffer_pool. The client uses master-master and so I was able to restart one master after making changes to the my.cnf and allow traffic to failover to the other server to maintain availability during these changes. At last everything was InnoDB and were able to dedicate about 75% of the server's memory to innodb_buffer_pool with innodb_file_per_table turned on. Lowered the key_buffer_cache to about 32 MB because it is still used with temp tables, and systems tables.
The good news was that we saw about a 15% improvement on queries. There were a few queries that got slower but overall the client is happy with the improvement. I told them that we will see a much greater improvement after we got off MyQL 5.1 and start looking at MySQL 5.5 or MySQL 5.6 because MyISAM is not getting any love and all the performance improvements have been for InnoDB. Additionally, there were a number of reporting queries that were taking minutes to complete and now are completing in about ten seconds.
The thing I had to be careful about was making sure the client still had enough disk space to handle InnoDB. Because InnoDB tables use clustered indexes, disk space usage increased by nearly 40%.
Another side note was the client started to get deadlock errors when before they hadn't had them. Since they were always using MyISAM, deadlock errors were not possible because MyISAM is not transactional. We've had to work through these deadlocks by changing how the application us using master-master and limit the number of concurrent operations to some tables.
