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.
User avatar
brunoais
Registered User
Posts: 964
Joined: Fri Dec 18, 2009 3:55 pm

Re: MyISAM to InnoDB

Post by brunoais » Tue Oct 21, 2014 12:19 pm

@VSE, When the table is created, you can mention which storage engine you want to use, if it doesn't exist, it uses the default one instead.
If a storage engine is specified that is not available, MySQL uses the default engine instead.
BTW,
For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

Marshalrusty
Project Manager
Project Manager
Posts: 272
Joined: Thu Oct 27, 2005 1:45 am

Re: MyISAM to InnoDB

Post by Marshalrusty » Tue Oct 21, 2014 11:43 pm

phpBB can force the database engine, but it's not its place to do so. Either the administrator knows what they are doing and has intentionally specified the correct engine to use, or they have no idea what they're doing, in which case the logical thing to do is trust the server administrator, who hopefully had set the correct engine for the setup. Either way, it seems to me, we should not be forcing this one way or the other.

Naturally, it is not possible to swap from one engine to the other on the fly (such as during an update) as doing so requires an entire rebuild of the data storage containers, which can take minutes (on a small board) to many hours (or a large one).

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

Re: MyISAM to InnoDB

Post by brunoais » Wed Oct 22, 2014 10:43 am

Is it OK, then, to add forign key definitions support to DBAL? In MySQL's case, if the selected default DB engine is InnoDB, then phpBB could have optimized DB speeds for the multiple JOIN's it has.
Currently, phpBB doesn't use that so JOIN's are way slower than how they could be.
We can check on foreign key support on case-by-case basis for other DBMS.

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1826
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: MyISAM to InnoDB

Post by DavidIQ » Wed Oct 22, 2014 1:43 pm

You'll need to provide some numbers in order to make a decision like that as adding foreign keys will break many things. Even if we decided on doing that I don't think it would/should be done in 3.2.
Image

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

Re: MyISAM to InnoDB

Post by Alien_Time » Wed Oct 22, 2014 3:18 pm

brunoais wrote:In MySQL's case, if the selected default DB engine is InnoDB, then phpBB could have optimized DB speeds for the multiple JOIN's it has.
Currently, phpBB doesn't use that so JOIN's are way slower than how they could be.
We can check on foreign key support on case-by-case basis for other DBMS.
That is exactly right... There is a reason Innodb (or other similar SE) came into existence and currently used as the default engine because of the limitation & performance on MyIsam. I know now might not be the time for phpbb to specifically use Innodb, but this is something that needs to be adapted in the future. Probably by the time 3.2 comes out, it should not be an issue for phpbb to use Innodb for the latest version since most users should be having the later version of Mysql (or would need to upgrade to a later version) to benefit from additional benefits it has to offer.

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

Re: MyISAM to InnoDB

Post by brunoais » Wed Oct 22, 2014 5:05 pm

DavidIQ wrote:You'll need to provide some numbers in order to make a decision like that as adding foreign keys will break many things. Even if we decided on doing that I don't think it would/should be done in 3.2.
What would break by using foreign keys?

User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 1826
Joined: Thu Mar 02, 2006 4:29 pm
Location: Earth
Contact:

Re: MyISAM to InnoDB

Post by DavidIQ » Wed Oct 22, 2014 6:21 pm

You would get errors when inserting, updating, deleting of certain data since the foreign key would mean that the table is dependent on another table. Right now it doesn't mater so there's no problem. Once you add a foreign key constraint you can get errors such as "Violation of foreign key constraint" when performing inserts or deletes. If the logic is not taking foreign keys into account then errors are going to happen (think about the queries that get processed when splitting a topic or moving a topic or deleting a user, etc.). Even though possible errors on insert wouldn't be avoidable the delete errors could be avoided with cascading deletes but not sure how that works in MySQL.

Given that we have a number of indexes I'm not sure how much of an improvement in performance there would be, if any, by adding foreign key constraints, which is why I said that you should probably produce some statistics showing how much of a difference there is between having foreign keys and not.
Image

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

Re: MyISAM to InnoDB

Post by brunoais » Wed Oct 22, 2014 10:16 pm

We just use the "ON DELETE SET NULL ON UPDATE CASCADE" and that shouldn't bother. Anyway, we have testes for all of those, right? Ultimately, it will only actually improve performance and errors are, most probably too rare.

If cascading deletes is used, it is quite direct, actually:
Considering three rows A, B, C. They are all in different tables. They are related as A has foreign key referencing B, B has foreign key referencing C.
If C is deleted, C, B and A are deleted. If B is deleted, B and A is deleted, if A is deleted A is deleted.
Foreign keys are 1 to many.

Do you have any good large example large database data I can use for testing? It would be useful to build some benchmarks on that instead of inserting random data directly into my personal DB. Benchmarks are only useful with really crowded DB's, not very small ones like the one I have for testing.

Marshalrusty
Project Manager
Project Manager
Posts: 272
Joined: Thu Oct 27, 2005 1:45 am

Re: MyISAM to InnoDB

Post by Marshalrusty » Thu Oct 23, 2014 8:13 pm

Oh, errors won't be rare at all. When I ran a few scripts on the phpBB.com a few years ago, I found lots of stranded posts (not attached to any topic) and topics (not attached to any forum). Then there were a few users without a group. Most of these were very old and carried over from phpBB 1 or early phpBB 2 days, but there were more recent ones as well. All of these inconsistencies don't currently cause any problems, but adding foreign keys would have generated errors. So it's not a small task to implement, and would be a massive undertaking for support, when they start causing problems for people who have no idea what they are.

Which brings me to my main point: where did you get the idea that adding foreign keys improves performance? It definitely slows down writes, and I cannot find any information about optimizations (and their level of effect) for reads. So let's go back to the part where you justify why this is even a good idea to consider.

User avatar
JoshyPHP
Registered User
Posts: 366
Joined: Fri Jul 08, 2011 9:43 pm

Re: MyISAM to InnoDB

Post by JoshyPHP » Thu Oct 23, 2014 8:49 pm

brunoais wrote:We just use the "ON DELETE SET NULL ON UPDATE CASCADE" and that shouldn't bother.
Can you even do that if the column is set as NOT NULL, which most of them are?
Marshalrusty wrote:Which brings me to my main point: where did you get the idea that adding foreign keys improves performance?
I've seen this myth being perpetuated online for as long as I can remember. In my experience, people can rarely trace back how that idea came to them. My hypothesis is that some people found better performance after adding foreign keys in databases that had no index. Foreign keys usually create or require a matching index, therefore adding foreign keys to a table that had no secondary index would sometimes have the same benefits than adding the right indexes, or close to.

Post Reply