Handling RDS for MySQL Long Transactions

Potential Impacts of Long Transactions

1. Long transactions lock resources and usually increase metadata locks and row locks. As a result, other transactions cannot access these resources, reducing the database concurrency.
2. Long transactions may occupy a large amount of memory.
3. Long transactions may cause too large log files and high storage usage.

Identifying Long Transactions

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

  After connecting to the DB instance, run the following command to view 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 Parameter description

  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 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 long transaction metric rds_long_transaction. If the metric increases linearly to a large value, there are long transactions.

Killing Long Transactions

1. Obtain the thread IDs corresponding to long transactions.

   Run the SQL statement 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 kill the transaction.

   mysql> kill trx_mysql_thread_id

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

Configuring Long 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 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 Transaction
      
   5. On the displayed page, set parameters as required. For details about the parameters, see Creating an Alarm Rule.