There’s an interesting post over at the Mysql Performance blog testing performance differences between several storage engines. Their tests show that for some micro benchmarks that cover a lot of the basic usage patterns of databases in a web type environment, that InnoDB can actually be much faster than MyIsam. This goes against the prevailing belief that MyIsam was the fastest for read access especially in the very read heavy world of Web applications and that InnoDb was only used when transactions were required.
I’ve used InnoDb for the backend of my latest personal project, Cloudgrove, which actually has a heavier tilt toward write performance. I’ve always been concerned about read access in very large threaded systems when there are some write access occurring concurrently. The table level locking of MyIsam seemed like it could cause problems as all reads would be blocked while a write occurred. With InnoDb there is row level locking so that writes would not block at the table level.
I’ve got a good feel for how very large MyIsam tables handle load while doing performance work at webshots, using ~20 large db servers in Read / Write Master – Read Only Slave setups. I never had a chance to see how a similarly structured InnoDB setup would behave though. As Cloudgrove grows though I should get a good view on how it will behave.
I’m also using foreign keys currently to enforce data consistency and that could have a much larger negative impact on performance. Might have to pull those out and move data consistency up to the application layer.