Problem description
The IOPS usage of an ApsaraDB RDS for MySQL instance is high.
Causes
A large amount of physical I/O is generated because the memory capacity of the RDS instance cannot meet the requirements of data caching or sorting.
The query efficiency is low because a large number of data rows are scanned.
Solutions
You can use one or two of the following methods to reduce the IOPS usage:
Generate a diagnosis report for the RDS instance. We recommend that you use this method.
Terminate abnormal queries.
View the diagnosis report of an RDS instance
Log on to the RDS instance in the Data Management (DMS) console.
Right-click the RDS instance and choose Performance > One-click diagnosis.
View the real-time sessions, deadlocks, and slow queries of the RDS instance in the Database Autonomy Service (DAS) console.
Click the details link of the required module to make optimization.
Terminate abnormal queries
You can query and terminate abnormal queries in the DMS console. We recommend that you terminate queries for which Physical_sync_read and Physical_async_read are set to large values. Large values of Physical_sync_read and Physical_async_read indicate high physical reads.
When the number of connections to the RDS instance reaches the upper limit, you cannot connect to the RDS instance by using DMS or the MySQL command-line tool.
If you cannot connect to the RDS instance by using DMS or the MySQL command-line tool, we recommend that you set the wait_timeout parameter to a small value in seconds, such as 60, in the ApsaraDB RDS console. If you set this parameter to 60, the RDS instance automatically closes the connections that are idle for more than 60 seconds. This way, new connections to the RDS instance can be established by using DMS or the MySQL command-line tool.
Terminate abnormal queries by terminating sessions of the RDS instance
If no diagnosis report is available or the SQL optimization suggestions cannot be immediately applied, you can terminate abnormal queries by terminating sessions of the RDS instance in the DMS console.
You must stop initiating abnormal queries. Otherwise, abnormal queries still persist.
Log on to the RDS instance in the Data Management (DMS) console.
Right-click the RDS instance and choose Performance > Instance session.
Select the session that you want to terminate and click Kill Selected.
Terminate abnormal queries by running commands
Connect to the RDS instance by using the MySQL command-line tool.
Identify the abnormal sessions.
Execute the
SHOW PROCESSLIST;statement.
If a large number of sessions are active, execute the
SHOW FULL PROCESSLIST;statement to query sessions.
Run the
kill [$ID]command to terminate the abnormal sessions. Set [$ID] to a value in the ID column.
Application scope
ApsaraDB RDS for MySQL