Guide to convert from MyISAM to InnoDB

[Jim Boy]

Assuming you are using MariaDB, this table can be converted to InnoDB.
Remember; MyISAM is not crash resistant. You can lose the entire contents of the table if something goes wrong and the MySQL process stops or worse the system itself stops.

Not really that big an issue for the tables listed.

Am toying with the idea of going to Aurora, which doesn't support MyISAM at all and probably never will.

 

[Xon]

Not really that big an issue for the tables listed.

If your session table crashes and requires manual repair, no one can login to your site. That is rather noticeable compared to the system fixing itself.

 

[Jim Boy]

If your session table crashes and requires manual repair, no one can login to your site. That is rather noticeable compared to the system fixing itself.

Yes, but the point I was making is that the data is disposable. You dont need to repair the table, you can drop and recreate. There will be inconvenience, but there wont be data loss. That would have been a major consideration when the guys specified the table as MyISAM. The benefits of using MyISAM outweighed the risk.

 

[eva2000]

Assuming you are using MariaDB, this table can be converted to InnoDB.

xf_search_index

might want to clarify that as MariaDB 10.0.5+ only as MariaDB 5.5 and MariaDB <10.0.5 don't support fulltext InnoDB AFAIK.

 

[TPerry]

As an aside... http://www.percona.com/blog/2013/07/31/innodb-full-text-search-in-mysql-5-6-part-3/
Just a discussion on the performance of FTS using Percona 5.6.

 

[fly]

I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?

 

[DeltaHF]

Just in case there were any doubts about the benefits of InnoDB...here's a before/after NewRelic application response time graph for my forum (10+ million posts, ~200k daily page views) after converting from MyISAM on March 2nd.

This is a 16GB Linode running @eva2000's Centminmod.



Thanks again for the handy list of ALTER TABLE queries, Tracy!

 

[fly]

I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?

I forgot about this post. Anyone?

 

[TPerry]

I forgot about this post. Anyone?

The DB's tend to be larger and I do believe it uses more memory - but for XenForo, that's what the it was designed to use. I can tell you for a fact that with MyISAM if you have a dirty shutdown you will have problems. Kind of like trying to run a Ferrari on 87 octane gas.

 

[SneakyDave]

I can tell you for a fact that with MyISAM if you have a dirty shutdown you will have problems.

Well, that's just the nature of MyISAM without transactions. You can develop something that doesn't utilize transactions, but yeah, when XenForo is designed to use them, you can have problems if those tables aren't Innodb.

 

[Xon]

I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?

There isn't any real difference for modern versions of MySQL.

But the modern defaults for both MyISAM and InnoDB are aimed at systems with more than a hundred megabytes of memory; so tuning may be required if the total memory of the system is small.

Well, that's just the nature of MyISAM without transactions. You can develop something that doesn't utilize transactions, but yeah, when XenForo is designed to use them, you can have problems if those tables aren't Innodb.

It isn't a matter of transactions, but file layout design.

The Aria table type from MariaDB doesn't support transactions but is crash resistant, where as MyISAM doesn't support transactions and will randomly lose all your data in a table on a crash.

 

[Bubka3]

ALTER TABLE `xf_trophy_combination` ENGINE=InnoDB;
ALTER TABLE `xf_trophy_category` ENGINE=InnoDB;
ALTER TABLE `xf_tmdb_ratings` ENGINE=InnoDB;

Doesn't exist on default installs. Or at least mine.

 

[TPerry]

Very likely that those were for another add-on that I missed.... I know the last one was. I think the other two may be one of Waindigo's add-ons. I did most of them from memory since I didn't have blank install. Will update the listing. Thanks!

 

[MikeMpls]

Why should these be left as MyISAM as per Guide to convert from MyISAM to InnoDB?

xf_import_log
xf_search_index
xf_session
xf_session_admin

 

[bzcomputers]

Why should these be left as MyISAM as per Guide to convert from MyISAM to InnoDB?

xf_import_log
xf_search_index
xf_session
xf_session_admin

That guide and alot of the responses to it are quite old...
xf_import_log is now import_log_* which is InnoDB by default.

There are still 4 tables that are MyISAM by default:
xf_search_index
xf_session
xf_session_admin
xf_session_install

There were some limitations with early versions of InnoDB including fulltext search limitations. The xf_search_index has fulltext indexes. These are now supported by InnoDB (as long as you have a version of MySQL and MariaDB released in the last 5 years or so).

The xf_session* tables I believe remained MyISAM due to early performance issues with InnoDB and the use of delayed inserts. I am pretty sure none of that implies anymore.

I personally haven't converted these table over to InnoDB ...yet, but I know some have without issue. Maybe they will chime in with their results.

 

[TPerry]

That guide and alot of the responses to it are quite old...

Considering it was in the 1.4 version era... yep, QUITE old.

As explained in the guide.. this was to get back to, what AT THAT TIME, was a default install of XF under the existing mySQL engines. Things have progressed in the 8 years since then.... and some of those tables can now be used reliably in INNODB format.