A migration is similar to an upgrade, in that it typically happens on a less sensitive timeframe, but also in that as well as bringing great new features; there is a higher chance there may be performance or functionality regressions.
When we talk about performance in a very broad sense, with users interacting with databases, they tend to feel the variance and not the mean. Let me explain this with an example:
Your application consists of 20 queries and 15 of those 20 actually have a performance improvement after the migration. On average, and on aggregate performance is better!
But that might not be good enough, since 2 of those queries now perform far worse!
Can you see the dilemma?
Those two queries were a critical path for a set of users and they are now intolerably worse. Telling them that most users will see a performance improvement is more likely to start a fight than give them any reassurance.
So as part of your upgrade: you want to try and find a way to identify those two queries. What are they? Can they be improved with the use of indexes? If we can’t use indexes, do we need to use an optimizer hint to force a particular execution plan?
You get the picture. What would be really helpful is if there was an automated tool to detect differences between two systems. And there are such tools!
Because TiDB speaks the MySQL protocol, another side benefit is you benefit from the tools ecosystem surrounding the MySQL Server. So for example, you can use
ProxySQL to mirror queries between a MySQL system and a
TiDB system, or
pt-upgrade to replay a slow query log and compare both results and timing between two systems.
I will link to both tools then in the resources section for this video.