Slow SQL Execution Due to Hot and Cold Data Problems

Scenario

When you migrate data from a self-built MySQL database or a peer vendor's MySQL database to an RDS for MySQL instance, the execution speed of the same SQL statement is much lower than that of the source database.

Possible Causes

The execution speed of a given SQL statement differs greatly when it is executed the first and second times. This is determined by the MySQL buffer pool mechanism.

• The first time the statement is executed, data is stored on the disk. This data is cold data. Reading cold data takes a certain period of time.
• The data you have queried is then cached in the buffer pool of the memory. It is called hot data and can be quickly accessed in the memory. When you execute the same statement for the second time, data is read from the buffer pool, which is much faster than reading data from a disk.

In this example, the data you queried in the source database is frequently accessed data. It is hot data. It can be read very quickly. After the data is migrated to the RDS for MySQL instance, when you execute the same SQL statement on the new database for the first time, the data you expect to query is probably cold data. This time, the access speed is slow. If you run the statement again, the data will become hot data again and the speed will improve significantly.

Solution

This issue is not an exception. Within a given database, it usually takes much more time to execute a statement for the first time, but when the statement is executed again later, it gets much faster. The access speed improves because the subsequent reads are reads of hot data from the buffer pool, which is much faster than reading cold data from a disk.