Jump to content

Determining whether InnoDB is a good fit


Recommended Posts

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?

Link to comment
Share on other sites

  • Management

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...