Troubleshooting Slow SQL Issues for RDS for MySQL DB Instances

There are many causes for SQL exceptions, for example, unsuitable database table structure design, missing indexes, or too many rows that need to be scanned.

On the slow query logs page of the management console, you can download slow query logs to identify the slow SQL statements and see how long they took to execute. For details, see Viewing and Downloading Slow Query Logs.

Optimize the SQL statements that you need to execute.

DB instance performance can be limited because:

• Your workloads have been increasing but the storage has not been scaled up accordingly.
• The performance of your DB instance has been deteriorating as the physical server of the instance ages.
• The amount of data has been increasing, and the data structure has been changing.

You can view the resource usage of the DB instance on the console. If the values of all resource usage metrics are close to 100%, your DB instance may reach its maximum performance. For details, see Viewing Performance Metrics of a DB Instance.

Upgrade the instance class. For details, see Changing a DB Instance Class.

Upgrading your DB instance may change the SQL execution plan. The join types determined in the execution plan are, in descending order of efficiency:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

For more information, see official MySQL documentation.

If your application frequently resends query requests that specify range and index joins but RDS processes these query requests slowly, a number of SQL statements are parallelized. In this case, your application is slow to release threads. As a result, the connections in the connection pool get depleted, affecting all the workloads on your DB instance.

You can log in to the console to see how many current connections your DB instance has established. For details, see Viewing Performance Metrics of a DB Instance.

Analyze the index usage and the number of rows to scan, estimate the query efficiency, reconstruct SQL statements, and adjust indexes. For details, see Executing SQL Plan.

Inappropriate settings of some parameters (such as innodb_spin_wait_delay) can impact performance.

You can view parameter modifications on the console. For details, see Viewing Parameter Change History of a DB Instance.

Modify related parameters based on your specific service scenario. For details, see Suggestions on RDS for MySQL Parameter Tuning.

A large number of operations are performed to import, delete, and query data.

You can view Total Storage Space, Storage Space Usage, and IOPS on the console. For details, see Viewing Performance Metrics of a DB Instance.

Perform batch operations during off-peak hours, or split them.

If the load of your DB instance changes regularly over time, there may be scheduled tasks causing this.

You can view DELETE Statements per Second, INSERT Statements per Second, INSERT_SELECT Statements per Second, REPLACE Statements per Second, REPLACE_SELECTION Statements per Second, SELECT Statements per Second, and UPDATE Statements per Second on the console to determine whether the load has been changing regularly. For details, see Viewing Monitoring Metrics.

Adjust the time when scheduled tasks are run. You are advised to run scheduled tasks during off-peak hours and change the maintenance window to off-peak hours. For details, see Changing the Maintenance Window.