Updated on 2026-09-15 GMT+08:00

Large Transaction Diagnosis

Scenarios

Large transactions affect the health and stability of DB instances. For example, lengthy rollbacks of large transactions often prolong upgrade and specification change processes. TaurusDB provides diagnosis for large transactions. When there is a large transaction, an alarm is generated to notify you to commit the transaction in a timely manner.

Prerequisites

The kernel version must be 2.0.39.230300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
The related parameter is configured based on the following conditions:
- If the kernel version is earlier than 2.0.45.230900, set the value of log-bin to ON.
- If the kernel version is 2.0.45.230900 or later, set the value of rds_global_sql_log_bin to ON.

How to Use

You can use system variables or run SQL statements to check whether there are large transactions.

Diagnosing Large Transactions Using System Variables

Configure the parameter rds_warn_max_binlog_cache_size as required.

**Table 1** Parameter description
Parameter	Level	Description
rds_warn_max_binlog_cache_size	global	Controls the maximum binlog cache size for a transaction. If the size in a transaction exceeds the parameter value, a WARNING message is reported. Default value: 18446744073709547520 Value range: 4096 to 18446744073709547520

To prevent multiple WARNING messages from being sent to the client, a WARNING message can be sent to the client once for each statement in a transaction.

In this example, rds_warn_max_binlog_cache_size is set to 40960 (40 KB).

CREATE TABLE t1 (
    -> a longtext
    -> ) DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql> show variables like 'rds_warn_max_binlog_cache_size';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| rds_warn_max_binlog_cache_size | 40960 |
+--------------------------------+-------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                             |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 4008 | Recommend you to INSERT/UPDATE/DELETE rows in batches by multiple transactions. The current transaction required more than 'rds_warn_max_binlog_cache_size' (40960) bytes of storage. Which shall cause replication latency. Please commit it. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

mysql> INSERT INTO t1 VALUES (REPEAT('a',50000));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message           |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 4008 | Recommend you to INSERT/UPDATE/DELETE rows in batches by multiple transactions. The current transaction required more than 'rds_warn_max_binlog_cache_size' (40960) bytes of storage. Which shall cause replication latency. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Diagnosing Large Transactions by Running SQL Statements

Check the binlog cache size of the transactions in the current connection.

mysql> CREATE TABLE t1 (
->   a longtext
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Rds_binlog_trx_cache_size | 0     |
+---------------------------+-------+
1 row in set (0.04 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected (0.01 sec)

mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Rds_binlog_trx_cache_size | 20150 |
+---------------------------+-------+
1 row in set (0.05 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Rds_binlog_trx_cache_size | 0     |
+---------------------------+-------+
1 row in set (0.09 sec)

Check the binlog cache size of transactions in all connections.

mysql> SHOW GLOBAL STATUS LIKE 'rds_binlog_trx_cache_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Rds_binlog_trx_cache_size | 40300 |
+---------------------------+-------+
1 row in set (0.05 sec)

Parent Topic: Transaction Optimization