Slow SQL Execution Due to Hot and Cold Data Problems

Scenario

When you migrate data from a self-managed MySQL database to a GaussDB(for MySQL) DB instance on the cloud, the execution speed of the same SQL statement is much lower than that of the source database.

Possible Causes

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

• When the statement is executed for the first time, data is stored on the disk, which is called 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 disks.

In this troubleshooting case, the data you queried in the source database is frequently accessed data, that is, hot data. Thus, it can be read at a high speed. After the data is migrated to the GaussDB(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 access speed will greatly improve.

Solution

This issue is not an exception. In the same database, it usually takes much time to execute a statement for the first time, but when the statement is executed again, it gets much faster. The access speed improves because reading hot data from the buffer pool is much faster than reading cold data from disks.