Guide to convert from MyISAM to InnoDB
- Thread starter TPerry
- Start date
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
Well-known member
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.
TPerry
Well-known member
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.
Just a discussion on the performance of FTS using Percona 5.6.
DeltaHF
Well-known member
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!
This is a 16GB Linode running @eva2000's Centminmod.
Thanks again for the handy list of ALTER TABLE queries, Tracy!
TPerry
Well-known member
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.
Last edited:
SneakyDave
Well-known member
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.
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.
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.
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
xf_import_log
xf_search_index
xf_session
xf_session_admin
bzcomputers
Well-known member
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
Well-known member
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.
Similar threads
- Question
