High IOPS on an ApsaraDB RDS for MySQL instance typically means queries are reading data from disk instead of memory. Use the Database Autonomy Service (DAS) diagnosis report to identify the root cause, then terminate the sessions responsible.
Causes
IOPS spikes on RDS for MySQL trace back to one of two root causes:
Insufficient memory for caching or sorting: When the memory capacity of the RDS instance cannot meet the requirements of data caching or sorting, a large amount of physical I/O is generated.
Full table scans or low-efficiency queries: Queries that scan a large number of rows without using an index produce I/O proportional to the rows touched rather than the rows returned. Repeated sorts without an efficient execution plan compound this effect.
Reduce IOPS usage
Use either or both of the following methods.
Generate a diagnosis report (recommended)
The DAS one-click diagnosis report surfaces active sessions, deadlocks, and slow queries in a single view and provides optimization suggestions you can act on immediately.
Log in to the RDS instance in the Data Management (DMS) console.
Right-click the RDS instance and choose Performance > One-click diagnosis.
In the DAS console, review the real-time sessions, deadlocks, and slow queries.
Click the details link for the relevant module and apply the optimization suggestions.
Terminate abnormal sessions
If no diagnosis report is available or optimization changes cannot be applied immediately, terminate the sessions generating excessive physical reads. In the DAS session view, focus on sessions where Physical_sync_read or Physical_async_read shows a large value — both metrics indicate heavy physical disk reads.
Terminating sessions provides temporary relief. Stop the queries at the application level to prevent them from reconnecting and re-running.
Terminate sessions in DMS
Log in to the RDS instance in the Data Management (DMS) console.
Right-click the RDS instance and choose Performance > Instance session.
Select the session to terminate and click Kill Selected.
Terminate sessions from the MySQL command line
SHOW PROCESSLIST and SHOW FULL PROCESSLIST return a snapshot of active sessions at the moment you run the command. Run them multiple times to identify sessions that are consistently active or consuming the most resources.
Connect to the RDS instance using the MySQL command-line tool.
Identify abnormal sessions:
Run
SHOW PROCESSLIST;for a summary view.
If many sessions are active, run
SHOW FULL PROCESSLIST;to see the complete query text.
Terminate the abnormal sessions. Replace
[$ID]with the value from the ID column.kill [$ID]
If you cannot connect to the instance
When the number of connections reaches the upper limit, DMS and the MySQL command-line tool both fail to connect. Set the wait_timeout parameter to a small value — such as 60 — in the ApsaraDB RDS console. This causes the instance to automatically close connections that have been idle for more than 60 seconds, freeing up connection slots so you can log in and begin diagnosis.
Application scope
ApsaraDB RDS for MySQL