MySQL Forums
Forum List  »  Newbie

MySQL 5.7- vs 8.0 referenced table update performance difference
Posted by: Real Deal
Date: November 21, 2022 09:11PM

Hello everyone,

I recently upgraded my MySQL server from 5.1 > 5.7 > 8.0. The server has over 3k databases and more than 1TB of data. All tables are InnoDB.
After upgrading, I noticed huge performance difference while updating a field on a table that is referenced by 3k tables on different databases.
I do not want to discuss the design or anything other than the difference between how MySQL used to handle these updates on versions lower than 8.0.

Before upgrading to the version 8.0, on both version 5.1 and 5.7, the update used to take less than 0.5 seconds (half of a second).
After upgrading to 8.0, the same update takes more than 50 seconds (fifty seconds).
I can speedup the update back to less than 0.5 seconds if I set the foreign_key_checks = 0, but I feel like this is not the right thing to do.

Last, the column being updated is not the primary key being referenced or any other index or referenced column. The field is just a tinyint(1) column on the table. In this case, I expected MySQL to not go over all the 3k referenced tables, as it seems to be doing on the older versions.

So, is there a more technical explanation for the abysmal difference? I mean, the difference on how MySQL 8.0 is implementing this update?

Options: ReplyQuote

Written By
MySQL 5.7- vs 8.0 referenced table update performance difference
November 21, 2022 09:11PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.