MyISAM to InnoDB

[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]

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.

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..

[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]

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]

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

[Alien_Time]

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.

[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.

[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.

[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...

[MattF]

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.