Mysql 5.7 -> 8.0 migration

[Max Shkutnyk]

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