miniBB Support Forums | 16 years on The Web
miniBB ® 

miniBB

®
  
 | Begin | Register | Reply | Search | Statistics | File Bank | Manual |
The Other miniBB Support Forums / The Other /   
 

Converting Database tables from MyISAM to InnoDB

 
Author tom322
Active Member
#1 | Posted: 26 Nov 2018 07:33 
Hello, my current host strongly suggest to use InnoDB vs. MyISAM for DB (considering it uses MariaDB which is probably the most popular MySQL now). It should increase overall performance over MyISAM.

So I wanted to check it - I know how to change it in PhpMyAdmin, BUT I read this article: https://mariadb.com/kb/en/library/converting-tables-from-myisam-to-innodb/ which makes valid points about Database INDEX issues / recommendations. So just changing the tables is not enough to do a proper upgrade.

My question is - would miniBB be affected by these Indexes? How to optimize them on InnoDB table to fully take advantage of InnoDB?

Author Paul
Lead Developer
#2 | Posted: 26 Nov 2018 14:42 
tom322:
my current host strongly suggest to use InnoDB vs. MyISAM for DB

May be you need to start from this. Why did this recommendation came up and what for? Any issues discovered?

As far as I remember, InnoDB is good when there's a lot of insertions and new updates of records. For example, it would be good in banking, there are a lot of new transactions coming up each second, and not so much reading of DB. Instead, myISAM is good at reading, and forums are exactly about this. Your visitors read the forums much more than write messages.

The article seems to provide valid points for the upgrade. The most notable is a remark about FULLTEXT index missing in InnoDB. Some miniBB mods could be affected by this.

Also, if I remember properly, some tables in miniBB or its add-ons may not have a primary key. That could also be the issue.

But you actually need to start from the very first question in my post. InnoDB is just not the proper indexing system for forums, IMO. It won't bring any advantages in this case.

Author tom322
Active Member
#3 | Posted: 26 Nov 2018 16:19 
It came up when it turned out MySQL 5.6 is at 'end of life' - see: https://www.fromdual.com/support-for-mysql-from-oracle - then Cpanel / WHM recommends MariaDB 10.2 at this point, that's why I researched it..

Author tom322
Active Member
#4 | Posted: 26 Nov 2018 16:38 
Besides, it looks that myISAM is going to be depreciated too: https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/

Author tom322
Active Member
#5 | Posted: 26 Nov 2018 18:41 | Edited by: tom322 
I did a test - copied a DB and changed MyISAM to InnoDB on all tables. Then added a condition to read innoDB when accessing the site from my IP. From what I see, the site worked without any changes, even though I'm sure the Indexes are not optimized (I think innoDB automatically adds a Primary key or Index to the first NULL item, but it's probably not the fastest and most optimized way of doing it).

Author Paul
Lead Developer
#6 | Posted: 26 Nov 2018 18:46 | Edited by: Paul 
I wouldn't pay big attention to bloggers and unofficial news (specially 2-years old). On mysql.com I didn't find a note about myISAM being deprecated soon. I suppose it's too early to take such news seriously, because definitely there will be a lot of users which will be against ruining myISAM. Like the author of one of the comments in above article,

How terribly depressing that the fast, small, efficient MyISAM engine is going away, forcing us to replace it with its bloated successor, InnoDB, which eats memory and disk resources and belches out complex, hard-to-manage, hard to backup, hard to restore, hard to copy, hard to move, databases.

I'd rather agree to this. There are a lot of websites build on myISAM and their structure doesn't require anything more complicated which InnoDB provides. I'm mostly sure that if there will be no more lite and free mySQL community edition which supports myISAM, then this niche will be taken by some other product. Millions of small websites simply no not need difficult database. In this case Oracle risks to lose mySQL community as it happened with postgreSQL some time ago. PostgreSQL already was similarly bloated and see where it's now - almost forgotten.

Also let's not forget that mySQL is not just the only one tool we need for forums. The primary thing is PHP and all scripts actually rely on PHP-mySQL connectors. Whatever new is invented in mySQL it should be also supported in PHP, but now guess how many servers still support PHP 5.x because in PHP 7.x many functions were abandoned and that made older websites freezed? There are a lot of such servers. People just start dislike critical changes happening too often, preferring to stay with something stable.

So I doubt Oracle will abandon myISAM easily :) it will kill the primary goal of mySQL project. Then mostly everyone will just switch to another database.

Author Paul
Lead Developer
#7 | Posted: 26 Nov 2018 18:50 
tom322:
From what I see, the site worked without any changes

And what about the search?

I suppose you would note some difference only if you'd have the amount if records counted by millions -5, 10, 20 millions... For the smaller database you should note the difference only in the size of DB i.e. how many space it takes on the disk.

Author tom322
Active Member
#8 | Posted: 26 Nov 2018 18:55 
Searching seems to work too. Yes - total DB size went from about 130MB to 220MB.

Author Paul
Lead Developer
#9 | Posted: 26 Nov 2018 19:01 
It's still a very small database :) InnoDB is more for websites like Facebook, instant chats with thousands if not millions of users online, heavy reading and writing... enterprise database editions, cloud servers. Without all that myISAM is more suitable for an average hardware server with a simple architecture, cheap projects, what miniBB is actually created for, too.

Author tom322
Active Member
#10 | Posted: 28 Nov 2018 00:24 
The size change was surprising - what it would need the extra 30% for, it is holding text one way or the other. I see possibly the best way is to upgrade to MariaDB which supports MyISAM in new versions too.

Author Paul
Lead Developer
#11 | Posted: 28 Nov 2018 19:38 
tom322:
the best way is to upgrade to MariaDB which supports MyISAM in new versions too

Indeed it could be the best decision at this period of time... Seems MariaDB is supported by most of hosters nowadays and at this stage it tends to replicate the "good old known" mySQL, thus with an official support myISAM in the future... As you may know, miniBB software doesn't need anything bloated, and InnoDB is definitely not the best choice for forums, that's why MariaDB should work like previously mySQL worked for years. At least, this is my personal recommendation so far.

The Other miniBB Support Forums / The Other /
 Converting Database tables from MyISAM to InnoDB
 Share Topic's Link

Your Reply Click this icon to move up to the quoted message


 ?
Post as a Guest, leaving the Password field blank. You could also enter a Guest name, if it's not taken by a member yet. Sign-in and post at once, or just sign-in, bypassing the message's text.


Before posting, make sure your message is compliant with our forum posting rules. If not, it may be locked or deleted with no explanation.

 

 
miniBB Support Forums Powered by Forum Software miniBB ® Home  Features  Requirements  Demo  Download  Showcase  Gallery of Arts
Compiler  Premium Extensions  Premium Support  License  Contact Us
Check out the Captcha add-on: protect your miniBB-forums from the automated spam and flood.
 ⇑