Storage Space Occupied by Undo Logs Due to Long Transactions

Scenario

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

Possible Causes

• According to the multiversion concurrency control (MVCC) mechanism, 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 a long transaction exists 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 is not committed for a long period of time:

  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 transaction.
• Do not execute long transactions or insert a large amount of data when the storage space is insufficient.
• Scale up storage space in advance.