Scenario Description
After data is migrated to a MySQL instance, the SQL statement execution is much slower than that of the original database.
Possible Causes
After data is migrated to a MySQL instance, the SQL statement execution performance for the first time is worse than that of the original database, which is determined by the buffer_pool mechanism of MySQL. When the SQL statement is executed for the first time, information such as the execution plan and query data is not loaded on the buffer_pool, and execution plans need to be generated. In addition, data needs to be loaded from the disk. The read and write performance of disk I/O is significantly worse than that of memory buffer_pool. As a result, the first execution speed is much slower. After the first execution of the SQL statement, the read data is stored in the buffer_pool of memory. When the same statement is executed for the second time, the data is read directly from buffer_pool. As a result, the data access speed is much faster than the first time from the disk.
In this scenario, the data that executes SQL reads in the original database is generally from buffer_pool, so the access speed is extremely fast. When the data is migrated to MySQL on the cloud, when the same SQL statement is executed for the first time, it needs to be read from disk, resulting in slower access. When the same SQL is executed again, the SQL statement is read from memory, so the execution speed will return to normal.
Solution
This issue is not an exception. In the same database, the first execution of the SQL statement is slow. When the statement is executed again later, the data is loaded to buffer_pool, resulting in a much faster execution speed.