Big board upgrade from XF1 to XF2 soon, should we go MySQL 8 or MariaDB ? and storage engine?

Filetrip

Active member
Hello,

Our board has been running XF1 up until now because, well, the XF2 upgrade is scary :)
Database is pretty large, these are our biggest tables : https://prnt.sc/1as3sbz (if you don't wanna click: 35 million xf_ip, 9 million posts xf_post, and a bunch of other things)

We are going to get a new server in the process so as to upgrade components.
We're thinking of PHP 8 which is apparently supported by XF2.
But more importantly, and the subject of this thread: the database server & storage engine.

I have asked XenForo's support for advice, they did give me a bit of info thankfully, but they mostly referred me here to get feedback from the community.
Currently, we're under Mysql 5.7. The storage engine as seen on the screenshot is mostly MyISAM, but a bunch of other smaller tables are InnoDB.
I am being told that MySQL 8 is phasing out MyISAM so that's out of the picture.

1) Should we go for MySQL 8 ?
If so, what storage engine do you recommend? should we change the storage engine for all the tables to InnoDB?

2) Or, should we go for MariaDB instead?
If so, what is the recommended storage engine that we'll need to switch to?
I imagine we'll have to "mysqldump" our database and reimport it in MariaDB, will that cause compatibility issues?

3) we're going to update the tables to utf8mb4 thanks to the instructions given here: https://xenforo.com/community/threads/converting-to-utf8mb4.133564/ (which are confirmed to be still valid by XenForo support). Will that work with MariaDB too if we were to retain that solution?

If you have any other information that you think would be useful I'd be happy to hear it.
Thanks in advance for your replies!
 

briansol

Well-known member
I recommend MariaDB 10. It's a direct upgrade to mysql, not export/import required and the utf8mb4 will work the same as well.

I would also recommend you provision the new server in the similar technology that you have today, migrate, then upgrade the technology and then upgrade xf.

What might not work is post-upgrade, the table engine may not move to innodb. I had to manually change about 25 tables when i upgraded from 1.0
 

Filetrip

Active member
I recommend MariaDB 10. It's a direct upgrade to mysql, not export/import required and the utf8mb4 will work the same as well.

I would also recommend you provision the new server in the similar technology that you have today, migrate, then upgrade the technology and then upgrade xf.

What might not work is post-upgrade, the table engine may not move to innodb. I had to manually change about 25 tables when i upgraded from 1.0
hey Brian,
thanks for your reply and suggestions!
when you say MariaDB 10, do you mean 10.X (latest, ie. 10.6 currently) or do you mean like straight 10.0 ?

as for MyISAM to InnoDB: yeah, I imagine we'll have to make the switch manually from command line.
I've tried doing this before but performances were awful, the "posts" table in particular did not like being converted to InnoDB at all.
But that may have been under MySQL 5.5. Hopefully with MariaDB that will be acceptable.

I'm guessing that everyone that has upgraded to XF 2 and that has a relatively large board is using InnoDB?
 

briansol

Well-known member
Yes, latest maria. I'm on 10.3.30 but plan to update soon.

I had no real issues converting the tables. I had 1.5mln posts when i converted and the longest table was maybe 25 seconds.
 

Xon

Well-known member
There is utterly no reason to use MyISAM for any sort of user data. You absolutely should be using InnoDB.

I would actually recommend changing all tables to InnoDB, which includes what are normally memory tables. This includes the xf_search_index table, modern versions of MySQL/MariaDB support full-text search on non-myiasm tables.
 

duderuud

Well-known member
Why didn't Xenforo do that out of the box? Memory table should be faster than Innodb for example...
 

eva2000

Well-known member
Why didn't Xenforo do that out of the box? Memory table should be faster than Innodb for example...
There's no way for Xenforo to know what MySQL version folks use. Well they could programmatically determine the version via the installer I suppose, and change tables accordingly :)
 

Filetrip

Active member
There is utterly no reason to use MyISAM for any sort of user data. You absolutely should be using InnoDB.

I would actually recommend changing all tables to InnoDB, which includes what are normally memory tables. This includes the xf_search_index table, modern versions of MySQL/MariaDB support full-text search on non-myiasm tables.
thanks for the recommendations everyone, it's great to have input from fellow board admins

yeah, MyISAM isn't something I really chose haha just been leaving everything as is, so it's default settings really. But I'm getting the point now.

as for xf_search_index, we have the ElasticSearch addon, so that table will be irrelevant, right? at least that's what I believe to be the caser under XF1
 

Xon

Well-known member
I'ld still convert all the XF tables so you don't need to worry about a crash causing issues just to be safe.
 

Chris D

XenForo developer
Staff member
yeah, MyISAM isn't something I really chose haha just been leaving everything as is, so it's default settings really. But I'm getting the point now.
I’m not sure what has happened exactly but the majority of XF tables have always been InnoDB by default, since XF 1.0.

I don’t understand why most of your tables would be MyISAM. We literally create them from code with the InnoDB engine in the majority of cases.

A notable exception being the xf_search_index table. I’d actually leave that as MyISAM as XF code expects it to be such and the full text queries we use may not be fully compatible with InnoDB full text search. That said, if you have that many posts I’d recommend using XF Enhanced Search which negates the need to use that table entirely.

I’ll reply again shortly to let you know what tables should not be InnoDB by default.
 

Chris D

XenForo developer
Staff member
Table nameDefault engine
xf_attachment_viewMEMORY
xf_search_indexMyISAM
xf_sessionMyISAM
xf_session_activityMEMORY
xf_session_adminMyISAM
xf_session_installMyISAM
xf_thread_viewMEMORY

These are the only tables that, by default, should be anything other than InnoDB. All the others should be InnoDB.

As per @Xon's advice you may be able to change some others to InnoDB but in the interests of making as fewer changes as possible for now I'd recommend getting your database to the default state first of all, then consider changing the defaults down the line.

(We don't officially recommend deviating from the defaults but we're aware that some people do).
 

Xon

Well-known member
@Chris D I would seriously recommend changing the defaults for xf_session/xf_session_admin/xf_session_install to InnoDB.

MyIASM isn't crash resistant. This means if the server crashes and MySQL restarts; the entire table can be corrupted requiring manual intervention. Which for xf_session (or xf_session_activity) means the site is essentially offline.

It is only with XF2.1+ that XenForo minimum version floor for xf_search_index is high enough that InnoDB full-text search works reliably as older versions where dodgy.

There are another advantages to all InnoDB tables (rather than a mix of MyIASM and InnoDB);
  • Cross engine table joins no longer risk invoking the cross-engine transaction manager which can trigger what is essentially full-table locks on the innodb tables.
  • MyIASM buffers can be reduced allowing more memory for InnoDB
  • Improved concurrent access to session table.
  • A fully-crash resistant database. InnoDB should nearly always be able to rollback or forward transaction. Where as MyIASM the table data is toast until an admin manually triggers recovery
 
Last edited:

Chris D

XenForo developer
Staff member
We know this and it has been discussed in the past. I'm sure it will be something we change in the future.

For now, for the purposes of this discussion, our advice remains to set the defaults as described above.
 

Filetrip

Active member
thanks for all the feedback & recos, this is going to make my life much easier. Surely this thread will help others as well.

As to why some tables are MyISAM on my DB, while they should be InnoDB, no mystery here - it's probably something I did (or one of my tech guys) a while back when we had workload issues. It's probably deviated from the default. Back in 2018 at our peak we had over 100 million page views/year. Our servers had issues during traffic peaks so we tried a very wide array of solutions to attempt to get it fixed. To date we still have issues, once a day, I suspect due to cron jobs and due to the mountain of addons we are using. Our migration to XF2 will allow us to dramatically reduce the number of addons. With a database server upgrade, I'm confident things will improve.
 

Chris D

XenForo developer
Staff member
Regardless of where data comes from, you have to install XF in the normal way and we only create the tables with InnoDB so it will be nothing to do with the import from another platform.
 
  • Like
Reactions: rdn
Top