After some talk with the developers of AIR, I did some of my own research into the difference between the MyISAM and InnoDB transactional storage engines found in MySQL, and found some interesting things. Contrary to my first belief, MyISAM under-performs when it comes to large-scale, simultaneous transactions (you really start seeing performance issues at around 1000 simultaneous transactions).
At first, all of the results I had were pointing to MyISAM being better, but after making InnoDB’s buffer pool larger and setting autocommit to 0 (meaning that it won’t auto commit any transactions), the results still weren’t looking too good, other than the CPU consumption was considerably lower (10-15%).
One thing I really liked was the fact that it uses row locking instead of table locking, which means that simultaneous reads and writes can be done without having to wait for the table to be unlocked. MyISAM is good for small scale websites, as InnoDB performs slower if there are fewer than 100 or so simultaneous connections and if the data is small (below ~10MB per transaction).
For now I think I will stick to MyISAM for my everyday coding, but I already know a few sites that could benefit from an engine switch…
