I have a large forum (>6 million posts) and numerous times a day, there will be a situation where a mysql query to load the message index of a particular board takes a good while to complete, and usually the query queue gets backed up with an UPDATE query 2nd in line, after the msg index SELECT query. The messages table is currently MyISAM, and my interpretation of what is going on here is that because UPDATE requires a table lock due to needing exclusive access, it means all the SELECT queries made after the UPDATE query start getting backlogged until the UPDATE query can run (which itself is waiting on the slow message index query). I've been watching the query queue when the forum stalls out and this happens just about every time. Just to note, other tables mentioned in this thread (https://www.simplemachines.org/community/index.php?topic=293441.0) have already been converted to InnoDB.
Is this a case where switching the messages table to InnoDB would be beneficial? After doing some research this morning I've noticed that users were discouraged from switching the messages table to InnoDB in SMF 1.x and 2.0.x due to the fulltext search option and that unless you get a ton of new posts on a regular basis, it's not all that helpful. I use Sphinx for search, so I don't think the fulltext issue is relevant for me. However, would InnoDB be useful in solving my issues?
Also, just curious, but approx. how long would it take to convert the messages table to InnoDB? Size is roughly 6.8 gb.
OK, so let's cover the initial stuff.
Yes, your problem is that the entire table locks pending writes (and writes happen quite often for things like the view count), so having InnoDB will immediately improve your situation.
If you care about fulltext indexing (using the natural fulltext, not Sphinx), then you'd want to be on at least MySQL 5.6 (I forget what version you'd need to have InnoDB with fulltext, but it is supported in 5.6+), but since you're using Sphinx I suspect this is a non issue in every respect because Sphinx won't care which table type, it's just most people don't have a setup that can cope with Sphinx.
The conversion mostly depends on your server and how quickly your server can rewrite the entire table. You can experiment with making a clone of the table to see how long it would take - https://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html#innodb-convert-convert - but before doing the real conversion you'd want to shut the forum to avoid losing any messages.
Thanks Arantor. Another question - the fulltext index option was set before Sphinx, and it built a large index for searches (>2gb), but we've since moved to Sphinx (quite some time ago, actually). I wasn't the one who set this up originally, so I'm not sure if the fulltext index is still necessary? I know that is not even compatible with InnoDB, but in terms of Sphinx, I can delete the index right now and it won't affect anything, right? I don't plan on doing the InnoDB conversion for a few days at least, but in the meantime can I delete that?
It isn't necessary, no, you can delete it.
I did try to clone the table first using the method described on that mysql doc page, but it didn't seem to work as it spent over an hour just building an index and didn't even get to inserting anything. I only knew that because I had pulled a list of active queries to see what it was doing. Will have to try again at some point.
What do your my.cnf settings look like for MySQL? and specs machine that hosts the database.
I'll have to come back to this later. In retrospect, I need more RAM added to the server before this will become viable, I think. The server has another large website on it which mostly uses MyISAM, and the 8gb on there now is already pretty much maxed out without the necessary dramatic increase in InnoDB's buffer pool being factored in.
Thanks for the help!