Determining whether InnoDB is a good fit

[eGullet]

Out forum is mostly InnoDB, with just the posts, topics, and messages tables as MyISAM. Our host was helping with some issues the other day and noticed that those large tables were still MyISAM and suggested that we switch to InnoDB for performance reasons. On the surface this seems reasonable, but I'm wondering if there is a better way of determining whether we would see any benefit from going to MyISAM (for the record, we use Sphinx for searching, so fulltext is not an issue). What factors do I need to consider when deciding whether to do this switch, and what sorts of things could I measure or look for in the logs to see if it would matter to us?

[Lindy]

You'll gain great benefit from switching to InnoDB provided you've optimized for such. One word of caution, however, is ensure you've enabled innodb_file_per_table.

InnoDB has always been the best option in terms of performance, but until several years ago, it was the least stable. Since then, more resources have been put into innodb and conversely, development of MyISAM has virtually stopped. In fact, as of MySQL 5.5.5 it's not even the default anymore. For your (as an IPS user) purpose, innodb will serve you well.

[Makoto]

You may even want to consider going a step further and migrating to MariaDB + XtraDB over MySQL + InnoDB.

MariaDB is a fully backwards compatible fork of MySQL, and XtraDB is similarly a fork of InnoDB with various performance and scalability enhancements.

If you're interested, shoot me a message and I'd even be willing to do all the work for you (including configuration and tuning) for a reasonable rate.

[eGullet]

Thanks for the suggestion Kirito: with our current setup we are limited to MySQL, but I'll keep that option in mind for the future. Can anyone give me a ballpark estimate of how long converting a 2.4GiB table might take? Are we talking about seconds, minutes, or hours here?

[Makoto]

It doesn't/shouldn't take long. Minutes.

[eGullet]

Thanks: in the end the whole process took about 45 minutes, most of which was spent backing up the tables as a "just in case" measure.

[eGullet]

A related question: for speed reasons up to this point we've had "Update topic views immediately" set to "No". Does switching to InnoDB obviate this setting?

[Makoto]

There's still row level locking, so I'd say it's still better to leave it set to "No" on high (or even medium) traffic forums.

I don't see a real need for immediate updating of topic views anyways. I always leave it off.

[Grumpy]

A related question: for speed reasons up to this point we've had "Update topic views immediately" set to "No". Does switching to InnoDB obviate this setting?

The problem with immediate update is that it forces a frequent purge of cache.