Mysql 5.7 -> 8.0 migration
Hey there
Recently we've been working on migration from mysql 5.7 to mysql 8.0 and after migration we found that performance for some endpoints has been degradaded.
For the context, we have a cluster with 1 writer and 1 read replica. Each customer has it's own database withing the database instance. And there's a separate database on the same instance that stores statistics for each customer. I understand that it might be not the optimal solution, just explain how things organized for better understanding.
In these endpoints we're using SQL views that aggregates data from multiple tables and multiple databases and there's a table with statistic information that are updated via table swap (using rename operation).
After some investigation we found that that in mysql8 has been introduced function called `update_referencing_views_metadata` which update meta information on views in case if referenced table is updated.
So in our case we have around 300 databases that reference statistics table and when we rename the table it takes ~50 seconds because it has to update information for 300 views.
In mysql 5.7 we didn't observe such behavior.
So I wonder if someone can explain or might know what's the reason for that method so it has been introduced and is there any workarounds, may be there're some mysql settings that manages that.
And another issue we've been observing is that mysql start using not optimal indexes on views and query takes much more time that it was in mysql 5.7. So for example instead of complex index is start using pk which doesn't perform so well as the complex index and we had to tune queryes y adding hints.
Thanks a lot
Subject
Views
Written By
Posted
Mysql 5.7 -> 8.0 migration
370
February 29, 2024 02:32AM
160
February 29, 2024 11:42PM
167
March 01, 2024 09:12AM
Sorry, only registered users may post in this forum.
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.