[Forum] MyIsam VS InnoDB

[neogeo]

I read a lot of threads/discussions about convert MyIsam to Innodb and as there are many contradictory opinions... I decided create this thread to ask directly to Vbulletin.com

In my particular case my forum works well with:

vBulletin 4.2.5
Php-fpm 7.1
MariaDB 10.3 (MyIsam)
+20 Million Messages

I would like to ask to vB support/experts if I can stay with MyIsam or whould convert it to InnoDB?

Regards

[Wayne Luke]

You should convert to INNODB. MyISAM is not suitable for high volume database use.

Part 1 vB4mance: Helping communities...nges in vB 4.0
Part 2 vB4mance: vBulletin4 Optimiza...oDB Conversion
vB4Mance Part 3: Database performanc...DB / 5.5 / 5.1
vB4Mance Part 4: The Basics - Unders...pt and Hosting
vB4Mance Part 5: Expert Level - Boos...vanced Caching

Most of the work in these blogs is default in vBulletin 5 and we have provided scripts to handle the conversion of upgraded databases to INNODB with UTF8MB4 support.

[neogeo]

You should convert to INNODB. MyISAM is not suitable for high volume database use.

Part 1 vB4mance: Helping communities...nges in vB 4.0
Part 2 vB4mance: vBulletin4 Optimiza...oDB Conversion
vB4Mance Part 3: Database performanc...DB / 5.5 / 5.1
vB4Mance Part 4: The Basics - Unders...pt and Hosting
vB4Mance Part 5: Expert Level - Boos...vanced Caching

Most of the work in these blogs is default in vBulletin 5 and we have provided scripts to handle the conversion of upgraded databases to INNODB with UTF8MB4 support.

I'm using MariaDB's version which is equivalent to MySQL 5.7+

I converted most vB4.2.5 tables to InnoDB without problems and the site functions well.

For other hand I have theses tables in MyISAM

language
phrase
searchcore_text
searchgroup_text
userfield
usertextfield


I did a fast test (dry run) with percona pt-online-scheme and it says that these tables can be converted correctly, but I'm doubting (too much different opinions in the forum)... so first of all I would like to ask you if you has any experience/suggestion converting these tables to InnoDB with mysql +5.7

Thanks!

[Wayne Luke]

1. vBulletin has the minimum required MySQL version of 5.6.10 at this time. Fulltext does not work with INNODB on that version of MySQL. All recommendations and documentation that we provide should be based on this minimum required version.
2. MySQL 5.7 did not exist while vBulletin 4.X was under active development. It was released in 2013 when we were in the process of releasing vBulletin 5.
3. MySQL 5.7+ supports fulltext indexes on INNODB but I have read it is recommended to wait until 8+. I don't recall where and that recommendation may have changed with newer releases of MySQL 5.7. The software was released over a decade ago and none of the support staff are database administrators. We don't support MySQL, we support vBulletin.
4. vBulletin hasn't been tested with Fulltext on INNODB. So it has been recommended not to use it. If, and when, it is tested, we will not be testing vBulletin 3 or 4 as they are end of life.

That said, if you find no problems with using INNODB as the engine for those tables on your database, then you're welcome to do so. Just don't be surprised if you have issues and the recommendation is convert them to MyISAM.

[neogeo]

1. vBulletin has the minimum required MySQL version of 5.6.10 at this time. Fulltext does not work with INNODB on that version of MySQL. All recommendations and documentation that we provide should be based on this minimum required version.
2. MySQL 5.7 did not exist while vBulletin 4.X was under active development. It was released in 2013 when we were in the process of releasing vBulletin 5.
3. MySQL 5.7+ supports fulltext indexes on INNODB but I have read it is recommended to wait until 8+. I don't recall where and that recommendation may have changed with newer releases of MySQL 5.7. The software was released over a decade ago and none of the support staff are database administrators. We don't support MySQL, we support vBulletin.
4. vBulletin hasn't been tested with Fulltext on INNODB. So it has been recommended not to use it. If, and when, it is tested, we will not be testing vBulletin 3 or 4 as they are end of life.

That said, if you find no problems with using INNODB as the engine for those tables on your database, then you're welcome to do so. Just don't be surprised if you have issues and the recommendation is convert them to MyISAM.

Thank you very much for you explanation.