Handling RDS for MySQL Long-Running Transactions

What Is a Long-Running Transaction?

A long-running transaction refers to a transaction in which DDL or DML operations are being performed and are not committed for a long time. Long-running transactions may:

• Exhaust I/O resources.
• Occupy a large number of CPU resources.
• Occupy a large amount of memory.
• Cause table bloats.
• Lock resources and usually increase metadata locks and row locks. As a result, other transactions cannot access these resources, reducing the database concurrency.
• Increase the log size, which can quickly exhaust all available storage.

Identifying Long-Running Transactions

• Connect to your DB instance and check for long-running transactions and their session IDs.

  After connecting to the DB instance, run the following command to query the ID of any transaction that has been executing for more than 3,000s, the executed SQL statement, and the corresponding session ID.

  mysql> SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_modified FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(),trx_started)) >3000;

  Table 1 Parameters

  Parameter	Description
  trx_id	Transaction ID.
  trx_state	Transaction status, which can be RUNNING, LOCK WAIT, or ROLLING BACK.
  trx_started	Time when the transaction was started.
  trx_mysql_thread_id	ID of the MySQL session to which the transaction belongs.
  trx_query	SQL statement executed by the transaction.
  trx_rows_modified	Number of rows modified by the transaction.

• Check monitoring metrics for long-running transactions.
  1. Log in to the management console.
  2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
  3. On the Instances page, locate the target DB instance and click View Metrics in the Operation column.
  4. Check the metric rds_long_transaction. A sustained linear increase in this metric, especially over an extended duration, indicates the presence of long-running transactions.

Terminating Long-Running Transactions

1. Obtain the thread IDs corresponding to long-running transactions.

   Run the SQL statement used in Connect to your DB instance to check long transactions and their session IDs to obtain the session ID of the transaction whose execution time exceeds a certain period (for example, 3,000s).

   mysql> SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(),trx_started)) >3000;

2. After obtaining the session ID, run the kill command to terminate that transaction.

   mysql> kill trx_mysql_thread_id

Terminating a long-running transaction will cause the transaction to roll back. Evaluate the potential impact before running this command.

Configuring Long-Running Transaction Alarms

1. View the configured alarms.
   1. Log in to the management console.
   2. Click in the upper left corner of the page and choose Management & Governance > Cloud Eye.
   3. Choose Alarm Management > Alarm Rules.
      Figure 1 Viewing alarm rules
      
2. Configure long-running transaction alarms.
   1. Click in the upper left corner of the page and choose Databases > Relational Database Service.
   2. On the Instances page, locate the target DB instance and click View Metrics in the Operation column.
   3. View the Long Transaction metric.
      Figure 2 Viewing metrics
      
   4. Click + in the upper right corner of the Long Transaction metric.
      Figure 3 Long-running transaction metric
      
   5. On the displayed page, set parameters as required. For details about the parameters, see Creating an Alarm Rule and Notifications.