Storage Filled Up by Undo Logs Due to Long Transactions

Scenario

An instance triggered an alarm reporting full storage. After a period of time, the alarm was automatically cleared.

Possible Causes

• Because Multi-Version Concurrency Control (MVCC) is used, RDS generates undo logs when you update data in database tables and stores the undo logs in your storage. Only after the transactions of all your sessions are committed or rolled back, the undo logs can be removed from the storage.
• If there is a long-running transaction in one of the sessions, the undo logs generated for the table update sessions cannot be cleared as long as the long transaction is not committed. As a result, the storage usage keeps increasing.

Troubleshooting:

• Run the following statement to check whether any transaction has been running for a long period of time and is still not committed:

  select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G;

• View audit logs or slow query logs to check whether a large amount of data is inserted at a time by a large transaction.

Solution

• Kill the long-running transaction.
• Do not execute long-running transactions or insert a large amount of data when the storage is limited.
• Scale up storage space in advance.