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

[Filetrip]

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]

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]

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]

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.

 

[SIneDave]

MariaDB all the way. Our 5 million post conversion was extremely fast and easy.

 

[Xon]

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]

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

 

[eva2000]

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]

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

 

[duderuud]

Correct

 

[Xon]

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]

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]

Table name	Default engine
xf_attachment_view	MEMORY
xf_search_index	MyISAM
xf_session	MyISAM
xf_session_activity	MEMORY
xf_session_admin	MyISAM
xf_session_install	MyISAM
xf_thread_view	MEMORY

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]

@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

 

[Chris D]

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]

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.

 

[briansol]

I think the vb 3 to xf 1 migration leaves the tables in myisam as i had the same issue.

 

[Chris D]

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.

 

[Filetrip]

hello,

a final word on this thread from me!
Our XF2 & site upgrade went live yesterday.

(by the way: the upgrade from XF 1.5 to XF 2.2 went absolutely smoothly, I am stunned, I knew the XenForo dev team were incredible but this? our database and code was hacked so much and so badly/poorly for the last 10 years, I never expected it to go so smooth. Seriously, props to you fellows)

We went for MariaDB 10.3 which is the latest stable on our repos, and PHP 7.4 for the same reasons -- with Nginx of course, behind CloudFlare. We have Elasticsearch installed and set up properly. All the tables are now in InnoDB except the ones that aren't supposed to be (like the session data one that's MEMORY).

The site is running amazingly fast, in spite of the size of our database.

This upgrade took us 8 months (and that's entirely our fault for making poor hacks and custom coded addons) but we finally did it and the new version works great.

Thanks again for all the advice and kudos to the amazing tech team at XenForo.

If you wanna check out our site it's https://gbatemp.net
We intend to submit it to the showcase -- after we work out the last details.

 

[Wildcat Media]

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

After having just had xf_session bork on us, leaving the entire forum dead for who knows how long (until a moderator text me about it 15 minutes ago), I'm at the point where I think it is only wise to change the MyISAM tables to InnoDB, no matter what performance hit the forum takes. My point is, what if I had been away from a computer (home, office, hotel room) for half of a day? We can't afford a time out like that.

MyISAM feels like a relic of the past, being failure prone with no way to automatically recover itself. I remember having crashed tables back when I ran phpBB back in the early days.

Anyhow, the tables are converted to InnoDB, and I can rest tonight...