---- "hot switch" from MyISAM to InnoDB. Is this dangerous?
1script - 4:56 pm on Sep 1, 2010 (gmt 0)
This is a table that gets a remarkable number of writes of all kinds: Updates, Inserts, Deletes.
I would never do such hot switch myself. Just put "we'll be back" page for a few minutes via a single rule in .htaccess and then take it down as soon as you are done.
But the bigger question is of course do you need to convert at all. The UPDATEs/INSERTs may be stacking up only because SELECTs are not done efficiently and quickly enough. Also, InnoDB does not do well with full text searches and generally any large text or blob fields. Counts as in COUNT(*) can be slow. And also important - InnoDB file sizes are much bigger which makes them slow to load and if your RAM is not too big (common on VPSes) - this may be a performance killer.
So, it's not really a silver bullet. You kinda need to look at your application first. Fix the bottlenecks there, see if you can actually use the benefit of transactions, tweak your mysql server settings. THEN decide if you still need to convert. And no, definitely not hot switch.