MyISAM to InnoDB

General discussion of development ideas and the approaches taken in the 3.x branch of phpBB. The next feature release of phpBB 3 will be 3.3/Proteus.
Forum rules
Please do not post support questions regarding installing, updating, or upgrading phpBB 3.2.x. If you need support for phpBB 3.2.x please visit the 3.2.x Support Forum on phpbb.com.

If you have questions regarding writing extensions please post in Extension Writers Discussion to receive proper guidance from our staff and community.
mrgtb
Registered User
Posts: 221
Joined: Wed Nov 28, 2007 10:09 pm

Re: MyISAM to InnoDB

Post by mrgtb »

You are more likely to get a faster response posted it on phpBB.com here: https://www.phpbb.com/community/viewforum.php?f=466

Rather than on Area51 forums

Alien_Time
Registered User
Posts: 165
Joined: Fri Apr 05, 2013 3:38 am

Re: MyISAM to InnoDB

Post by Alien_Time »

brunoais wrote:AFAIK, phpBB does not use FullText search nor spartial indexes so it is ready for InnoDB. It just does not use InnoDB to its fullest such as foreign keys which would be its greatest gain in performance.
That is great to hear. Then with 3.1, would you guys consider adding phpbb default engine as InnoDB then? So when user installs 3.1 or converts from 3.0.12, their database storage engine for phpbb tables are updated to InnoDb? I think this should probably be a simple update to table DDL. This also shows that phpbb 3.1 is moving towards InnoDB. In the future whenever you guys get time after 3.1 release, you can slowly start integrating other features like foreign keys and transaction to take full advantage of Innodb. This will not interfere with the release of 3.1 neither and the change will be for good too.

I feel that changing it to InnoDB will have immediate effect to the UPDATE, INSERT, DELETE operations and will improve a lot for read heavy sites too. The biggest benefit of all is InnoDB does not do table-lock like MyIsam. It only uses row-lock. This will improve the performance dramatically for busy forums too since table-locking that MyIsam uses holds the users that needs access to those tables until the previous one is completed. So overall adding InnoDB as the default engine to phpbb will have immediate effect I think. Especially when it doesnt use FullText search, I dont see a real gain of the tables have MyIsam type.
mrgtb wrote:You are more likely to get a faster response posted it on phpBB.com here: https://www.phpbb.com/community/viewforum.php?f=466

Rather than on Area51 forums
I thought of that too but in the end this topic seemed to be too technical for phpbb support forum. Hence I posted it here since it is mostly targeted to the developers..

User avatar
brunoais
Registered User
Posts: 964
Joined: Fri Dec 18, 2009 3:55 pm

Re: MyISAM to InnoDB

Post by brunoais »

I'm not phpBB staff and I do not represent staff. I'm just telling my opinion, intention and knowledge. phpBB is not moving to InnoDB before DBAL does not support it. That's the main issue.

Alien_Time
Registered User
Posts: 165
Joined: Fri Apr 05, 2013 3:38 am

Re: MyISAM to InnoDB

Post by Alien_Time »

But if DBAL doesn't use anything specific features that is only available in MyIsam, shouldn't it technically support Innodb already? It might not be ready to use the additional power this storage engine provides but that would not necessary mean DBAL doesn't support it. There is a high chance that phpbb is already ready to use Innodb instead of MyIsam as off the current state. Do correct me if I am wrong :)

Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 367
Joined: Thu Sep 16, 2004 9:02 am
Contact:

Re: MyISAM to InnoDB

Post by Paul »

As far I know we use the default engine when installing, and dont provide a engine when creating the tables.

Alien_Time
Registered User
Posts: 165
Joined: Fri Apr 05, 2013 3:38 am

Re: MyISAM to InnoDB

Post by Alien_Time »

paulus wrote:As far I know we use the default engine when installing, and dont provide a engine when creating the tables.
That's what I thought too since when I installed 3.1 it was showing Innodb for all phpbb tables. If no storage engine is specified then the default engine will be chosen which is based on the server environment. Since phpbb seems to work on both engine, then leaving it to default should be fine. I am happy with that since I now know phpbb doesn't specifically use any feature that only MyIsam has, so it won't limit phpbb if it was changed to Innodb. When phpbb starts using Innodb features, then it would be necessary to specify the storage engine in that case. This will only happen in the future...

Thanks everyone for clearing this for me.. I can now configure my server accordingly.

User avatar
brunoais
Registered User
Posts: 964
Joined: Fri Dec 18, 2009 3:55 pm

Re: MyISAM to InnoDB

Post by brunoais »

AFAIK, if you try to add foreign keys to MyISAM it will not throw any errors, just warnings. So it is not an issue if the implementation doesn't include InnoDB. Besides, some specific webservers (for some crazy idea) deactivated InnoDB in MySQL. So it must be able to work if InnoDB is disabled. It shouldn't be a problem anyway but the DBAL still requires supporting foreign keys.
Last edited by brunoais on Tue Oct 21, 2014 1:11 pm, edited 2 times in total.

Alien_Time
Registered User
Posts: 165
Joined: Fri Apr 05, 2013 3:38 am

Re: MyISAM to InnoDB

Post by Alien_Time »

I just found out 1 more thing. As of mysql 5.6 Innodb now also supports fulltext search index and apparently it does it even better than MyIsam. So if you have Innodb then you have no reason not to use it. This is great..

About the reason why some webservers deactivate Innodb is because it's heavy on ram and that's what makes it crazy fast. The hosts that oversells like shared hosts doesn't want users to use much ram due to their greed and disables them. If you are hosting your own server, the you can do wonders with it.

User avatar
brunoais
Registered User
Posts: 964
Joined: Fri Dec 18, 2009 3:55 pm

Re: MyISAM to InnoDB

Post by brunoais »

That looks good but most web servers are still stuck in MySQL 5.1 and such due to "compatibility issues" as they like to call. So there's no way we can assume that they keep it updated...

User avatar
VSE
Extension Customisations
Extension Customisations
Posts: 673
Joined: Mon Mar 08, 2010 9:18 am

Re: MyISAM to InnoDB

Post by VSE »

MyISAM and InnoDB are storage engines set by your web host's MySQL config. phpBB can not choose one or the other, and does not prefer one or the other. It's determined by your web host, or yourself if you can manage your MySQL server (like in phpMyAdmin).

Any version of phpBB will work with either engine, and has for years.
Has an irascible disposition.

Post Reply